Unique duplicates within a column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Unique duplicates within a column


Hello,

I have a dataset along the following lines:

ID          Status

12          0

12          0    

12          1    

12          0    

15          0

15          0

18          1

18          1

19          0

19          1

19          1

19          0

I need my program to look at the ID. If there is any instance where the status is 1 for a given ID, then that is the only observation I am interested in keeping (i.e., I can delete the remaining observations where the status is 0.) If all of the observations for a given ID have a status of 0, then I only need to keep 1 instance. For example, I would like the above dataset to look as follows:

ID          Status

12          1

15          0

18          1

19          1

Any suggestions? Thank you!


Accepted Solutions
Solution
‎09-10-2014 12:40 PM
Super User
Posts: 18,993

Re: Unique duplicates within a column

The code I provided will work...except I put the descending in the wrong place, always forget if its before or after the variable.

proc sort data=have;

by id descending status;

run;

data want;

set have;

by id;

if first.id;

run;

View solution in original post


All Replies
Super User
Posts: 18,993

Re: Unique duplicates within a column

proc sort data=have;

by id status descending;

run;

data want;

set have;

by id;

if first.id;

run;

Trusted Advisor
Posts: 1,228

Re: Unique duplicates within a column

proc sql;
create table want as
select distinct id,status from have
order by id,status;
quit;

data final;
set want;
by id;
if last.id;
run;

Super User
Super User
Posts: 6,842

Re: Unique duplicates within a column

Are there other variables?

If not then it sounds like you want MAX(STATUS) by ID.

proc sql  ;

  create table want as

    select id,max(status) as status

    group by id

  ;

quit;

Occasional Contributor
Posts: 10

Re: Unique duplicates within a column

There are other variables. stat@sas provided the correct code for getting what I want, but it strips the rest of my dataset (only leaving me with id and status).

Solution
‎09-10-2014 12:40 PM
Super User
Posts: 18,993

Re: Unique duplicates within a column

The code I provided will work...except I put the descending in the wrong place, always forget if its before or after the variable.

proc sort data=have;

by id descending status;

run;

data want;

set have;

by id;

if first.id;

run;

Occasional Contributor
Posts: 10

Re: Unique duplicates within a column

Perfect. Thank you so much.

Respected Advisor
Posts: 3,782

Re: Unique duplicates within a column

You should change the correct answer to select .

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 483 views
  • 6 likes
  • 5 in conversation