merging two files, add the variable when both ID exist and keep original if ID only in one data set

[fengyuwuzu]

I have two data files, both have ID and amount (amount_A and amount_B)

If the ID in both data sets, I want to add the amount together;

if ID only in one data set, I will keep the original amount (amount_A or amount_B)

can any one give me a sample code in both proc sql or data step? Thanks a lot.

[Reeza]

Each ID is present in each dataset only once? 

Can you post sample data and expected output? 

[fengyuwuzu]

yes， each ID appears only once in each set; some IDs appears in both sets

example:

A:

ID amount

1 10

3 15

4 20

7 15

9 12

10 14

B:

ID amount

2 15

3 20

4 10

5 12

7 20

8 15

9 10

11 20

want:

1 10

2 15

3 35

4 30

5 12

7 35

8 15

9 22

10 14

11 20

in my real data the ID is characteric, not sorted.

[BrunoMueller]

Hi

The DATA Step can merge two table, it needs sorted input. Whether a row was read from a table for a given id can be checked by uisng the IN= data set option. For an example to show the concept have a look at the code below. The joinType will show which table contributed to a given id value. With this information you can do whatever needs to be done.

also have a look at the COALESCE function.

data have_a;
  infile cards;
  input
    ID
    amount_a
  ;
cards;
1 10
3 15
4 20
7 15
9 12
10 14
; 

data have_b;
  infile cards;
  input
    ID
    amount_b
  ;
cards;
2 15
3 20
4 10
5 12
7 20
8 15
9 10
11 20
;

data want;
  merge have_a(in=inA) have_b(in=inb);
  by id;
  length joinType $ 2;
  joinType = cats(inA, inB);
run;

Bruno

[fengyuwuzu]

Thank you, Bruno. I learned something was new to me. Thanks!

[Astounding · Accepted solution]

If you are merging in a DATA step, this statement would do it:

total = sum(amount_A, amount_B);

The SUM function ignores missing values.

[fengyuwuzu]

This is great. I was worrying missing values in one causes missing in sum

I was thinking about using In= option in set statement, and then use if condition. Will this work?

[Astounding]

You could use IN=

However, it won't help if you are using SET.  You would need to use MERGE for IN= to help.  If you were using SET, you might code:

data want;

set a b;

by ID;

if first.ID then total = amount;

else total + amount;

if last.ID;

run;

There wouldn't be a need for IN= when using SET.

[fengyuwuzu]

Thank you so much. I learned more today.