topic Re: Divide population into 10 groups by percentiles in SAS Programming

Divide population into 4 groups by percentiles

Ronein — Wed, 24 Feb 2021 09:53:58 GMT

Hello

I want to divide population of variable invoice in data set sashelp.cars into 4 groups by variable invoice.

In each group there will be equal number of rows .

So, the criteria is using percentile .

Then I need to put the values (range of values) in proc format.

So, in proc format there will be defined 5 groups

How should we do it pls?

Re: Divide population into 10 groups by percentiles

PeterClemmensen — Wed, 24 Feb 2021 09:21:31 GMT

Percentile of what?

Here is something to get you started

proc rank data=sashelp.cars groups=10 descending out=cars;
   var MPG_City;
   ranks rank;
run;

Re: Divide population into 5 groups by percentiles

Kurt_Bremser — Wed, 24 Feb 2021 09:47:33 GMT

Here's a try with UNIVARIATE:

proc univariate data=sashelp.cars;
var invoice;
output
  out=Pctls
  pctlpts = 10 20 30 40 50 60 70 80 90 100
  pctlpre = invoice
  pctlname = pct10 pct20 pct30 pct40 pct50 pct60 pct70 pct80 pct90 pct100
;
run;

proc sort
  data=sashelp.cars (keep=invoice)
  out=cars
;
by invoice;
run;

proc transpose
  data=pctls
  out=p_long
;
var _all_;
run;

data cntlin;
set p_long;
fmtname = "carspct";
type = "N";
start = ifn(_n_ = 1,0,lag(col1));
end = col1;
label = catx(" ","Percentile",_n_);
keep fmtname type start end label;
run;

proc format cntlin=cntlin;
run;

data carsfmt;
set cars;
format invoice carspct.;
run;

Re: Divide population into 4 groups by percentiles

Ronein — Wed, 24 Feb 2021 09:56:41 GMT

Hello,

I don't understand why the ranges are overlapped???

rank 0 is valued from 35777 till 173560

rank 1 is valued from 25371 till 35688

rank 2 is valued from 18881 till 25218

rank 3 is valued from 9875 till 18821

proc sort data=sashelp.cars out=cars;by  invoice ;run;

proc rank data=cars groups=4 descending out=cars;
   var invoice;
   ranks rank;
run;

PROC SQL;
	create table tbl  as
	select  0 as rank,
            min(invoice) as rank_min,
		    max(invoice) as rank_max
	from  cars
	where rank=0
	union all
  select  1 as rank,
            min(invoice) as rank_min,
		    max(invoice) as rank_max
	from  cars
	where rank=1
	union all
  select  2 as rank,
            min(invoice) as rank_min,
		    max(invoice) as rank_max
	from  cars
	where rank=2
		union all
  select  3 as rank,
            min(invoice) as rank_min,
		    max(invoice) as rank_max
	from  cars
	where rank=3

;
QUIT;

Re: Divide population into 5 groups by percentiles

Ronein — Wed, 24 Feb 2021 10:01:22 GMT

May you please explain why proc rand is not working well?

I expect that in each group there will be non-overlapped values.

proc sort data=sashelp.cars out=cars;by  invoice ;run;
proc rank data=cars groups=10 descending out=cars;
   var invoice;
   ranks rank;
run;

Re: Divide population into 5 groups by percentiles

PaigeMiller — Wed, 24 Feb 2021 12:14:29 GMT

@Ronein wrote:

May you please explain why proc rand is not working well?

I expect that in each group there will be non-overlapped values.
proc sort data=sashelp.cars out=cars;by  invoice ;run;
proc rank data=cars groups=10 descending out=cars;
   var invoice;
   ranks rank;
run;

What do you mean by "not working well"? PROC RANK will not produce overlapping groupings, except in the case of ties in which case there can be (and should be) some overlap. Please show us what you are seeing that doesn't work.

Re: Divide population into 5 groups by percentiles

Ronein — Wed, 24 Feb 2021 13:20:08 GMT

proc sort data=sashelp.cars out=cars;by  invoice ;run;
proc rank data=cars groups=4 descending out=cars;
   var invoice;
   ranks rank;
run;

How can it happen that invoice value in first group is between 35777 till 173560

and invoice value in second group is between 25371 till 35688

I expect that minimum value in second group will be 173560 !!!

Re: Divide population into 5 groups by percentiles

PaigeMiller — Wed, 24 Feb 2021 13:48:24 GMT

@Ronein wrote:

I expect that minimum value in second group will be 173560 !!!

I don't understand this, why do you expect that? I expect the MAXIMUM value in the FIRST group to be 173560, and that the MINIMUM value in the first group to be less than 173560; and that the maximum value in the other groups would be less than 173560, and also less than or equal to the minimum of the previous group, and that is what I see.

proc rank data=sashelp.cars groups=4 descending out=cars;
    var invoice;
    ranks rank;
run;
proc summary nway data=cars;
    class rank;
    var invoice;
    output out=stats min=min_invoice max=max_invoice;
run;

Re: Divide population into 4 groups by percentiles

ballardw — Thu, 25 Feb 2021 16:56:22 GMT

@Ronein wrote:

Hello,

I don't understand why the ranges are overlapped???

rank 0 is valued from 35777 till 173560

rank 1 is valued from 25371 till 35688

rank 2 is valued from 18881 till 25218

rank 3 is valued from 9875 till 18821

What overlap? There is no overlap there. 35777 is larger than 35688, 25371 is larger than 25218, 18881 is larger than 18821.