How to fix the "ERROR: Expression using equals (=) has components that are of different data types"?

[Jaaa]

Hi SAS professionals,

I confirm all of three date variables: LINKDT, endfyr and LINKENDDT are numerical dates in YYMMDDN8. format. But somewhow, it still produces errors, is there any professional who can spot the issue? Many thanks in advance!! 

1037 proc sql;
1038 create table mydata as select *
1039 from compx2 as a, LinkTable as b
1040 where a.cusip = b.cusip and
1041 b.LINKTYPE in ("LC", "LS", "LU", "LX", "LD", "LN") and
1042 (b.LINKDT <= a.endfyr or b.LINKDT = .B) and (a.endfyr <= b.LINKENDDT or b.LINKENDDT =
1042! .E);
ERROR: Expression using equals (=) has components that are of different data types.
1042! /*. 
1043 /***************************************************************************************
1043! *********************
1044 * The previous condition requires the end of fiscal year to fall within the link range.
1044! *
1045 ***************************************************************************************
1045! *********************/
1046 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
 

[RW9]

Without seeing a proc contents of both datasets we are just guessing.  What about cuspid is that the same type in both datasets?  What about linktype is that character?  What about endfyr?

The log is telling you one of the variables in that SQL does not match the type of a comparitor, so you have a mismatch somewhere - look at the column attributes of each.

[Jaaa]

Thank you for your reply!

Yes, you are right that one cusip is numerical BEST12. format and the other one is character $9. format.

For clarification, LinkType= Character; LINKDT=Numerical; Endfyr= Numerical; LINKENDDT=Numerical.

After converting the numerical cusip to character type for matching with each other, SAS produced an empty dataset with zero rows. Do you possibly know where got wrong? Many thanks for your help indeed!

1503  proc sql;
1504    create table mydata as select *
1505    from compx2 as a, LinkTable as b
1506    where a.cusip = b.cusip and
1507    b.LINKTYPE in ("LC", "LS", "LU", "LX", "LD", "LN") and
1508    (b.LINKDT <= a.endfyr or b.LINKDT = .B) and (a.endfyr <= b.LINKENDDT or b.LINKENDDT =
1508! .E);             /*. letter (a decimal point followed by a letter, for example, .B) means
WARNING: Variable CUSIP already exists on file SIQI_IO.MYDATA.
NOTE: Table SIQI_IO.MYDATA created, with 0 rows and 72 columns.

1508!          
1509   /***************************************************************************************
1509! *********************
1510    * The previous condition requires the end of fiscal year to fall within the link range.
1510!                     *
1511    ***************************************************************************************
1511! *********************/
1512  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.89 seconds
      cpu time            0.87 seconds

[RW9]

Am having difficulties posting at the moment. Depends on your match criteria. When you converted cuspid, did you add any unintentional spaces in maybe? Perhaps try strip(cuspid). Have a look at the merges and make sure they do really merge. Can't really help much without seeing the data as it is on your system, but something doesn't match.

[LinusH]

b.LINKDT = .B
Looks like a typo.