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!
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;
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;
Thanks, I feel like i tried several variants of that code and none of them worked.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.