Expression using equals has components that are of different data types

[zjanuske]

Hi,

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!

[art297]

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

[zjanuske]

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. 

[art297]

What about t.store and r.store?

Art, CEO, AnalystFinder.com

[zjanuske]

they;re both fine, the error is on those two lines i've highlighted in red

[art297]

Post your log and the full proc contents of both datasets.

Art, CEO, AnalystFinder.com

[Astounding]

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.

[Amir]

Hi,

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

17 ,b.weight

18 from sashelp.class(drop = weight) a

19 left join sashelp.class b

20 on a.name >= b.age

21 ;

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.

22 quit;

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?

Regards,

Amir.