BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
oriti
Fluorite | Level 6

Hello, all,

I'm trying to find in SAS/IML function that :

Returns the rank of a value in a data set as a percentage of the data set,

This function can be used to evaluate the relative standing of a value within a data set.

For example, you can use the function  to evaluate the standing of an aptitude test score among all scores for the test.

In Excel there is the PERCENTRANK that do that.

The Rank function just creates a new matrix containing elements that are the ranks of the corresponding elements of matrix. So I cant evaluate new value.

Thanks!!

Orit

1 ACCEPTED SOLUTION

Accepted Solutions
Rick_SAS
SAS Super FREQ

I'd allocate a row vector to hold the results and use a DO loop over the columns.

View solution in original post

6 REPLIES 6
Rick_SAS
SAS Super FREQ

Could you provide a small example of data and the results that you consider correct?

ballardw
Super User

Does it have to be in IML? Proc Rank might do what you need.

oriti
Fluorite | Level 6

Thank you for your reply. It will be easier to write a code if IML has a function that do that.

I need an output of vector with results.

I need to evaluate the 'xvalues' from the simulation result

Enclosed two results (from 'PERCENTRANK' in Excel)

I would really appreciate if there is an efficient way to do that in SAS.

Thanks!

Orit

xvalue-17.8710.38566

b0b1
PERCENTRANK from excel0.7120.67

b0b1
Sim1-19.4660.34684
Sim2-20.9350.28048
Sim3-23.5910.23789
Sim4-17.030.41215
Sim5-19.4550.27507
Sim6-19.1120.37149
Sim7-21.9790.24889
Sim8-17.0660.39767
Sim9-22.5430.22619
Sim10-16.2280.41261
Sim11-22.780.28657
Sim12-21.0730.36045
Sim13-23.8830.18773
Sim14-18.5530.37433
Sim15-15.9630.38656
Sim16-18.9740.32481
Sim17-18.3730.35586
Sim18-20.0660.34573
Sim19-16.0910.40736
Sim20-18.9510.3738
Sim21-16.9180.39794
Sim22-17.8190.3412
Sim23-20.1460.3637
Sim24-17.6770.31051
Sim25-20.2020.33014
Sim26-17.9610.40499
Sim27-17.9940.40927
Sim28-20.8040.28163
Sim29-18.910.26595
Sim30-19.1350.30117
Sim31-19.4190.37921
Sim32-22.3660.28681
Sim33-15.6810.4264
Sim34-18.0370.38472
Sim35-16.070.4479
Sim36-17.2260.38755
Sim37-18.1290.41324
Sim38-19.2630.35744
Sim39-20.0530.30839
Sim40-19.0320.39052
Sim41-20.050.30791
Sim42-21.2280.23211
Sim43-17.1220.40638
Sim44-17.1170.40645
Sim45-18.8550.31151
Sim46-18.2760.33163
Sim47-14.6830.47646
Sim48-19.8880.34165
Sim49-18.2830.36015
Rick_SAS
SAS Super FREQ

I create a preliminary attempt, but I don't match your results for your simulation data. Still, this might help you get started in the right direction. I have to run to a meeting, so I didn't have time to comment the code.

HTH

proc iml;
start PercentRankInList(x, target);  /* pct rank when target value is in list */
   NLower = ncol(loc(x<target));
   NUpper = ncol(loc(x>target));
   return( NLower/(NLower+NUpper));
   return( pr );
finish;

start PercentRank(x, target); /* pct rank whether or not target value is in list */
   idx = loc(x=target);
   if ncol(idx)>0 then              /* target value is in list */
      return(PercentRankInList(x, target));

   idxLower = loc(x<target);
   idxUpper = loc(x>target);
   x0 = max(x[idxLower]);
   pr0 = PercentRankInList(x, x0);  /* x0 is in list */
   x1 = min(x[idxUpper]);
   pr1 = PercentRankInList(x, x1); /* x1 is in list */
   f = (target-x0)/(x1-x0);               /* target is fraction f between x0 and x1 */
   return( pr0 + f*(pr1-pr0) );         /* interpolate */
finish;

/* test on examples at
http://office.microsoft.com/en-us/excel-help/percentrank-HP005209212.aspx
*/
/*
x = {13,12,11,8,4,3,2,1,1,1};
print (PercentRank(x, 2));
print (PercentRank(x, 4));
print (PercentRank(x, 8));
print (PercentRank(x, 5));
*/

/* test on simulation example data */

use PercentRank; read all var {b0 b1}; close PercentRank;

print (PercentRank(b0, -17.871));
print (PercentRank(b1, 0.38566));

oriti
Fluorite | Level 6

Thank you Rick!

But what would you do if the matrix of x (as in your example) is not N*1 but N*M

and the Target is 1*M vector (and not a scalar)

and I need to print a result vector (PercentRank vector) of 1*M

I would really appreciate your answer.

Thanks!

Orit

Rick_SAS
SAS Super FREQ

I'd allocate a row vector to hold the results and use a DO loop over the columns.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 6 replies
  • 1239 views
  • 4 likes
  • 3 in conversation