BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi there

Firstly apologies if this question is in the wrong place.

Secondly, more apologies for being such a newbie and having to ask about such basic things!

and finally.....
A bit of a theoretical question for you all.
How would you go about deleting all duplicate observations from a dataset??

eg: I have two datasets containing the same information, downloaded a month apart. The latest dataset contains all the observations which appear in the earlier set plus a few dozen new observations.

I know NODUP and NODUPKEY only write the first occurrence of a duplicate to the set you’re creating. That’s great, but I want to remove both occurrences of the duplicates, leaving me with a dataset that contains only the new observations.

Any useful tips would be much appreciated.

Thanks in advance
14 REPLIES 14
DBailey
Lapis Lazuli | Level 10
proc sql;
create table tabB as
select obs from tabA
group by obs having count(*)>1;

delete from tabA where obs in (select obs from tabB);
quit;
buckeye
Obsidian | Level 7
I wonder if it achieves the intended goal. Adre the remaing obs all new ones? I'd like to know how to delete extra dup using SQL. Thanks in advance.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
How would you distinguish "..only the new observation"? If there is some event-date/time variable, you can sort the file with your "base" BY variable list along with DESCENDING -- but don't code NODUPKEY in the first sort.

Then issue a second SORT using NODUPKEY along with the EQUALS parameter while only providing your "base" BY variable list.

The other alternative using a DATA step is to ensure that your input file gets sorted into the desired sequence, then use a DATA step with a SET and a BY statement with your "base" BY variable list. And in an IF /THEN OUTPUT; statement, use the LAST. technique so that only the last-occurrence of any "by variable group" of observations (presumably the newest obs?) will be output.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search arguments, this topic / post:

proc sort nodupkey site:sas.com

data step by group processing site:sas.com

data step remove duplicates site:sas.com
RickM
Fluorite | Level 6
I think people might be overthinking this. How about:

[pre]
proc sql;
select * from new
except
select * from old;
[/pre]
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
To RickM: How would the PROC SQL example address the OP's stated objective with removing all duplicates within a given SAS file, while only retaining the "most recent" observation for a given "by variable list"?

Scott Barry
SBBWorks, Inc.
RickM
Fluorite | Level 6
sbb,

From the OP:
"eg: I have two datasets containing the same information, downloaded a month apart. The latest dataset contains all the observations which appear in the earlier set plus a few dozen new observations. "

So the problem, as I interpreted it, is that they are starting out with two data sets. The other solutions assume the data is already combined and removing duplicates within one dataset is the only way to solve the problem. Why not find the new values before the two datasets are put together.
Ksharp
Super User
[pre]
proc sort data=class ;
by name sex weight height;
run;
data class;
set class;
by name sex weight height;
if not (first.height and last.height) then delete;
run;
[/pre]




Ksharp
GreggB
Pyrite | Level 9
Is there a unique identifier in each record? If so....

proc sort data=one;by ID;run;
proc sort data=two;by ID;run;

data match;
merge one (in=a) two (in=b);
by ID;
if a and b then from='M';
if a and not b then from='A';
if not a and b then from='B';
run;
proc freq data=match;
table from;
run;
*/the observations where from='B' are in the 2nd set only*/
data new;
set match;
if from ne 'B' then delete;
run;
deleted_user
Not applicable
Hi there and thanks for all your help,

I probably didn't explain things as clearly as I could, so to clear things up, my problem was two data sets ('this month' and 'last month') with identical variables (although the values of one variable 'annualpremium' may differ from month to month). 'last month' has an unidentified amount of observations which have been dropped and are not contained in 'this month'.

I needed to produce one dataset containing only the dropped observations, ie: those that appear in 'last month' that don't appear in 'this month'. No other criteria at all.

This is how I managed it in the end, probably not the most efficient method but it seems to have generated the right results........


proc sql;
create table combined as
select t1.PolicyNumber,
t1.OriginalStartDate,
t1.AnnualPremium,
t2.PolicyNumber as PolicyNumber2
from thismonth as t1
left join
lastmonth as t2
on t1.PolicyNumber = t2.PolicyNumber;
quit;

data droppedpolicy (keep = PolicyNumber OriginalStartDate AnnualPremium);
set combined;
if PolicyNumber = PolicyNumber2 then delete;
run;
DBailey
Lapis Lazuli | Level 10
how about:
proc sql;
create table DroppedRecs as
select
t1.*
from
lastmonth t1
left join thismonth t2
on t1.policynumber=t2.policynumber
where
t2.policynumber is null;
quit;
sss
Fluorite | Level 6 sss
Fluorite | Level 6
Hi

Proc sort data=datasetname noduprec out=newdatasetname;
by varname1 varname2;
run;
R_A_G_
Calcite | Level 5
Hi,

My suggestion is to watch Cynthia's video on the very same problem where she suggests using NODUPKEY
this is the URL:

http://www.youtube.com/watch?v=Srs7VhEqBts
cadams47
Fluorite | Level 6

Her video was on the basic proc sort. It did not address what the OP was asking.

morgbri
Calcite | Level 5

I realize this is an old thread and the op already came up with a solution, but for future reference and my own development would the "distinct" keyword not have worked to remove all observations that were identical across all variables in proc sql? For instance, 

 

proc sql;

     create table x as

     select DISTINCT (variable1),

              variable2,

              variable3,

              variable4,

              etc....

      from old_table;

quit;

 

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
  • 14 replies
  • 11029 views
  • 0 likes
  • 11 in conversation