## Issues with Merging datasets

Solved
Occasional Contributor
Posts: 10

# Issues with Merging datasets

Hello,

I am trying to do a simple merge of two data sets. The first one has all of the data needed, the second one has a few missing areas that I would like to have filled with zeros

Say this is data set 1:

input id spp count;

cards;

1w 250 16

1w 322 30

1w 99 2

2r 10 1

2r 99 1

;

And this is the second data set:

input id prey amount;

cards;

1w 250 10

1w 322 15

2r 10 1

;

What I need it to look like is:

input id spp prey count amount;

cards;

1w 250 250 16  10

1w 322 322 30  15

1w  99   99   2    0

2r  10   10   1    1

2r  99   99   1    0

;

The key being where the item is not present in the second set is is given a zero as later I will be obataining the difference between count and amount and continuing on with my manipulation.  I just cant seem to get my head around how to get this to work the way I need to.

Accepted Solutions
Solution
‎11-21-2011 03:59 PM
PROC Star
Posts: 8,164

## Issues with Merging datasets

If you want to use a datastep, I think that the following does what you want:

data first;

input id \$ spp count;

cards;

1w 250 16

1w 322 30

1w 99 2

2r 10 1

2r 99 1

;

data second;

input id \$ prey amount;

cards;

1w 250 10

1w 322 15

2r 10 1

;

proc sort data=first;

by id spp;

run;

proc sort data=second;

by id prey;

run;

data want;

merge first (in=in_first)

second (rename=(prey=spp) in=in_second);

by id spp;

if in_first and not in_second then amount=0;

run;

All Replies
Solution
‎11-21-2011 03:59 PM
PROC Star
Posts: 8,164

## Issues with Merging datasets

If you want to use a datastep, I think that the following does what you want:

data first;

input id \$ spp count;

cards;

1w 250 16

1w 322 30

1w 99 2

2r 10 1

2r 99 1

;

data second;

input id \$ prey amount;

cards;

1w 250 10

1w 322 15

2r 10 1

;

proc sort data=first;

by id spp;

run;

proc sort data=second;

by id prey;

run;

data want;

merge first (in=in_first)

second (rename=(prey=spp) in=in_second);

by id spp;

if in_first and not in_second then amount=0;

run;

Occasional Contributor
Posts: 10

## Issues with Merging datasets

Thanks, I feel like i tried several variants of that code and none of them worked.

Super User
Posts: 10,778

## Issues with Merging datasets

I like Tom's code more.

```data first;
input id \$ spp count;
cards;
1w 250 16
1w 322 30
1w 99 2
2r 10 1
2r 99 1
;
run;

data second;
input id \$ prey amount;
cards;
1w 250 10
1w 322 15
2r 10 1
;
run;

proc sort data=first;
by id ;
run;

proc sort data=second;
by id ;
run;
data want;
ina=0;inb=0;
merge first(in=ina) second(in=inb);
by id;
if not inb then do; prey=spp;amount=0;end;
run;
```

Ksharp

🔒 This topic is solved and locked.