Hi, I have a large data of monthly stock price from 1985 Aug-1990 July. And I am required to calculate the 5 year compounded return for each stock. How do i go about it? An example would as below, from table A to B. The tricky thing not all stocks have full monthly data from 1985 Aug to 1990 July and there are blanks data too.
table A
Cusip | Months | price |
111111 | 1985 Aug | 2.05 |
111111 | … | … |
111111 | 1990 Jul | 5.36 |
100000 | 1985 Aug | 5.8 |
100000 | … | … |
100000 | 1990 Jul | 8.6 |
table b
Cusip | Months | price | 5 years return |
111111 | 1985 Aug | 2.05 | xxx |
111111 | … | … | |
111111 | 1990 Jul | 5.36 | |
100000 | 1985 Aug | 5.8 | xxx |
100000 | … | … | |
100000 | 1990 Jul | 8.6 |
What's your formula?
Have you looked into the Financial formula's?
Can you post a worked example with data and expected output?
If you are subtracting beginning price from end price here you go:
data have;
infile cards dsd;
input Cusip$ Months$ price;
cards;
111111,1985 Aug,2.05
111111,,,
111111,1990 Jul,5.36
100000,1985 Aug,5.8
100000,,
100000,1990 Jul,8.6
;
data prep;
set have;
if not missing(months) then do;
_month = month(input(put(cats('01',scan(months,2,' '),2015),$9.),date9.));
end;
_year = scan(months,1,' ');
run;
proc sort data=prep out=prep2;by cusip _year _month;where not missing(months);
data want;
set prep2;
retain _begin;
by cusip _year _month;
if first.cusip then _begin = price;
if last.cusip then _end = price;
return = _end-_begin;
drop _:;
run;
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.