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

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
ballardw
Super User

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.

View solution in original post

3 REPLIES 3
ballardw
Super User

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
Obsidian | Level 7
Awesome .. Thank you so much ...
Thank you for the input to post code on code box ..I will do it from next time .. 🙂
Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 359 views
  • 1 like
  • 3 in conversation