SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Selecting distinct Combinations

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

Selecting distinct Combinations

Hi,

suppose I have the following file:

NameDatesomeinfo
A2010a
A2010a
A2009b
A2009b

What I would like to get is:

NameDatesomeinfo
A2010a
A2009b

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

Thank you


Accepted Solutions
Solution
‎12-21-2014 12:01 AM
Respected Advisor
Posts: 3,895

Re: Selecting distinct Combinations

Hope that helps:

data have;

  infile datalines truncover;

  input name $ date someinfo $;

  datalines;

A 2010 a

A 2010 a

A 2009 b

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,895

Re: Selecting distinct Combinations

Proc sql; select distinct ....

Super Contributor
Posts: 413

Re: Selecting distinct Combinations

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

Super Contributor
Posts: 413

Re: Selecting distinct Combinations

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...

Solution
‎12-21-2014 12:01 AM
Respected Advisor
Posts: 3,895

Re: Selecting distinct Combinations

Hope that helps:

data have;

  infile datalines truncover;

  input name $ date someinfo $;

  datalines;

A 2010 a

A 2010 a

A 2009 b

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;

Super User
Posts: 9,682

Re: Selecting distinct Combinations

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

Super Contributor
Posts: 413

Re: Selecting distinct Combinations

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

Super Contributor
Posts: 312

Re: Selecting distinct Combinations

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

Super User
Super User
Posts: 6,502

Re: Selecting distinct Combinations

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 4150 views
  • 7 likes
  • 5 in conversation