CODEWITHSUNDEEP
sqlsas
LovetoLearn
LovetoLearn

Reputation: 41

SAS/PROC SQL - remove ALL observations in BY group as long as there are duplications (not just remove the duplications)

I am new to SAS and I am trying to remove groups if they fulfil two conditions. I currently have this data set:

ID ID_2 ID_3;

A 1 1;

A 1 1;

A 1 1;

A 2 0;

A 2 1;

B 3 0;

B 3 0;

I am grouping by ID then by ID_2.

I want to remove ALL entries in the by groups as long as (1) there exists duplication across all three variables - I don't just want to remove the duplicates, I would like to remove the entire group AND (2) this duplication involves value '1' in ID_3 across all rows in each by group.

In other words, the outcome I want is:

ID ID_2 ID_3;

A 2 0;

A 2 1;

B 3 0;

B 3 0;

I have spent at least 5 hours on this and I have tried various methods:

I am open to using proc sql as well. Would really appreciate any input at all, thank you in advance!

Upvotes: 0

Views: 258

Answers (1)

vknowles
vknowles

Reputation: 784

I believe this will accomplish what you want. The logic could be tweaked to be a little more clear, I guess, but it worked when I tested it.

data x;
    input id $ id_2 id_3;
cards;
A 1 1
A 1 1
A 1 1
A 2 0
A 2 1
B 3 0
B 3 0
;
run;

* I realize the data are already sorted, but I think it is better
* not to assume they are.;
proc sort data=x;
    by id id_2 id_3;
run;

* It is helpful to create a dataset for the duplicates as well as the 
* unduplicated observations.;
data nodups
     dups
     ;

    set x;
    by id id_2 id_3;

    * When FIRST.ID_3 and LAST.ID_3 at the same time, there is only
    * one obs in the group, so keep it;
    if first.id_3 and last.id_3
     then output nodups;

     * Otherwise, we know we have more than one obs. According to
     * the OP, we keep them, too, unless ID_3 = 1;
     else do;
        if id_3 = 1
         then output dups;
         else output nodups;
     end;

run;

Upvotes: 1

Related Questions