SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ariel
Calcite | Level 5

Hello, 

Currently I have multiple daily data for multiple firms of the 5 day,  1 month(21 trading days), 3 months(63 trading days), 6 months(126 trading days), 9 months(189 trading days) and  12 months(252 trading days) stock buy-and-hold returns.

MY data is as below: (252 may not be the last data of a firm)

Firm_ID

date

daye

rtn

1101

2009/8/26

1

-0.0173

1101

2009/8/27

2

0.00147

 

 

 

1101

2010/8/25

252

0.03

1101

 

253

 

1101

 

 

1102

2011/3/23

1

0.0014

1102

2011/3/24

2

-0.007

 

 

 

1102

2011/9/9

252

0.045

1102

 

 

1205

2013/2/4

1

0.065

 

 

 

 

If I calculate it by hand then:

(1) 5 days: Buy and hold return=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day5)-1

(2) 1 month: BHR=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day21)-1

(3) 3 months: BHR=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day63)-1

....

(6) 12 months:BHR=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day252)-1

 

However, I don't know how to write a sas program to do this...  

Please give me some suggestions. Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
user24feb
Barite | Level 11

A brute force approach would be (maybe proc timedata is worth looking):

 

%Let Span=5; *!;

Data A;
  Do Firm=1 To 3;
    Do i=0 To 20;
      Date=IntNX('month',"01JAN2000"d,i,'same');
     Rtn=RanNor(1)/10;
   Output;
 End;
  End;
  Drop i;
  Format Date Date9.;
Run;

%Macro L;
%Do i=1 %To &Span.;
  %If &i.=1 %Then %Do;
  Data B;
    Set A;
    A_Rtn1=(1+Rtn);
  Run;
  %End;
  %Else %Do;
  Data B;
    Set B;
    By Firm;
    A_Rtn&i.=Lag(A_Rtn%Eval(&i.-1));
    If First.Firm Then Call Missing (A_Rtn&i.);
  Run;
  %End;
%End;
%Mend;
%L;

Data B (Drop=A_:);
  Set B;
  Array A A_:;
  Total_Rtn_&Span.=1;
  Do over A;
    Total_Rtn_&Span.=Total_Rtn_&Span.*A;
  End;
  Total_Rtn_&Span.=Total_Rtn_&Span.-1;
Run;

View solution in original post

3 REPLIES 3
user24feb
Barite | Level 11

A brute force approach would be (maybe proc timedata is worth looking):

 

%Let Span=5; *!;

Data A;
  Do Firm=1 To 3;
    Do i=0 To 20;
      Date=IntNX('month',"01JAN2000"d,i,'same');
     Rtn=RanNor(1)/10;
   Output;
 End;
  End;
  Drop i;
  Format Date Date9.;
Run;

%Macro L;
%Do i=1 %To &Span.;
  %If &i.=1 %Then %Do;
  Data B;
    Set A;
    A_Rtn1=(1+Rtn);
  Run;
  %End;
  %Else %Do;
  Data B;
    Set B;
    By Firm;
    A_Rtn&i.=Lag(A_Rtn%Eval(&i.-1));
    If First.Firm Then Call Missing (A_Rtn&i.);
  Run;
  %End;
%End;
%Mend;
%L;

Data B (Drop=A_:);
  Set B;
  Array A A_:;
  Total_Rtn_&Span.=1;
  Do over A;
    Total_Rtn_&Span.=Total_Rtn_&Span.*A;
  End;
  Total_Rtn_&Span.=Total_Rtn_&Span.-1;
Run;

user24feb
Barite | Level 11

However, this would give the same result:

 

%Let Span=5; *!;

Data A;
  Do Firm=1 To 3;
    Do i=0 To 20;
      Date=IntNX('month',"01JAN2000"d,i,'same');
     Rtn=RanNor(1)/10;
   Output;
 End;
  End;
  Drop i;
  Format Date Date9.;
Run;

Data B (Keep=Firm Date Rtn Total_Rtn_&Span.);
  Retain dummy ;
  Set A;
  By Firm;
  If First.Firm Then Do;
    count=0;
 dummy=1; 
  End;
  count+1;
  Drop_Value=Lag&Span.(Rtn); 
  If count gt &Span. Then Do;
    dummy=dummy*(1+Rtn)/(1+Drop_Value);
  End;
  Else Do;
    dummy=dummy*(1+Rtn);
  End;
  Total_Rtn_&Span.=dummy-1;
Run;

 

Ariel
Calcite | Level 5
Thanks for your help! 🙂 🙂

sas-innovate-white.png

Join us for our biggest event of the year!

Four days of inspiring keynotes, product reveals, hands-on learning opportunities, deep-dive demos, and peer-led breakouts. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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