BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fengyuwuzu
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

8 REPLIES 8
Reeza
Super User

Each ID is present in each dataset only once? 

 

Can you post sample data and expected output? 

fengyuwuzu
Pyrite | Level 9

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
SAS Super FREQ

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
Pyrite | Level 9
Thank you, Bruno. I learned something was new to me. Thanks!
Astounding
PROC Star

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
Pyrite | Level 9

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
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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