CASE WHEN with date filter

[bhca60]

I am trying to do a CASE WHEN with a date filter. I need to add new criteria from 5/6/2024 onward - the new criteria is that we're looking at los >= 20 days instead of rpm_score=.13.  I need to keep the rpm_score=.13 but it should only apply to admdt on or before 5/5/2024.  How do I update this WHEN statement to reflect the new criteria and keep the old criteria so I can use them all in the same WHEN statement? 

        select a.*,b.CMS_ID as HPLAN,
             case
                 when a.RPM_Score >= 0.13 and admdt <="05may2024"d  then 1 
	         when a.los>= 20 and admdt >= "06may2024"d then 1 
		else 0

[PaigeMiller]

Ok, I don't see what is wrong with the code you are showing. (Why does everything have to be in the same WHEN statement?)

Is there an error message? If so show us the log for this PROC SQL (all the lines in the log for PROC SQL, not just the lines with the error). Are you getting incorrect output? If so, please provide (a portion of) the input data and show us the incorrect output.

[Tom · Accepted solution]

I cannot tell from your description what you want.  Providing example input data with the expected result would help a lot to clarify (and you will probably answer your own question just be going through the process of making the example data).

I suspect you wnat to NEST the CASE clauses.

case when missing(admdt) then .
     when admdt <="05may2024"d then
          case when a.RPM_Score >= 0.13 then 1 else 0 end
     else case when a.RPM_Score >= 0.20 then 1 else 0 end
end

[bhca60]

yes except for the last case when it would be los >= 20 then 1 else 0. Thank you!!

[bhca60]

If i wanted to include another date filter such as admtdt >="06may2024"d for when the days_total >=20 (instead of rpm_score >=0.20), where would I put that?