topic Invalid (or missing) arguments to the DATEPART function have caused the function to return in SAS Programming

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

RB3 — Wed, 17 Jul 2019 19:38:28 GMT

To Whom it may concern:

I am trying to do a full join and below is my syntax:

proc sql;
	create table Monthly as 
		SELECT c.*, d.*
			FROM careods.Care_deaths c Full Join Mthdthrp.dthr717 d
				on input(c.care_id, $8.) = d.care_id and c.lastup_DT = d.DOS 
					order datepart(c.lastup_DT) Between '17JUL2018'd and '17JUL2019'd;
QUIT;

when I execute the code, is get this feedback:

611  proc sql;
612      create table Monthly as
613          SELECT c.*, d.*
614              FROM careods.Care_deaths c Full Join Mthdthrp.dthr717 d
615                  on input(c.care_id, $8.) = d.care_id and c.lastup_DT = d.DOS
616                      order datepart(c.lastup_DT) Between '17JUL2018'd and '17JUL2019'd;
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT
      clause.
WARNING: Variable CARE_ID already exists on file WORK.MONTHLY.
WARNING: Variable Autopsy already exists on file WORK.MONTHLY.

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


NOTE: Table WORK.MONTHLY created, with 17765 rows and 56 columns.

617  QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.30 seconds
      cpu time            0.20 seconds

'invalid (or missing) argument to the datepart function have caused the function to return a missing value'

Please help!

Thanks!

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

RB3 — Wed, 17 Jul 2019 19:39:52 GMT

to add to my previous statement, my return comes back with NULLS, and I want all the records to show.

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

novinosrin — Wed, 17 Jul 2019 19:44:07 GMT

This is incorrect i think

 input(c.care_id, $8.)

should be without the $

 input(c.care_id, 8.)

Also no idea what's your objective here

order datepart(c.lastup_DT) Between '17JUL2018'd and '17JUL2019'd;

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

Tom — Wed, 17 Jul 2019 19:44:56 GMT

Are you really trying to order based on whether the date falls in some interval or not? Where to you want the values that are missing to fall? Before the false values? Or after the true values?

You can use a CASE statement to skip the function when the value is missing.

proc sql;
create table Monthly as
SELECT c.*, d.*
FROM careods.Care_deaths c
Full Join Mthdthrp.dthr717 d
on input(c.care_id, $8.) = d.care_id
 and c.lastup_DT = d.DOS
order case when missing(c.lastup_dt) then -1
      else   datepart(c.lastup_DT) Between '17JUL2018'd and '17JUL2019'd
      end
;

Also note that there is no need to use INPUT() function to take the first 8 characters of a string. You can use SUBSTR() or SUBSTRN() for that.

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

LaurieF — Wed, 17 Jul 2019 20:40:56 GMT

The line

order datepart(c.lastup_DT) Between '17JUL2018'd and '17JUL2019'd;

forces the rows between those dates to the bottom of the output - don't you mean where instead of order?

Assuming that you do, this would be better:

where c.lastup_DT Between '17JUL2018:0:0'dt and '17JUL2019:23:59:59'dt;

That way any null values won't trigger, and there won't be any warning messages.

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

Kurt_Bremser — Thu, 18 Jul 2019 08:05:30 GMT

Or could it be that somehow "or" mutated to "order"?

And your use of asterisks for both datasets is bound to cause a WARNING, which must not be tolerated (see Maxims 24 & 25) .

At least for dataset

Mthdthrp.dthr717

be more selective to avoid the WARNING.