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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.