Merging two sets with multiple observations

[bretthouston]

Hello, 

I am trying to merge two datasets based on a character variable 'AdmissionID.' I am looking for a left join. I've tried the following code (see below), however only very few observations from the table DADmerge3 merge (despite seemingly apparent AdmissionIDs). I've read through multiple SAS help forums, tried to harmonize the AdmissionID variables etc, and no luck!

I've tried:

proc sort data=export.combine6;

by AdmissionID;

run;

proc sort data=export.DADmerge4;

by AdmissionID;

run;

data mergeDADCR;
merge export.combine6 (in=CR) export.DADmerge4 (in=DAD);
by admissionID;
if CR then export;
run;

Here is an example of how I would like the data to merge:

Combine 6:

AdmissionID   RBC    WBC

1                      5           7

1                      4          6

2                      4          5

DADmerge4:

AdmissionID       Transfusion         Num

1                            1                        8

2                            4                        3 

3                            5                        4

Merged set:

 AdmissionID     RBC        WBC    Transfusion    Num

1                         5                7            1                 8

1                         4                6            1                 8

2                         4               5              4                3

Any help would be very appreciated!!

Thanks,

Brett

[ballardw]

You may want to go back to the step(s) that brought the data into SAS.

You AdmissionID variable has an informat of $10 in the Dadmerge4 data set and $11 in the Combine6 data.

That makes one suspect that perhaps a number of values in the Dadmerge4 set were truncated when read.

Large economy size hint: If you are using Proc Import and the source data is a spreadsheet then you need to very carefully examine the properties of variables as a very few rows of the data used in general to set the length and type.

If this sounds to be a plausible source of your data differences then you might consider 1) save the spreadsheets to a CSV file format, then if you use Proc Import you can add the Guessingrows=max; or some largish number , so more rows of data are used to set the properties. Better would be to write a data step to read the data where you control all the properties

[bretthouston · Accepted solution]

Thanks for this reply, I really appreciate it. 

I have re-imported as suggested, but that doesn't seem to make a difference. Many of the admissionIDs (linking variable) are seemingly identical between the two tables (with the same number of digits; no truncation) and yet the data from table 2 is only populated in 185/1390 rows (when I would expect nearly all of the admissionIDs in table 1 to have a match in table 2).

If I misunderstood your solution though, please let me know as it's entirely possible!

[ballardw]

Please don't say Table1 and Table2. You haven't indicated which would be which.

Why do you believe that "most" of the ids in one data set would be in the other?