topic Re: Join 2 tables with Different Field type types in SAS Enterprise Guide

Join 2 tables with Different Field type types

thegraduate — Wed, 04 Aug 2010 14:29:20 GMT

Hi All,

Quite a simple one for most, i have two tables with a URN for each, one is a text and one is a number. When i link them, it gives me an error due to invalid field type (ERROR: Expression using equals (=) has components that are of different data types.)

How can I convert one of the fields so it is a number?

Thanks.

Re: Join 2 tables with Different Field type types

SASKiwi — Wed, 04 Aug 2010 22:49:01 GMT

You haven't said how you are doing the joining.

However in SAS SQL you can do:

create table xxxx as
select *
from table1 as a, table2 as b
where a.urn = input(b.urn,12.);

Here I am assuming that table1 has the numeric URN and table2 the character one.

Re: Join 2 tables with Different Field type types

thegraduate — Wed, 11 Aug 2010 09:00:56 GMT

Hi,

I am joining through Query builder.

So Table one has AccountNumber (Text)
Table two has AccNum(Number)

Just wondering how I would do it through there?

Re: Join 2 tables with Different Field type types

SASKiwi — Wed, 11 Aug 2010 22:22:29 GMT

I suggest you use the computed column option, and the Advanced Expression Editor to type in the INPUT function as I have already mentioned, to create a new computed column that converts the character column to numeric. Then you should be able to use the new computed column in your join.

Re: Join 2 tables with Different Field type types

deleted_user — Thu, 12 Aug 2010 01:15:44 GMT

Nice to see a kiwi here lol