topic Re: How to delete duplicate if a certain condition is satisfied in SAS Programming

How to delete duplicate if a certain condition is satisfied

lb16fa — Fri, 25 Dec 2020 22:23:59 GMT

Hi everyone! I was wondering how to delete duplicates if a specific variable="yes".

Essentially, I want the code to say IF an observation is a duplicate AND variable = "yes" then delete.

To find duplicates I've been using "if first.var=0 or last.var=0" but I can't seem to combine this with an "AND" statement as well.

Is there any way I can do this?

Thanks a lot in advance!

Re: How to delete duplicate if a certain condition is satisfied

Kurt_Bremser — Fri, 25 Dec 2020 22:29:29 GMT

Please supply example data in a data step with datalines, and show the expected result from that.

Re: How to delete duplicate if a certain condition is satisfied

SASKiwi — Fri, 25 Dec 2020 22:33:17 GMT

Using - if not (first.var and last.var); - will pick up duplicate BY variables. However what does "delete duplicate" mean? Delete all rows in a duplicate group, keep the first row in a duplicate group or the last row?

Re: How to delete duplicate if a certain condition is satisfied

PGStats — Fri, 25 Dec 2020 22:38:26 GMT

The condition should be

if (first.var=0 or last.var=0) and variable = "yes" then delete;

this will get rid of all copies. If you want to keep, say, the first copy and get rid of the others, you could use

if first.var=0 and variable = "yes" then delete;

Re: How to delete duplicate if a certain condition is satisfied

lb16fa — Fri, 25 Dec 2020 22:49:49 GMT

Thank you for the quick response!

Example data would be (lib1.have)

Company Year Variable

Apple 2010 Yes

Apple 2010 No

Microsoft 2012 Yes

Microsoft 2012 No

Then the desired output would be (lib1.want)

Company Year Variable

Apple 2010 No

Microsoft 2012 No

I've been doing something like the code below, but I'm not sure if it's ignoring the first "if" statement and simply deleting everything with variable='Yes'

data lib1.have;
set lib1.want;
by company year
if first.year=0 or last.year=0;
if variable='Yes' then delete;
run;

Re: How to delete duplicate if a certain condition is satisfied

Astounding — Sat, 26 Dec 2020 07:28:31 GMT

It's possible the solution could be a little complex.

What should happen if there are 3 observations, but they are all "No"? Delete all? Delete all but one? Which one (or does it matter)?

Re: How to delete duplicate if a certain condition is satisfied

lb16fa — Sat, 26 Dec 2020 19:12:14 GMT

In that case I'd like to delete all but one observation, how would I code for this? I'm quite new to SAS so I'm still figuring out the more complex issues.

Re: How to delete duplicate if a certain condition is satisfied

SASKiwi — Sat, 26 Dec 2020 21:43:24 GMT

@lb16fa - That question has already been answered by @PGStats in his response.

Re: How to delete duplicate if a certain condition is satisfied

Astounding — Sun, 27 Dec 2020 03:06:52 GMT

Here's an approach that might be just what you want.

proc sort data=have;
   by company year variable;
run;
data want;
   set have;
   by company year;
   if first.year;
run;

It selects just one observation per COMPANY/YEAR, taking a "No" if one exists, and a "Yes" if there aren't any "No" in the data. There's some question in my mind whether this is the result you are looking for, so you will have to comment on that.