topic Re: Howto: Merge tables where columns don't match exactly in SAS Enterprise Guide

Howto: Merge tables where columns don't match exactly

Otis_ — Mon, 06 Jan 2014 23:06:09 GMT

I would like to join two tables using one of EG's built in features like query builder. The two columns I would like to use contain matching characters, but also contain other extraneous ones. Both columns are character columns, but one contains a max length of 12 and the other a max length of 128. Is it possible to join these two tables?

Example Column 1

E001234

E012345

E123456

Example Column 2

1234@email.com

12345@email.com

123456@email.com

Re: Howto: Merge tables where columns don't match exactly

Mit — Tue, 07 Jan 2014 01:05:50 GMT

Could you please explain a bit more like what you have and what you would like to get?

You can chnage the length. But it looks like your column1 (numeric part)) are same as the numeric part of column2.

Do you like to match on these?

Here is a suggestion:

create a column (say match_col) as the numeric part from Column1

create a column(same name match_col) as the numeric part from Column2

then match the tables on match_col

Thanks

Mita

Re: Howto: Merge tables where columns don't match exactly

Otis_ — Thu, 09 Jan 2014 19:46:38 GMT

Hi Mita,

I have two tables, and I would like to use the common numbers between one column in each table to merge two tables into one.

I don't know how to create new columns and only bring the numbers over from the existing columns I would like to match.

Re: Howto: Merge tables where columns don't match exactly

Reeza — Thu, 09 Jan 2014 19:58:23 GMT

Yes it's possible.

In your example it looks you can make them "look" the same.

So lets work on making the columns in Table 2 look like the ones in Table 1.

It looks like the ones in table one all start with E and have 6 digits afterwards. Assuming this is consistent across all your data, then we can make the ones from Table 2 similar by

1) Extracting first portion of the email (use Scan() Function)

2) Making the number a length of 6 by zero padding. (Use Z6. Format in a put statement)

3) Adding an E to the beginning (Concatenate)

For EG make a calculated column that implements this and then join on the calculated column.