turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-17-2013 02:48 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to oriti

12-19-2013 06:08 AM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to oriti

12-17-2013 06:08 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to oriti

12-17-2013 02:26 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to oriti

12-17-2013 03:23 PM

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 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to oriti

12-18-2013 12:02 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to oriti

12-19-2013 05:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to oriti

12-19-2013 06:08 AM

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