topic Re: Proc Rank in SAS Programming

Proc Rank

Jyuen204 — Wed, 21 Oct 2020 22:11:40 GMT

I have a data table created with 3 field values
Table name = CUST_EMAIL

Fields:

CUSTOMER_ID

BILLING_NUMBER

EMAIL_ADDRESS

The Customer_ID is the highest level granularity meaning it can have multiple BILLING_ACCOUNTS. Each Billing account only has 1 email though.

I have a rank to pull this data together to provide a ranking

proc rank data= CUST_EMAIL out=RANK_RESULTS ties = low descending;
by CUSTOMER_ID;
var BILLING_NUMBER;
ranks EMAIL_RANK ;
run;

However when I run the PROC RANK i am getting this error
ERROR: Data set WORK.CUST_EMAIL is not sorted in ascending sequence. The current BY group has CUSTOMER_ID = 1-9990SF and the next BY group has CUSTOMER_ID = 1-9990NY.

Even though when I created the table CUST_EMAIL, I had it ordered by CUSTOMER_ID ASC so I am not sure why it is saying that my data set isnt sorted

Re: Proc Rank

PaigeMiller — Wed, 21 Oct 2020 22:21:21 GMT

You need a PROC SORT before you run PROC RANK. The sort needs to be BY CUSTOMER_ID;

Re: Proc Rank

Jyuen204 — Wed, 21 Oct 2020 22:26:04 GMT

Thanks! That worked. I figured i could sort the data in my table build and it would be good enough.

Re: Proc Rank

ballardw — Wed, 21 Oct 2020 22:27:27 GMT

@Jyuen204 wrote:

I have a data table created with 3 field values
Table name = CUST_EMAIL

Fields:

CUSTOMER_ID

BILLING_NUMBER

EMAIL_ADDRESS

The Customer_ID is the highest level granularity meaning it can have multiple BILLING_ACCOUNTS. Each Billing account only has 1 email though.

I have a rank to pull this data together to provide a ranking

proc rank data= CUST_EMAIL out=RANK_RESULTS ties = low descending;
by CUSTOMER_ID;
var BILLING_NUMBER;
ranks EMAIL_RANK ;
run;

However when I run the PROC RANK i am getting this error
ERROR: Data set WORK.CUST_EMAIL is not sorted in ascending sequence. The current BY group has CUSTOMER_ID = 1-9990SF and the next BY group has CUSTOMER_ID = 1-9990NY.

Even though when I created the table CUST_EMAIL, I had it ordered by CUSTOMER_ID ASC so I am not sure why it is saying that my data set isnt sorted

Simple solution: Sort the data by the variable on the By statement.

You might check to see if any of those id values have leading blanks. You might think they are in "order" but a leading blank can throw that off. The error message though does show the values at the time your run Proc Rank are out of order.

Interpretation of a rank on a value like Billing_Account (is that the same as Billing_number?) sounds a tad off if it is anything other than a sequence number of some sort.