06-18-2018 11:58 AM
I'm running a proc sql table, trying to compare dates in the WHERE statement. I keep getting this error: "Expression using equals (=) has components that are of different data types.".
I have run proc contents to compare formats of both variables, and they look the same:
The code I'm running:
PROC SQL; CREATE TABLE TRACKER_REDEMPTIONS AS SELECT t.*, r.redemptions FROM tracker_work4 T LEFT JOIN new_redemptionst r ON ((T.STORE=r.store) AND (T.CAMPAIGN = r.campaign) AND (T.start <= r.sunday) AND (T.'Last Expiry Date'n >= r.sunday)) ; RUN;
I'm out of ideas for what could be wrong. Help!
06-18-2018 12:12 PM
You didn't show the contents for ALL of the variables shown in your statement, specifically t.start.
You also might run into a problem, albeit a different one, in that the lengths of t.campaign and r.campaign are different.
Art, CEO, AnalystFinder.com
06-18-2018 12:14 PM
t.start specifications are exactly the same as Last Expiry Date (or r.sunday). I'm currently only trying to solve the dates issue, not worrying about campaign length yet.
06-18-2018 12:28 PM
Things not shown, that need to be checked:
T.start ... is it character or numeric?
The global option VALIDVARNAME has to be set to ANY to permit name literals as field names.
06-18-2018 01:17 PM
The error might not be in the lines you highlighted in red as they are using comparison operators <= and >= which for the following different data types causes a different error message:
proc sql noprint; create table tmp as select a.* ,b.weight from sashelp.class(drop = weight) a left join sashelp.class b on a.name >= b.age ; quit;
gives the error:
14 proc sql noprint;
15 create table tmp as
16 select a.*
18 from sashelp.class(drop = weight) a
19 left join sashelp.class b
20 on a.name >= b.age
ERROR: Expression using greater than or equal (>=) has components that are of different data types.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
As you can see it refers to greater than or equal (>=) not just equals (=) which your message does.
Changing my code to use = instead of >= gives the same error message as yours, so this indicates that the issue is with your equality tests T.STORE=r.store or T.CAMPAIGN = r.campaign.
As others have advised, seeing all the relevant information would help us to help you.
Just another thought, are either of the data sets views?