Obsidian | Level 7

## If the sequence is not continuous i need to delete all remaining observations for particular part_no

Input:

data test; infile datalines; input @2 part_no \$4. @7 seq_no 2.; datalines; 1234 1 1234 2 1234 3 1234 4 1234 1 1234 2 1234 3 1234 4 1234 5 1234 6 1456 1 1456 2 1456 3 1456 4 1456 1 1456 2 1456 3 1456 4 1456 5 ; run;

I will require output like :

part_no seq_no
1234      1
1234      2
1234      3
1234      4
1456      1
1456      2
1456      3
1456      4

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: If the sequence is not continuous i need to delete all remaining observations for particular par

For your example data, once the data step is cleaned up a bit, this gets the desired result.

```data test;
infile datalines;
input part_no :\$4. seq_no :2.;
datalines;
1234 1
1234 2
1234 3
1234 4
1234 1
1234 2
1234 3
1234 4
1234 5
1234 6
1456 1
1456 2
1456 3
1456 4
1456 1
1456 2
1456 3
1456 4
1456 5
;
run;

/* assumes test is sorted by Part_no*/
data want;
set test;
by part_no;
retain counter;
if first.part_no then counter=1;
else counter+1;
if counter ne seq_no then delete;
drop counter;
run;```

However this is very subject to the actual order of values and if the blocks of seq_no do not behave as shown then this won't work.

Your data step does not execute because 1) the @2 and such get scrambled into uselessness because the message windows on the forum will remove much white space (blanks and such). 2) Datalines must be on a line all by itself. The data starts on the following line. 3) the ending ; or ;;;; for a data lines block must be on a line by itself as well.

And the data in one line doesn't work with the given input statement.

It is best to post code into a code box opened with the </> or "running man" icons to preserve formatting.

3 REPLIES 3
Super User

## Re: If the sequence is not continuous i need to delete all remaining observations for particular par

For your example data, once the data step is cleaned up a bit, this gets the desired result.

```data test;
infile datalines;
input part_no :\$4. seq_no :2.;
datalines;
1234 1
1234 2
1234 3
1234 4
1234 1
1234 2
1234 3
1234 4
1234 5
1234 6
1456 1
1456 2
1456 3
1456 4
1456 1
1456 2
1456 3
1456 4
1456 5
;
run;

/* assumes test is sorted by Part_no*/
data want;
set test;
by part_no;
retain counter;
if first.part_no then counter=1;
else counter+1;
if counter ne seq_no then delete;
drop counter;
run;```

However this is very subject to the actual order of values and if the blocks of seq_no do not behave as shown then this won't work.

Your data step does not execute because 1) the @2 and such get scrambled into uselessness because the message windows on the forum will remove much white space (blanks and such). 2) Datalines must be on a line all by itself. The data starts on the following line. 3) the ending ; or ;;;; for a data lines block must be on a line by itself as well.

And the data in one line doesn't work with the given input statement.

It is best to post code into a code box opened with the </> or "running man" icons to preserve formatting.

Obsidian | Level 7

## Re: If the sequence is not continuous i need to delete all remaining observations for particular par

Awesome .. Thank you so much ...
Thank you for the input to post code on code box ..I will do it from next time .. 🙂
Super User

## Re: If the sequence is not continuous i need to delete all remaining observations for particular par

You need to retain a flag variable so you know when the sequence error has appeared.

Here is code that does what you want by comparing the current value to SEQ_NO to the previous value plus 1.  It resets the flag for each new PART_NO group.

``````data test;
input part_no \$ seq_no @@;
datalines;
1234 1 1234 2 1234 3 1234 4 1234 1 1234 2 1234 3 1234 4 1234 5 1234 6
1456 1 1456 2 1456 3 1456 4 1456 1 1456 2 1456 3 1456 4 1456 5
;
data want;
set test;
by part_no ;
retain found ;
if seq_no ne sum(lag(seq_no),1) then found=1;
if first.part_no then found=0;
run;``````

If you want to also test if the sequence starts with one the change the value set on first observation.

``  if first.part_no then found=(seq_no ne 1);``
Discussion stats
• 3 replies
• 363 views
• 1 like
• 3 in conversation