BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ahappel
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

3 REPLIES 3
art297
Opal | Level 21

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
Calcite | Level 5

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

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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