BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasspan
Obsidian | Level 7

Hello All - 

 

I'll try to explain this one the best I can but I fear it may come across wonky over text.  I have a program I have created that generates a "risk score" which is the total number of triggers hit and some weighting.  A trigger is a specific pharmacy behavior in this instance.  The program I have now just assigns a value of 1 to each trigger and aggregates them to get the final score.  What I need to figure out how to code is say we have the use of a product A as a trigger with a maximum value of 15.  However, for a good reason, we do not want the first appearance of this action to add 15 points to the overall score.  Say the first appearance we want 5, then if this product is paid for again in the following month (this will run on a monthly basis) the score should be 10, and if it is bought a third month the score tops out at 15 and will remain 15 no matter how many more consecutive months the product is paid for.  Now, the same thing goes for a month where the product is not purchased.  Say the product was purchased two months in a row, if the following month comes and it is not purchased, we want the total number of points this product adds to the score to be 5.  

 

So essentially, I need to incrementally raise and lower the point value this variable adds to my score based off prior months' runs of the program.  Previous months runs can be stored on a monthly basis if needed, but we could also just recalculate previous runs' scores each run.  Any help on how to do this would be greatly appreciated.  

 

Thanks,

 

Andrew 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

In general, you're going to get vague answers unless you posts sample data. Sample data does not need to be real data but it should mimic your structure and complexity. 

 

So, the vague answer. 

 

1. Use BY processing to identify new users in groups.

2. Use LAG() to get the previous value. 

3. Use RETAIN to keep a value across rows 

4. IFN() can likely do what you need. 

 

score = ifn( < condition>, /*condition based on previous value and other logic*/
max(score+5, 15) , /*increment to max of 15*/
score - 1 /*decrement score*/
)

Also:

1. Are you guaranteed to have continuous data? If not, how should that be dealt with?

2. Are you caclulating from scratch each time or 'adding' in new data with the appropriate calculation

3. Do you need to retain previous scores?

4. Is the data subject to revision, if so, #2 is really important

5. Does efficiency matter, this can be relatively easy to program but if you have tens of millions you may want a more efficient approach. 

View solution in original post

5 REPLIES 5
Astounding
PROC Star

You will need to describe your data further.  Too much of the programming depends on that.

 

Do you have a separate data set for each month?

 

What is in a single observation?  Just one trigger, or just one person with many triggers? 

 

Did I understand the rule correctly, that absence of a trigger for a month lowers the score going forward?

sasspan
Obsidian | Level 7

Do you have a separate data set for each month? - I was thinking of just having one final dataset and saving over it each month

 

What is in a single observation?  Just one trigger, or just one person with many triggers? the final data set is at the claim level (one person) with many triggers (now 0/1 but instead of 1, want the point values going forward) However, to get to the final table, there's transactional level data - 1 prescription = 1 observation

 

Did I understand the rule correctly, that absence of a trigger for a month lowers the score going forward? Yes, to 0 being the minimum 

Reeza
Super User

In general, you're going to get vague answers unless you posts sample data. Sample data does not need to be real data but it should mimic your structure and complexity. 

 

So, the vague answer. 

 

1. Use BY processing to identify new users in groups.

2. Use LAG() to get the previous value. 

3. Use RETAIN to keep a value across rows 

4. IFN() can likely do what you need. 

 

score = ifn( < condition>, /*condition based on previous value and other logic*/
max(score+5, 15) , /*increment to max of 15*/
score - 1 /*decrement score*/
)

Also:

1. Are you guaranteed to have continuous data? If not, how should that be dealt with?

2. Are you caclulating from scratch each time or 'adding' in new data with the appropriate calculation

3. Do you need to retain previous scores?

4. Is the data subject to revision, if so, #2 is really important

5. Does efficiency matter, this can be relatively easy to program but if you have tens of millions you may want a more efficient approach. 

sasspan
Obsidian | Level 7

I'm going to run the code once, and create a history table.  Then I'll join to this history table each month.  Add 5, unless adding 5 exceeds 15 then 15 for when the activity occurs.  Subtract 5 unless subtracting is less than 0 then 0 for when it does not occur.  I was over complicating this in my head.  Sorry, I'll except Reeza's response as he's basically there.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1142 views
  • 0 likes
  • 3 in conversation