BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Agent1592
Pyrite | Level 9

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

8 REPLIES 8
Reeza
Super User

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


 

PGStats
Opal | Level 21

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

PG
Reeza
Super User

@PGStats wrote:

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


Those would be the same though 😄

 

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

Ksharp
Super User

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


PGStats
Opal | Level 21

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

PG
Ksharp
Super User
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.

Agent1592
Pyrite | Level 9

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.

 

 

 

Reeza
Super User

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. 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1078 views
  • 3 likes
  • 4 in conversation