DATA Step, Macro, Functions and more

Using IFN and IFC in SQL

Accepted Solution Solved
Reply
Regular Contributor
Posts: 210
Accepted Solution

Using IFN and IFC in SQL

[ Edited ]

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 ? 


Accepted Solutions
Solution
‎12-27-2017 08:41 AM
Regular Contributor
Posts: 210

Re: Using IFN and IFC in SQL

[ Edited ]

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;

View solution in original post


All Replies
Super User
Posts: 5,829

Re: Using IFN and IFC in SQL

Posted in reply to DmytroYermak

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.

Data never sleeps
Solution
‎12-27-2017 08:41 AM
Regular Contributor
Posts: 210

Re: Using IFN and IFC in SQL

[ Edited ]

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 231 views
  • 0 likes
  • 2 in conversation