DATA Step, Macro, Functions and more

Calculating returns over intervalls

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Calculating returns over intervalls

Hi

I need to calculate returns over 3,6 and 12 month, I have monthly stock data. Can I do this with a do loop? Can somebody help me with a code to solve this problem, because I am new user os SAS.

For example the first 3 month return in 1990 would be ((46.25)/(38.75))-1, the second 3 month return in 1990 would be ((38.75)/(37.5))-1 and so on...

The first six month return  in 1990 would be ((46.25)/( 37.5))-1 and the second would be ((37.5)/(22.875))-1 and so on..

The 12 month return in 1990 would be ((46.25)/22.875))-1. The names of the variables are date and prc.

My data set looks as follows:

DATE          PRC

19891229 46.25000

19900131 42.00000

19900228 44.50000

19900330 38.75000

19900430 38.00000

19900531 38.75000

19900629 37.50000

19900731 35.87500

19900831 32.62500

19900928 21.37500

19901031 17.25000

19901130 24.00000

19901231 22.87500

19910131 28.12500

19910228 29.25000

19910328 34.62500

19910430 36.87500

19910531 42.12500

19910628 35.75000

19910731 34.75000

19910830 38.62500

19910930 35.62500

19911031 37.37500

19911129 34.37500

19911231 40.62500

19920131 43.87500

19920228 47.50000

19920331 45.50000

19920430 47.75000

19920529 46.50000

19920630 47.62500

19920731 46.00000

19920831 42.87500

19920930 44.37500

19921030 46.25000

19921130 50.37500

19921231 51.37500

19930129 53.75000

19930226 53.50000

19930331 54.62500

19930430 49.62500

19930528 47.12500

19930630 49.62500

19930730 50.37500

19930831 51.50000

19930930 51.50000

19931029 46.62500

19931130 47.12500

19931231 49.00000

The output should look like this for the 3 month return:

1990P1     return of the first 3 month period in 1990

1990P2     return of the second 3 month period in 1990

1990P3    and so on....

1990P4    and so on...

The output for the 6 month return should look like this:

1990P1     return of the first 6 month period in 1990

1990P2     return of the second 6 month period in 1990

The output for the 12 month return should look like this:

1990 return of the 12 month period in 1990

I do not need rolling returns. The output for each period can be in a separate output/table, they do not have to be in the same table column by column.

I hope that somebody can help me.

Many thanks.


Accepted Solutions
Solution
‎04-24-2013 11:41 PM
Super User
Posts: 10,044

Re: Calculating returns over intervalls

Easy.

data have;
 input DATE : yymmdd8.         PRC     ;
 format date yymmddn.;
 cards;
19891229 46.25000
19900131 42.00000
19900228 44.50000
19900330 38.75000
19900430 38.00000
19900531 38.75000
19900629 37.50000
19900731 35.87500
19900831 32.62500
19900928 21.37500
19901031 17.25000
19901130 24.00000
19901231 22.87500
19910131 28.12500
19910228 29.25000
19910328 34.62500
19910430 36.87500
19910531 42.12500
19910628 35.75000
19910731 34.75000
19910830 38.62500
19910930 35.62500
19911031 37.37500
19911129 34.37500
19911231 40.62500
19920131 43.87500
19920228 47.50000
19920331 45.50000
19920430 47.75000
19920529 46.50000
19920630 47.62500
19920731 46.00000
19920831 42.87500
19920930 44.37500
19921030 46.25000
19921130 50.37500
19921231 51.37500
19930129 53.75000
19930226 53.50000
19930331 54.62500
19930430 49.62500
19930528 47.12500
19930630 49.62500
19930730 50.37500
19930831 51.50000
19930930 51.50000
19931029 46.62500
19931130 47.12500
19931231 49.00000
;
run;
data quarter semi_year a_year ;
 set have;
 month=month(date);
 if month in (3 6 9 12) then output quarter;
 if month in (6 12) then output semi_year;
 if month eq 12 then output a_year;
run;
data quarter;
 set quarter;
 ratio=lag(prc)/prc  - 1 ;
run;






Ksharp

Message was edited by: xia keshan

View solution in original post


All Replies
Solution
‎04-24-2013 11:41 PM
Super User
Posts: 10,044

Re: Calculating returns over intervalls

Easy.

data have;
 input DATE : yymmdd8.         PRC     ;
 format date yymmddn.;
 cards;
19891229 46.25000
19900131 42.00000
19900228 44.50000
19900330 38.75000
19900430 38.00000
19900531 38.75000
19900629 37.50000
19900731 35.87500
19900831 32.62500
19900928 21.37500
19901031 17.25000
19901130 24.00000
19901231 22.87500
19910131 28.12500
19910228 29.25000
19910328 34.62500
19910430 36.87500
19910531 42.12500
19910628 35.75000
19910731 34.75000
19910830 38.62500
19910930 35.62500
19911031 37.37500
19911129 34.37500
19911231 40.62500
19920131 43.87500
19920228 47.50000
19920331 45.50000
19920430 47.75000
19920529 46.50000
19920630 47.62500
19920731 46.00000
19920831 42.87500
19920930 44.37500
19921030 46.25000
19921130 50.37500
19921231 51.37500
19930129 53.75000
19930226 53.50000
19930331 54.62500
19930430 49.62500
19930528 47.12500
19930630 49.62500
19930730 50.37500
19930831 51.50000
19930930 51.50000
19931029 46.62500
19931130 47.12500
19931231 49.00000
;
run;
data quarter semi_year a_year ;
 set have;
 month=month(date);
 if month in (3 6 9 12) then output quarter;
 if month in (6 12) then output semi_year;
 if month eq 12 then output a_year;
run;
data quarter;
 set quarter;
 ratio=lag(prc)/prc  - 1 ;
run;






Ksharp

Message was edited by: xia keshan

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 174 views
  • 0 likes
  • 2 in conversation