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.......
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.