## Observations above the 75th percentile

Solved
Regular Contributor
Posts: 238

# Observations above the 75th percentile

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.

Thanks.

Nicholas Kormanik

Accepted Solutions
Solution
‎01-07-2013 07:22 AM
SAS Employee
Posts: 23

## Re: Observations above the 75th percentile

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;

All Replies
Solution
‎01-07-2013 07:22 AM
SAS Employee
Posts: 23

## Re: Observations above the 75th percentile

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;

PROC Star
Posts: 1,315

## Re: Observations above the 75th percentile

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;

Regular Contributor
Posts: 238

## Re: Observations above the 75th percentile

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!

Regular Contributor
Posts: 238

## Re: Observations above the 75th percentile

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.

Super User
Posts: 23,754

## Re: Observations above the 75th percentile

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.

Regular Contributor
Posts: 238

## Re: Observations above the 75th percentile

Okay.  Thanks.

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

Super User
Posts: 23,754

## Re: Observations above the 75th percentile

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

Regular Contributor
Posts: 238

## Re: Observations above the 75th percentile

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.

🔒 This topic is solved and locked.