DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

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

Hi. I'm having trouble understanding the issue here.  Both A.Actual_Dlvry_Date and B.Actual_Dlvry_Date come from Oracle database tables where they are defined as DATE types. I'm just trying to compare them to see if they mismatch and handle the circumstance where one or both are missing values.  I don't see where I'm being inconsistant with the data types.

 

Any help would be greatly appreciated.

 

NOTE: Libref IV_ORA was successfully assigned as follows:
Engine: ORACLE
Physical Name: IVASPRD
1 libname iv_ora oracle user=&orauser pass=&orapass path="IVASPRD" schema="IVPRL" UPDATE_LOCK_TYPE=row;
NOTE: Libref BIDS_ORA was successfully assigned as follows:
Engine: ORACLE
Physical Name: IBSCRPRD
2 libname bids_ora oracle user=exfcread pass=XXXXXXXXXXXXXXXXXX path="IBSCRPRD" schema="IMAPSSCR" UPDATE_LOCK_TYPE=row;

9 + proc sql; 9 + create table QueryData as ( select 'ACTUAL DELIVERY DATE MISMATCH' as RULE_NM, b.actual_dlvry_date as AD_DT, b.imb_code, 2 as rule_order from 10 + iv_ora.bi_spm_piece_recon a, bids_ora.bi_spm_piece_recon b where a.imb_code = b.imb_code and COALESCE(A.ACTUAL_DLVRY_DATE,PUT(today(), yymmddn8.)) <> COALESCE(B.ACTUAL_DLVRY_DATE,PUT(today(), yymmddn8.)) ); NOTE: The "<>" operator is interpreted as "not equals". ERROR: The COALESCE function requires its arguments to be of the same data type. ERROR: The COALESCE function requires its arguments to be of the same data type. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 11 + quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.02 seconds cpu time 0.00 seconds

 


Accepted Solutions
Solution
‎05-02-2016 12:54 PM
Super User
Posts: 5,081

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

The two dates may both be date-type, but the PUT function always returns a character string.  Luckily, you can change that pretty easily by getting rid of the PUT function and just using the numeric part of that term (in two places):

 

today()

View solution in original post


All Replies
Solution
‎05-02-2016 12:54 PM
Super User
Posts: 5,081

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

The two dates may both be date-type, but the PUT function always returns a character string.  Luckily, you can change that pretty easily by getting rid of the PUT function and just using the numeric part of that term (in two places):

 

today()

Occasional Contributor
Posts: 11

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

Hi,

 

The put(...) function converts your today() date value to a text string, hence it's a different type to the Oracle date value which SAS will provide as a SAS datetime value which is numeric.

 

Coalesce is a string function so requires both arguments to be text, hence you'll need to convert your Oracle date too.

 

A factor you might also consider is that Oracle only has a single data type DATE which is actually date-time - a date in Oracle simply has zero hours, minutes and seconds - so you might need to do a bit more transformation before comparing.

 

- Steve

 

 

Regular Contributor
Posts: 212

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

Thank you all for the help!
☑ This topic is SOLVED.

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

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