turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Ranking Task only runs only one way - ASC

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-03-2014 08:44 PM

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

Accepted Solutions

Solution

12-04-2014
07:47 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-04-2014 07:47 AM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-04-2014 02:50 AM

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

Thanks again.

Solution

12-04-2014
07:47 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-04-2014 07:47 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-06-2014 09:34 PM

Thanks Chris,

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

Cheers