Desktop productivity for business analysts and programmers

NOTE: Invalid (or missing) arguments to the DATEPART function have caused the function to return a

Reply
Regular Contributor
Posts: 168

NOTE: Invalid (or missing) arguments to the DATEPART function have caused the function to return a

Hello,

 

I doing multiple left join in order to create a new table but I getting the following note:

 

NOTE: Invalid (or missing) arguments to the DATEPART function have caused the function to return a missing value.

 

NOTE: SAS threaded sort was used.
NOTE: Invalid (or missing) arguments to the DATEPART function have caused the function to return a missing value.
NOTE: Invalid (or missing) arguments to the TIMEPART function have caused the function to return a missing value.
NOTE: Invalid (or missing) arguments to the DATEPART function have caused the function to return a missing value.
NOTE: Invalid (or missing) arguments to the TIMEPART function have caused the function to return a missing value.
NOTE: Table WORK.PROVNBQT1 created, with 1205036 rows and 45 columns.

 

Despite a note is not an error message is there a way to avoid this note.  The only information that I found on the web is that I might have missing value or wrong format data.

 

I have checked for missing value and I did not find any.  How can I check the date format of table1.dttrnsys because they look all the same?

 

What are the consequences of those missing values on the join?

Thanks in advance for your help

 

Here's my code:

 

proc sql;
create table provnbqt1
as select table1.*, table5.dtcreationVIP, table5.hrcreationVIP, table5.VIP,
table6.clicassure, datepart(table6.started) as dtcreationCLA format yymmdd10., timepart(table6.started) as hrcreationCLA format time8.,
table7.rentecoll, datepart(table7.started) as dtcreationRCO format yymmdd10., timepart(table7.started) as hrcreationRCO format time8.,
table8.web

FROM provnbqt as table1

left join ref_vip as table5
on table1.ACCOUNTNUMBER = table5.ACCOUNT_NUMBER and table1.dttrnsys = table5.dtcreationVIP

left join clicassure as table6

left join rente_coll as table7
on table1.ACCOUNTNUMBER = table7.primaryentityid and datepart(table7.started)<=table1.dttrnsys<=sum(datepart(table7.started),7) and table1.type_lot="QT"

left join SoumEnLigne (where=( PRIMARYENTITYCLASS eq "Quote")) as table8
on table1.POLICYNUMBER = table8.primaryentityid and
datepart(table8.started)<=table1.dttrnsys<=sum(datepart(table8.started),7)

 

left join SoumEnLigne (where =(PRIMARYENTITYCLASS eq "Account")) as table9
on table1.ACCOUNTNUMBER = table9.primaryentityid and
datepart(table9.started)<=table1.dttrnsys<=sum(datepart(table9.started),7) and table1.type_lot="QT"

;
QUIT;

Super User
Posts: 24,004

Re: NOTE: Invalid (or missing) arguments to the DATEPART function have caused the function to return

Check it in a datastep. The errors from the data step indicate which rows are the problematic ones and you can find the issue. 

 

Look at the ?? Option for INPUT() so you can suppress these notes if needed. 

 

 

You have a lot of tables to check though. If you post the full log it may be more informative. 

Super User
Super User
Posts: 8,279

Re: NOTE: Invalid (or missing) arguments to the DATEPART function have caused the function to return

Since you are doing a series of LEFT JOINs any observation where TABLE6 or TABLE7 do not contribute will have missing values for these variables in your selection list.

 

, datepart(table6.started) as dtcreationCLA format yymmdd10.
, timepart(table6.started) as hrcreationCLA format time8.
...
, datepart(table7.started) as dtcreationRCO format yymmdd10.
, timepart(table7.started) as hrcreationRCO format time8.

You could try adding a CASE statement and see if that removes the notes.

, case when missing(table6.started) then . 
  else datepart(table6.started) 
  end as dtcreationCLA format yymmdd10.

Also you do not have an ON clause for the join to TABLE6.

Regular Contributor
Posts: 168

Re: NOTE: Invalid (or missing) arguments to the DATEPART function have caused the function to return

I did a mistake when I have paste my code...

 

Here's the complete code:

 

I might have erase few lines accidentaly

 

Here's the complete code

 

proc sql;
create table provnbqt1


as select table1.*, table5.dtcreationVIP, table5.hrcreationVIP, table5.VIP,
table6.clicassure, datepart(table6.started) as dtcreationCLA format yymmdd10., timepart(table6.started) as hrcreationCLA format time8.,
table7.rentecoll, datepart(table7.started) as dtcreationRCO format yymmdd10., timepart(table7.started) as hrcreationRCO format time8.,
table8.web

 

FROM provnbqt as table1

 

left join ref_vip as table5
on table1.ACCOUNTNUMBER = table5.ACCOUNT_NUMBER and table1.dttrnsys = table5.dtcreationVIP

 

left join clicassure as table6
on table1.ACCOUNTNUMBER = table6.primaryentityid and datepart(table6.started)<=table1.dttrnsys<=sum(datepart(table6.started),7) and table1.type_lot="QT"

 

left join rente_coll as table7
on table1.ACCOUNTNUMBER = table7.primaryentityid and datepart(table7.started)<=table1.dttrnsys<=sum(datepart(table7.started),7) and table1.type_lot="QT"


left join SoumEnLigne (where=( PRIMARYENTITYCLASS eq "Quote")) as table8
on table1.POLICYNUMBER = table8.primaryentityid and
datepart(table8.started)<=table1.dttrnsys<=sum(datepart(table8.started),7)

 

left join SoumEnLigne (where =(PRIMARYENTITYCLASS eq "Account")) as table9
on table1.ACCOUNTNUMBER = table9.primaryentityid and
datepart(table9.started)<=table1.dttrnsys<=sum(datepart(table9.started),7) and table1.type_lot="QT"

;
QUIT;

Regular Contributor
Posts: 168

Re: NOTE: Invalid (or missing) arguments to the DATEPART function have caused the function to return

I will try this solution on Monday. I let you know if it works.

I wish you a nice weekend.

Regular Contributor
Posts: 168

Re: NOTE: Invalid (or missing) arguments to the DATEPART function have caused the function to return

Thanks for your help! It works...
Ask a Question
Discussion stats
  • 5 replies
  • 712 views
  • 1 like
  • 3 in conversation