Percentile

[Ronein]

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

[Tom]

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.

[Ronein]

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;

[Ronein]

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

[LinusH]

Why are you peculiar what PROC to use?

You can also accomplish this using MEANS/SUMMARY.

What are your actual constraints?

[Ronein]

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

[Ronein]

I was asked to use proc rank only

[Ronein · Accepted solution]

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;