BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rakeshvvv
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

3 REPLIES 3
Loko
Barite | Level 11

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;

Ksharp
Super User
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

rakeshvvv
Quartz | Level 8

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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