keep selected records

[sam369]

Hi All,

I have to keep, if certain grp have both "ABC' and 'DEF' types for that grp i need to keep Type "ALL"

Have data:

grp       type

1          All

1          All

1          All

1          All

1         ABC

1         ABC

1         ABC

1         ABC

2         All

2          All

2          All

2          All

2         ABC

2         ABC

2         ABC

2         ABC

2         DEF

2         DEF

2         DEF

2         DEF

3         All

3         All

2          All

3          All

3         DEF

3         DEF

3         DEF

3         DEF

want:

grp       type

1         ABC

1         ABC

1         ABC

1         ABC

2         All

2          All

2          All

2          All

2         ABC

2         ABC

2         ABC

2         ABC

2         DEF

2         DEF

2         DEF

2         DEF

3         DEF

3         DEF

3         DEF

3         DEF

This is my try:

data want;

do until(last.grp);

set have(keep=group type);

by grp type;

flg=(TYPE eq 'ALL')*1+(TYPE eq 'ABC')*2+(TYPE eq 'DEF')*3;

if flg in(2,3) then keep=1;

output;

end;

run;

Thanks

Sam

[stat_sas · Accepted solution]

proc sql;

create table want as

select * from have

group by grp

having count(distinct type)=3 or type ne 'All'

order by grp, type;

quit;

proc print data=want;

run;