BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Laasse
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Loko
Barite | Level 11

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)

View solution in original post

2 REPLIES 2
Loko
Barite | Level 11

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
Calcite | Level 5

Perfect solution. Thank you very much!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Multiple Linear Regression in SAS

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.

Discussion stats
  • 2 replies
  • 1162 views
  • 0 likes
  • 2 in conversation