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;
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.
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;
This is exactly what I needed in order to calculate it.thank you so much
Why are you peculiar what PROC to use?
You can also accomplish this using MEANS/SUMMARY.
What are your actual constraints?
I was asked to use proc rank only
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.