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 |
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
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;
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
Thanks. Worked for me.......
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.