How to lag multiple variables by fiscal year end?

[salv100]

Hey guys!

I'm trying to lag multiple variables (16 in total, e.g. Earnings, Bookvalue, Dividends,...) for each group (permno) depending on their fiscal year-end on SAS 9.4.

Here's what I tried for the variable bookvalue:

%let _var=bookvalue;
Data want;
SET have;
by permno;
If fyr=1 then &_var=lag4(&_var);
If fyr=2 then &_var=lag5(&_var);
If fyr=3 then &_var=lag6(&_var);
If fyr=4 then &_var=lag7(&_var);
If fyr=5 then &_var=lag8(&_var);
If fyr=6 then &_var=lag9(&_var);
If fyr=7 then &_var=lag10(&_var);
If fyr=8 then &_var=lag11(&_var);
If fyr=9 then &_var=lag12(&_var);
If fyr=10 then &_var=lag13(&_var);
If fyr=11 then &_var=lag14(&_var);
If fyr=12 then &_var=lag15(&_var);
RUN;

It's working fine, but I don't want to repeat that code for each variable. Is there a way to include the other 15 variables?

Thanks a lot!

[Jagadishkatam]

This is an untested code which could be tried

%let _var=bookvalue;
%macro test;
Data want;
SET have;
by permno;
%do i = 1 %to 12;
If fyr=&i. then &_var=lag%evalf(&i.+3)(&_var);
%end;
RUN;
%mend;
%test

[salv100]

Hey Jag,

Thanks for your fast response! Your code looks great, but doesn't solve my problem. I'd like to lag multiple variables (Bookvalue, Earnings, Dividends,...) at the same time. 

I tried to include them in the %let statement, but that doesn't work. 

Thanks again!

[Jagadishkatam]

ok then you can alternatively try to create a dataset with all the variables and use that in this code as below

so that you could avoid the use of the %let

This is an untested code, so you need to test and let me know

data vars;
input vars$10.;
cards;
Bookvalue
Earnings
Dividends
;

%macro test;
Data _null_;
set vars;
call execute("data want;SET have;by permno;%do i = 1 %to 12;If fyr=&i. then "||vars||"=lag%evalf(&i.+3)("||strip(vars)||");%end;RUN;");
%mend;
%test

[Kurt_Bremser · Accepted solution]

Try a double macro loop:

data vars;
input vars$10.;
call symputx(cats('var',_n_),vars);
call symputx('varnum',_n_);
cards;
Bookvalue
Earnings
Dividends
;
run;

%macro test;
data want;
set have;
by permno;
%do j = 1 to &varnum.;
  %do i = 1 %to 12;
  if fyr = &i. then &&var&j. = lag%evalf(&i.+3)(&&var&j.);
  %end;
%end;
run;
%mend;
%test

[salv100]

Hello KurtBremser,

That worked! I used %eval instead of %evalf and %to instead of to in the second part.

Thank you so much! One last question, I promise 🙂 :

What happened to the first few observations for each permno where no prior data is available? How can I set them to missing data (.) ?

Thanks again!

[Kurt_Bremser]

Add a counter that starts with every permno:

%macro test;
data want;
set have;
by permno;
if first.permno
then count = 1;
else count + 1;
%do j = 1 to &varnum.;
  %do i = 1 %to 12;
  if fyr = &i. and count >= &i. + 4 then &&var&j. = lag%evalf(&i.+3)(&&var&j.);
  %end;
%end;
run;
%mend;