Weekly time lags

Question

suppose to have the following dataset:

data dataset;
  input week_number Var1 Var2;
cards;
1  55  98
2  34  34
3  21  0
4  76  12
5  98  0
.. ... ..
52 21  43
;run;

what is the best way to calculate and add the following: lag of -1 week; lag of +1 week; lag of -2 weeks; lag of +2 weeks to the original dataset?

Thank you in advance

Answer 1

Using proc sql you can join the data with itself multiple times and control which weeks appear in each row with the join conditions:

proc sql;
    create table want as
    select have.*
            ,lag1.Var1 as Var1_lag1
            ,lag1.Var2 as Var2_lag1
            ,lag2.Var1 as Var1_lag2
            ,lag2.Var2 as Var2_lag2
            ,lead1.Var1 as Var1_lead1
            ,lead1.Var2 as Var2_lead1
            ,lead2.Var1 as Var1_lead2
            ,lead2.Var2 as Var2_lead2
    from dataset have left join dataset lag1 on lag1.Week_number = have.week_number - 1
                    left join dataset lag2 on lag2.week_number = have.week_number - 2
                    left join dataset lead1 on lead1.week_number = have.week_number + 1
                    left join dataset lead2 on lead2.week_number = have.week_number + 2
    ;
quit;

Variables with _lag1 have values from one week behind, _lag2 2 weeks behind, _lead1 1 week ahead and _lead2 2 weeks ahead.