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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.