Expression using equals has components that are of different data types

Reply
Occasional Contributor
Posts: 7

Expression using equals has components that are of different data types

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:

Capture1.PNGCapture2.PNG

 

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!

PROC Star
Posts: 8,165

Re: Expression using equals has components that are of different data types

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

 

Occasional Contributor
Posts: 7

Re: Expression using equals has components that are of different data types

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. 

PROC Star
Posts: 8,165

Re: Expression using equals has components that are of different data types

What about t.store and r.store?

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 7

Re: Expression using equals has components that are of different data types

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

PROC Star
Posts: 8,165

Re: Expression using equals has components that are of different data types

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

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 6,785

Re: Expression using equals has components that are of different data types

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.

Super Contributor
Posts: 340

Re: Expression using equals has components that are of different data types

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.

Ask a Question
Discussion stats
  • 7 replies
  • 108 views
  • 0 likes
  • 4 in conversation