Ranking Task only runs only one way - ASC

[Oscarboots]

Hi Forum,

I'm using SAS EG 5.1 (64 bit) on Windows 7.

I'm trying to Rank some Order Dates & Customer numbers.

I've sorted the dates in the previous Query Builder & then added a Ranking Task.

I've added the 'Order Dates' to the 'Columns to rank' area of the Rank wizard & 'Customer Number' to the 'Rank By' area.

I then select 'Descending' in the 'Rank By' fields sort order & run the task.

No matter what I do, it always sorts in Ascending order.

I've tried sorting the previous node the other way but no use.

What am I missing here?

Thanks

[Oscarboots]

Can anyone suggest any tutorials on how to use the Rank Task so I can find what I'm doing wrong?

Thanks again.

[ChrisHemedinger · Accepted solution]

The Rank Data task is used to compute a rank across one or more numeric variables in the input data.  Usually you would use this to assign an ordinal rank to a quantitative variable, such as "Sales amount" or "Units Sold".  You would not use it to rank time series data (like Dates) or ID variables (like a Customer ID Number).

The "Rank By" role is for grouping purposes, not for sorting.  For example, if you wanted to compute the rank for Customer Sales in each Country, Customer Sales would be the Rank variable and Country would be the Rank By.  The output would include rank output (1, 2, 3, and so on) for each value of Country within your data set.

The output data is not sorted by rank.  If you need the data sorted, you can feed the output data set into a Sort Data task or the Query Builder.

I'm not sure what your ultimate goal is, but you might be interested in this sample for creating a Top N report in SAS.

Chris

[Oscarboots]

Thanks Chris,

In the end I got around it by coding 'ROW_NUMBER() (Partition by...).

Cheers