re: removing duplicates

twildone — Sat, 25 Feb 2023 19:16:09 GMT

Hi...I am not quite getting what I need. I need to remove duplicate records. The criteria for the removal is that for the grouping of ID, Term, Department and Section, if there is at least one record where the entry for Condition is blank, then all records having a "W" for Condition are to be deleted. If either all entries for condition are blank or "W", then all records need to kept.If anyone can help it would be greatly appreciated. thanks

data Have;
    length ID 8 Term $ 9 Department $ 5 Type $ 5 Section $ 5 Condition $ 2;
    format ID F12. Term $char9. Department $char5. Type $char5. Section $char5. Condition $char2.;
    informat ID best12. Term $char9. Department $char5. Type $char5. Section $char5. Condition $char2.;
    infile datalines4 dlm='7F'x missover dsd;
    input ID : best32. Term : $char9. Department : $char5. Type : $char5. Section : $char5. Condition  : $CHAR2. ;
datalines4;
1746362022-2023ASTAttndF22D1 
1746362022-2023ASTAttndW22 
969052020-2021BAAAttndS20W
969052020-2021BAAAttndW21B2 
1569682021-2022BAAAttndF21B2W
1569682021-2022BAAAttndW22B2W
1606422020-2021BAAAttndS21 
1606422020-2021BAAAttndW21B2W
;;;;

 proc sort data=Have;
  by ID Term Department Type descending Condition;
run;

data want(drop=found);
 do until(last.Type);
  set Have;
  by ID Term Department Type;
  if Condition^='' then found=1;
 end;
 
 do until(last.Type);
  set Have;
  by  ID Term Department Type;
  if found then do; 
    if last.Type then output;
    end;
   else output;
 end;
run;


Want:

ID	Term	Department	Type	Section	Condition
96905	2020-2021	BAA	Attnd	W21B2	
156968	2021-2022	BAA	Attnd	F21B2	W
156968	2021-2022	BAA	Attnd	W22B2	W
160642	2020-2021	BAA	Attnd	S21	
174636	2022-2023	AST	Attnd	F22D1	
174636	2022-2023	AST	Attnd	W22

Re: re: removing duplicates

Kurt_Bremser — Sat, 25 Feb 2023 19:38:51 GMT

Your code does not reflect your rules. First look for a blank, then use this as flag:

data want(drop=found);
 do until(last.Type);
  set Have;
  by ID Term Department Type;
  if Condition = '' then found = 1;
 end;
 do until(last.Type);
  set Have;
  by  ID Term Department Type;
  if not found or condition ne 'W' then output; 
 end;
run;

Re: re: removing duplicates

twildone — Sat, 25 Feb 2023 19:50:07 GMT

Thanks Kurts.....works perofectly.

topic re: removing duplicates in SAS Programming

re: removing duplicates

Re: re: removing duplicates

Re: re: removing duplicates