topic Re: Variable changes based on previous value in SAS Enterprise Guide

Variable changes based on previous value

sasspan — Wed, 24 Jan 2018 18:59:08 GMT

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

Re: Variable changes based on previous value

Astounding — Wed, 24 Jan 2018 19:14:54 GMT

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?

Re: Variable changes based on previous value

sasspan — Wed, 24 Jan 2018 19:20:06 GMT

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

Re: Variable changes based on previous value

Reeza — Wed, 24 Jan 2018 19:44:21 GMT

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.

Re: Variable changes based on previous value

sasspan — Wed, 24 Jan 2018 20:11:39 GMT

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.

Re: Variable changes based on previous value

Reeza — Wed, 24 Jan 2018 20:13:09 GMT

She.