topic Using IFN and IFC in SQL in SAS Programming

Using IFN and IFC in SQL

DmytroYermak — Wed, 27 Dec 2017 11:24:32 GMT

Hi all,

May I use IFN and IFC in SQL?

I receive the following error when I run the code:

Code:

proc sql;
  create table aphase as 
  select USUBJID,
  		 ifn(length(SESTDTC)>10, input(SESTDTC,is8601dt.), input(SESTDTC, is8601dn.)) as PH1SDT
  from se
  where TAETORD in (4)
  order by USUBJID;
quit;

Error:

NOTE: Invalid date value
NOTE: Invalid argument to function INPUT. Missing values may be generated.
NOTE: Table WORK.APHASE11 created, with 44 rows and 2 columns.

14580  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds

Or, possibly it is a problem with is8601dn. format ?

Re: Using IFN and IFC in SQL

LinusH — Wed, 27 Dec 2017 11:45:10 GMT

IFN/IFC is supported, but your log message is not about that, is about invalid date values.

Also, not sure if IFN/IFC functions gain you something in SQL, where you have CASE.

Re: Using IFN and IFC in SQL

DmytroYermak — Wed, 27 Dec 2017 12:32:03 GMT

I had no choice but to change on 'case when':

proc sql;
  create table aphase as 
  select USUBJID,
  		 case when length(SESTDTC)>10 then datepart(input(SESTDTC,is8601dt.))
			  else datepart(input(SESTDTC, is8601dn.))
			  end as PH1SDT
  from se
  where TAETORD in (4)
  order by USUBJID; 

quit;