topic Re: handling missing data with ifn function in proc sql ifn( missin(MPG_CITY), . , MPG_CITY>20 ) in SAS Programming

handling missing data with ifn function in proc sql

braam — Mon, 24 Feb 2020 23:11:30 GMT

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;

Re: handling missing data with ifn function in proc sql

Krueger — Mon, 24 Feb 2020 23:28:00 GMT

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.

Re: handling missing data with ifn function in proc sql

Tom — Mon, 24 Feb 2020 23:53:47 GMT

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.

Re: handling missing data with ifn function in proc sql

novinosrin — Tue, 25 Feb 2020 00:23:12 GMT

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;

Re: handling missing data with ifn function in proc sql ifn( missin(MPG_CITY), . , MPG_CITY>20 )

ChrisNZ — Tue, 25 Feb 2020 01:58:18 GMT

> 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 )