keep selected records

Accepted Solution Solved
Reply
Regular Contributor
Posts: 168
Accepted Solution

keep selected records

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


Accepted Solutions
Solution
‎09-09-2014 11:47 AM
Trusted Advisor
Posts: 1,228

Re: keep selected records

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;

View solution in original post


All Replies
Solution
‎09-09-2014 11:47 AM
Trusted Advisor
Posts: 1,228

Re: keep selected records

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;

🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 182 views
  • 0 likes
  • 2 in conversation