DATA Step, Macro, Functions and more

Issues with Merging datasets

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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.

Thanks for your time!


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

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;

View solution in original post


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

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,046

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 126 views
  • 1 like
  • 3 in conversation