topic Evaluate the standing of an aptitude test score among all scores for the test in SAS/IML Software and Matrix Computations

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

oriti — Tue, 17 Dec 2013 07:48:52 GMT

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

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

Rick_SAS — Tue, 17 Dec 2013 11:08:21 GMT

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

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

ballardw — Tue, 17 Dec 2013 19:26:08 GMT

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

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

oriti — Tue, 17 Dec 2013 20:23:26 GMT

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

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

Rick_SAS — Wed, 18 Dec 2013 17:02:21 GMT

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

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

oriti — Thu, 19 Dec 2013 10:33:24 GMT

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

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

Rick_SAS — Thu, 19 Dec 2013 11:08:02 GMT

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