topic Re: ERROR: The COALESCE function requires its arguments to be of the same data type in SAS Programming

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

tobyfarms — Fri, 18 Aug 2017 19:37:22 GMT

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;

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

SuryaKiran — Fri, 18 Aug 2017 20:07:50 GMT

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

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

art297 — Fri, 18 Aug 2017 20:25:59 GMT

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

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

tobyfarms — Mon, 21 Aug 2017 13:16:58 GMT

This worked. Thank you.