Help using Base SAS procedures

Using proc rank in a multiple group by

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Using proc rank in a multiple group by

Hello All,

 

I am trying to rank some data using Proc Rank and a group by clause but to be honest I don't even know where to start.

 

1. The 4 ranking columns should use the  following group by clauses to define their ranking

2. The ranking should stay the same if the group by clause returns the exact same result 

 

rank_ftp > group by system_id, reporting_date, ftp

rank_preprocess > group by system_id, reporting_date, preprocess

rank_calculation > group by system_id, reporting_date, calculation

rank_synchronisation > group by system_id, reporting_date, synchronisation

 

Ranking help.JPG

 

Until now I came up with this (for the ftp part), but it gives me rank 0 for everything.

proc sort data=WORK.LFS_VAA_BC_TMP out=work.sorted;
   by system_id reporting_date;
run;


proc rank data=WORK.sorted out=work.rankings;
   var   ftp;
   ranks rank_ftp;
   by  system_id reporting_date;
run;

Thanks in advance.

 

 


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 23,776

Re: Using proc rank in a multiple group by

Posted in reply to MihaiViju

That sounds like enumeration rather than ranking.

Enumeration is when you count records to identify groups. 

Ranking is grouping data based on a variables value so that the top 10 are together for example.

 

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

 

 

View solution in original post


All Replies
Super User
Posts: 23,776

Re: Using proc rank in a multiple group by

Posted in reply to MihaiViju

Can you include some data so we can replicate your issue?

 


@MihaiViju wrote:

 

 

Ranking help.JPG

 

Until now I came up with this (for the ftp part), but it gives me rank 0 for everything. 

 


 

Your results also show a rank of 1/2 so I'm confused. SAS does the ranks from 0 to the number of groups. You didn't specify a ranking methodology either, what type of ranks do you want to calculate? Break it into deciles, quartlies, groups of 3?

 

 

New Contributor
Posts: 3

Re: Using proc rank in a multiple group by

I added the data in the excel sheet. If you remove the ranking columns then you have the data I am working on.

 

A bit of background story:

 

This is an ETL processing street framework. Basicaly a DSR file comes in via ftp then is preprocessed then calculated and then synchronized. For every failed attempt to load the file, a new record is created with the new attempt. The failure can occur anywhere in the chain. So in my case I have rank 1 everywhere for ftp because I am always using the same file. If a new file should be delivered then the ranking will be 2 (as long as the system_id and reporting_date are the same).

 

My unique identifiers are the system_id and reporting_date. The ranking should start again from 1 when a new file is delivered or a new reporting_date is encountered.

Solution
2 weeks ago
Super User
Posts: 23,776

Re: Using proc rank in a multiple group by

Posted in reply to MihaiViju

That sounds like enumeration rather than ranking.

Enumeration is when you count records to identify groups. 

Ranking is grouping data based on a variables value so that the top 10 are together for example.

 

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 97 views
  • 0 likes
  • 2 in conversation