BookmarkSubscribeRSS Feed
Sandhya
Fluorite | Level 6
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.
4 REPLIES 4
deleted_user
Not applicable
If I understand right, you can try something like this:
proc sql;
delete from ds2
where cid in (select distinct cid from ds1);
quit;
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
DanielSantos
Barite | Level 11
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
Sandhya
Fluorite | Level 6
Thank you for all your suggestions. It was very helpful. Also, got to know about dupout. Cool feature.

Sandy.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 1211 views
  • 0 likes
  • 4 in conversation