topic Re: Different format for matching two sql tables in SAS Programming

Different format for matching two sql tables

Agent1592 — Mon, 28 Nov 2016 02:06:44 GMT

I have two tables that I would like to match and I have the following code:

proc sql;

create table want as

select distinct *

from have1 a as a left join

have 2 as b on a.ID=b.ID;

quit;

The problem is the ID in HAVE1 is of the sort: 001003 and in table 2 1003. The format of both is Character $6. Why I can't match them.

How hcan I convert the IDs to be the same. Format is the same but there are zeros in front of the IDs in have 1.

Re: Different format for matching two sql tables

Reeza — Mon, 28 Nov 2016 02:17:06 GMT

@Agent1592 wrote:

The problem is the ID in HAVE1 is of the sort: 001003 and in table 2 1003. The format of both is Character $6. Why I can't match them.

Because they're characters and don't match, that's equivalent to saying "Alfred" and "fred" are the same.

If you want to treat them as numbers, convert them to numbers in your join condition using INPUT.

have 2 as b on input(a.ID, 8.)=input(b.ID, 8.);

Re: Different format for matching two sql tables

PGStats — Mon, 28 Nov 2016 02:58:36 GMT

Or I could think of another example involving our Grand Advisor's name

Re: Different format for matching two sql tables

Ksharp — Mon, 28 Nov 2016 03:43:31 GMT


   have 2 as b on prxchange('s/^0+//',1,a.ID)=b.ID;

Re: Different format for matching two sql tables

Reeza — Mon, 28 Nov 2016 03:47:58 GMT

@PGStats wrote:

Or I could think of another example involving our Grand Advisor's name

Those would be the same though 😄

Grand Advisor? I hadn't noticed my elevated status. Better not let it get to my head!!

Re: Different format for matching two sql tables

PGStats — Mon, 28 Nov 2016 04:27:33 GMT

@Ksharp you would need =left(b.id) to make sure that both strings are justified the same way.

Re: Different format for matching two sql tables

Ksharp — Mon, 28 Nov 2016 06:06:42 GMT

PG,
If b.id is character variable, the default value in it should be left align?
Of course, Add left() for make sure it happen.

Re: Different format for matching two sql tables

Agent1592 — Mon, 28 Nov 2016 07:36:20 GMT

Sorry I should have been more specific:

have1 table ID is a character $6. :

ID 001010

ID 098900

ID 109899

have2 table ID is numeric BEST12.:

ID 1010

ID 98900

ID 109899

The code provided did not work.

Re: Different format for matching two sql tables

Reeza — Mon, 28 Nov 2016 07:55:52 GMT

If only 1 is char, use input to convert that value to numeric. Or convert numeric to char using Z6 format.

Either can be applied directly on join condition. The point is make them the same.