BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MAC1430
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

7 REPLIES 7
PeterClemmensen
Tourmaline | Level 20

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? 🙂

MAC1430
Pyrite | Level 9

Thanks, only one observation per month for each stock. 

Ksharp
Super User
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;
MAC1430
Pyrite | Level 9

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). 

Ksharp
Super User

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;

MAC1430
Pyrite | Level 9
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?
MAC1430
Pyrite | Level 9
I guess it might be because of error in date format, I just noticed it now. I will try to fix it.

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
  • 7 replies
  • 1305 views
  • 3 likes
  • 3 in conversation