DATA Step, Macro, Functions and more

Maximum value for each firm in a month based on daily returns

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

Maximum value for each firm in a month based on daily returns

Hi Everyone,

 At the end of each month t, I want to get the value of maximum return for each stock based on its daily returns from month t. There will be almost 20-25 daily observations in a month, but I am keeping very few in the sample for the convenience. Furthermore, I also want to get the average of two, three, four and five maximum values in a month for each stock too  (I put few daily observations; therefore, I understand that the average of four and five maximum returns might not be possible, but I just need the code).

The original data is around 30 million observations. Please find the SAS data below. Based on this data, I estimate the maximum return on for stock ID 1 for 201001 (January 2010), and it should be 0.04135

 

Thanks a lot for your help.

 

Best,

 

Cheema

data have;
infile cards expandtabs truncover;
input stock date : yymmdd8. ret ;
format date yymmdd8.;
cards;
1 20100107 0.04135
1 20100126 -0.02544
1 20100129 0.03384
1 20100201 0.01089
1 20100202 0.02621
1 20100226 0.01001
1 20100301 -0.0132
1 20100330 -0.0181
1 20100331 -0.00308
1 20100401 0.05394
1 20100429 -0.01077
1 20100430 0.0031
1 20100503 0.00931
1 20100521 0
1 20100531 -0.01427
1 20100601 -0.02718
1 20100617 0.05019
1 20100630 -0.01236
1 20100701 0
1 20100728 0.01193
1 20100730 0.01621
1 20100802 -0.0174
2 20100107 0.06069
2 20100126 -0.06219
2 20100129 0.01989
2 20100201 -0.04551
2 20100202 0.04768
2 20100226 -0.05462
2 20100301 0.02613
2 20100330 -0.04155
2 20100331 0.04336
2 20100401 -0.00536
2 20100429 -0.02561
2 20100430 -0.0166
2 20100503 0.00563
2 20100521 0
2 20100531 0.01119
2 20100601 0
2 20100617 0.01521
2 20100630 0.27384
2 20100701 0.09519
2 20100728 -0.12793
2 20100730 -0.04815
2 20100802 -0.02235
run
;

Cheema


Accepted Solutions
Solution
‎02-27-2017 06:41 AM
Super User
Posts: 10,035

Re: Maximum value for each firm in a month based on daily returns

1) sort the dataset by descending order

 

data have;

 set have;

 year=(date);

 month=month(date);

run;

proc sort data=have;

by id year month  descending ret;

run;

 

2) pick up the most large five obs for each id,after that do PROC SUMMAYR just as I did.

data top_five;

 set have;

 by id year month;

 if first.month then n=0;

 n+1;

 if n le 5;

run;

View solution in original post


All Replies
PROC Star
Posts: 759

Re: Maximum value for each firm in a month based on daily returns

So do you wish to output three observations for stock ID 1 for 201001 (January 2010) and only have the maximum value at the last observation in that month or only one observation per month? Smiley Happy

Contributor
Posts: 63

Re: Maximum value for each firm in a month based on daily returns

Thanks, only one observation per month for each stock. 

Super User
Posts: 10,035

Re: Maximum value for each firm in a month based on daily returns

data have;
infile cards expandtabs truncover;
input stock date : yymmdd8. ret ;
format date yymmdd8.;
cards;
1 20100107 0.04135
1 20100126 -0.02544
1 20100129 0.03384
1 20100201 0.01089
1 20100202 0.02621
1 20100226 0.01001
1 20100301 -0.0132
1 20100330 -0.0181
1 20100331 -0.00308
1 20100401 0.05394
1 20100429 -0.01077
1 20100430 0.0031
1 20100503 0.00931
1 20100521 0
1 20100531 -0.01427
1 20100601 -0.02718
1 20100617 0.05019
1 20100630 -0.01236
1 20100701 0
1 20100728 0.01193
1 20100730 0.01621
1 20100802 -0.0174
2 20100107 0.06069
2 20100126 -0.06219
2 20100129 0.01989
2 20100201 -0.04551
2 20100202 0.04768
2 20100226 -0.05462
2 20100301 0.02613
2 20100330 -0.04155
2 20100331 0.04336
2 20100401 -0.00536
2 20100429 -0.02561
2 20100430 -0.0166
2 20100503 0.00563
2 20100521 0
2 20100531 0.01119
2 20100601 0
2 20100617 0.01521
2 20100630 0.27384
2 20100701 0.09519
2 20100728 -0.12793
2 20100730 -0.04815
2 20100802 -0.02235
;
run;
proc summary data=have;
by stock date;
format date monyy.;
var ret;
output out=want max=ret_max;
run;
Contributor
Posts: 63

Re: Maximum value for each firm in a month based on daily returns

Thanks Ksharp. It will be great if you can please help me with the second part where I want to get the average of two, three, four and five maximum values in a month for each stock too  (I put few daily observations; therefore, I understand that the average of four and five maximum returns might not be possible based on the provided dataset, but please make it for the average of three returns in a month and I will edit the code then). 

Solution
‎02-27-2017 06:41 AM
Super User
Posts: 10,035

Re: Maximum value for each firm in a month based on daily returns

1) sort the dataset by descending order

 

data have;

 set have;

 year=(date);

 month=month(date);

run;

proc sort data=have;

by id year month  descending ret;

run;

 

2) pick up the most large five obs for each id,after that do PROC SUMMAYR just as I did.

data top_five;

 set have;

 by id year month;

 if first.month then n=0;

 n+1;

 if n le 5;

run;

Contributor
Posts: 63

Re: Maximum value for each firm in a month based on daily returns

Hi Ksharp, thanks for it. There is just one problem that n remains 1, I guess it might be an error in first.month. Can you please kindly check it?
Contributor
Posts: 63

Re: Maximum value for each firm in a month based on daily returns

I guess it might be because of error in date format, I just noticed it now. I will try to fix it.
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 175 views
  • 3 likes
  • 3 in conversation