Help using Base SAS procedures

How to Rank combination of two columns using Proc Rank?

Reply
Occasional Contributor
Posts: 11

How to Rank combination of two columns using Proc Rank?

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!

Super User
Posts: 9,671

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

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;  
Occasional Contributor
Posts: 11

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

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 ?

Super User
Posts: 10,483

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

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.

Respected Advisor
Posts: 4,641

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

You were almost there. Try :

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

BY Customer_Id;

VAR Sales;

RANKS Rank;

RUN;


PG

PG
Occasional Contributor
Posts: 11

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

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

Respected Advisor
Posts: 4,641

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

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

PG
Occasional Contributor
Posts: 10

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

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;

Occasional Contributor
Posts: 11

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

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!

Respected Advisor
Posts: 4,641

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

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.

PG

PG
Ask a Question
Discussion stats
  • 9 replies
  • 587 views
  • 0 likes
  • 5 in conversation