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

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

ginak — Thu, 20 Apr 2017 08:03:19 GMT

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

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

PeterClemmensen — Thu, 20 Apr 2017 08:06:36 GMT

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;

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

RW9 — Thu, 20 Apr 2017 08:26:41 GMT

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;

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

ginak — Thu, 20 Apr 2017 19:46:04 GMT

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

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

ginak — Thu, 20 Apr 2017 19:46:20 GMT

Yes, thank you!!