Merging two SAS datasets by ID

Question

I want to merge two sas datasets that have the same ID but there is always something wrong.

I checked that the variable baseid in two datasets are all character, so I tried to adjust the id formats for both datasets by using the same code like this

data a; 
   set a; 
   baseidtemp = put(baseid,12);
   drop baseid; 
   rename baseidtemp = baseid; 
run;

After that I sorted both datasets by baseid.

But I don't know why when I used proc compare to compare their ID, all of the obs are unequal even if their values are all the same.

I merged them in this way

data A; 
   merge A (in = a) B; 
   by baseid; 
   if a; 
run;

They just cannot be merged correctly.

I am very confused about it, so could someone help me with how to solve this issue?

Thank you in advance!

Add some sample data to the question, and if possible, the output from Proc CONTENTS — Richard
If they are already character then there is no need to use PUT() function. You need to check for leading spaces or characters that you are not seeing (like tab) that are making the values different. — Tom

Jonni Jonni · Accepted Answer · 2020-08-26T06:37:41

It would be helpful to see the content of your data to know how they aren't merging correctly. But you could try this. I generally don't overwrite a data sets so made some new data names

data newa; 
set a;
baseidtemp = baseid;    
drop baseid;
run;
    
proc sort data=newa out=outA;
by baseidtemp;
run;
          
proc sort data=b out=outB;
by baseid;
run;

data new;
merge outA outB (rename = (baseid = baseidtemp));
by baseidtemp;
run;

Merging two SAS datasets by ID

1 Answers