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.......
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.