topic Re: Using PROC RANK for Groups of the Same Variable in SAS Programming

Using PROC RANK for Groups of the Same Variable

elwayfan446 — Mon, 20 Apr 2020 16:07:23 GMT

Today's hurdle:

I have 2 variables that are numeric. The first variable can have duplicates, the second will not. I am trying to use the PROC RANK function to assign a rank based on variable. What I have found so far is how to rank all the observations. This is the reference I have been working with: HERE.

Here is my example and the desired outcome for "New Rank" where the "New Rank" is in descending order:

Variable 1	Variable 2	New Rank
10	100	1
20	100	2
20	200	1
30	100	1

I feel like this should be another simple one but I can't get my head wrapped around it.

Re: Using PROC RANK for Groups of the Same Variable

Jagadishkatam — Mon, 20 Apr 2020 16:19:53 GMT

please try the below code without the proc rank

data have;
input Variable1	Variable2	;
cards;
10 100 
20 100 
20 200 
30 100 
;

proc sort data=have;
by Variable1 Variable2;
run;

data want;
set have;
by Variable2 notsorted;
retain rank;
if first.Variable2 then rank=1;
else rank+1;
run;

Re: Using PROC RANK for Groups of the Same Variable

novinosrin — Mon, 20 Apr 2020 16:27:28 GMT

data have;
input Variable1	Variable2;*	NewRank;
cards;
10	100	1
20	100	2
20	200	1
30	100	1
;
proc rank data=have out=want descending ties=dense;
by variable1;
   var variable2;
   ranks newrank;
run;

Re: Using PROC RANK for Groups of the Same Variable

elwayfan446 — Mon, 20 Apr 2020 16:50:23 GMT

Here is my actual code:

data dwacq.data_sale;
	set dwacq.data_sale;
	by sale_loan_number_num notsorted;
	retain rank;
	if first.sale_loan_number then rank=1;
	else rank+1;
run;

Here are the results I want:

sale_loan_key	sale_loan_number	New Rank
10	100	1
20	100	2
20	200	1
30	100	1

Here are the results I am getting with your code (and all the code I was trying before I posted the question):

sale_loan_key	sale_loan_number	New Rank
10	100	1
20	100	2
20	200	3
30	100	4

Re: Using PROC RANK for Groups of the Same Variable

elwayfan446 — Mon, 20 Apr 2020 16:51:24 GMT

New Rank is not currently a variable in the dataset. This is the new variable I want to create with the new rank based on var 1 and var2.

Re: Using PROC RANK for Groups of the Same Variable

Reeza — Mon, 20 Apr 2020 16:51:50 GMT

Reverse the sort, numerate and then resort. Simplest to code option.

proc sort data=have;
by var1 descending var2;
run;

data want;
set have;
by var1 descending var2;
if first.var1 then count=1;
else count+1;
run;

proc sort data=want;
by var2 var2;
run;

@elwayfan446 wrote:

Today's hurdle:

I have 2 variables that are numeric. The first variable can have duplicates, the second will not. I am trying to use the PROC RANK function to assign a rank based on variable. What I have found so far is how to rank all the observations. This is the reference I have been working with: HERE.

Here is my example and the desired outcome for "New Rank" where the "New Rank" is in descending order:

Variable 1 Variable 2 New Rank

10 100 1

20 100 2

20 200 1

30 100 1

I feel like this should be another simple one but I can't get my head wrapped around it.

Re: Using PROC RANK for Groups of the Same Variable

novinosrin — Mon, 20 Apr 2020 17:07:35 GMT

Yes you are right, albeit you perhaps missed to notice the newrank being commented in the input statement ?

input Variable1	Variable2;*	NewRank;

Re: Using PROC RANK for Groups of the Same Variable

elwayfan446 — Mon, 20 Apr 2020 17:23:31 GMT

This worked well and I always like as little code as possible! Thanks @Reeza and thanks @novinosrin and @Jagadishkatam for your help as well!