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