topic Re: getting only the non intersection records in SAS Procedures

getting only the non intersection records

Sandhya — Fri, 15 Jan 2010 16:45:48 GMT

Hi,

I have few data sets like DS1, DS2 and DS3 with "cid" as the only variable.

DS1 DS2 DS3

CID CID CID
100 100 101
101 102 106
102 105 107
103 108
104

In these Data sets there are some records which is present in more than one data set. I want the remove the intersecting records for the other data sets. My way is to use merge statement for each combination to remove the intersections, but it is so ugly and lot of data steps. Is there a smooth ways of dealing with such issues?

Thanks in advance.

Sandy.

Re: getting only the non intersection records

deleted_user — Fri, 15 Jan 2010 17:03:31 GMT

If I understand right, you can try something like this:
proc sql;
delete from ds2
where cid in (select distinct cid from ds1);
quit;

Re: getting only the non intersection records

sbb — Fri, 15 Jan 2010 17:11:26 GMT

Sounds like you want to replace your original files (at some point) and eliminate the redundant observations (based on CID values).

Explore using PROC SORT NODUPKEY with DUPOUT and identify each incoming observation by file and observation_number. With the DUPOUT file, then you have a source to use to DELETE observations back in the original file with a MERGE/BY process.

Scott Barry
SBBWorks, Inc.

Re: getting only the non intersection records

DanielSantos — Fri, 15 Jan 2010 17:21:35 GMT

If I got this right, a single merge with every datasets will suffice.

You just need to work a subsetting if that will suite your needs.

For example the following:
[pre]
data OUT;
merge DS1 (in=D1) DS2 (in=D2) DS3 (in=D3);
by CID;
if (D1 and D2 and D3) then delete;
run;
[/pre]
Or..
[pre]
data OUT;
merge DS1 (in=D1) DS2 (in=D2) DS3 (in=D3);
by CID;
if not (D1 and D2 and D3);
run;
[/pre]
both will do the same, and will exclude the observations that full match on the 3 tables (3 way intersection).

Bare in mind that one requisite for the merge to work, is that only one of the tables may hold duplicate key values.

Check this info:
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a000761932.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: getting only the non intersection records

Sandhya — Fri, 15 Jan 2010 19:51:20 GMT

Thank you for all your suggestions. It was very helpful. Also, got to know about dupout. Cool feature.

Sandy.