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

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.

1 ACCEPTED SOLUTION

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

16 REPLIES 16
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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

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

dpa
Obsidian | Level 7 dpa
Obsidian | Level 7

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

 

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

dpa
Obsidian | Level 7 dpa
Obsidian | Level 7

great worked thank you so much ksharp

 

dpa
Obsidian | Level 7 dpa
Obsidian | Level 7

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?

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


dpa
Obsidian | Level 7 dpa
Obsidian | Level 7

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

Ksharp
Super User
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;
..............
dpa
Obsidian | Level 7 dpa
Obsidian | Level 7

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?

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

dpa
Obsidian | Level 7 dpa
Obsidian | Level 7

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.

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




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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 16 replies
  • 1678 views
  • 0 likes
  • 4 in conversation