topic Re: Merging two sets with multiple observations in New SAS User

Merging two sets with multiple observations

bretthouston — Wed, 02 Jun 2021 21:51:04 GMT

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

Re: Merging two sets with multiple observations

ballardw — Wed, 02 Jun 2021 22:21:33 GMT

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

Re: Merging two sets with multiple observations

bretthouston — Thu, 03 Jun 2021 00:02:14 GMT

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!

Re: Merging two sets with multiple observations

ballardw — Thu, 03 Jun 2021 00:57:03 GMT

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?