Hi everyone,
Sorry if I miss any details for what I'm trying to achieve.
I work with confidential data so I won't be able to post the details here but I'm running into an issue where my case statement is assigning a value to one bucket when it should be a different one.
I have a piece of code that calculates the % of the limit a member met:
( DEDUCTIBLE_MEDICAL / FAMILY_DED ) = MED_PERCENT_DEDUCTIBLE FORMAT PERCENT.
DEDUCTIBLE_MEDICAL = the $ amount someone incurred
FAMILY_DED = a fixed value based on the product the member is in
Then I use this calculated field (MED_PERCENT_DEDUCTIBLE) to use in a case statement:
CASE
WHEN MED_PERCENT_DEDUCTIBLE <= 0 THEN 'No Deductible'
WHEN 0 < MED_PERCENT_DEDUCTIBLE <= .25 THEN '1st Quartile'
WHEN .25 < MED_PERCENT_DEDUCTIBLE <= .50 THEN '2nd Quartile'
WHEN .50 < MED_PERCENT_DEDUCTIBLE <= .75 THEN '3rd Quartile'
WHEN .75 < MED_PERCENT_DEDUCTIBLE < 1 THEN '4th Quartile'
WHEN MED_PERCENT_DEDUCTIBLE >= 1 THEN 'Hit Deductible'
END AS MED_DEDUCTIBLE_STATUS,
For this example I have someone who has a total of $3,000 for their DEDUCTIBLE_MEDICAL and the FAMILY_DED assigned to them is also $3,000. So based on the calculation, the members MED_PERCENT_DEDUCTIBLE is equal to 1. Based on the case statement I would assume that they would be in the bucket "Hit Deductible" but they are being assigned to the "4th Quartile" bucket instead.
Any thoughts on why this could be? My assumption is that the case statement logic needs to be adjusted but I can't figure out what in particular needs to be changed
Any thoughts or advise would be greatly appreciated!
thank you!
Looks like a numeric precision problem to me. The problem percentage calculation is likely very close to 1 but is not exact. Maybe rounding the result will help:
MED_PERCENT_DEDUCTIBLE = round(( DEDUCTIBLE_MEDICAL / FAMILY_DED ), 0.01)
Looks like a numeric precision problem to me. The problem percentage calculation is likely very close to 1 but is not exact. Maybe rounding the result will help:
MED_PERCENT_DEDUCTIBLE = round(( DEDUCTIBLE_MEDICAL / FAMILY_DED ), 0.01)
I wish I could thank you a million times! That worked perfectly
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.