Non-consecutive Integer Ranges Within Single Column

[jawhitmire]

Current version: 9.04.01M5P091317

I need to find the non-consecutive integer ranges for a single column variable.

data have;
	infile datalines missover;
	input batchno;
datalines;
375
376
377
388
980
981
982
983
2008
2009
2010
;
run;

The output should provide beginning and ending values within each consecutive range. 

That is, the output is a list of the following values for the input dataset shown above:

375

388

980

983

2008

2010

Thank you in advance for assistance.

Jane

[novinosrin]

HI @jawhitmire  

375

388  /*is this correct or should it be 378?*/

My apologies if i am asking a dumb question plz

[novinosrin · Accepted solution]

/*Going with my assumption  378 is what to be in place instead of 388*/

data have;
infile datalines missover; 
input batchno; 
datalines; 
375 
376 
377 
378
980
981
982 
983
2008 
2009
2010
;
run;

data temp;
set have;
k=dif(batchno);
if k ne 1 then grp+1;
drop k;
run;
data want;
set temp;
by grp;
if first.grp or last.grp;
drop grp;
run;

[mkeintz]

You need

1. A way to compare current batchno to the prior batchno.  If the prior batchno is not current-batchno minus 1, then the record-in-hand is a new START.
2. A way to compare current batchno to the following batchno.  If they are not different by exactly 1, then the record-in hand is a new END.
3. Only when you have an END value do you output the start/end pair.

data have;
	infile datalines missover;
	input batchno;
datalines;
375
376
377
388
980
981
982
983
2008
2009
2010
run;

data want (keep=start end);
  merge have have (firstobs=2 keep=batchno rename=(batchno=nxt_batchno));
  if batchno-1^=lag(batchno) then start=batchno;
  retain start;
  if batchno+1^=nxt_batchno;
  end=batchno;
run;

Comparing the current to the prior (lag(batchno)) is relatively intuitive.

Comparing to the next can't rely on the lag function, so you need a way to read in 2 records at a time: the current record and the following record.  Since they have the same variable, you have to rename one of them.  Then do the comparison ("if batchno+1 ^= nxt_batchno;").

[jawhitmire]

This worked perfectly and the output is clean.  It seems my questions are slowly becoming a bit more difficult.  I look forward to the day when I can contribute as much as I ask.

Thank you!

Jane