Help using Base SAS procedures

assign subgroup-percentiles to portfolios

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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.

                
CUSIPdateSURPRISEquarteryear
20003602020090630-0.02322009
300036020200909300.09632009
40003611020090228-0.00112009
500036110200905310.05722009
600036110200908310.04432009
700036110200911300.07842009
80003611020100228-0.18812010
900036110201005310.02022010
1000036110201008310.18432010
1100036110201011300.29342010
1200081T10200812310.04542008
1300081T10201003311.00012010
1400081T10201006300.09122010

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

Re: assign subgroup-percentiles to portfolios

Posted in reply to FinanceCandidate
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

View solution in original post


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

Re: assign subgroup-percentiles to portfolios

Posted in reply to FinanceCandidate
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.

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

Discussion stats
  • 2 replies
  • 209 views
  • 2 likes
  • 2 in conversation