DATA Step, Macro, Functions and more

Quoting Help

Reply
Regular Contributor
Posts: 212

Quoting Help

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.

 

Super User
Posts: 19,789

Re: Quoting Help

Posted in reply to buechler66

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...

Regular Contributor
Posts: 212

Re: Quoting Help

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;
Super User
Super User
Posts: 7,942

Re: Quoting Help

Posted in reply to buechler66

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. 

PROC Star
Posts: 1,322

Re: Quoting Help

Posted in reply to buechler66
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.
Regular Contributor
Posts: 212

Re: Quoting Help

I was talking about the error: ERROR: The COALESCE function requires its arguments to be of the same data type.
PROC Star
Posts: 1,322

Re: Quoting Help

Posted in reply to buechler66

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.

Super User
Posts: 19,789

Re: Quoting Help

Posted in reply to buechler66

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

Trusted Advisor
Posts: 1,117

Re: Quoting Help

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.

Ask a Question
Discussion stats
  • 8 replies
  • 300 views
  • 7 likes
  • 5 in conversation