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

I have about 100 variables that I would like to create lags for. Is there a quick way of creating lags for 5 time periods?

Thanks for your help.

VKG

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Here is the new Data Step code , also faster than John King(data _null_)'s code and no need to consider about if the variable like var1-var5 . It is applied to any variable name .

 

 

data have;
infile cards expandtabs truncover;
input X1	X2	X3	X4	X5;
cards;
1	11	21	31	41
2	12	22	32	42
3	13	23	33	43
4	14	24	34	44
5	15	25	35	45
6	16	26	36	46
7	17	27	37	47
8	18	28	38	48
9	19	29	39	49
;
run;

%let lag=5;

data x;
 do i=1 to &lag;
  output;
 end;
run;
proc transpose data=have(obs=0) out=temp;
var _all_;
run;
proc sql;
  select cats(_name_,'_',i,'=lag',i,'(',_name_,')') into : lag separated by ';'
   from temp,x ;
quit;
data want;
 set have;
 &lag ;
run;

View solution in original post

17 REPLIES 17
Reeza
Super User

Can you post some sample data and your expected output?

 

Sadly there isn't an easy way 😞

VKG
Calcite | Level 5 VKG
Calcite | Level 5
Reeza, I just posted at the bottom.
Regards,
VKG>
data_null__
Jade | Level 19

Are you laging the observations for 100 variable or the values variables x1-x100?  Example data is always useful and example ouput.

VKG
Calcite | Level 5 VKG
Calcite | Level 5
Hello, please see the data set below.
Regards,
VKG
ballardw
Super User

What are you going to do with the results? I find that building an extra 500 variables (100 * 5 lag periods each) might well become difficult to code to use in short order.

VKG
Calcite | Level 5 VKG
Calcite | Level 5
Hello, I am creating 500 variables for some model building.
Regards,
Vaneeta.
Steelers_In_DC
Barite | Level 11

This is what you are asking for but I find it hard to believe this is the best solution.  Something with array, hash or transpose will probably work.  Please provide an example of what you have and what you are looking for:

 

data have;
input var1 var2 var3 var4 var5;
cards;
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
4 4 4 4 4
5 5 5 5 5
;run;

data want;
set have;
%macro lag;
%do i = 1 %to 5;
var&i.lag&i = lag&i(var&i);
%end;
%mend;
%lag;
run;

Ksharp
Super User

You want lag1-lag5 for each and every variables ?

Sorry. I make a mistake . John King(data _null_)'s code could be better.

Or if you want IML code ?

 

 

data have;
input var1 var2 var3 var4 var5;
cards;
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
4 4 4 4 4
5 5 5 5 5
;run;
data lag1;
if _n_ eq 1 then output;
 else output;
 set have(rename=(var1-var5=var_lag1_1-var_lag1_5));
run;
data lag2;
if _n_ eq 1 then do;
 output;
 output;
end;
 else output;
 set have(rename=(var1-var5=var_lag2_1-var_lag2_5));
run;
data lag3;
if _n_ eq 1 then do;
 output;
 output;
 output;
end;
 else output;
 set have(rename=(var1-var5=var_lag3_1-var_lag3_5));
run;
data lag4;
if _n_ eq 1 then do;
 output;
 output;
 output;
 output;
end;
 else output;
 set have(rename=(var1-var5=var_lag4_1-var_lag4_5));
run;
data lag5;
if _n_ eq 1 then do;
 output;
 output;
 output;
 output;
 output;
end;
 else output;
 set have(rename=(var1-var5=var_lag5_1-var_lag5_5));
run;
data want;
 merge have(in=ina) lag1-lag5;
 if ina;
run;

 

Babloo
Rhodochrosite | Level 12

May I request you to tell me why you've used output statement before the set statement while you create the datasets lag1-lag5? How it is helpful?

Ksharp
Super User

Make a lag value (missing value) for all the variables ? Aren't they what you want ?

VKG
Calcite | Level 5 VKG
Calcite | Level 5
Yes, but to do that for 100 variables and 5 times - I was looking for a quicker way to do it
data_null__
Jade | Level 19

@Ksharp wrote:

You want lag1-lag5 for each and every variables ?

 

 

That doesn't look right when I run it. I think it is this.

data have;
   input var1-var5;
   cards;
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
4 4 4 4 4
5 5 5 5 5
6 6 6 6 6
7 7 7 7 7
8 8 8 8 8
;;;;
   run;
proc print;
   run;

data Fivelags;
   set have end=eof;
   if _n_ gt 1 then set have(rename=(var1-var5=lag1_1-lag1_5));
   if _n_ gt 2 then set have(rename=(var1-var5=lag2_1-lag2_5));
   if _n_ gt 3 then set have(rename=(var1-var5=lag3_1-lag3_5));
   if _n_ gt 4 then set have(rename=(var1-var5=lag4_1-lag4_5));
   if _n_ gt 5 then set have(rename=(var1-var5=lag5_1-lag5_5));
   run;
Proc print width=min;
   run;

 


Capture.PNG
VKG
Calcite | Level 5 VKG
Calcite | Level 5

Here is the example of what I am trying to do, assuming i have 5 variables X1 - X5:

X1X2X3X4X5
111213141
212223242
313233343
414243444
515253545
616263646
717273747
818283848
919293949

 

I would like to create a lag for each one of them for 5 time periods resulting i the following data set:

X1X2X3X4X5X1_1X2_1X3_1X4_1X5_1X1_2X2_2X3_2X4_2X5_2X1_3X2_3X3_3X4_3X5_3X1_4X2_4X3_4X4_4X5_4X1_5X2_5X3_5X4_5X5_5
111213141                         
212223242111213141                    
313233343212223242111213141               
414243444313233343212223242111213141          
515253545414243444313233343212223242111213141     
616263646515253545414243444313233343212223242111213141
717273747616263646515253545414243444313233343212223242
818283848717273747616263646515253545414243444313233343
919293949818283848717273747616263646515253545414243444

 

Any help programming this would be appreciated vs using lag for each of the 100 variables 5 times.

Regards,

Vaneeta.

Steelers_In_DC
Barite | Level 11

The macro that I've provided does just that, just change the 5 to 100.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 1841 views
  • 1 like
  • 7 in conversation