Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Observations above the 75th percentile

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-07-2013 02:07 AM

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

Accepted Solutions

Solution

01-07-2013
07:22 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-07-2013 07:22 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-07-2013 07:22 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-07-2013 12:42 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-07-2013 02:52 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-07-2013 06:23 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-07-2013 06:26 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-07-2013 07:24 PM

Okay. Thanks.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-08-2013 11:00 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-08-2013 06:22 PM

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.