DATA Step, Macro, Functions and more

Different format for matching two sql tables

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Different format for matching two sql tables

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.

 


Accepted Solutions
Solution
‎11-28-2016 03:00 AM
Super User
Posts: 19,770

Re: Different format for matching two sql tables

Posted in reply to Agent1592

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. 

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Different format for matching two sql tables

Posted in reply to Agent1592

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.);


 

Respected Advisor
Posts: 4,919

Re: Different format for matching two sql tables

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

PG
Super User
Posts: 19,770

Re: Different format for matching two sql tables


PGStats wrote:

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


Those would be the same though Smiley Very Happy

 

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

Super User
Posts: 10,020

Re: Different format for matching two sql tables

Posted in reply to Agent1592

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


Respected Advisor
Posts: 4,919

Re: Different format for matching two sql tables

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

PG
Super User
Posts: 10,020

Re: Different format for matching two sql tables

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.

Contributor
Posts: 36

Re: Different format for matching two sql tables

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.

 

 

 

Solution
‎11-28-2016 03:00 AM
Super User
Posts: 19,770

Re: Different format for matching two sql tables

Posted in reply to Agent1592

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. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 294 views
  • 3 likes
  • 4 in conversation