missing values with .

[tmm]

I am doing a case statement to calculate date1 to date2 for admissions. Some of them come back with an elapsed time like 527 or 15 but then others come back with a . because there is no elapsed time. I know SAS puts in a . when you are calculating for numeric and there is no value. I want to code my case statement so it will still calculate the elapsed time but if it finds a . it will make it just a 0. I tried the following but it does not work.

case when admitdt2 is not null and (dischdt1 <> admitdt2) then (admitdt2 - dischdt1) end as time1

case when admitdt3 is not null and (dischdt2 <> admitdt3) then (admitdt3 - dischdt2) end as time2

This code has more cases for all admit and disch dates and goes up to time30. Because some have no numeric return it replaces it with a . because it does not have a value to return.

I want to code in my case that if there is no numeric returned to put a 0 in instead of that .

I thought maybe

case when admitdt2 is not null and (dischdt1 <> admitdt2) then (admitdt2 - dischdt1)  end as time1

if time1 = . then 0

That does not work

[Hima]

case when admitdt2 is not null and (dischdt1 <> admitdt2) then (admitdt2 - dischdt1) else 0 end as time1

[Astounding]

tmmm,

You should be able to do this without a CASE clause, something like:

sum(0, admitdt2-dischdt1) as time1,

sum(0, admitdt3-dischdt2) as time 2

Good luck.

[Ksharp]

I am not sure whether this code could work. not test.

case when admitdt2 is not null and (dischdt1 <> admitdt2) then ifn( admitdt2 - dischdt1=.,0, admitdt2 - dischdt1) end

Ksharp

[PGStats]

The SQL syntax to use when replacing missing values with something else is the COALESCE function:

select coalesce(dischdt2 - admitdt1, 0) as time1,

     coalesce(dischdt4 - admitdt3, 0) as time2, ...

also, since most DBMS have functions equivalent to coalesce, SAS can translate when accessing remote data.

PG

[tmm]

Thanks