topic Percentile in SAS Programming

Percentile

Ronein — Mon, 03 Feb 2025 14:47:48 GMT

Hello
I want to calculate percentile 3 and 6 (for yearly salary)+only for people with salary higher than 0.
Then I want to classify each person ( also classify people with salary 0)to one of 3 groups-
If salary lower equal percentile 3 then ind=1.
Else if salary higher percentile 3 and lower equal percentile 6 then ind=2.
Else if salary higher percentile 6 than ind=3.
I want to know how to do it via proc rank and not proc univaraite.
Please note again-
Percentile is calculated for people with salary higher than 0 only.
Classification is done for all people included people with salary 0

Re: Percentile

Tom — Mon, 03 Feb 2025 15:01:07 GMT

What did you try? How did it work out?

You might want to start by making a new variable that copies SALARY but sets zeros to missing.

if salary>0 then salary2 = salary;

Then use that to find your percentile ranks.

Re: Percentile

LinusH — Mon, 03 Feb 2025 15:04:59 GMT

Why are you peculiar what PROC to use?

You can also accomplish this using MEANS/SUMMARY.

What are your actual constraints?

Re: Percentile

Ronein — Mon, 03 Feb 2025 18:30:54 GMT

I must use proc rank.
Why? People from my work want to make similar programs that in different models calculation be made same way....I know it is funny but it is the truth

This is my code (working 100%) However' I was asked to do...

Ronein — Mon, 03 Feb 2025 20:36:52 GMT

This is my code (working 100%)

However' I was asked to do it via proc rank and not proc univariate.

Can you please show the code via proc rank that provide same result?

proc univariate data =raw_data(Where=(Income_NOEXT_AMT>0))  noprint;
var Income_NOEXT_AMT;
output out=P_Income_tbl  PCTLPTS =3,6   PCTLPRE =P;
run;

Data WANT;
set raw_data;
If _N_=1 then set P_Income_tbl;
IF Income_NOEXT_AMT=0 then _Income_NOEXT_AMT=1;
else IF Income_NOEXT_AMT>0 AND Income_NOEXT_AMT<=P3 then _Income_NOEXT_AMT=2;
else IF Income_NOEXT_AMT>P3 AND Income_NOEXT_AMT<=P6 then  _Income_NOEXT_AMT=3;
else IF Income_NOEXT_AMT>P6 then _Income_NOEXT_AMT=4; 
Run;

Re: Percentile

Ronein — Mon, 03 Feb 2025 20:39:20 GMT

I was asked to use proc rank only

Re: Percentile

Ronein — Mon, 03 Feb 2025 21:43:54 GMT

Okay,

Here is the solution

 

Data raw_data;
set raw_data;
IF Income_NOEXT_AMT>0 then _Income_NOEXT_AMT_=Income_NOEXT_AMT;
Run;

proc rank data=raw_data out=want_rank(KEEP=lakoach _Income_NOEXT_AMT_  rank) groups=100;
var _Income_NOEXT_AMT_;
ranks rank;
run;
proc sort data=want_rank;
by rank;
run;

proc sql;
create table want_rank_b(KEEP=maxx P rename=(maxx=value)) as
select rank+1 as P,
         min(_Income_NOEXT_AMT_) as minn,
		 max(_Income_NOEXT_AMT_) as maxx
from want_rank
where rank in (2,5)
group by calculated P
;
quit;
/*rank=2 (percentile=3)*/
/*rank=5 (percentile=6)*/
 
proc transpose data=want_rank_b out=P_Income_tbl(DROP=_name_) prefix=P;
var value;
ID P;
Run;

/*proc univariate data =raw_data(Where=(Income_NOEXT_AMT>0))  noprint;*/
/*var Income_NOEXT_AMT;*/
/*output out=P_MACHZOR_tbl  PCTLPTS =3,6   PCTLPRE =P;*/
/*run;*/

Data WANT;
set raw_data;
If _N_=1 then set P_Income_tbl;
IF Income_NOEXT_AMT=0 then IND=1;
else IF Income_NOEXT_AMT>0 AND Income_NOEXT_AMT<=P3 then IND=2;
else IF Income_NOEXT_AMT>P3 AND Income_NOEXT_AMT<=P6 then  IND=3;
else IF Income_NOEXT_AMT>P6 then IND=4; 
Run;

Re: Percentile

Ronein — Tue, 04 Feb 2025 05:41:17 GMT

This is exactly what I needed in order to calculate it.thank you so much