Remove group by conditions

[lydiawawa]

Hi All,

I have a similar structure of dataset as of the following:

Session       Code

1                   7.1

1                   10.1

1                   10.2

1                   10.3

2                   10.2

2                   10.3

3                   10.2

3                   10.3

4                   7.1

I'm trying to extract cases by session. The condition is that if a session contains code 7.1 then the session is removed, and we only keep the sessions with code 10.2 or 10.3, but these sessions must not contain code 7.1.

The desired output should be:

Session       Code

2                   10.2

2                   10.3

3                   10.2

3                   10.3

My current code below for some reason does not remove session 1:

data have;
 input session$ code$;
 cards;
1     7.1
1     10.1
1     10.2
1     10.3
2     10.2
2     10.3
3     10.2
3     10.3
4      7.1
;

run;

proc sql;
 create table want as
 select session, code
 from have
 group by session
 having code ne "7.1" and (code = "10.2" or code = "10.3");

quit;

[gamotte · Accepted solution]

Hello,

The condition you want to check concerns a group of rows so you have to express it using an agregate function :

proc sql;
 create table want as
 select session, code
 from have
 group by session
 having sum(code="7.1")=0; /* meaning no row in the group has code="7.1" */
quit;

[Jagadishkatam]

Please try

proc sql;
create table want as select session,code from have where session not in  (select session from have where code='7.1');
quit;

[Riteshdell]

Try this logic.

proc sql;
create table want as select * from have 
group by session, code having session not in (select session  from have where code eq '7.1');
quit;

[PeterClemmensen]

A data step approach

data want(drop=flag);
   do until (last.session);
      set have;
      by session;
      if code=7.1 then flag=1;
   end;
   do until (last.session);
      set have;
      by session;
      if flag ne 1 then output;
   end;
run;