topic Re: Merge two tables with douplicated data in SAS Programming

Merge two tables with douplicated data

sebastianmendez — Thu, 13 Oct 2022 10:26:12 GMT

Hello,

I'm a novice at SAS and i would ask you how I can do a merge in these conditions:

I have Table1(IDs, xvariable), and i have a smaller one, Table2(IDs, xvariable). Both tables share some IDs, but i want to keep those in Table2 (and not those in Table1, for those that are in both tables). Sometimes the data (xvariable) is different for the same ID, but data in Table2 is more truthful.

Thank you!

Re: Merge two tables with douplicated data

PeterClemmensen — Thu, 13 Oct 2022 10:33:32 GMT

Can you post some example of what your data looks like and what you expect as result?

Makes it easier to provide a usable code answer.

Re: Merge two tables with douplicated data

sebastianmendez — Thu, 13 Oct 2022 11:31:31 GMT

Something like this

Re: Merge two tables with douplicated data

LinusH — Thu, 13 Oct 2022 12:28:36 GMT

To me it looks like a LEFT JOIN.

For variableX, use the COALESCE function.

Re: Merge two tables with douplicated data

Tom — Thu, 13 Oct 2022 13:14:43 GMT

That is a simple data step MERGE.

data want;
  merge table1 table2;
  by id;
run;

Re: Merge two tables with douplicated data

PeterClemmensen — Thu, 13 Oct 2022 13:24:18 GMT

data one;
input ID X $;
datalines;
1 a 
2 b 
3 a 
4 b 
5 b 
6 c 
;

data two;
input ID X $;
datalines;
1 c 
8 b 
9 b 
;

data want;
   merge one two;
   by ID;
run;

Re: Merge two tables with douplicated data

ballardw — Thu, 13 Oct 2022 14:43:37 GMT

@sebastianmendez wrote:

Something like this

If any of your data steps have multiple observations with the same value of ID you should provide an example with some of the repeated values.

If BOTH data sets have multiple observations with the same value of ID then it is critical that you show example data and the desired result.

Re: Merge two tables with douplicated data

sebastianmendez — Thu, 13 Oct 2022 14:53:16 GMT

Well, i thought i did that.
You can see the ID 1 is present in both tables. And the desired result is keep the observation (ID1) from the table 2.

Re: Merge two tables with douplicated data

Tom — Thu, 13 Oct 2022 15:21:23 GMT

@sebastianmendez wrote:
Well, i thought i did that.
You can see the ID 1 is present in both tables. And the desired result is keep the observation (ID1) from the table 2.

The fact that there are ID values that can appear in both datasets was already covered by the data in the photographs you posted. But that was not the question that was asked.

The question was whether or not the ID value uniquely identifies an observation (in either dataset individually). Your data did not show any cases where the same ID value appeared more than once in the same dataset. So the question is whether that is possible with your data or not. If the same ID can appear multiple times in one (or both) of the dataset will require a more complicated solution. And also more explanation from you about what output you would want in that situation.

Re: Merge two tables with douplicated data

ballardw — Thu, 13 Oct 2022 15:32:30 GMT

@sebastianmendez wrote:
Well, i thought i did that.
You can see the ID 1 is present in both tables. And the desired result is keep the observation (ID1) from the table 2.

I should have provided an example of what I meant. Sorry, but I couldn't edit your pictures.

Stealing from @PeterClemmensen , suppose that your data actually looked like

(the simpler case of only one data set with duplicate id values:

Case 1: data set One is the only one with duplicates of ID

data one;
input ID X $;
datalines;
1 a 
1 z
2 b 
3 a 
4 b 
5 b 
6 c 
;

data two;
input ID X $;
datalines;
1 c 
8 b 
9 b 
;

Case 2: where data set 2 has duplicates of Id

data one;
input ID X $;
datalines;
1 a 
2 b 
3 a 
4 b 
5 b 
6 c 
;

data two;
input ID X $;
datalines;
1 c 
1 w
8 b 
9 b 
;

Case 3: both sets have duplicates of Id

data one;
input ID X $;
datalines;
1 a 
1 h
1 m
1 s
2 b 
3 a 
4 b 
5 b 
6 c 
;

data two;
input ID X $;
datalines;
1 c 
1 z
8 b 
9 b 
;

If you have any of these cases you need to consider what you expect for a result and show that. The last one can be very complicated depending on exact expectation.