topic Re: merging two files, add the variable when both ID exist and keep original if ID only in one data in SAS Programming

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

fengyuwuzu — Mon, 18 Apr 2016 16:00:03 GMT

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.

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

Reeza — Mon, 18 Apr 2016 16:10:31 GMT

Each ID is present in each dataset only once?

Can you post sample data and expected output?

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

Astounding — Mon, 18 Apr 2016 16:20:44 GMT

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.

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

fengyuwuzu — Mon, 18 Apr 2016 16:24:44 GMT

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

example:

ID amount

1 10

3 15

4 20

7 15

9 12

10 14

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.

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

fengyuwuzu — Mon, 18 Apr 2016 16:34:36 GMT

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?

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

BrunoMueller — Mon, 18 Apr 2016 16:37:39 GMT

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

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

Astounding — Mon, 18 Apr 2016 16:51:06 GMT

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.

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

fengyuwuzu — Mon, 18 Apr 2016 16:55:40 GMT

Thank you so much. I learned more today.

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

fengyuwuzu — Mon, 18 Apr 2016 16:56:35 GMT

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