DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

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

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;


Accepted Solutions
Solution
‎08-21-2017 09:15 AM
PROC Star
Posts: 7,487

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

[ Edited ]
Posted in reply to tobyfarms

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

View solution in original post


All Replies
Frequent Contributor
Posts: 141

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

Posted in reply to tobyfarms

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

Thanks,
Suryakiran
Solution
‎08-21-2017 09:15 AM
PROC Star
Posts: 7,487

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

[ Edited ]
Posted in reply to tobyfarms

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

Contributor
Posts: 44

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

This worked.  Thank you. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 127 views
  • 1 like
  • 3 in conversation