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!
I don't think you need proc rank.
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;
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 ?
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.
You were almost there. Try :
PROC RANK DATA = T1 OUT= T2 DESCENDING TIES = LOW;
BY Customer_Id;
VAR Sales;
RANKS Rank;
RUN;
PG
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
I didn't test, but yes, it should. Try it!
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;
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!
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
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.
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.