topic Re: Calculating percentile for a variable but based on another variable? in SAS Procedures

Calculating percentile for a variable but based on another variable?

UniversitySas — Thu, 27 Aug 2020 10:52:47 GMT

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?

Re: Calculating percentile for a variable but based on another variable?

ballardw — Thu, 27 Aug 2020 15:11:13 GMT

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.

Re: Calculating percentile for a variable but based on another variable?

UniversitySas — Thu, 27 Aug 2020 23:04:26 GMT

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!

Re: Calculating percentile for a variable but based on another variable?

Rick_SAS — Fri, 28 Aug 2020 20:26:51 GMT

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).

Re: Calculating percentile for a variable but based on another variable?

UniversitySas — Fri, 28 Aug 2020 22:02:47 GMT

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.