A B C D E F
Q1 2000 1.2 11.8 4.0 14296.2 101.6
Q2 2000 7.8 6.1 3.9 13618.5 104.5
Q3 2000 0.5 7.4 4.0 13613.3 106.9
Q4 2000 2.1 3.6 3.9 12175.9 109.5
I want to calculate the Lag value of the Variable(column) B,C,D... in Teradata
The lag value of Q1 for each variable(B,C,D..F) should be 0
The Lag value for Q2 : lag1Q_B = 1.2 ,lag1Q_C = 11.8 and so on.
The lag value for Q3 : lag1Q_B= 7.8 , lag2Q_B =1.2 , lag1Q_C=6.1 ,lag2Q_C=11.8
The equivalent SAS code for time series calculation is :
convert B = lag1Q_B / transformout = (lag 1);
convert C = lag1Q_C / transformout = (lag 1);
convert B = lag2Q_B / transformout = (lag 2);
convert C = lag2Q_C / transformout = (lag 2);
convert B = lag3Q_B / transformout = (lag 3);
convert C = lag3Q_C / transformout = (lag 3);
Please provide the equivalent SQL in Teradata.
Use a "window aggregate function" with ORDER BY and ROWS clauses (and optionally PARTITION BY). Unlike SAS, there is no intrinsic order of rows; you must explicitly say how the rows are to be logically ordered.
SELECT COALESCE(MIN(B) OVER (ORDER BY SUBSTRING(A FROM 3 FOR 4), SUBSTRING(A FROM 2 FOR 1)
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),0.0) AS LAG1Q_B,
Note that MIN is only to make syntax acceptable; MAX or SUM would also work since we are limiting the "window" to a single row. When there is no row, the function returns NULL and COALESCE supplies a default.