Desktop productivity for business analysts and programmers

select particular rows based on condition

Accepted Solution Solved
Reply
Contributor dpa
Contributor
Posts: 35
Accepted Solution

select particular rows based on condition

Hi All

this is first time I am posting so any mistake please forgive.

ok so I have data like below;

data person;

input spool $ batch $ number;

datalines;

su r1234 1

su r1234 2

su r1234 3

Su r1234 4

su r1234 5

1 r1234 6

1 r1234 7

1 r1234 8

2 r1234 9

2 r1234 10

su r1234 11

su r1234 12

su r1234 13

su r1234 14

3 r1234 15

3 r1234 16

3 r1234 17

su r1235 18

su r1235 19

su r1235 20

su r1235 21

Su r1235 22

Su r1235 23

su r1235 24

1 r1235 25

2 r1235 26

2 r1235 27

;

 

I want to select only last four observation where spool is equal to su or SU where this more than four in sequence and it can be more than one sequence of that in same batch and in sequence can be less than four if it is the case then only select that dont want to include number.

 

I can not sort data on spool as they are timely mannered result.

how I do it in Enterprise guide or in SAS base.


Accepted Solutions
Solution
‎08-17-2016 09:25 AM
Grand Advisor
Posts: 9,596

Re: select particular rows based on condition

OK. Just need a little change .




data have;
input spool $ batch $ number;
spool=upcase(spool);
datalines;
su r1234 1
su r1234 2
su r1234 3
Su r1234 4
su r1234 5
1 r1234 6
1 r1234 7
1 r1234 8
2 r1234 9
2 r1234 10
su r1234 11
su r1234 12
su r1234 13
su r1234 14
3 r1234 15
3 r1234 16
3 r1234 17
su r1235 18
su r1235 19
su r1235 20
su r1235 21
Su r1235 22
Su r1235 23
su r1235 24
1 r1235 25
2 r1235 26
2 r1235 27
;
run;
data want;
do i=1 by 1 until(last.spool);
 set have;
 by batch spool notsorted;
end;

 if spool='SU' then isSU=1;
 
do j=1 by 1 until(last.spool);
 set have;
 by batch spool notsorted;
 if i gt 4 and isSU then do;
  if j=i-3 or j=i-2 or j=i-1 or j=i then output;
 end;
 else output;
end;
 drop i j isSU;
run;

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,263

Re: select particular rows based on condition

Well, if I understand your request correctly:

data person;
input spool $ batch $ number;
datalines;
su r1234 1
su r1234 2
su r1234 3
Su r1234 4
su r1234 5
1 r1234 6
1 r1234 7
1 r1234 8
2 r1234 9
2 r1234 10
su r1234 11
su r1234 12
su r1234 13
su r1234 14
3 r1234 15
3 r1234 16
3 r1234 17
su r1235 18
su r1235 19
su r1235 20
su r1235 21
Su r1235 22
Su r1235 23
su r1235 24
1 r1235 25
2 r1235 26
2 r1235 27
;
run;

data want;
  set person;
  if upcase(spool)="SU" and upcase(lag1(spool))="SU" and upcase(lag2(spool))="SU" and
      upcase(lag3(spool))="SU" then output;
run;

Do note, there is nothing stopping you sorting the data, and then sorting back using the original sort - you should keep that time data in the dataset so you can sort as much as you like.  Also, to get original ordering, you could just set a variable n=_n_; then sort back by n (_n_ is an automatic variable for logical observation position). 

Grand Advisor
Posts: 9,596

Re: select particular rows based on condition

You didn't post output yet ?
What if there are only four ,three or two SU ,you want delete them ?


data have;
input spool $ batch $ number;
spool=upcase(spool);
datalines;
su r1234 1
su r1234 2
su r1234 3
Su r1234 4
su r1234 5
1 r1234 6
1 r1234 7
1 r1234 8
2 r1234 9
2 r1234 10
su r1234 11
su r1234 12
su r1234 13
su r1234 14
3 r1234 15
3 r1234 16
3 r1234 17
su r1235 18
su r1235 19
su r1235 20
su r1235 21
Su r1235 22
Su r1235 23
su r1235 24
1 r1235 25
2 r1235 26
2 r1235 27
;
run;
data want;
do i=1 by 1 until(last.spool);
 set have;
 by batch spool notsorted;
end;

 if spool='SU' then isSU=1;
 
do j=1 by 1 until(last.spool);
 set have;
 by batch spool notsorted;
 if i gt 4 and isSU then do;
  if j=i-3 or j=i-2 or j=i-1 or j=i then output;
 end;
end;
 drop i j isSU;
run;

Grand Advisor
Posts: 9,596

Re: select particular rows based on condition

You didn't post output yet ?
What if there are only four ,three or two SU ,you want delete them ?


data have;
input spool $ batch $ number;
spool=upcase(spool);
datalines;
su r1234 1
su r1234 2
su r1234 3
Su r1234 4
su r1234 5
1 r1234 6
1 r1234 7
1 r1234 8
2 r1234 9
2 r1234 10
su r1234 11
su r1234 12
su r1234 13
su r1234 14
3 r1234 15
3 r1234 16
3 r1234 17
su r1235 18
su r1235 19
su r1235 20
su r1235 21
Su r1235 22
Su r1235 23
su r1235 24
1 r1235 25
2 r1235 26
2 r1235 27
;
run;
data want;
do i=1 by 1 until(last.spool);
 set have;
 by batch spool notsorted;
end;

 if spool='SU' then isSU=1;
 
do j=1 by 1 until(last.spool);
 set have;
 by batch spool notsorted;
 if i gt 4 and isSU then do;
  if j=i-3 or j=i-2 or j=i-1 or j=i then output;
 end;
end;
 drop i j isSU;
run;

Contributor dpa
Contributor
Posts: 35

Re: select particular rows based on condition

[ Edited ]

so basically i want output without row 1,18,19,20.

 and in my data there is quite lot observation with SU or su value and I want to keep last four just before any numbered value

 

Solution
‎08-17-2016 09:25 AM
Grand Advisor
Posts: 9,596

Re: select particular rows based on condition

OK. Just need a little change .




data have;
input spool $ batch $ number;
spool=upcase(spool);
datalines;
su r1234 1
su r1234 2
su r1234 3
Su r1234 4
su r1234 5
1 r1234 6
1 r1234 7
1 r1234 8
2 r1234 9
2 r1234 10
su r1234 11
su r1234 12
su r1234 13
su r1234 14
3 r1234 15
3 r1234 16
3 r1234 17
su r1235 18
su r1235 19
su r1235 20
su r1235 21
Su r1235 22
Su r1235 23
su r1235 24
1 r1235 25
2 r1235 26
2 r1235 27
;
run;
data want;
do i=1 by 1 until(last.spool);
 set have;
 by batch spool notsorted;
end;

 if spool='SU' then isSU=1;
 
do j=1 by 1 until(last.spool);
 set have;
 by batch spool notsorted;
 if i gt 4 and isSU then do;
  if j=i-3 or j=i-2 or j=i-1 or j=i then output;
 end;
 else output;
end;
 drop i j isSU;
run;

Contributor dpa
Contributor
Posts: 35

Re: select particular rows based on condition

great worked thank you so much ksharp

 

Contributor dpa
Contributor
Posts: 35

Re: select particular rows based on condition

Can I ask something as well.

 

How you rename the only SU which are present in middle of batch not the beginning of the batch?

Grand Advisor
Posts: 9,596

Re: select particular rows based on condition

?? I can't understand what you mean. Can you give us an example to explain it ?
You also want know if SU is included in variable Like: 'xxxSuxxxx' ? FIND() and do that.

if find(variable,'su','i') then .........


Contributor dpa
Contributor
Posts: 35

Re: select particular rows based on condition

SO OUTPUT WOULD LIKE THIS

Only want to change the value for SU which comes before any number other than 1
SU  r1234 2
SU r1234 3
Su r1234 4
SU r1234 5
1 r1234 6
1 r1234 7
1 r1234 8
2 r1234 9
2 r1234 10
IBSU r1234 11
IBSU r1234 12
IBSU r1234 13
IBSU r1234 14
3 r1234 15
3 r1234 16
3 r1234 17
SU r1235 21
SU r1235 22
Su r1235 23
SU r1235 24
1 r1235 25
2 r1235 26
2 r1235 27

Grand Advisor
Posts: 9,596

Re: select particular rows based on condition

You want set SPOOL value back as what it is ? Make a copy variable and keep it in the final dataset.
Where is IBSU coming from ?


data have;
input spool $ batch $ number;
copy_spool=spool;
spool=upcase(spool);
datalines;
..............
Contributor dpa
Contributor
Posts: 35

Re: select particular rows based on condition

I want to replace SU to IBSU but only that SU which inbetween batch not in the beginning of new batch  does it make sense?

Grand Advisor
Posts: 9,596

Re: select particular rows based on condition

OK. It is not a big deal.




data have;
input spool $ batch $ number;
spool=upcase(spool);
datalines;
su r1234 1
su r1234 2
su r1234 3
Su r1234 4
su r1234 5
1 r1234 6
1 r1234 7
1 r1234 8
2 r1234 9
2 r1234 10
su r1234 11
su r1234 12
su r1234 13
su r1234 14
3 r1234 15
3 r1234 16
3 r1234 17
su r1235 18
su r1235 19
su r1235 20
su r1235 21
Su r1235 22
Su r1235 23
su r1235 24
1 r1235 25
2 r1235 26
2 r1235 27
;
run;
data temp1;
do i=1 by 1 until(last.spool);
 set have;
 by batch spool notsorted;
end;

 if spool='SU' then do;isSU=1;count+1;end;
 
do j=1 by 1 until(last.spool);
 set have;
 by batch spool notsorted;
 if i gt 4 and isSU then do;
  if j=i-3 or j=i-2 or j=i-1 or j=i then output;
 end;
 else output;
end;
 drop i j isSU;
run;
proc sql;
create table temp2 as
 select *,max(count) as max_count from temp1;
quit;
data want;
 set temp2;
 if spool='SU' and (count ne 1 and count ne max_count) then spool='IBSU';
 drop count max_count;
run;

Contributor dpa
Contributor
Posts: 35

Re: select particular rows based on condition

No I doesn't work as when you add more data as we have then it also converts the SU to IBSU which are present before 1 for  new batch.if you see in data there is two diffrent batch and for each batch i have first four SU and then 1,2 and so on and sometime in between number as well but I dont want to change su which are present first four for each batch.

Grand Advisor
Posts: 9,596

Re: select particular rows based on condition

Opps. I missed the group variable BATCH .
This ought to be right.




data have;
input spool $ batch $ number;
spool=upcase(spool);
datalines;
1 r1234 6
1 r1234 7
1 r1234 8
su r1234 1
su r1234 2
su r1234 3
Su r1234 4
su r1234 5
1 r1234 6
1 r1234 7
1 r1234 8
2 r1234 9
2 r1234 10
su r1234 11
su r1234 12
su r1234 13
su r1234 14
3 r1234 15
3 r1234 16
3 r1234 17
su r1235 18
su r1235 19
su r1235 20
su r1235 21
Su r1235 22
Su r1235 23
su r1235 24
1 r1235 25
2 r1235 26
2 r1235 27
;
run;
data want;
do i=1 by 1 until(last.spool);
 set have;
 by batch spool notsorted;
 if first.batch then count=0;
end;

 if spool='SU' then do;isSU=1;count+1;end;
 
do j=1 by 1 until(last.spool);
 set have;
 by batch spool notsorted;
 if spool='SU' and count ne 1 then spool='IBSU';

 if i gt 4 and isSU then do;
  if j=i-3 or j=i-2 or j=i-1 or j=i then output;
 end;
 else output;
end;
 drop i j isSU;
run;




☑ This topic is SOLVED.

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

Discussion stats
  • 16 replies
  • 511 views
  • 0 likes
  • 4 in conversation