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

Showing results for

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 12-17-2013 02:48 AM
(1106 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.