handling missing data with ifn function in proc sql

[braam]

In the below code, ifn function doesn't work as I expected. Can I assign missing (.) to HigherThan20 if mpg_city is missing?

I would like to use PROC SQL for this, though I know there is a way to do so when using data step.

data temp;
	set sashelp.cars;
	if mpg_city= 22 then mpg_city= .; run;

proc sql;
	create table temp
	as select a.type, a.mpg_city, ifn(mpg_city>20, 1, 0, .) as HigherThan20
	from temp a; quit;
proc print data= temp(obs= 20); run;

[Krueger]

The problem is your logical expression results in a true/false regardless if mpg_city is missing or not. In this case it returns as false. 

I'm unsure if this is the correct or best approach but it's the first solution that came to mind.

proc sql;
	create table temp1
	as select a.type, a.mpg_city, ifn(mpg_city>20, 1, ifn(mpg_city is missing, ., 0, 0), .) as HigherThan20
	from temp a; quit;

I do think a CASE statement would be more appropriate here but I come from a SQL background.  

[Tom]

proc sql;
select type, mpg_city
,case when (not missing(mpg_city)) then (mpg_city > 20) end as HigherThan20
from temp 
; 
quit;

Not sure why you want to use IFN() if you want to use SQL.  Use CASE instead.

Boolean expressions return 1 when true and 0 when false. They never return a missing value. You will have to test for missing with your own code. 

[novinosrin · Accepted solution]

Hi @braam  You need a CASE WHE construct and here is a traditional way of writing a CASE WHEN THEN ELSE END construct

data temp;
	set sashelp.cars;
	if mpg_city= 22 then mpg_city= .; 
run;

proc sql;
create table temp_want as 
select a.type, a.mpg_city,case when  mpg_city>20 then 1 
when  .< mpg_city<=20 then 0 else . end as  HigherThan20
from temp a; 
quit;

proc print noobs;
run;

[ChrisNZ]

>  ifn(mpg_city>20, 1, 0, .) as HigherThan20

You are making up the function's syntax as you go, aren't you? 

This isn't Excel. Read the documentation.

Does this do what you want?

ifn( missing(MPG_CITY), . , MPG_CITY>20 )