Combined do loop and lag function?

Laasse — Fri, 05 Dec 2014 11:45:15 GMT

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?

Re: Combined do loop and lag function?

Loko — Fri, 05 Dec 2014 12:07:47 GMT

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)

Re: Combined do loop and lag function?

Laasse — Fri, 05 Dec 2014 12:27:48 GMT

Perfect solution. Thank you very much!

topic Combined do loop and lag function? in SAS Forecasting and Econometrics

Combined do loop and lag function?

Re: Combined do loop and lag function?

Re: Combined do loop and lag function?