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

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Excel Function "Rank.AVG" - SAS Counterpart?

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 11-16-2021 03:30 PM
(649 views)

Friends:

Does SAS have a function that does the work of excel's rank.avg. The work I'm doing using this specific function and I would like to be able to manipulate the data I currently have in SAS without having to cross platforms and re-load the data back into SAS.

Let's say there are ~150 observations in the dataset. Each has unique column observations by year (i.e. _2015_expenditures) with each row being a different department. I want to use the "rank.avg" function to rank these specific departments (in ascending rank order) by their yearly expenditure columns individually into a new variable (that shows the numeric rank.avg values).

Excel's "RANK.AVG" function is summarized as: "Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned."

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

proc rank ....ties=mean;

Does the TIES=MEAN option work for you?

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p16s2o8e4bnqrin1phywxdaxqba7.htm#n128utp...

Does the TIES=MEAN option work for you?

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p16s2o8e4bnqrin1phywxdaxqba7.htm#n128utp...

9 REPLIES 9

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

PROC RANK?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Yes, I realize that function exists.

I think what I'm after is more of a calculation that I can make that does the same work as the procedure, but in a data step instead.

I think what I'm after is more of a calculation that I can make that does the same work as the procedure, but in a data step instead.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Why in a data step?

What differs from passing the input data into the procedure instead of doing it manually?

You can do it manually but that requires sorting which can be time intensive and you need to manually handle ties. Even Excel requires an ARRAY for this type of calculation because you need the whole data set to process this accurately.

What differs from passing the input data into the procedure instead of doing it manually?

You can do it manually but that requires sorting which can be time intensive and you need to manually handle ties. Even Excel requires an ARRAY for this type of calculation because you need the whole data set to process this accurately.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

To answer your first question: Stubbornness, I suppose. I'll gladly pursue the proc rank approach if I can get it to do what I need. Thank you for your suggestion.

I need, essentially, 50 columns of data (10 columns x five years total). Each of the data points are critical for their own purposes as I intend to index them later. There are 10 total measures over the course of five years. There's a 131 total departments (row data). The column data is what I need ranked.

Currently, my proc rank would look something like this?

```
proc rank data=have out=want descending;
var _2015_se_expend /*_2016_se_expend _2017_se_expend _2018_se_expend _2019_se_expend*/;
ranks _2015_rankavg_se_expend;
```

run;

When I run this, my 2015 column data is ranked correctly. My question then, at this point, is how do I get the commented variables into the mix to have those variables ranked by year in the same manner as 2015? I need all of the annual column data ranked just as I did the first.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Please post an excerpt of the data you have in usable form, so that we don't have to guess what might work in your case.

When i see variables named like _2015_se_expend, _2016_se_expend, _2017_se_expend ... i always tend to fix the miss-shaped data first. Then you could use something like

```
proc rank data= work.transposed out=work.ranked descending;
by year;
var se_expend;
ranks se_expand_ranked;
run;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

If you do not want to use the very sensible suggestion from @andreas_lds (add a YEAR variable and reduce the number of variables to be ranked), it should be quite easy to do what you want like this:

```
proc rank data=have out=want descending;
var _2015_se_expend _2016_se_expend _2017_se_expend _2018_se_expend
_2019_se_expend;
ranks _2015_rankavg_se_expend _2016_rankavg_se_expend _2017_rankavg_se_expend _2018_rankavg_se_expend _2019_rankavg_se_expend;
run;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks.

Last thing, I think:

In proc rank, is there a way to assign all "ties" with the average value of the group followed by the next available number? For example, the seven numbers 10, 8, 8, 4, 4, 4, 2 would be ranked 7, 5.5, 5.5, 3, 3, 3, 1?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Does the TIES=MEAN option work for you?

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p16s2o8e4bnqrin1phywxdaxqba7.htm#n128utp...

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.