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.
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...
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;
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.
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.
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
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.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.