ERROR: The COALESCE function requires its arguments to be of the same data type

[tobyfarms]

Hello, I'm having an issue with the dates in my logic, where they both appear to be the same date type.  My code below is the full code as of now - and please let me know if you need additional information in order to help..  Many Thanks.

proc sql;

create table work.Priest_srvcptidrdailypeak_arch as

select DATEPART(operatingdate) format ddmmyy10. as OperatingDate , uidservicepoint,

h1, h2, h3, h4, h5, h6, h7, h8, h9, h10, h11, h12, h13, h14, h15, h16,

h17, h18, h19, h20, h21, h22, h23, h24

from hud.srvcptidrdailypeak_arch

where operatingdate between "01JUN2016"D and "02JUN2016"D ;

quit;

proc sql;

create table work.Priest_lsservicepoint as

select uidservicepoint, juriscode

from hud.lsservicepoint;

quit;

　

proc sql;

create table work.PRIEST_Merge1 as

select A.*, b.juriscode

from work.Priest_srvcptidrdailypeak_arch as A left join work.Priest_lsservicepoint as b

on a.UIDSERVICEPOINT=b.UIDSERVICEPOINT;

quit;

proc sort data=work.PRIEST_Merge1 NODUP; by _all_ ; run;

proc sql;

create table work.Priest_srvcpthist as

select uidservicepoint, uiddp, uiddlc, nem, uidratefactor, starttime, stoptime

from hud.srvcpthist

where uidratefactor in (237,238) and uiddlc = 1 and nem is null or 'N'

and uiddp not in (1,6,13,5,26,7,8,12,14,10,9,11) ;

quit;

proc sql;

create table work.PRIEST_Merge2 as

select A.*, b.uiddp, b.uiddlc, b.nem, b.uidratefactor, b.starttime, b.stoptime

from work.PRIEST_Merge1 as A left join work.Priest_srvcpthist as b

on a.UIDSERVICEPOINT=b.UIDSERVICEPOINT

where OperatingDate between starttime and coalesce(stoptime,"01JAN4000");

quit;

proc sort data=work.PRIEST_Merge2 NODUP; by _all_ ; run;

[SuryaKiran]

Hi,

From where are you getting the Starttime and stoptime.

where OperatingDate between starttime and coalesce(stoptime,"01JAN4000");

This where clause is not valid. If your trying to take the starttime and stoptime from a table, than use a data _Null_ and CALL SYMPUT to put your values in a macro and later reference them in the where clause.

Thanks,

Suryakiran

[art297 · Accepted solution]

I haven't reviewed your code but, in your last proc sql call, you didn't define your date constant as a date. i.e., you used:

coalesce(stoptime,"01JAN4000")

rather than

coalesce(stoptime,"01JAN4000"d)

Art, CEO, AnalystFinder.com

[tobyfarms]

This worked.  Thank you.