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
/*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;
HI @jawhitmire
375
388 /*is this correct or should it be 378?*/
My apologies if i am asking a dumb question plz
/*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;
You need
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;").
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.