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

[vidyasagar1]

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

[ballardw · Accepted solution]

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.

[vidyasagar1]

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

[Tom]

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;
  if not found;
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);