Calculating percentile for a variable but based on another variable?

[UniversitySas]

Hello,

I was wondering if it is possible to calculate percentiles of a variable, not for itself, but relative to the values of another variable?

Consider the data below:

data neighborhood_money;
   input Name $ 1-7 Gender $ 9 Poor 11-14 Rich 16-19;
   datalines;
Andrea  F 28.6 .
Carole  F 24.0 24.0
Clayton M 27.0 .
Curtis  M 22.6 22.6
Doug    M 27.3 27.3
Ellen   F 27.0 27.0
Jan     F 31.2 31.2
Jimmy   M 22.4 22.5
Karin   F 34.6 .
Mick    M 29.0 .
Richard M 29.7 .
Sam     M 27.2 .
Susan   F 35.1 .
;

proc sort data=neighborhood_money out=pairs;
   by gender;
run;

If I use proc rank as follows:

proc rank data=pairs out=rankpair PERCENT;
   by gender;
   var poor rich;
run;

I will get percentile values, by gender, for "poor" and "rich". However, the percentile value for "poor" is based on other "poor" values, and similarly for "rich" percentiles.

Is there instead a way, that I can get percentile values for "poor" relative to the values of "rich"?

What I want:

So for Andrea, who has  Poor = 28.6, when calculating the percentile, I only want to consider how Andrea's value of 28.6 is ranked against the other entries in the "Rich" column, for gender = F.

So Andrea's percentile for poor= 28.6 is calculated by looking at: Carole = 24.0 , Ellen = 27.0, and Jan = 31.2, as these women have values for the "Rich" column.

But she is not compared to the other two females, Karin = 34.6 (poor) and Susan = 35.1 (poor) or any of the men.

Is this possible to do? Calculate ranks based on other variables?

[ballardw]

And for your example of Andrea what is the resulting "percentile" you expect?

Also you should provide an example of what you expect the output data set or report to look like.

[UniversitySas]

Im not at my computer right now, so i can update the output i want when I get home.

If we are ranking Andrea's value of 28.6 against the values: 24.0, 27.0, and 31.2, Andrea's percentile rank should = ~67 (66.666*), given that 2/3 of the sample are smaller than 28.6.


Thanks!

[Rick_SAS · Accepted solution]

Let me see if I understand this. Since you are doing a BY-group analysis, you only need to look at one gender at a time. Let's look at females:

data F;
   input Name $ 1-7 Gender $ 9 Poor 11-14 Rich 16-19;
   datalines;
Andrea  F 28.6 .
Carole  F 24.0 24.0
Ellen   F 27.0 27.0
Jan     F 31.2 31.2
Karin   F 34.6 .
Susan   F 35.1 .
;

Now you want to compute the rank of the 'Poor' score with respect to the nonmissing 'Rich'  scores.

The Rich scores are always {24, 27, 31.2}. So you want 

Andrea: 28.6 is what percentile in {24, 27, 28.6, 31.2}

Carol: 24 is what percentile in {24, 27, 31.2}. (OR do we use {24, 24, 27, 31.2} ?)

...

Susan: 35.1 is what percentile in {24, 27, 31.2, 35.1}.

The short answer is that I do not think this is a computation built into any SAS procedure. I think you will have to program it yourself in the DATA step or PROC IML (much easier in IML).

[UniversitySas]

You are correct.

My solution was to separate the "rich" group into a separate table, then using left join in proc sql and using count(b.name) to find the number of people that meet the condition that a.poor ge b.rich , grouping by gender.

This gave me the number of people 'rich' below each 'poor', by gender, and then I manually computed a percentile by dividing by the total number of rich.

Seemed like the easiest way to go about it.