topic Issues with Merging datasets in SAS Programming

Issues with Merging datasets

ahappel — Mon, 21 Nov 2011 20:30:52 GMT

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!

Issues with Merging datasets

art297 — Mon, 21 Nov 2011 20:59:29 GMT

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;

Issues with Merging datasets

ahappel — Mon, 21 Nov 2011 21:17:53 GMT

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

Issues with Merging datasets

Ksharp — Tue, 22 Nov 2011 08:00:52 GMT

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