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;
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;
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.
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.
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;
> 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 )
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.