topic Re: Compare Two Datasets and Remove Duplicates in SAS Enterprise Guide

Compare Two Datasets and Remove Duplicates

jen123 — Sun, 12 Aug 2012 02:26:44 GMT

Hello,

I have two datasets: Master dataset (with all weeks' data) and Weekly (current week's data). The Weekly file could contain updates to IDs that are existing in the Master file. I've tried to use UPDATE statement but had no luck with it. So I am going to go about it a different way.

I would like to compare the two files by a field name: app_id. If the same application id is in both datasets, then I would like remove them from the Master file. Once I have a "modified" Master file, I then can merge the Master and Weekly files. I know how to remove duplicates within the same dataset, but have never compared between two.

What function or statement would I use to accomplish this. Using an inner join will give me the IDs that are in both datasets, but would not delete the ID from the Master file.

PROC SQL;

CREATE TABLE MASTER_NEW AS

SELECT t1.APPLICATION_ID

FROM MASTER t1 INNER JOIN WEEKLY t2 ON (t1.APPLICATION_ID NOT = t2.APPLICATION_ID);

QUIT;

The MASTER_NEW table should only have IDs from T1 that are not in T2.

Any advise is appreciated.

Re: Compare Two Datasets and Remove Duplicates

HK_EndeavourForever — Sun, 12 Aug 2012 13:24:49 GMT

Hi Jen,

Can you please tell if there is any specific reason why you prefer to use PROC SQL?

The requirement can be achieved by DATA MERGE statements. Please see below two ways where the MASTER_NEW will have only IDs from T1 and not T2.

DATA MASTER_NEW;

MERGE MASTER (IN=T1) WEEKLY (IN=T2);

BY APPLICATION_ID;

IF T1 AND NOT T2 THEN OUTPUT;

RUN;

DATA MASTER_NEW;

MERGE MASTER (IN=T1) WEEKLY (IN=T2);

BY APPLICATION_ID;

IF T1 AND T2 THEN DELETE;

RUN;

And the code piece below is one single step to arrive at your final result, to have MASTER dataset have the updates from WEEKLY without the need to merge them seperately after refining the MASTER dataset.

This way:

DATA MASTER_NEW;

MERGE MASTER (IN=T1) WEEKLY (IN=T2);

BY APPLICATION_ID;

IF T1 AND T2 THEN PUT T2;

IF T1 AND NOT T2 THEN PUT T1;

RUN;

***But remember that to use DATA MERGE statements, your sas datasets need to be in sorted order by application_id field.

And I have given the code based on the assumption the MASTER and WEEKLY has the same layout and WEEKLY doesn't have new records not present in MASTER.

And if you still want to use PROC SQL, LEFT JOIN with COALESCE is what you need to look at. SAS Website has good description of LEFT JOIN with examples.

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473712.htm

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002206368.htm

Thank You!!

Re: Compare Two Datasets and Remove Duplicates

jen123 — Sun, 12 Aug 2012 13:34:20 GMT

Thank you for the response!

I did try the MERGE statement and it didn't work. That's because I had opposite of what you suggested - I had the table names after PUT reversed:

DATA MASTER_NEW;

MERGE MASTER (IN=T1) WEEKLY (IN=T2);

BY APPLICATION_ID;

IF T1 AND T2 THEN PUT T1;

IF T1 AND NOT T2 THEN PUT T2;

RUN;

Thanks for the suggestion!

Re: Compare Two Datasets and Remove Duplicates

Amir — Mon, 13 Aug 2012 10:57:31 GMT

Hi,

I agree that a merge statement should work in this situation, but please be aware that the in= data set option "Creates a variable that indicates whether the data set contributed data to the current observation" - from the documentation. So in the merge statement data set options, T1 and T2 are just variables, they are not like the table alias names specified in the proc sql code.

Further the put statement is not used to output data to a SAS data set, for that in your code you should use the output statement along with the data set name you want to output to, e.g.:

DATA MASTER_NEW /* WEEKLY_NEW */;

MERGE MASTER (IN=T1) WEEKLY (IN=T2);

BY APPLICATION_ID;

/* IF T1 AND T2 THEN OUTPUT WEEKLY_NEW; */

IF T1 AND NOT T2 THEN OUTPUT MASTER_NEW;

RUN;

I was not sure if you wanted a WEEKLY_NEW data set, if you do then you can just remove the comment markers "/*" and "*/".

hth.

Regards,

Amir.

Message was edited by: Amir Malik - minor correction.