topic Re: Selecting rows with only one unique value per subject in SAS Programming

Selecting rows with only one unique value per subject

katsas1 — Thu, 29 Dec 2016 22:59:12 GMT

The dataset looks something like this

VENUE SPORT

A Hockey

B Basketball

C Softball

B Hockey

C Softball

A Football

I need to select only venues with one unique sport played there, so in this case Venue C would get selected because only one sport (softball) is played there. Any help would be greatly appreciated. Thanks.

Re: Selecting rows with only one unique value per subject

Jagadishkatam — Thu, 29 Dec 2016 23:15:08 GMT

proc sort data=have dupout=want nodup;

by venue sport;

run;

want dataset will have your expected output.

Re: Selecting rows with only one unique value per subject

katsas1 — Thu, 29 Dec 2016 23:34:27 GMT

Nodup deletes duplicated observations. I'm after retaining records with a single unique sport per venue and removing those with more than than one unique observation. Nodup retains venues with multiple unique observations.

Re: Selecting rows with only one unique value per subject

Astounding — Thu, 29 Dec 2016 23:36:08 GMT

Another approach:

proc freq data=have;

tables venue * sport / noprint out=combinations;

run;

proc freq data=combinations;

tables venue / noprint out=want (where=(count=1));

run;

proc print data=want;

run;

Re: Selecting rows with only one unique value per subject

Reeza — Fri, 30 Dec 2016 02:15:38 GMT

SQL again offers a one step solution.

proc sql;

create table want as

select venue, count(distinct sport) as num_sports

from have

group by venue

where calculated num_sports=1;

quit;

Re: Selecting rows with only one unique value per subject

Ksharp — Fri, 30 Dec 2016 03:35:11 GMT


data have;
input VENUE $ SPORT : $20.;
cards;
A           Hockey
B           Basketball
C           Softball 
B           Hockey 
C           Softball
A           Football 
;
run;
proc sql;
create table want as
 select *
  from have
   group by venue
    having count(distinct sport)=1;
quit;

Re: Selecting rows with only one unique value per subject

Jagadishkatam — Fri, 30 Dec 2016 04:12:25 GMT

by data step

proc sort data=have nodupkey;
by venue sport;
run;

data want;
set have;
by venue sport;
retain cnt;
if first.venue then cnt=1;
else cnt=cnt+1;
if last.venue and cnt<=1;
run;

Re: Selecting rows with only one unique value per subject

katsas1 — Tue, 03 Jan 2017 16:57:33 GMT

This solution worked great. It's particularly useful if duplicate records need to be retain (like dollar amounts for each row). Thank you!