Help using Base SAS procedures

group by

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

group by

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

4700100022abcNOT CHANGED
4700100022abcnot done
4700100024defNOT CHANGED
4700100024defNOT CHANGED
4700100027jklNOT CHANGED
47001000719xyz INTERRUPTED
47001000719xyznot done
47001001010pqrnot done
4700100125suvNOT CHANGED
4700100126lmn INTERRUPTED
4700100126lmn INTERRUPTED
4700100126lmn INTERRUPTED
4700100126lmn INTERRUPTED
4700100126lmnnot done
the output looks like this
4700100024defNOT CHANGED
4700100024defNOT CHANGED
4700100027jklNOT CHANGED
4700100125suvNOT CHANGED

Accepted Solutions
Solution
‎10-01-2014 09:51 AM
Super User
Posts: 10,023

Re: group by

Posted in reply to rakeshvvv
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

View solution in original post


All Replies
Super Contributor
Posts: 308

Re: group by

Posted in reply to rakeshvvv

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;

Solution
‎10-01-2014 09:51 AM
Super User
Posts: 10,023

Re: group by

Posted in reply to rakeshvvv
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

Frequent Contributor
Posts: 145

Re: group by

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 205 views
  • 0 likes
  • 3 in conversation