topic Deleting duplications in Proc SQL in SAS Procedures

Deleting duplications in Proc SQL

Alankar — Thu, 02 Apr 2009 02:58:31 GMT

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

Re: Deleting duplications in Proc SQL

LinusH — Thu, 02 Apr 2009 06:24:09 GMT

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

Re: Deleting duplications in Proc SQL

Alankar — Thu, 02 Apr 2009 06:45:05 GMT

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

Thanks a lot.

Re: Deleting duplications in Proc SQL

DanielSantos — Thu, 02 Apr 2009 07:38:54 GMT

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

Re: Deleting duplications in Proc SQL

deleted_user — Tue, 05 May 2009 22:59:18 GMT

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;

Re: Deleting duplications in Proc SQL

Doc_Duke — Wed, 06 May 2009 03:05:23 GMT

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.

Re: Deleting duplications in Proc SQL

deleted_user — Fri, 08 May 2009 13:23:40 GMT

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