Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
SAS Super FREQ

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.

6 REPLIES 6
SAS Super FREQ

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?

Super User

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.

Fluorite | Level 6

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

 b0 b1 PERCENTRANK from excel 0.712 0.67

 b0 b1 Sim1 -19.466 0.34684 Sim2 -20.935 0.28048 Sim3 -23.591 0.23789 Sim4 -17.03 0.41215 Sim5 -19.455 0.27507 Sim6 -19.112 0.37149 Sim7 -21.979 0.24889 Sim8 -17.066 0.39767 Sim9 -22.543 0.22619 Sim10 -16.228 0.41261 Sim11 -22.78 0.28657 Sim12 -21.073 0.36045 Sim13 -23.883 0.18773 Sim14 -18.553 0.37433 Sim15 -15.963 0.38656 Sim16 -18.974 0.32481 Sim17 -18.373 0.35586 Sim18 -20.066 0.34573 Sim19 -16.091 0.40736 Sim20 -18.951 0.3738 Sim21 -16.918 0.39794 Sim22 -17.819 0.3412 Sim23 -20.146 0.3637 Sim24 -17.677 0.31051 Sim25 -20.202 0.33014 Sim26 -17.961 0.40499 Sim27 -17.994 0.40927 Sim28 -20.804 0.28163 Sim29 -18.91 0.26595 Sim30 -19.135 0.30117 Sim31 -19.419 0.37921 Sim32 -22.366 0.28681 Sim33 -15.681 0.4264 Sim34 -18.037 0.38472 Sim35 -16.07 0.4479 Sim36 -17.226 0.38755 Sim37 -18.129 0.41324 Sim38 -19.263 0.35744 Sim39 -20.053 0.30839 Sim40 -19.032 0.39052 Sim41 -20.05 0.30791 Sim42 -21.228 0.23211 Sim43 -17.122 0.40638 Sim44 -17.117 0.40645 Sim45 -18.855 0.31151 Sim46 -18.276 0.33163 Sim47 -14.683 0.47646 Sim48 -19.888 0.34165 Sim49 -18.283 0.36015
SAS Super FREQ

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

Fluorite | Level 6

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

Thanks!

Orit

SAS Super FREQ

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.

From The DO Loop