Help using Base SAS procedures

Deleting duplications in Proc SQL

Reply
Contributor
Posts: 25

Deleting duplications in Proc SQL

I want to Delete all Duplicate records in Proc SQL step.
we have
Proc sort data = a nodupkey;
By cust;
run;

I need the same result in Proc SQL step.

Thanks
Alankar
Super User
Posts: 5,255

Re: Deleting duplications in Proc SQL

I don't think that there is a direct equivalent to NODUPKEY in SQL.
The closest you'll get (at least with some basic SQL coding) is to use DISTINCT.
But that will work more like NODUPRECS in SORT.

Why do you want to use SQL if SORT works...?


/Linus
Data never sleeps
Contributor
Posts: 25

Re: Deleting duplications in Proc SQL

I'm trying to compare SAS & SQL.
then i got this doubt.

Thanks a lot.
Super Contributor
Posts: 474

Re: Deleting duplications in Proc SQL

Linus is correct, I also think there's no direct way to mimic the NODUPKEYS feature of PROC SORT in SQL.

One way could be:

proc sql noprint;
create table NODUPKEYS (drop = _N) as
select T1.* from
(select monotonic() as _N, * from A) as T1,
(select monotonic() as _N, count(CUST) from A group by CUST) as T2
where T1._N = T2._N ;
quit;

The monotonic() function here, is used similarly as the automatic variable _N_ (try to run the two selects for T1 and T2 separately to see the results of monotonic()).

But it always involves remerging the data with some stat table (in the example T2).
So I guess it will be always much more "messy", complex and inefficient than the SORT NODUPKEYS options.

Greetings from Portugal.

Daniel Santos at www.cgd.pt Message was edited by: Daniel Santos
N/A
Posts: 0

Re: Deleting duplications in Proc SQL

Data XyZ ;
Input ID $ tot cost;
cards;
ABC 10 100
XYZ 20 200
ABC 10 100
;
RUN;
proc sql;
title 'Unique Recods in XYZ Dataset';
create table noduprec as
select *, count(*) as Count
from xyz
group by ID
having count(*) = 1;


proc print data=noduprec;
run;
Trusted Advisor
Posts: 2,113

Re: Deleting duplications in Proc SQL

There is an extensive discussion of the things that are easier in the DATA step vs SQL in the archives of comp.soft-sys.sas . You can search them in Google Groups.
N/A
Posts: 0

Re: Deleting duplications in Proc SQL

Hi Alankar,

Here are some methods
Look for these


Method [1]

Proc sort data = a nodupkey out=one;
By cust;
run;

Method [2]

Create table table_name as
Select distinct * from table;
Quit;


Regards,
sassupport
http://www.reportinghouse.com
Ask a Question
Discussion stats
  • 6 replies
  • 160 views
  • 0 likes
  • 5 in conversation