topic Re: Selecting distinct Combinations in SAS Data Management

Selecting distinct Combinations

ilikesas — Sun, 21 Dec 2014 04:17:53 GMT

Hi,

suppose I have the following file:

Name	Date	someinfo
A	2010	a
A	2010	a
A	2009	b
A	2009	b

What I would like to get is:

Name	Date	someinfo
A	2010	a
A	2009	b

That is, I would like to get all the columns from my table but with the name/date combination being distinct

Thank you

Re: Selecting distinct Combinations

Patrick — Sun, 21 Dec 2014 04:34:39 GMT

Proc sql; select distinct ....

Re: Selecting distinct Combinations

ilikesas — Sun, 21 Dec 2014 04:49:54 GMT

Hi Patrick and thanks for the reply.

I did the select distinct but it only gave me the 2 selected columns, but what I would like to get if possible is all the columns (like in the small example that I put in the question), in other words to delete all the name/date combination duplicates

I also tried to do

data board_summary6;

set board_summary6;

by comp_name date;

if first.comp_name and first.date;

run;

but got an error message ...

Thank you

Re: Selecting distinct Combinations

ilikesas — Sun, 21 Dec 2014 04:57:40 GMT

I actually could distinct select all the columns of interest but the thing is that since there are many of them I thought if its possible to make a shortcut by selecting the entire row corresponding to the name/date combination...

Re: Selecting distinct Combinations

Patrick — Sun, 21 Dec 2014 05:01:00 GMT

Hope that helps:

data have;

infile datalines truncover;

input name $ date someinfo $;

datalines;

A 2010 a

A 2009 b

;

run;

proc sql;

create table want1 as

select distinct *

from have;

quit;

proc sort data=have out=want2 nodupkey;

by _all_;

run;

proc sort data=have out=inter3;

by name date someinfo;

run;

data want3;

set inter3;

by name date someinfo;

if first.someinfo;

run;

And a fourth option:

data _null_;

if 0 then set have;

dcl hash h1(dataset:'have');

_rc=h1.defineKey(all:'y');

_rc=h1.defineData(all:'y');

_rc=h1.defineDone();

_rc=h1.output(dataset:'Want4');

stop;

run;

Re: Selecting distinct Combinations

Tom — Sun, 21 Dec 2014 05:04:07 GMT

Not sure what you mean.

Sounds like you want to keep only one record for each distinct by group, but that you have extra non-key variables.

You can just use PROC SORT with the NODUPKEY option.

proc sort data=have out=want NODUPKEY ;

by name date;

run;

Re: Selecting distinct Combinations

Ksharp — Sun, 21 Dec 2014 05:58:46 GMT

And the fifth option:

proc summary data=have nway;
 class _all_;
 output out=want(drop=_:);
run;

And the sixth optioin:

proc freq data=have noprint;
 table name*date*someinfo/list out=want1(drop=count percent) nofreq norow nocol nopercent nocum;
run;

Xia Keshan

Re: Selecting distinct Combinations

ilikesas — Sun, 21 Dec 2014 13:20:35 GMT

Thanks a lot to all, now I have a lot to choose from!!!

Re: Selecting distinct Combinations

fengyuwuzu — Wed, 17 Feb 2016 14:48:51 GMT

I think you need to sort with the by variables first.