assign subgroup-percentiles to portfolios

[FinanceCandidate]

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

[Ksharp · Accepted solution]

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

[FinanceCandidate]

Thank you Ksharp.

I really appreciate it!