Combined do loop and lag function?

[Laasse]

I have a problem where I want to lag the columns in a data set one additional period per column. My original data set looks like:

Period                NP0                     NP1                     NP2                     NP3

2010-03-31       320                      316                      305                      297

2010-04-30       2857                    2763                    2655                    2589

2010-05-31       783                      743                      719                      689

2010-06-30       1014                    989                      945                      909

2010-07-31       792                      757                      722                      697

2010-08-31       626                      600                      557                      535

2010-09-30       858                      824                      781                      748

2010-10-31       1027                    986                      933                      893

2010-11-30       907                      865                      820                      791

2010-12-31       861                      806                      751                      726

I want it to look like this:

Period                NP0                     NP1                     NP2                     NP3

2010-03-31       320                                                                               

2010-04-30       2857                    316                                                  

2010-05-31       783                      2763                    305                     

2010-06-30       1014                    743                      2655                    297

2010-07-31       792                      989                      719                      2589

2010-08-31       626                      757                      945                      689

2010-09-30       858                      600                      722                      909

2010-10-31       1027                    824                      557                      697

2010-11-30       907                      986                      781                      535

2010-12-31       861                      865                      933                      748

Of course it is easy to lag a few columns, but this is just a limited example. I have several data sets like this with columns up to NP136. Is there any way to combine a do loop with the lag function in this sense?

[Loko · Accepted solution]

Hello,

Something like this:

data have;
input Period $10. NP0 NP1 NP2 NP3;
datalines;
2010-03-31 320    316    305    297
2010-04-30 2857        2763        2655        2589
2010-05-31 783    743    719    689
2010-06-30 1014        989    945    909
2010-07-31 792    757    722    697
2010-08-31 626    600    557    535
2010-09-30 858    824    781    748
2010-10-31 1027        986    933    893
2010-11-30 907    865    820    791
2010-12-31 861    806    751    726
;

%macro func (table);

proc sql noprint;
select count(name) into :numvar from sashelp.vcolumn where memname=upcase("&table") and name like 'NP%';
quit;

data want;
set have (keep=Period NP0) ;
%do i=1 %to %eval(&numvar-1);
if _N_>=%eval(&i+1) then set have (keep=NP&i);
%end;
run;

%mend func;

%func(have)

[Laasse]

Perfect solution. Thank you very much!