BookmarkSubscribeRSS Feed
danielhu
Calcite | Level 5

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

CusipMonthsprice
1111111985 Aug2.05
111111
1111111990 Jul5.36
1000001985 Aug5.8
100000
1000001990 Jul8.6

table b

CusipMonthsprice5 years return
1111111985 Aug2.05xxx
111111
1111111990 Jul5.36
1000001985 Aug5.8xxx
100000
1000001990 Jul8.6
2 REPLIES 2
Reeza
Super User

What's your formula?

Have you looked into the Financial formula's?

 

Can you post a worked example with data and expected output?

Steelers_In_DC
Barite | Level 11

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 720 views
  • 0 likes
  • 3 in conversation