topic Re: Not able to join two tables who have same data types. in SAS Programming

Not able to join two tables who have same data types.

Abhinav26 — Mon, 26 Sep 2022 12:49:51 GMT

Hi,

I am not able to merge two tables and getting below error.

ERROR: Expression using equals (=) has components that are of different data types.

When i am checking contents of the variables its same for both.

LIBNAME	MEMNAME	MEMLABEL	TYPEMEM	NAME	TYPE	LENGTH	VARNUM	LABEL	FORMAT	FORMATL	FORMATD	INFORMAT	INFORML	INFORMD	JUST	NPOS
WORK	MEMBER_CRMINT			cust_detail_ky	2	20	26			0	0		0	0	0	448
WORK	CUSTOMER			cust_detail_ky	2	20	1			0	0		0	0	0	16

proc sql;
create table member_crmint(drop=med_cov_ind) as
select a.*,
b.subgrp_2_cd
from work.member_crmint a
left join work.customer b
on TRIM(LEFT(a.cust_detail_ky_char))=TRIM(LEFT(b.cust_detail_ky))
/* where a.med_cov_ind='Y'*/
;
quit;

Any help will be deeply appreciated.

Thanks

Re: Not able to join two tables who have same data types.

PaigeMiller — Mon, 26 Sep 2022 13:52:54 GMT

cust_detail_ky_charERROR: Expression using equals (=) has components that are of different data types.

So SAS thinks that the variables have different data types (one is numeric and one is character), and you have to figure out why SAS says this. You are trying to join on variable a.cust_detail_ky_char and b.cust_detail_ky

You have to look at the PROC CONTENTS output from both of these variables, but you have not shown us that. You are showing cust_detail_ky twice.

One might assume that cust_detail_ky_char is a character variable, based upon the name; but I hate to assume, it's really better if you find that information yourself and then decide what to do.

Re: Not able to join two tables who have same data types.

Abhinav26 — Mon, 26 Sep 2022 15:17:00 GMT

There was a issue with case statement, sorry for bothering you guys. This is resolved.

Re: Not able to join two tables who have same data types.

ballardw — Mon, 26 Sep 2022 15:32:46 GMT

I notice in the post that there is no CASE statement but the OP says that was where the issue was.

@Abhinav26 wrote:

Hi,

I am not able to merge two tables and getting below error.

ERROR: Expression using equals (=) has components that are of different data types.

When i am checking contents of the variables its same for both.

LIBNAME MEMNAME MEMLABEL TYPEMEM NAME TYPE LENGTH VARNUM LABEL FORMAT FORMATL FORMATD INFORMAT INFORML INFORMD JUST NPOS

WORK MEMBER_CRMINT cust_detail_ky 2 20 26 0 0 0 0 0 448

WORK CUSTOMER cust_detail_ky 2 20 1 0 0 0 0 0 16

proc sql;
create table member_crmint(drop=med_cov_ind) as
select a.*,
b.subgrp_2_cd
from work.member_crmint a
left join work.customer b
on TRIM(LEFT(a.cust_detail_ky_char))=TRIM(LEFT(b.cust_detail_ky))
/* where a.med_cov_ind='Y'*/
;
quit;

Any help will be deeply appreciated.

Thanks

Re: Not able to join two tables who have same data types.

PaigeMiller — Mon, 26 Sep 2022 17:38:56 GMT

@Abhinav26 wrote:

There was a issue with case statement, sorry for bothering you guys. This is resolved.

From now on, please show us the ENTIRE log for the step (PROC or DATA step) that has the error. Please do not show us error messages detached from the code, as it appears in the log. @Abhinav26 : We expect this 100% of the time from now on, thank you for helping us.