Hi,
I have a .sas file containing monthly returns. In order to get annual returns, I am trying to write a SAS code, which takes each monthly return of each given year, adds 1 and multiplies them together.
For instance, if there are monthly returns from March- Dec,
March 2008 +5%
April 2008 -7%
etc
Dec 2008 +2%
Jan 2009 +3%
etc.
Dec 2009 -2%
the calculation should look like (1.05*0.93*...*1.02) for 2008, and (1.03*...*0.98) for 2009
I have many different stocks, which are all identified by a PERMNO number unique to that stock, and stock returns from 1998-2013.
After the calculation, SAS should return the annual cumulative return and copy it into a separate database, i.e. the resulting output database should look like this:
Permno Year Cumulative return
10104 2008 1.035
2009 1.015
11038 1998 1.01
1999 1.005
2000 1.08
I have written the SAS code below, but for some reason, it doesn't work as intended. I have tried quite a few workarounds, but unfortunately, wasn't successful.
-------------------------------------------------------------------------------------------------------------------------------------
data ReturnsSAS;
set cleandat.ReturnSAS; n
year=year(date);
month=month(date);
proc sql;
create table cum_returns as
select permno , product(1+ret) as cum_return
, min(ret) as minret , max(ret) as maxret
, n(ret) as n_periods , nmiss(ret) as n_miss
, sum(ret=.P) as n_dot_p , min(date) as first_date
, max(date) as last_date
from ReturnsSAS
where ('01jan1998'd <= date <= '31dec2013'd);
quit;
print data=ReturnsSAS.cum_returns; format first_date last_date yymmdd10.;
run;
------------------------------------------------------------------------------------------------------------------
Many thanks for your help,
Amatron
How about something like:
data have;
length month $3 pcent $10;
infile datalines delimiter=',';
input permno year month $ pcent $;
datalines;
10104,2008,Jan,+5
10104,2008,Feb,-7
10104,2008,Mar,+2
10104,2008,Apr,+3
10104,2009,Jan,-2
10104,2009,Feb,+5
;
run;
proc sql;
create table WANT as
select PERMNO,
YEAR,
MONTH,
case when index(PCENT,"+")>0 then 1.00 + (input(strip(tranwrd(PCENT,"+","")),best.) / 100)
else 1.00 - (input(strip(tranwrd(PCENT,"-","")),best.) / 100) end as VAL
from WORK.HAVE;
quit;
data want (drop=lstyear lstpermno);
set want;
attrib cum_return format=best.;
by permno;
retain cum_return lstyear lstpermno;
if _n_=1 then do;
lstpermno=permno;
lstyear=year;
cum_return=val;
end;
else if lstyear ne year then do;
lstyear=year;
cum_return=val;
end;
else if lstpermno ne permno then do;
lstpermno=permno;
lstyear=year;
cum_return=val;
end;
else do;
cum_return=cum_return * val;
end;
if last.permno then output;
run;
You need post some sample data .
And I am sure @Pierre Gagnon will be very happy to help you. he have done it before in SQL.
Maybe OT, but I found it absurd that SAS does not have a PRODUCT function in par with SUM function, given that GEOMEAN function is at your disposal, otherwise Proc SQL will be fit right in.
Here I would recommend using a data step and a DOW.
Haikuo
I would do something like:
proc sql;
create table annualReturns as
select permNo, year,
count(ret) as nbMonths,
exp(sum(log(1+ret))) as cumulativeReturn,
exp(12*mean(log(1+ret))) as extrapolatedReturn
from monthlyReturns
group by permNo, year;
select * from annualReturns;
quit;
where cumulativeReturn is the cumulated return for the months present in the data and extrapolatedReturn is the expected cumulative return assuming that missing months will have the same average return as the known returns for that year.Note there is no PRODUCT summary function in SAS SQL, hence the exp(sum(log())).
(untested)
PG
If you would like to do it in data step.
data have; length month $3 ; infile datalines delimiter=','; input permno year month $ pcent ; datalines; 10104,2008,Jan,+5 10104,2008,Feb,-7 10104,2008,Mar,+2 10104,2008,Apr,+3 10104,2009,Jan,-2 10104,2009,Feb,+5 ; run; data want ; set have; by permno year; retain cum 1; if not missing(pcent) then cum=cum*(1+pcent*0.01); if last.year then do; output;cum=1;end; run;
Xia Keshan
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.