06-26-2015 03:24 AM
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;
Thanks in advance!
06-26-2015 09:22 AM
I don't think you need proc rank.
input Customer_Id Plan_Name $;
by Customer_Id ;
if first.Customer_Id then rank=0;
06-28-2015 10:20 PM
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 ?
06-26-2015 11:15 AM
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.
06-29-2015 02:06 AM
This will help you to reach to your desire result:
Please Try this
proc sort data=T1;
PROC RANK DATA=T1 out=T2 DESCENDING TIES = LOW ;
06-29-2015 11:06 AM
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.
CREATE TABLE INTMD_PLAN1 AS
Month7_TotalUnits_Junior,Month8_TotalUnits_Junior,Month9_TotalUnits_Junior,Month10_TotalUnits_Junior,Month11_TotalUnits_Junior,Month12_TotalUnits_Junior) AS Total_Units_Junior
WHERE SRA_1 IS NOT NULL
GROUP BY SRA_1,Plan_Name;
Thanks in advance!
06-29-2015 02:48 PM
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.