BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14
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
1 ACCEPTED SOLUTION

Accepted Solutions
Ronein
Meteorite | Level 14

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;

View solution in original post

7 REPLIES 7
Tom
Super User Tom
Super User

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
Meteorite | Level 14

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
Meteorite | Level 14

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

LinusH
Tourmaline | Level 20

Why are you peculiar what PROC to use?

You can also accomplish this using MEANS/SUMMARY.

What are your actual constraints?

Data never sleeps
Ronein
Meteorite | Level 14
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
Meteorite | Level 14

I was asked to use proc rank only

Ronein
Meteorite | Level 14

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1276 views
  • 3 likes
  • 3 in conversation