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;
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.