BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buechler66
Barite | Level 11

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
Astounding
PROC Star

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()

SteveM_UK
Obsidian | Level 7

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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