BookmarkSubscribeRSS Feed
vishal09
Calcite | Level 5

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!

9 REPLIES 9
Ksharp
Super User

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;  
vishal09
Calcite | Level 5

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 ?

ballardw
Super User

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.

PGStats
Opal | Level 21

You were almost there. Try :

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

BY Customer_Id;

VAR Sales;

RANKS Rank;

RUN;


PG

PG
vishal09
Calcite | Level 5

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

PGStats
Opal | Level 21

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

PG
Dalveer
Calcite | Level 5

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;

vishal09
Calcite | Level 5

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!

PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 5064 views
  • 0 likes
  • 5 in conversation