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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.