topic Re: Missing in proc sql in SAS Procedures

Missing in proc sql

Thalitacosta — Tue, 24 Aug 2021 14:08:23 GMT

The code below is returning 0 for all values below 0, including missing values. How to count missing values separately?

proc sql;

create table xxx as

select *,

case when pd < 0 then 0 else

case when pd>= 1.3220535 then 1.3220535 else pd end end as pd_new*/

from tab

;

quit;

Re: Missing in proc sql

ballardw — Tue, 24 Aug 2021 14:14:40 GMT

@Thalitacosta wrote:

The code below is returning 0 for all values below 0, including missing values. How to count missing values separately?

proc sql;

create table xxx as

select *,

case when pd < 0 then 0 else

case when pd>= 1.3220535 then 1.3220535 else pd end end as pd_new*/

from tab

;

quit;

Rule number 1: Missing is less than any value.

If you want to detect missing then use the MISSING function when missing(pd) then ...

Re: Missing in proc sql

Tom — Tue, 24 Aug 2021 14:23:22 GMT

You seem to have too many CASE keywords in your code. You only need one CASE to handle this.

You can use the MISSING() function to test for missing.

case when missing(pd) then . 
     when pd < 0 then 0 
     when pd>= 1.3220535 then 1.3220535 
     else pd 
end

Or use MIN() and MAX() functions.

case when not missing(pd) then max(0,min(pd,1.3220535)) else . end

Note also that the largest missing value is .Z so you could make sure the value is larger than that.

Re: Missing in proc sql

tarheel13 — Mon, 06 Sep 2021 19:41:39 GMT

do case when .z < pd < 0 instead of just < 0