Hi
I'm trying to execute the below code to essentially compund 3 month return ..essentially rolling 3 month comp return for each ID but found a peculiar issue while calculating using lag3 function as you may note below. I've copied the output and also shown what the expected outcome should be for comp3_ret. Could you kindly let me know where I'm missing out:
data pras2;
input DAT1 mmddyy11. ID $ RET VAR1 VAR2 FLAG ;
cards;
12/31/2010 A 0.20 25 100 1
11/30/2010 A 0.20 15 100 1
01/31/2011 A 0.20 35 100 1
08/31/2010 A 0.20 35 100 1
10/31/2010 A 0.20 20 100 1
09/30/2010 A 0.20 35 100 1
12/31/2010 AA 0.25 25 100 1
11/30/2010 AA 0.25 15 100 1
01/31/2011 AA 0.25 35 100 1
08/31/2010 AA 0.25 35 100 1
10/31/2010 AA 0.25 20 100 1
09/30/2010 AA 0.25 35 100 1
;
run;
PROC SORT DATA = PRAS2;
BY ID DAT1;
data pras1;
set pras2;
by ID DAT1;
retain comp;
if first.id then comp = 1;
comp = comp*(1+ret);
/**** Trailing 3 month compounded return ****/
if (first.id eq 0) and (ID eq lag2(ID)) then comp3_ret = comp - 1 ;
if (first.ID eq 0) and (ID eq lag3(ID)) then comp3_ret = (comp/lag3(comp))-1 ;
run;
PROC PRINT DATA = PRAS1;
format DAT1 MMDDYY10. ;
RUN;
output : (see attached img)
Create a counter that counts the records per ID. If counter is greater than 3 then do calculations.
Also calculate LAG outside of the IF statement.
LAG doesn't work the way you think it should, is the quick answer.
Longer explanation
http://support.sas.com/resources/papers/proceedings09/055-2009.pdf
Not sure what you are looking for. data pras2; input DAT1 mmddyy11. ID $ RET VAR1 VAR2 FLAG ; cards; 12/31/2010 A 0.20 25 100 1 11/30/2010 A 0.20 15 100 1 01/31/2011 A 0.20 35 100 1 08/31/2010 A 0.20 35 100 1 10/31/2010 A 0.20 20 100 1 09/30/2010 A 0.20 35 100 1 12/31/2010 AA 0.25 25 100 1 11/30/2010 AA 0.25 15 100 1 01/31/2011 AA 0.25 35 100 1 08/31/2010 AA 0.25 35 100 1 10/31/2010 AA 0.25 20 100 1 09/30/2010 AA 0.25 35 100 1 ; run; PROC SORT DATA = PRAS2; BY ID DAT1; data pras1; set pras2; by ID DAT1; retain comp found comp3_ret; if first.id then do;comp = 1; found=0;comp3_ret=.;end; comp = comp*(1+ret); /**** Trailing 3 month compounded return ****/ if (first.id eq 0) and (ID eq lag2(ID)) and not found then do; comp3_ret = comp - 1 ;found=1; end; drop found; run;
Trying to change as little as possible in the existing program, the DATA step would become:
data pras1;
set pras2;
by ID DAT1;
id_back2 = lag2(id);
id_back3 = lag3(id);
retain comp;
if first.id then comp = 1;
comp = comp*(1+ret);
comp_back3 = lag3(comp);
/**** Trailing 3 month compounded return ****/
if (ID eq id_back2) then comp3_ret = comp - 1 ;
if (ID eq id_back3) then comp3_ret = (comp/comp_back3)-1 ;
run;
You don't need to check for first.ID=0. If ID is equal to ID from a couple of records previously, that can only happen when first.ID=0.
thanks; but the objective is to replicate this for long duration comp. returns like say 6, 9 & 12 month returns. So I was hesitant to declare that many lag variabels upfront using arrays,
Meanwhile, got this sorted out using proc expand from another post
"convert ret=comp_ret_3 / transformout=(+1 log reverse movsum 3 exp -1 ); "
Thanks
PrOC EXPAND is definitely the way to go. But for those without SAS/ETS (and therefore without proc expand), here is a data step solution, with by groups, to get rolling returns of any size (9 in this case) without recourse to multiple lag functions. Just a lag for the window size (lag9 here):
Notes:
data want (drop=need);
set have;
by id;
retain need ;
if first.id then need=0;
need = (1+need)*(1+ret) / (1+ifn(lag9(id)=id,lag9(ret),0)) - 1;
if lag9(id)=id then rolling_return9=need;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.