BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DmytroYermak
Lapis Lazuli | Level 10

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 ? 

1 ACCEPTED SOLUTION

Accepted Solutions
DmytroYermak
Lapis Lazuli | Level 10

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

2 REPLIES 2
LinusH
Tourmaline | Level 20

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
DmytroYermak
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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