topic Re: Non-consecutive Integer Ranges Within Single Column in SAS Programming

Non-consecutive Integer Ranges Within Single Column

jawhitmire — Wed, 12 Dec 2018 19:11:38 GMT

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

Re: Non-consecutive Integer Ranges Within Single Column

novinosrin — Wed, 12 Dec 2018 19:42:45 GMT

HI @jawhitmire

375

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

My apologies if i am asking a dumb question plz

Re: Non-consecutive Integer Ranges Within Single Column

novinosrin — Wed, 12 Dec 2018 19:56:25 GMT


/*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;

Re: Non-consecutive Integer Ranges Within Single Column

mkeintz — Wed, 12 Dec 2018 19:58:48 GMT

You need

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

Re: Non-consecutive Integer Ranges Within Single Column

jawhitmire — Wed, 12 Dec 2018 20:17:14 GMT

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