topic Re: Creating 5 lags of about 100 variables in SAS Procedures

Creating 5 lags of about 100 variables

VKG — Wed, 20 Jan 2016 15:56:35 GMT

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

Re: Creating 5 lags of about 100 variables

Reeza — Wed, 20 Jan 2016 16:01:19 GMT

Can you post some sample data and your expected output?

Sadly there isn't an easy way 😞

Re: Creating 5 lags of about 100 variables

data_null__ — Wed, 20 Jan 2016 16:05:25 GMT

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

Re: Creating 5 lags of about 100 variables

ballardw — Wed, 20 Jan 2016 16:22:47 GMT

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.

Re: Creating 5 lags of about 100 variables

Steelers_In_DC — Wed, 20 Jan 2016 17:07:21 GMT

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;

Re: Creating 5 lags of about 100 variables

Ksharp — Fri, 22 Jan 2016 01:21:43 GMT

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;

Re: Creating 5 lags of about 100 variables

Babloo — Thu, 21 Jan 2016 09:03:28 GMT

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?

Re: Creating 5 lags of about 100 variables

Ksharp — Thu, 21 Jan 2016 09:27:17 GMT

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

Re: Creating 5 lags of about 100 variables

data_null__ — Thu, 21 Jan 2016 12:23:08 GMT

@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;

Re: Creating 5 lags of about 100 variables

VKG — Thu, 21 Jan 2016 18:47:59 GMT

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

X1	X2	X3	X4	X5
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

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

X1	X2	X3	X4	X5	X1_1	X2_1	X3_1	X4_1	X5_1	X1_2	X2_2	X3_2	X4_2	X5_2	X1_3	X2_3	X3_3	X4_3	X5_3	X1_4	X2_4	X3_4	X4_4	X5_4	X1_5	X2_5	X3_5	X4_5	X5_5
1	11	21	31	41
2	12	22	32	42	1	11	21	31	41
3	13	23	33	43	2	12	22	32	42	1	11	21	31	41
4	14	24	34	44	3	13	23	33	43	2	12	22	32	42	1	11	21	31	41
5	15	25	35	45	4	14	24	34	44	3	13	23	33	43	2	12	22	32	42	1	11	21	31	41
6	16	26	36	46	5	15	25	35	45	4	14	24	34	44	3	13	23	33	43	2	12	22	32	42	1	11	21	31	41
7	17	27	37	47	6	16	26	36	46	5	15	25	35	45	4	14	24	34	44	3	13	23	33	43	2	12	22	32	42
8	18	28	38	48	7	17	27	37	47	6	16	26	36	46	5	15	25	35	45	4	14	24	34	44	3	13	23	33	43
9	19	29	39	49	8	18	28	38	48	7	17	27	37	47	6	16	26	36	46	5	15	25	35	45	4	14	24	34	44

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

Regards,

Vaneeta.

Re: Creating 5 lags of about 100 variables

VKG — Thu, 21 Jan 2016 18:48:39 GMT

Reeza, I just posted at the bottom.
Regards,
VKG>

Re: Creating 5 lags of about 100 variables

VKG — Thu, 21 Jan 2016 18:48:58 GMT

Hello, please see the data set below.
Regards,
VKG

Re: Creating 5 lags of about 100 variables

VKG — Thu, 21 Jan 2016 18:49:43 GMT

Hello, I am creating 500 variables for some model building.
Regards,
Vaneeta.

Re: Creating 5 lags of about 100 variables

VKG — Thu, 21 Jan 2016 18:50:58 GMT

Yes, but to do that for 100 variables and 5 times - I was looking for a quicker way to do it

Re: Creating 5 lags of about 100 variables

Steelers_In_DC — Thu, 21 Jan 2016 19:12:03 GMT

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

Re: Creating 5 lags of about 100 variables

Ksharp — Fri, 22 Jan 2016 02:15:32 GMT

Here is the IML code . I believe it is going to be faster than John King(data _null_) 's code .

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;
proc iml;
use have;
read all var _num_ into x[c=vnames]; 
close;  
varname=j(ncol(x),&lag,blankstr(nleng(vnames)+8));
do i=1 to &lag;
 varname[,i]=t(cats(vnames,'_',i));
end; 
varname=vnames||rowvec(varname); 

lag=j(nrow(x),&lag#ncol(x),.); 
idx=do(1,&lag#ncol(x),&lag); 
l=1:&lag;
do i=1 to ncol(idx);
 lag[,idx[i]:idx[i]+&lag-1]=lag(x[,i],l);
end;
want=x||lag;  
create want from want[c=varname];
append from want;
close;
quit;

Re: Creating 5 lags of about 100 variables

Ksharp — Fri, 22 Jan 2016 03:10:43 GMT

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;

Re: Creating 5 lags of about 100 variables

VKG — Fri, 22 Jan 2016 15:01:28 GMT

Thanks!

X1	X2	X3	X4	X5
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

X1	X2	X3	X4	X5
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

X1	X2	X3	X4	X5
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