topic Re: How to Rank combination of two columns using Proc Rank? in SAS Procedures

How to Rank combination of two columns using Proc Rank?

vishal09 — Fri, 26 Jun 2015 07:24:32 GMT

Hi,
I am new to SAS. I have this dataset where I have two dimentions Namely Customer_Id and Plan_Name. One Metric namely Sales.
I need to rank Plan_Name for each Customer_Id. For eg:

I would require something like this-

Customer_Id      Plan_Name    Rank
1                       A                   1
1                       B                   2
1                       C                   3
2                       E                   1
2                       F                   2
2                       D                   3

Below is the code that I have written but it is giving some syntax error. Can anyone help me out with this.

PROC RANK DATA = T1 OUT= T2 DESCENDING TIES = LOW;
BY DESCENDING Customer_Id,Plan_Name;
VAR Sales;
RANKS Rank;
RUN;

Thanks in advance!

Re: How to Rank combination of two columns using Proc Rank?

Ksharp — Fri, 26 Jun 2015 13:22:16 GMT

I don't think you need proc rank.

Code: Program

data have;
input Customer_Id   Plan_Name  $;
cards;
1 A  
1 B  
1 C  
2 E  
2 F  
2 D  
;
run;
data want;
 set have;
 by  Customer_Id ;
 if first.Customer_Id  then rank=0;
 rank+1;
 run;

Re: How to Rank combination of two columns using Proc Rank?

ballardw — Fri, 26 Jun 2015 15:15:36 GMT

Since your example data doesn't show any sales its a tad difficult to determine what the input looked like. Provide the example data that would be used to create the example desired output.

Re: How to Rank combination of two columns using Proc Rank?

vishal09 — Mon, 29 Jun 2015 02:20:54 GMT

Hi xia Keshan,

We do have Sales data in the dataset based on which ranking is to be done. For eg the data set would look something like this :

Customer_Id Plan_Name Sales Rank

1 A 50 1

1 B 40 2

1 C 30 3

2 E 40 1

2 F 30 2

2 D 20 3

In order to assign new ranks to next customer, I guess we need to retain the rank as well. Correct me if I am wrong. Let me know if the above code would still work for this requirement ?

Re: How to Rank combination of two columns using Proc Rank?

PGStats — Mon, 29 Jun 2015 03:09:48 GMT

You were almost there. Try :

PROC RANK DATA = T1 OUT= T2 DESCENDING TIES = LOW;

BY Customer_Id;

VAR Sales;

RANKS Rank;

RUN;

Re: How to Rank combination of two columns using Proc Rank?

vishal09 — Mon, 29 Jun 2015 03:16:17 GMT

Hi PGStats,

Would this code give this type of result ?

Customer_Id Plan_Name Sales Rank

1 A 50 1

1 B 40 2

1 C 30 3

2 E 40 1

2 F 30 2

2 D 20 3

Re: How to Rank combination of two columns using Proc Rank?

PGStats — Mon, 29 Jun 2015 03:20:05 GMT

I didn't test, but yes, it should. Try it!

Re: How to Rank combination of two columns using Proc Rank?

Dalveer — Mon, 29 Jun 2015 06:06:56 GMT

This will help you to reach to your desire result:

Please Try this

proc sort data=T1;

by customer_id;

run;

PROC RANK DATA=T1 out=T2 DESCENDING TIES = LOW ;

BY Customer_Id;

VAR sales;

RANKS Rank;

RUN;

Re: How to Rank combination of two columns using Proc Rank?

vishal09 — Mon, 29 Jun 2015 15:06:23 GMT

Thanks for the help. However, I was stuck at one point before calculating Rank. If anyone could help me with that as well. I am using a PROC SQL before calculating Ranks. I am summing up all the monthwise Sales into one column. But somehow the column is populated as blank. I check for values in individual columns, all of them contain either 0 or numeric data. Please find below the code for the same.

PROC SQL;

CREATE TABLE INTMD_PLAN1 AS

SELECT SRA_1,Plan_Name,

sum(Month1_TotalUnits_Junior,Month2_TotalUnits_Junior,Month3_TotalUnits_Junior,Month4_TotalUnits_Junior,Month5_TotalUnits_Junior,Month6_TotalUnits_Junior,

Month7_TotalUnits_Junior,Month8_TotalUnits_Junior,Month9_TotalUnits_Junior,Month10_TotalUnits_Junior,Month11_TotalUnits_Junior,Month12_TotalUnits_Junior) AS Total_Units_Junior

FROM INTMD_PLAN

WHERE SRA_1 IS NOT NULL

GROUP BY SRA_1,Plan_Name;

QUIT;

Thanks in advance!

Re: How to Rank combination of two columns using Proc Rank?

PGStats — Mon, 29 Jun 2015 18:48:27 GMT

Didn't you get the message:

WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because

neither the SELECT clause nor the optional HAVING clause of the associated

table-expression referenced a summary function.

SUM with multiple arguments is a SAS function and not a SQL summary function. You should use sum(sum(...)) AS Total_Units_Junior instead. The first sum is the SQL summary function and the second is the SAS sum over many variables.