DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 318
Accepted Solution

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

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.


Accepted Solutions
Solution
‎04-18-2016 12:54 PM
Super User
Posts: 5,498

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

Posted in reply to fengyuwuzu

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.

View solution in original post


All Replies
Super User
Posts: 19,770

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

Posted in reply to fengyuwuzu

Each ID is present in each dataset only once? 

 

Can you post sample data and expected output? 

Super Contributor
Posts: 318

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

[ Edited ]

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.

SAS Super FREQ
Posts: 708

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

[ Edited ]
Posted in reply to fengyuwuzu

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

Super Contributor
Posts: 318

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

Posted in reply to Bruno_SAS
Thank you, Bruno. I learned something was new to me. Thanks!
Solution
‎04-18-2016 12:54 PM
Super User
Posts: 5,498

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

Posted in reply to fengyuwuzu

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.

Super Contributor
Posts: 318

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

Posted in reply to Astounding

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?

 

 

Super User
Posts: 5,498

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

Posted in reply to fengyuwuzu

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.

Super Contributor
Posts: 318

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

Posted in reply to Astounding
Thank you so much. I learned more today.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 332 views
  • 1 like
  • 4 in conversation