topic Observations above the 75th percentile in SAS Programming

Observations above the 75th percentile

NKormanik — Mon, 07 Jan 2013 07:07:32 GMT

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

Re: Observations above the 75th percentile

Fraktalnisse — Mon, 07 Jan 2013 12:22:41 GMT

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;

Re: Observations above the 75th percentile

TomKari — Mon, 07 Jan 2013 17:42:30 GMT

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;

Re: Observations above the 75th percentile

NKormanik — Mon, 07 Jan 2013 19:52:11 GMT

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!

Re: Observations above the 75th percentile

NKormanik — Mon, 07 Jan 2013 23:23:01 GMT

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.

Re: Observations above the 75th percentile

Reeza — Mon, 07 Jan 2013 23:26:37 GMT

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.

Re: Observations above the 75th percentile

NKormanik — Tue, 08 Jan 2013 00:24:41 GMT

Okay. Thanks.

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

Re: Observations above the 75th percentile

Reeza — Tue, 08 Jan 2013 16:00:01 GMT

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

Re: Observations above the 75th percentile

NKormanik — Tue, 08 Jan 2013 23:22:26 GMT

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.