BookmarkSubscribeRSS Feed
Otis_
Fluorite | Level 6

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

3 REPLIES 3
Mit
Calcite | Level 5 Mit
Calcite | Level 5

Hi

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

Otis_
Fluorite | Level 6

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.

Reeza
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1166 views
  • 0 likes
  • 3 in conversation