Issues with Merging datasets

[ahappel]

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.

Thanks for your time!

[art297 · Accepted solution]

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;

[ahappel]

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

[Ksharp]

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