Observations above the 75th percentile

[NKormanik]

Help.

Want only observations above the 75th percentile.  Here's the code I tried to use:

data nicholas._21603_;

set nicholas.combined;

where

combo contains '21603'

and

'50501'n >= pctl(75, '50501'n)

;

run;

The first where clause works fine.  The second, for the percentile, doesn't.

Sure hoping for the answer.

Thanks.

Nicholas Kormanik

[Fraktalnisse · Accepted solution]

Hi!

The data step will process only one observation at a time. The function pctl calculates the percentile based on the values that you send into the function, in the function call. A "row-calculation". You would like to calculate the percentile based on a column, and then compare this value to each row.

Mabye something like this?

/*Calculate 75 percentile*/
proc means data=sashelp.class noprint;
  var height;
  output out=p75dataset p75=p75DSvar;
run;

/*Store 75 percentile in a macro variable*/
data _null_;
  set p75dataset;
  call symputx('p75Mvalue',p75DSvar);
run;

/*Find the subset*/
data subset;
  set sashelp.class;
  where height>=&p75Mvalue;
run;

[TomKari]

PROC RANK will do it also:

PROC RANK DATA=nicholas.combined(where=(combo contains '21603')) GROUPS=4 OUT=nicholas._21603_(where=(rank_var=3));

VAR '50501'n;

RANKS rank_var;

RUN;

[NKormanik]

My goodness I have a lot to learn....

I'll give these approaches a try and get back after I do.

Thanks so much for the help!

[NKormanik]

Could I create a variable:

p75 = pctl(75, '50501'n);

and then use that in the where data step?

where

'50501'n >= p75(75, '50501'n);

Sure seems reasonable.

[Reeza]

Check the output of the pctl function. 

data class;

set sashelp.class;

test=pctl(75, weight);

run;

PCTL is a ROW operation, it calculates the 75th percentile across the row, which for one variable is the variable value. 

If you want to do something like above use @fraktalnisse suggestion above. 

[NKormanik]

Okay.  Thanks.

pctl() being a row-only function, wouldn't seem to have much use.

[Reeza]

It is if your data is formatted wide instead of long, example below. This often happens in medical fields, ie up to 20 diagnosis per patient and 20 different dates.

SAS also has mean/median/average and a whole set of functions that work across the row, so you may want ensure you understand the functions you're using.

Person metric1 metric2 metrc3 metric4 -- metric99

Person1 1 0 2 3...  99

Person2 1 3 3 4... 99

Person3 3 5 6 3.. 99

[NKormanik]

I see your point, Reeza, however..., it would certainly be quite useful and convenient to be able to use those functions on a particular column as well.

My example at top should be a walk in the park:

where '50501'n >= pctl(75, '50501'n);

Unfortunately it's not.

But, VERY FORTUNATELY, you folks are helpful enough to show the long path to accomplish the task.