Missing in proc sql

[Thalitacosta]

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;

[ballardw · Accepted solution]

---

@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 ...

[Tom]

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.

[tarheel13]

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