Dear all,
my sample data as well as a code someone else wrote to compute stdev of returns over rolling 10year window are attached. I ask for your help to compute stdev of returns over rolling 24 months window. For example, for year 1985 , I want to use the past two years (1983-84)24 monthly returns and compute a standard deviation, output to year 1985.
I tried to modify his code, but not sure how to deal with month and year.
Thanks for your time!
lan
Don't touch that messy code. Start anew with:
data have(keep=cusip ret monthDate);
infile "&sasforum.\datasets\24sample.txt" dlm='09'x dsd firstobs=2;
input CUSIP RET Year month;
monthDate = mdy(month, 1, year);
run;
proc sql;
create table roll as
select
h2.cusip,
h2.monthDate as periodEndDate format=yymmd7.,
h2.ret,
mean(h1.ret) as meanRet,
std(h1.ret) as stdRet
from
have as h1 inner join
have as h2 on h1.cusip=h2.cusip and
intck("MONTH", h1.monthDate, h2.monthDate) between 1 and 24
group by h2.cusip, h2.monthDate, h2.ret
having count(h2.cusip)=24;
select * from roll;
quit;
PG
Don't touch that messy code. Start anew with:
data have(keep=cusip ret monthDate);
infile "&sasforum.\datasets\24sample.txt" dlm='09'x dsd firstobs=2;
input CUSIP RET Year month;
monthDate = mdy(month, 1, year);
run;
proc sql;
create table roll as
select
h2.cusip,
h2.monthDate as periodEndDate format=yymmd7.,
h2.ret,
mean(h1.ret) as meanRet,
std(h1.ret) as stdRet
from
have as h1 inner join
have as h2 on h1.cusip=h2.cusip and
intck("MONTH", h1.monthDate, h2.monthDate) between 1 and 24
group by h2.cusip, h2.monthDate, h2.ret
having count(h2.cusip)=24;
select * from roll;
quit;
PG
Thanks so much!!!
is it easy to change your code a little to have the same standard deviation value for all the month within the same year, i.e. for year 1987 month1-12, they all have the same stdev based on 24 months between 85 and 86.
Lan
If I understand correctly, you could simply copy the January std over the rest of the year. Add the extra step :
data want(drop=currentYear);
set roll; by cusip;
retain stdRetYear currentYear;
if first.cusip or year(periodEndDate) ne currentYear
then call missing(stdRetYear, currentYear);
if month(periodEndDate) = 1 then do;
stdRetYear = stdRet;
currentYear = year(periodEndDate);
end;
run;
PG
Thank again, PG!
here is a rudimentary request,
following your code, I wrote
data want_1 (keep=cusip year stdRetYear);
set want;
year=(periodEndDate);
run;
this year variable is numeric,e.g. 10683 for year 1989, how can I convert it back to normal year ?
Lan
To get a list of Stds, use :
data previousTwoYearStd (keep=cusip year stdRetYear);
set want;
if month(periodEndDate) = 1 then do;
year = year(periodEndDate);
output;
end;
run;
PG
PG, you are fantastic. Thanks!!!!!!
My real data is over 980000 observations (it is a large panel). when I ran your code (see below), it ran out of memory and stopped responding.
+++++++++++++++
proc sql;
create table roll as
select
h2.cusip,
h2.monthDate as periodEndDate format=yymmd7.,
h2.ret,
mean(h1.ret) as meanRet,
std(h1.ret) as stdRet
from
have as h1 inner join
have as h2 on h1.cusip=h2.cusip and
intck("MONTH", h1.monthDate, h2.monthDate) between 1 and 24
group by h2.cusip, h2.monthDate, h2.ret
having count(h2.cusip)=24;
select * from roll;
quit;
++++++++++++++++
I plan to do the following, but have 1 more question.
1. I plan to split the sample (1983-2011) into 3 sets, e.g. 1983-1993, then 1992-2002, then 2001-2011, the overlap is to include the prior 24-month data to compute my stdev.
I just ran your code above with 1983-1993, in the output window, it printed the results,
is there a way to not have the results printed- maybe that helps with memory size issue?
-Lan
Just remove the statement
select * from roll;
I put it there only for checking the result on the small sample. It should make a big difference; try with the whole dataset again. The result is in dataset roll.
PG
It worked for full data set.
Thanks
-Lan
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.