topic Re: Remove group by conditions in SAS Programming

Remove group by conditions

lydiawawa — Thu, 06 Jun 2019 12:48:01 GMT

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;

Re: Remove group by conditions

gamotte — Thu, 06 Jun 2019 12:55:32 GMT

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;

Re: Remove group by conditions

Jagadishkatam — Thu, 06 Jun 2019 12:59:10 GMT

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;

Re: Remove group by conditions

Riteshdell — Thu, 06 Jun 2019 13:13:47 GMT

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;

Re: Remove group by conditions

PeterClemmensen — Thu, 06 Jun 2019 14:05:08 GMT

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;