I'm using SAS 9.3 and trying to merge two datasets. In dataset1, my merge key variable called "id" is originally formatted as a character $200. In dataset2, the merge key variable also called "id" is formatted as a character $6. The values of id are actually numbers (for example, 001004 or 134875). I have tried the following code that reformats the id in dataset1 and tries to perform the merge: data dataset1; set dataset1;
newid=input(id,BEST12.);
newid2=put(newid,z6.);
run;
proc sql;
create table combo as
select a.*, b.var1, b.var2
from dataset2 as a left join dataset1 as b
on a.id = b.newid2;
quit;
run; The above code appears to work at first, but fails to fully merge all the records in dataset1. The merge works correctly for low values of id, but fails when there are no longer any leading zeros. As a specific example, the merge works for id=099999 but not for id=100000. How can I format my id variable to make sure all records are appropriately merged?
... View more