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
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;
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;
Thanks Kurts.....works perofectly.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.