Rank by Multiple Column Variables

[ekthomas13]

Hi All, 

I'm trying to rank order transactions by multiple variables. I've tried using the proc rank function but keep running into the ERROR: Data set WORK.TABLE1 is not sorted in ascending sequence.

I have the table properly ordered in the below, but can't seem to get the ranking to work. 

Table1

Transactions	District_Manager_Name	Sales_Associate_Name	Month_Num	Year_Date
12	John Smith	Sally Smith	4	2019
11	John Smith	John Roberts	4	2019
10	John Smith	Andrew Jones	4	2019
9	John Smith	Cam Barry	4	2019
20	John Smith	Sally Smith	5	2019
19	John Smith	John Roberts	5	2019
18	John Smith	Andrew Jones	5	2019
17	John Smith	Cam Barry	5	2019
12	Michael Brown	Joanne Low	4	2019
11	Michael Brown	Jimmy Lary	4	2019
10	Michael Brown	Joe King	4	2019
9	Michael Brown	Sherry Bowman	4	2019
20	Michael Brown	Joanne Low	5	2019
19	Michael Brown	Jimmy Lary	5	2019
18	Michael Brown	Joe King	5	2019
17	Michael Brown	Sherry Bowman	5	2019

Desired Output

Rank	Transactions	District_Manager_Name	Sales_Associate_Name	Month_Num	Year_Date
1	12	John Smith	Sally Smith	4	2019
2	11	John Smith	John Roberts	4	2019
3	10	John Smith	Andrew Jones	4	2019
4	9	John Smith	Cam Barry	4	2019
1	20	John Smith	Sally Smith	5	2019
2	19	John Smith	John Roberts	5	2019
3	18	John Smith	Andrew Jones	5	2019
4	17	John Smith	Cam Barry	5	2019
1	12	Michael Brown	Joanne Low	4	2019
2	11	Michael Brown	Jimmy Lary	4	2019
3	10	Michael Brown	Joe King	4	2019
4	9	Michael Brown	Sherry Bowman	4	2019
1	20	Michael Brown	Joanne Low	5	2019
2	19	Michael Brown	Jimmy Lary	5	2019
3	18	Michael Brown	Joe King	5	2019
4	17	Michael Brown	Sherry Bowman	5	2019

[Reeza · Accepted solution]

Please show your code and log. This would require having multiple variables in the BY statement but should work fine as long as you the DESCENDING key word set in both. 

It does look like a basic enumeration problem so assuming your data is sorted correctly (which may not be true given your error) the following will also work but does require the correct sort.

proc sort data=have;
by district_manager_name month_num descending transactions;
run;

data want;
set have;
by district_manager_name month_num;
if first.month_num the rank=0;
rank+1;
run;

---

@ekthomas13 wrote:

Hi All, 

I'm trying to rank order transactions by multiple variables. I've tried using the proc rank function but keep running into the ERROR: Data set WORK.TABLE1 is not sorted in ascending sequence.

I have the table properly ordered in the below, but can't seem to get the ranking to work. 

Table1

Transactions	District_Manager_Name	Sales_Associate_Name	Month_Num	Year_Date
12	John Smith	Sally Smith	4	2019
11	John Smith	John Roberts	4	2019
10	John Smith	Andrew Jones	4	2019
9	John Smith	Cam Barry	4	2019
20	John Smith	Sally Smith	5	2019
19	John Smith	John Roberts	5	2019
18	John Smith	Andrew Jones	5	2019
17	John Smith	Cam Barry	5	2019
12	Michael Brown	Joanne Low	4	2019
11	Michael Brown	Jimmy Lary	4	2019
10	Michael Brown	Joe King	4	2019
9	Michael Brown	Sherry Bowman	4	2019
20	Michael Brown	Joanne Low	5	2019
19	Michael Brown	Jimmy Lary	5	2019
18	Michael Brown	Joe King	5	2019
17	Michael Brown	Sherry Bowman	5	2019

Desired Output

Rank	Transactions	District_Manager_Name	Sales_Associate_Name	Month_Num	Year_Date
1	12	John Smith	Sally Smith	4	2019
2	11	John Smith	John Roberts	4	2019
3	10	John Smith	Andrew Jones	4	2019
4	9	John Smith	Cam Barry	4	2019
1	20	John Smith	Sally Smith	5	2019
2	19	John Smith	John Roberts	5	2019
3	18	John Smith	Andrew Jones	5	2019
4	17	John Smith	Cam Barry	5	2019
1	12	Michael Brown	Joanne Low	4	2019
2	11	Michael Brown	Jimmy Lary	4	2019
3	10	Michael Brown	Joe King	4	2019
4	9	Michael Brown	Sherry Bowman	4	2019
1	20	Michael Brown	Joanne Low	5	2019
2	19	Michael Brown	Jimmy Lary	5	2019
3	18	Michael Brown	Joe King	5	2019
4	17	Michael Brown	Sherry Bowman	5	2019

---