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.
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.
@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.);
Or I could think of another example involving our Grand Advisor's name
@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!!
have 2 as b on prxchange('s/^0+//',1,a.ID)=b.ID;
@Ksharp you would need =left(b.id) to make sure that both strings are justified the same way.
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.