select distinct

Reply
Contributor
Posts: 42

select distinct

Hi ,

If I have a table with around 200 columns and I want to select distinct rows from it. how could I do it without having to list all the column names in the select query.

Will   '  select distinct * from tablname work '  ?


Thanks

Super Contributor
Posts: 644

Re: select distinct

select distinct * from tablname.work 


should work in Proc SQL.  Try it.  Copy any error messages so we can be more helpful.


Richard

N/A
Posts: 1

Re: select distinct

Yes, SELECT DISTINCT * works in SAS!

Here are a couple ways of doing it.

-Sy

*-------------------------------------------
PROC SQL;
CREATE TABLE HR_Unique AS
SELECT DISTINCT *
FROM HR_Composite;
QUIT;

* Another method would be using PROC SORT with NODUPKEY;

PROC SORT DATA = HR_Composite
    OUT = HR_Unique2 NODUPKEY;
    BY _all_;
RUN;

SAS LOGS:
41   PROC SQL;
42       CREATE TABLE HR_Unique AS
43       SELECT DISTINCT *
44       FROM HR_Composite;
NOTE: Table WORK.HR_UNIQUE created, with 946 rows and 3 columns.

45   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


46
47   * Another method would be using PROC SORT with NODUPKEY;
48
49   PROC SORT DATA = HR_Composite
50             OUT = HR_Unique2 NODUPKEY;
51             BY _all_;
52   RUN;

NOTE: There were 12565 observations read from the data set

WORK.HR_COMPOSITE.
NOTE: 11619 observations with duplicate key values were deleted.
NOTE: The data set WORK.HR_UNIQUE2 has 946 observations and 3 variables.
NOTE: PROCEDURE SORT used (Total process time):

Ask a Question
Discussion stats
  • 2 replies
  • 291 views
  • 1 like
  • 3 in conversation