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

[RB3]

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!

[RB3]

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

[novinosrin]

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;

[Tom]

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. 

[LaurieF]

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.

[Kurt_Bremser]

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.