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!
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
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
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!
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
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
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!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.