Case when to comparing NULL values

[Babloo]

Below case when is not working when both the fields discharge_date and admit_date is NULL. If the values of both the fields are NULL then I want to display it as NULL in the field ADMIT_DISCHARGE but it's showing as '1'. Any help to correct this CASE WHEN or would like to know how to simplify it using with any other ways in SQL?

Both discharge_date and admit_date is character datatype

SELECT DISCHARGE_DATE, ADMIT_DATE, 
CASE WHEN DISCHARGE_DATE IS NULL THEN NULL 
WHEN ADMIT_DATE IS NULL THEN NULL
WHEN DISCHARGE_DATE IS NULL AND ADMIT_DATE IS NULL THEN NULL
WHEN DISCHARGE_DATE = ADMIT_DATE THEN '1'
ELSE DISCHARGE_DATE - ADMIT_DATE END AS ADMIT_DISCHARGE

[PaigeMiller]

Is this SQL done in a passthru to ORACLE or other data base? Or is it just plain SAS SQL?

Can you please provide sample data (even if it is fake) to illustrate the problem?

[Babloo]

Plain SAS SQL.

May be it's an empty string both admit and discharge date fields causing
the issue.

[Kurt_Bremser]

In SAS, dates are NOT strings, unless someone fucked up big time. Dates are numbers.

[Kurt_Bremser]

case
  when discharge_date = . or admit_date = . then .
  when discharge_date = admit_date and discharge_date ne . then 1 
  else discharge_date - admit_date
end as admit_discharge

[rvikram]

Hi! 

You are mixing character and numeric assignments. 

In one case when you have '1' and the other you are assigning it as 

DISCHARGE_DATE - ADMIT_DAT

which is numeric. 

Please check. You may just assign 1 instead of '1' here :

WHEN DISCHARGE_DATE = ADMIT_DATE THEN '1'

[Tom]

SAS should tell you explicitly what is wrong.

Let's try it.

data have;
  input (admit_date discharge_date) (:date);
  format admit_date discharge_date date9.;
cards;
. .
. 01JAN2023
01JAN2023 .
01JAN2023 01JAN2023
01JAN2023 02JAN2023
;

proc sql;
select *
 , CASE WHEN DISCHARGE_DATE IS NULL THEN NULL 
        WHEN ADMIT_DATE IS NULL THEN NULL
        WHEN DISCHARGE_DATE IS NULL AND ADMIT_DATE IS NULL THEN NULL
        WHEN DISCHARGE_DATE = ADMIT_DATE THEN '1'
        ELSE DISCHARGE_DATE - ADMIT_DATE 
  END AS ADMIT_DISCHARGE
from have
;
quit;

191  proc sql;
192  select *
193   , CASE WHEN DISCHARGE_DATE IS NULL THEN NULL
194          WHEN ADMIT_DATE IS NULL THEN NULL
195          WHEN DISCHARGE_DATE IS NULL AND ADMIT_DATE IS NULL THEN NULL
196          WHEN DISCHARGE_DATE = ADMIT_DATE THEN '1'
197          ELSE DISCHARGE_DATE - ADMIT_DATE
198    END AS ADMIT_DISCHARGE
199  from have
200  ;
ERROR: Result of WHEN clause 5 is not the same data type as the preceding results.
ERROR: The following columns were not found in the contributing tables: ADMIT_DATE, DISCHARGE_DATE, NULL.
201  quit;
NOTE: The SAS System stopped processing this step because of errors.

So the first three WHEN generate NULL (which it probably not valid there. I suspect SAS is looking for a variable named NULL).  The fourth generates a character string.  And the fifth generates a number.

Change the NULL to the actual missing value you want. Change the '1' to just 1 so it is also a number.  (Note this means you cannot tell the different between a discharge on the same date and one where they are discharged the next day.)

Let's make an example that demonstrates how you might want to do this.  Let's include some data with 1 day and 2 day stays. So data with invalid sequence of dates.  And let's include a variable named NULL so we can see what happens when trying the use NULL as a value instead of just a keyword.

data have;
  input (admit_date discharge_date) (:date.);
  format admit_date discharge_date date9.;
  null=.n;
cards;
. .
. 01JAN2023
01JAN2023 .
01JAN2023 01JAN2023
01JAN2023 02JAN2023
02JAN2023 01JAN2023
;

proc sql;
select *
 , CASE WHEN DISCHARGE_DATE IS NULL THEN null 
        WHEN ADMIT_DATE IS NULL THEN null
        WHEN DISCHARGE_DATE >= ADMIT_DATE THEN (DISCHARGE_DATE - ADMIT_DATE)+1
        ELSE .E
  END AS ADMIT_DISCHARGE
from have
;
quit;

Results

              discharge_                  ADMIT_
admit_date          date      null     DISCHARGE
------------------------------------------------
         .             .         N             N
         .     01JAN2023         N             N
 01JAN2023             .         N             N
 01JAN2023     01JAN2023         N             1
 01JAN2023     02JAN2023         N             2
 02JAN2023     01JAN2023         N             E

Or perhaps you want to distinguish between all of the possible ways that that make it impossible to calculate length of stay.

proc sql;
select *
 , CASE WHEN DISCHARGE_DATE IS NULL and ADMIT_DATE IS NULL THEN .B 
        WHEN DISCHARGE_DATE IS NULL THEN .D
        WHEN ADMIT_DATE IS NULL THEN .A
        WHEN DISCHARGE_DATE >= ADMIT_DATE THEN (DISCHARGE_DATE - ADMIT_DATE)+1
        ELSE .E
  END AS ADMIT_DISCHARGE
from have
;
quit;

Result

              discharge_                  ADMIT_
admit_date          date      null     DISCHARGE
------------------------------------------------
         .             .         N             B
         .     01JAN2023         N             A
 01JAN2023             .         N             D
 01JAN2023     01JAN2023         N             1
 01JAN2023     02JAN2023         N             2
 02JAN2023     01JAN2023         N             E

[Reeza]

Do the fields have the word NULL or are they blank? I ask because you say they're character and want to display it as NULL but that's not really something SAS does, NULL in SAS is essentially blank/empty.