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?
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)
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)
Perfect solution. Thank you very much!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.
Find more tutorials on the SAS Users YouTube channel.