Dear all,
I hope that you can help me with the following problem.
My data file contains quarterly earnings surprises.
I want to create quintile-portfolios that are sorted on a quarterly basis.
In other words: I want to have 5 portfolios PF={1,2,3,4,5}. Each quarter I want to rank the corresponding earnings-surprises and assign them to one of these portfolios. For example PF1 contains the 20% lowest earnings surprises of each period, and PF5 the 20% highest earnings surprises of each period.
I thought about simply including a variable Portfolio that can take the values PF={1,2,3,4,5}, and hence indicates the quintile the earnings surprise is in.
However, I do not know how to do that.
What I look for is the following:
A function that says: variable PF takes the value 1 if surprise<[20%-percentile of surprises in that quarter of that year]
2 if [20%-percentile of surprises in that quarter of that year]< surprise < [40%-percentile of surprises in that quarter of that year]
3 ..........
Below is a small sample of how the data looks like at the moment.
I would really appreciate it if ssomebody who knows how to solve this problem could help me.
Thank you very much in advance.
CUSIP | date | SURPRISE | quarter | year | |
2 | 00036020 | 20090630 | -0.023 | 2 | 2009 |
3 | 00036020 | 20090930 | 0.096 | 3 | 2009 |
4 | 00036110 | 20090228 | -0.001 | 1 | 2009 |
5 | 00036110 | 20090531 | 0.057 | 2 | 2009 |
6 | 00036110 | 20090831 | 0.044 | 3 | 2009 |
7 | 00036110 | 20091130 | 0.078 | 4 | 2009 |
8 | 00036110 | 20100228 | -0.188 | 1 | 2010 |
9 | 00036110 | 20100531 | 0.020 | 2 | 2010 |
10 | 00036110 | 20100831 | 0.184 | 3 | 2010 |
11 | 00036110 | 20101130 | 0.293 | 4 | 2010 |
12 | 00081T10 | 20081231 | 0.045 | 4 | 2008 |
13 | 00081T10 | 20100331 | 1.000 | 1 | 2010 |
14 | 00081T10 | 20100630 | 0.091 | 2 | 2010 |
data have; input SURPRISE quarter year ; cards; -0.023 2 2009 0.096 4 2009 -0.001 1 2009 0.057 2 2009 0.044 4 2009 0.078 4 2009 -0.03 2 2009 0.06 1 2009 -0.01 1 2009 0.57 2 2009 0.44 3 2009 0.08 4 2009 0.023 2 2009 0.06 3 2009 -0.01 1 2009 0.05 2 2009 0.04 3 2009 0.07 4 2009 -0.03 2 2009 0.06 1 2009 0.001 1 2009 0.57 2 2009 0.44 4 2009 0.08 4 2009 -0.3 2 2009 0.6 3 2009 -0.1 1 2009 0.7 2 2009 0.4 3 2009 0.78 4 2009 ; run; proc sort data=have ; by year quarter; run; proc rank data=have out=want groups=5 ; by year quarter; var surprise; ranks groups; run;
Ksharp
data have; input SURPRISE quarter year ; cards; -0.023 2 2009 0.096 4 2009 -0.001 1 2009 0.057 2 2009 0.044 4 2009 0.078 4 2009 -0.03 2 2009 0.06 1 2009 -0.01 1 2009 0.57 2 2009 0.44 3 2009 0.08 4 2009 0.023 2 2009 0.06 3 2009 -0.01 1 2009 0.05 2 2009 0.04 3 2009 0.07 4 2009 -0.03 2 2009 0.06 1 2009 0.001 1 2009 0.57 2 2009 0.44 4 2009 0.08 4 2009 -0.3 2 2009 0.6 3 2009 -0.1 1 2009 0.7 2 2009 0.4 3 2009 0.78 4 2009 ; run; proc sort data=have ; by year quarter; run; proc rank data=have out=want groups=5 ; by year quarter; var surprise; ranks groups; run;
Ksharp
Thank you Ksharp.
I really appreciate it!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.