Keeping track of data that didn't make it when merging two datasets

Accepted Solution Solved
Reply
Contributor
Posts: 67
Accepted Solution

Keeping track of data that didn't make it when merging two datasets

Hello!

 

I have two data sets I want to merge. One is application information, the second is registrations. There are some who registered but did not fill out an application.

 

Suppose we have:

 

Application:

 

ID  Name  ... (other variables)

1    Bob

2    Bill

3    Judy

4    John

 

 

Registration:

ID  Name ... (other variables)

1    Bob

2    Bill

3    Judy

4    John

5    Jake

6    Jack

 

I know how to merge them as such:

 

data merge;

merge application (in=a) registration (in = b);

by ID;

if a and b;

run;

 

However. I would like SAS to also give me an output dataset of those who were not in both application and registration (i.e., ID's # 5 and 6 - Jake and Jack).

 

So I'd like code to give me two datasets:

 

Merge: 


ID  Name... (other variables from both datasets)

1    Bob

2    Bill

3    Judy

4    John

5    Jake

6    Jack

 

and remainder:

 


ID  Name ...(other variables)

5    Jake

6    Jack

 

So it gives me the names of just those two where not in the application dataset as well

 

How can I do this? I've seen it before, but can't remember how to search it unfortunately.


Thanks!

Best,

Gina


Accepted Solutions
Solution
‎04-20-2017 03:45 PM
Valued Guide
Posts: 518

Re: Keeping track of data that didn't make it when merging two datasets

Like this?

 

data Application;
input ID $ Name $;
datalines;
1    Bob
2    Bill
3    Judy
4    John
;

data Registration;
input ID $ Name $;
datalines;
1    Bob
2    Bill
3    Judy
4    John
5    Jake
6    Jack
;

data matches nonmatches;
merge application (in=a) registration (in = b);
by ID;
if a and b then output matches;
else output nonmatches;
run;


View solution in original post


All Replies
Solution
‎04-20-2017 03:45 PM
Valued Guide
Posts: 518

Re: Keeping track of data that didn't make it when merging two datasets

Like this?

 

data Application;
input ID $ Name $;
datalines;
1    Bob
2    Bill
3    Judy
4    John
;

data Registration;
input ID $ Name $;
datalines;
1    Bob
2    Bill
3    Judy
4    John
5    Jake
6    Jack
;

data matches nonmatches;
merge application (in=a) registration (in = b);
by ID;
if a and b then output matches;
else output nonmatches;
run;


Contributor
Posts: 67

Re: Keeping track of data that didn't make it when merging two datasets

Yes, thank you!!

Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: Keeping track of data that didn't make it when merging two datasets

Please also use the search bar, and look at the previous posts.  This merging question was asked at least 3 or 4 time yesterday and the day before, and the day before that.  Also, post test data in the form of a datastep!  You can use the alias to output whre you want e.g.: (and I have not test data to check this on):

data inboth other;
  merge first (in=a) second (in=b);
  by keyvariable;
  if a and b then output inboth;
  else output other;
run;

 

Contributor
Posts: 67

Re: Keeping track of data that didn't make it when merging two datasets

Thank you!! Yes, it was late at night and I tried but wasn't sure what keywords exactly to use Smiley Sad Good advice re formatting w/ a data step. Will note.

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 107 views
  • 3 likes
  • 3 in conversation