topic Re: CASE WHEN with date filter in SAS Enterprise Guide

CASE WHEN with date filter

bhca60 — Mon, 06 Jan 2025 21:47:05 GMT

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

Re: CASE WHEN with date filter

PaigeMiller — Mon, 06 Jan 2025 22:09:24 GMT

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.

Re: CASE WHEN with date filter

Tom — Mon, 06 Jan 2025 23:18:16 GMT

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

Re: CASE WHEN with date filter

bhca60 — Mon, 06 Jan 2025 23:31:46 GMT

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

Re: CASE WHEN with date filter

bhca60 — Tue, 07 Jan 2025 15:36:55 GMT

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?