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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.