Deleting duplications in Proc SQL

[Alankar]

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

[LinusH]

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

[Alankar]

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

Thanks a lot.

[DanielSantos]

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

[deleted_user]

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;

[Doc_Duke]

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.

[deleted_user]

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