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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.