topic Re: group by in SAS Procedures

group by

rakeshvvv — Wed, 01 Oct 2014 12:42:28 GMT

I have data-set with 4 with variable ID, SEQNUM, NAME, and ACTION.

We should group by id, seqnum, and name in such a way that action variable should not have "not done" for that group. Below is the sample data with ID SEQNUM, NAME, and ACTION as variables

470010002	2	abc	NOT CHANGED
470010002	2	abc	not done
470010002	4	def	NOT CHANGED
470010002	4	def	NOT CHANGED
470010002	7	jkl	NOT CHANGED
470010007	19	xyz	INTERRUPTED
470010007	19	xyz	not done
470010010	10	pqr	not done
470010012	5	suv	NOT CHANGED
470010012	6	lmn	INTERRUPTED
470010012	6	lmn	INTERRUPTED
470010012	6	lmn	INTERRUPTED
470010012	6	lmn	INTERRUPTED
470010012	6	lmn	not done
the output looks like this


470010002	4	def	NOT CHANGED
470010002	4	def	NOT CHANGED
470010002	7	jkl	NOT CHANGED
470010012	5	suv	NOT CHANGED

Re: group by

Loko — Wed, 01 Oct 2014 13:44:45 GMT

Hello,

One solution:

data have ;
infile datalines truncover;
input ID $10. SEQNUM NAME $ ACTION & $20.;
datalines;
470010002 2 abc NOT CHANGED
470010002 2 abc not done
470010002 4 def NOT CHANGED
470010002 4 def NOT CHANGED
470010002 7 jkl NOT CHANGED
470010007 19 xyz INTERRUPTED
470010007 19 xyz not done
470010010 10 pqr not done
470010012 5 suv NOT CHANGED
470010012 6 lmn INTERRUPTED
470010012 6 lmn INTERRUPTED
470010012 6 lmn INTERRUPTED
470010012 6 lmn INTERRUPTED
470010012 6 lmn not done
;

proc sql;
select a.* from have a left join (select id, SEQNUM, NAME from have where trim(action)='not done') b
on a.id=b.id and a.SEQNUM=b.SEQNUM and a.NAME=b.NAME
where b.id is missing;
quit;

Re: group by

Ksharp — Wed, 01 Oct 2014 13:51:27 GMT

data have ;
infile datalines truncover;
input ID $10. SEQNUM NAME $ ACTION & $20.;
datalines;
470010002 2 abc NOT CHANGED 
470010002 2 abc not done 
470010002 4 def NOT CHANGED 
470010002 4 def NOT CHANGED 
470010002 7 jkl NOT CHANGED 
470010007 19 xyz INTERRUPTED 
470010007 19 xyz not done 
470010010 10 pqr not done 
470010012 5 suv NOT CHANGED 
470010012 6 lmn INTERRUPTED 
470010012 6 lmn INTERRUPTED 
470010012 6 lmn INTERRUPTED 
470010012 6 lmn INTERRUPTED 
470010012 6 lmn not done 
;
proc sql;
create table want as
select *
 from have
  group by      ID, SEQNUM ,NAME
   having  sum(ACTION='not done')=0;
quit;

Xia Keshan

Re: group by

rakeshvvv — Wed, 01 Oct 2014 14:11:53 GMT

Thanks. Worked for me.......