topic Re: select particular rows based on condition in SAS Enterprise Guide

select particular rows based on condition

dpa — Wed, 17 Aug 2016 11:02:35 GMT

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.

Re: select particular rows based on condition

RW9 — Wed, 17 Aug 2016 11:12:50 GMT

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).

Re: select particular rows based on condition

Ksharp — Wed, 17 Aug 2016 11:48:05 GMT

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;

Re: select particular rows based on condition

Ksharp — Wed, 17 Aug 2016 11:50:03 GMT

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;

Re: select particular rows based on condition

dpa — Wed, 17 Aug 2016 12:17:12 GMT

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

Re: select particular rows based on condition

Ksharp — Wed, 17 Aug 2016 13:07:59 GMT

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;

Re: select particular rows based on condition

dpa — Wed, 17 Aug 2016 13:14:50 GMT

great worked thank you so much ksharp

Re: select particular rows based on condition

dpa — Wed, 17 Aug 2016 13:25:21 GMT

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?

Re: select particular rows based on condition

Ksharp — Thu, 18 Aug 2016 02:03:06 GMT

?? 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 .........

Re: select particular rows based on condition

dpa — Thu, 18 Aug 2016 07:51:54 GMT

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

Re: select particular rows based on condition

Ksharp — Thu, 18 Aug 2016 08:29:36 GMT

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;
..............

Re: select particular rows based on condition

dpa — Thu, 18 Aug 2016 08:46:02 GMT

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?

Re: select particular rows based on condition

Ksharp — Thu, 18 Aug 2016 09:45:54 GMT

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;

Re: select particular rows based on condition

dpa — Thu, 18 Aug 2016 13:07:32 GMT

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.

Re: select particular rows based on condition

Ksharp — Fri, 19 Aug 2016 02:07:58 GMT

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;

Re: select particular rows based on condition

edelv — Tue, 17 Jan 2017 07:53:06 GMT

Hei,

I have a big dataset but i would selct some rows based in a condition.

i have som exeple:

ID org_nr kode v1 v2 v3 v4

1 3 3 x x x

1 3 2 x x x

I have to select

1 3 3

1 3 2

with v1, v2, v3 and v4

What is the prosedure?

Re: select particular rows based on condition

RW9 — Tue, 17 Jan 2017 09:13:00 GMT

Please do not re-open an old question. If you have a new question, open a new post. Include in your post test data - in the form of a datastep - and what the output should look like. Also, be clear as to the logic behind gettnig the output. In your description below you mention nothing of which row to take, 3, 3 appears once, however 3,2 appears multiple times, so which one should it be?