Rank Tie Issue

[sasspan]

I am trying to rank claimant's doctors in the order they received prescriptions from them.  Some claims have fills from different doctors on the same day.  This forces the rank to tie.  Example:  Patient A fills a prescription from Doctor A and B on the same day, and doctor C on the following day.  My rank would give 1.5, 1.5, and 2 respectively.  I would like to add an additional requirement to the rank such as how much they spent with the doctor in order to achieve 1, 2, and 3.  What I need to know is what number doctor this is for them in order of dates of service, when dates of service tie, I want the higher total spend doctor going last.  All of this needs to happen without affecting the other ranks.  So if I have several doctors with ranks 1, 2, 3.5 3.5, 4, 5.  I need this to show up as 1, 2, 3 ,4 ,5, 6 based off the billed amount on the tied doctors.

Sorry for rambling, rough question to ask via a forum.  Hopefully, one of you can make sense of what I'm saying.

[Reeza]

Your question makes sense (sort of), but you'll need to convert your requirements to tech speak .

What does your data look like and what's your issue in terms of SAS.  How are you assigning the ranks currently would help in determining how to change them as well.

[sasspan]

Right now I have a dataset unqiue by CL_ID AND DEA_ID.  The table consists of  CL_ID DEA_ID MIN_DOS SUM_BILL_AMT  I am ranking DOS by CL_ID in order to know the order the doctors were visited.  My issue is when the DOS are the same for different DEA_ID I am getting ties.  I need a way in SAS Enterprise guide to assign these tied values a different value.  Is there a way to rank under some given conditions?  While I consider myself a heavy EG user, I cannot convert this to code speak :-).  Does that clear some things up?

[ChrisHemedinger]

I'm assuming that you're looking to avoid writing code for this step, so I'll suggest some non-code approaches.

You may be able to do the entire thing in Query Builder, if you have EG 5.1  (or even if you don't).

In EG 5.1, you can use subqueries to compare values in data records to aggregated columns (sums and averages, for example).  Lots of detail in this paper:

http://support.sas.com/resources/papers/proceedings12/292-2012.pdf

Or another approach that's multiple steps:

- Create a query that SUMs all of the bill amounts, grouped by CL_ID (your doctors).

- Create a numbered ranking of CL_ID by this value.

- Join that ranking back with the original detail data

- Use that second ranking field as a sort/rank value for your tie-breaker cases when multiple docs are visited on a single day.

Chris

[sasspan]

This combined with a case statement on SUBSTR(PUT(RANK_DOS,...................... should get me there.

Thanks!