Quoting Help

[buechler66]

Hi. I have the following line of code. My goal is to set Rule = 'X' when Rule is missing.

    rule = tranwrd(rule,"SYSDATE",'X');

However I'm getting the following error (I think) because the value X isn't resolving as 'X'.  Does anyone have thoughts on resolving this issue?

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,X) <> COALESCE(B.ACTUAL_DLVRY_DATE,X)         );
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.
ERROR: The following columns were not found in the contributing tables: X.

[Reeza]

I can't see the relation to the log and the code you've posted. 

The = sytnax is used in a data step not proc sql...

[buechler66]

Sorry, here's the code relating to the above log.

		proc sql;
		create table QueryData as 
		(       select b.actual_dlvry_date as AD_DT, 
				b.imb_code, 
				&rule_order as rule_order
	            from iv_ora.bi_spm_piece_recon a,  bids_ora.bi_spm_piece_recon b                                                                                                                                                                                                                                                                                                                                                                                                                                                   
	            where a.imb_code = b.imb_code
	            and &rule
		); 
		quit;

[RW9]

Lets start again.  You have this code:

proc sql;
		create table QueryData as 
		(       select b.actual_dlvry_date as AD_DT, 
				b.imb_code, 
				&rule_order as rule_order
	            from iv_ora.bi_spm_piece_recon a,  bids_ora.bi_spm_piece_recon b                                                                                                                                                                                                                                                                                                                                                                                                                                                   
	            where a.imb_code = b.imb_code
	            and &rule
		); 
		quit;

Now the macro variable &RULE is resolving to:

COALESCE(A.ACTUAL_DLVRY_DATE,X) <> COALESCE(B.ACTUAL_DLVRY_DATE,X)

 Now, what the error is telling you is that X is not a variable in any of the contributing tables.  I assume what you want to do is to take the character X if the variable is null, so you want &RULE to resolve to:

COALESCE(A.ACTUAL_DLVRY_DATE,"X") <> COALESCE(B.ACTUAL_DLVRY_DATE,"X")

So that is how you need to setup the &RULE macro variable.  As we can't see how this is set - or in fact see any reason why that bit of code is in a macro variable in the first place, its hard to say where your going wrong.  I would suggest that your logic is in the first place incorrect, if either of those variables are actually date variables then "X" will not be valid compare.

Post some test data - in the form of a datastep and what you want the output to look like. 

[Quentin]

The error message is saying that the variable X was not found in the contributing tables. Perhaps you meant to reference the variable RULE? It's hard to guess.

[buechler66]

I was talking about the error: ERROR: The COALESCE function requires its arguments to be of the same data type.

[Quentin]

I believe the cause is the code you generated refers to a variable named X which doesn't exist in the contributing tables.  Then SAS will likely assume X is numeric, and your variable actual_delivery_date is probably character.  I would start by reviewing the code that generates the where clause.  Do really intend to refer to a variable named X?  If so, check why this variables isn't being found.

[Reeza]

It seems to be the same issue you asked yesterday...you have a date in one part, the second part X needs to be a date.  

I still don't know what your first line of Rule= has to do with the code, what are you expecting to happen with that line of code?

Two other things:

If you want to use coalesce with character variables use COALESCEC- but since this is a date variable that doesn't make sense either. 

<> resolves to either MAX or NE depending on the usage, the log will state which one. This isn't a native SAS operator so if it was me, I would use NE.

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000780367.htm

[FreelanceReinh]

Just a side note: The COALESCE function of PROC SQL is different from the data step functions COALESCE and COALESCEC in that it accepts both numeric and character arguments as long as all arguments are of the same type.