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.
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.
Each ID is present in each dataset only once?
Can you post sample data and expected output?
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.
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
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.
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?
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: