Proc Rank

[Jyuen204]

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

[PaigeMiller · Accepted solution]

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

[Jyuen204]

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

[ballardw]

---

@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.