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

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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