Excel Function "Rank.AVG" - SAS Counterpart?

[eer_seer]

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!

[Reeza]

PROC RANK?

[eer_seer]

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.

[ballardw]

Proc rank with a By Year, or what ever your variable name is, is likely a much better way then attempting by data step. For one thing there are multiple ways to address ties.

[Reeza]

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.

[eer_seer]

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.

[andreas_lds]

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;

[s_lassen]

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;

[eer_seer]

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?

[Reeza · Accepted solution]

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...