topic Re: how to search for/ remove subjects (rows) with matching IDs in SAS Programming

how to search for/ remove subjects (rows) with matching IDs

amigapomba — Sat, 09 Feb 2019 23:45:25 GMT

hi all,

I have a large data set that includes multiple rows for some subjects. Can anyone tell me how to (1) view all of the entries for which this is the case and (2) remove all entries except for the first one for each subject, And ideally place them in a different data set?

tia for any help,

leslie

Re: how to search for/ remove subjects (rows) with matching IDs

PaigeMiller — Sat, 09 Feb 2019 23:54:42 GMT

For part 2:

/* UNTESTED CODE */
proc sql;
    create table want as select * from have 
        group by subject having count(subject)=1;
quit;

Re: how to search for/ remove subjects (rows) with matching IDs

Patrick — Sun, 10 Feb 2019 00:15:57 GMT

@amigapomba

Proc Sort should give you an easy option to implement such logic.

data have;
  do subject=3,2,4,1,1,3,2,2;
    otherVar+1;
    output;
  end;
  stop;
run;

proc sort nodupkey
  data=have 
  out=firstSubj  
  dupout=dupSubj 
  ;
  by subject;
run;

title 'firstSubj';
proc print data=firstSubj;
run;
title 'dupSubj';
proc print data=dupSubj;
run;
title;

Re: how to search for/ remove subjects (rows) with matching IDs

amigapomba — Sun, 10 Feb 2019 01:32:23 GMT

Thank you!

Re: how to search for/ remove subjects (rows) with matching IDs

amigapomba — Sun, 10 Feb 2019 01:42:03 GMT

Thank you! I am not sure I need to delete yet so I have not tried your code. does it remove all entries where count>1, including first?

thanks again,

leslie

Re: how to search for/ remove subjects (rows) with matching IDs

PaigeMiller — Sun, 10 Feb 2019 12:25:23 GMT

You are correct, and so I withdraw my solution.