topic missing values with . in SAS Procedures

missing values with .

tmm — Tue, 28 Feb 2012 20:34:53 GMT

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

missing values with .

Hima — Tue, 28 Feb 2012 20:37:24 GMT

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

missing values with .

Astounding — Tue, 28 Feb 2012 20:48:21 GMT

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.

missing values with .

Ksharp — Wed, 29 Feb 2012 02:21:48 GMT

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

missing values with .

PGStats — Wed, 29 Feb 2012 02:40:13 GMT

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.

missing values with .

tmm — Wed, 29 Feb 2012 15:40:45 GMT

Thanks