Rank by Multiple Column Variables

ekthomas13 — Mon, 15 Mar 2021 22:41:29 GMT

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

Re: Rank by Multiple Column Variables

Reeza — Mon, 15 Mar 2021 22:59:46 GMT

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

topic Re: Rank by Multiple Column Variables in SAS Programming

Rank by Multiple Column Variables

Re: Rank by Multiple Column Variables