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

[alepage]

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;

[Reeza]

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. 

[Tom]

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.

[alepage]

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;

[alepage]

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

I wish you a nice weekend.

[alepage]

Thanks for your help! It works...