Solved
New Contributor
Posts: 2

# assign subgroup-percentiles to portfolios

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.

 2 3 4 5 CUSIP date SURPRISE quarter year 00036020 20090630 -0.023 2 2009 00036020 20090930 0.096 3 2009 00036110 20090228 -0.001 1 2009 00036110 20090531 0.057 2 2009 00036110 20090831 0.044 3 2009 00036110 20091130 0.078 4 2009 00036110 20100228 -0.188 1 2010 00036110 20100531 0.020 2 2010 00036110 20100831 0.184 3 2010 00036110 20101130 0.293 4 2010 00081T10 20081231 0.045 4 2008 00081T10 20100331 1.000 1 2010 00081T10 20100630 0.091 2 2010

Accepted Solutions
Solution
‎06-23-2013 10:12 PM
Super User
Posts: 10,778

## Re: assign subgroup-percentiles to portfolios

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

All Replies
Solution
‎06-23-2013 10:12 PM
Super User
Posts: 10,778

## Re: assign subgroup-percentiles to portfolios

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

New Contributor
Posts: 2

## Re: assign subgroup-percentiles to portfolios

Thank you Ksharp.

I really appreciate it!

🔒 This topic is solved and locked.