topic Re: percentiles P5,P10,P20,P30,P40,P50,P60,P70,P80,P90,P95 and classify to group in SAS Programming

percentiles P5,P10,P20,P30,P40,P50,P60,P70,P80,P90,P95 and classify to group

Ronein — Sat, 19 Oct 2024 18:46:14 GMT

Hello

I want to calculate following percentiles and then classify each row into a group.

I want to define groups (Ranks) based on following percentiles-

P5,P10,P20,P30,P40,P50,P60,P70,P80,P90,P95

The following code create the percentiles-P10,P20,P30,P40,P50,P60,P70,P80,P90 but no P5,P95

What is the way to add them???

proc rank data=sashelp.cars out=have groups=10;
var invoice;
ranks rank;
run;


proc sql;
create table rank_groups_Definition as
select rank,
		min(invoice) as min,
		max(invoice) as max
from have
group by rank
;
quit;


data rank_groups_Definition_2;
set rank_groups_Definition;
_MIN_=lag(max)+1;
IF _MIN_=. then _MIN_=0;
drop min;
rename _min_=min;
Run;


proc sql;
create table want as
select a.*,b.rank
from sashelp.cars as a
left join rank_groups_Definition_2 as b
on    b.Min<=a.invoice<=b.Max
;
quit;

Re: percentiles P5,P10,P20,P30,P40,P50,P60,P70,P80,P90,P95 and classify to group

PaigeMiller — Sat, 19 Oct 2024 19:54:01 GMT

proc rank data=sashelp.cars out=have fraction;
var invoice;
ranks rank;
run;

proc format;
	value percentile 0-<0.05="< 5 percentile"
	    0.05-<0.1='5-10 percentile'
	    0.1-<0.2='10-20 percentile'
	    /* I'm lazy, you type the rest */
	    ...
    ;
run;

proc datasets library=work nolist;
    modify have;
    format rank percentile.;
run; quit;

Also, stay away from mathematical analyses in SQL steps when possible. In the long run, SQL is a poor choice for math. Just because you can do things in SQL does not mean you should do things in SQL. In this case and many others, PROC FORMAT is a better solution. Using PROC FORMAT allows you to put labels on the group that actually mean something to humans and it keeps the variable numeric (instead of 'P5' 'P10' etc.) so it will sort numerically in tables (instead of sorting alphabetically).

Re: percentiles P5,P10,P20,P30,P40,P50,P60,P70,P80,P90,P95 and classify to group

Ksharp — Sun, 20 Oct 2024 01:24:27 GMT

Split your data into 100 groups. And recode it as your wish.

proc rank data=sashelp.cars out=have groups=100;
var invoice;
ranks rank;
run;
data have;
set have;
if rank<=4 then want='P5 ';
else if rank<=9 then want='P10 ';
else if rank<=19 then want='P20 ';
.........................

Re: percentiles P5,P10,P20,P30,P40,P50,P60,P70,P80,P90,P95 and classify to group

Ronein — Sun, 20 Oct 2024 12:12:02 GMT

I am using it on my data set and I dont get the whole percentiles that I ask .


proc rank data=temp(Where=(revenue>0)) out=want groups=100;
var revenue;
ranks rank;
run;

Re: percentiles P5,P10,P20,P30,P40,P50,P60,P70,P80,P90,P95 and classify to group

PaigeMiller — Sun, 20 Oct 2024 13:45:32 GMT

@Ronein wrote:

I am using it on my data set and I dont get the whole percentiles that I ask .

What does that mean?

Re: percentiles P5,P10,P20,P30,P40,P50,P60,P70,P80,P90,P95 and classify to group

ballardw — Mon, 21 Oct 2024 15:46:38 GMT

Show the log.

If you don't have ~~more than~~ at least 100 non-missing non-tied values in the input set you are not going to get 100 ranks. Period.

Re: percentiles P5,P10,P20,P30,P40,P50,P60,P70,P80,P90,P95 and classify to group

Ksharp — Mon, 21 Oct 2024 00:37:10 GMT

As Ballardw said ,maybe your data do not have unique 100 value , Try groups=20:

proc rank data=sashelp.cars out=have groups=20;
var invoice;
ranks rank;
run;
data have;
set have;
if rank=0 then want='P5 ';
else if rank=1 then want='P10 ';
else if rank in (2 3) then want='P20 ';
.........................