Statistical programming, matrix languages, and more

Evaluate the standing of an aptitude test score among all scores for the test

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Evaluate the standing of an aptitude test score among all scores for the test

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


Accepted Solutions
Solution
‎12-19-2013 06:08 AM
SAS Super FREQ
Posts: 3,420

Re: Evaluate the standing of an aptitude test score among all scores for the test

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

View solution in original post


All Replies
SAS Super FREQ
Posts: 3,420

Re: Evaluate the standing of an aptitude test score among all scores for the test

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

Grand Advisor
Posts: 10,251

Re: Evaluate the standing of an aptitude test score among all scores for the test

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

Contributor
Posts: 39

Re: Evaluate the standing of an aptitude test score among all scores for the test

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
SAS Super FREQ
Posts: 3,420

Re: Evaluate the standing of an aptitude test score among all scores for the test

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));

Contributor
Posts: 39

Re: Evaluate the standing of an aptitude test score among all scores for the test

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

Solution
‎12-19-2013 06:08 AM
SAS Super FREQ
Posts: 3,420

Re: Evaluate the standing of an aptitude test score among all scores for the test

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 521 views
  • 4 likes
  • 3 in conversation