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
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.