I am trying to count and group a sequence of numbers from the Input and produce the output.

Reply
Occasional Contributor
Posts: 10

I am trying to count and group a sequence of numbers from the Input and produce the output.

Input

100000001

100000002

100000003

100000004

100000010

100000011

100000012

100000101

100000102

100000103

100000104

100000105

100000107

Desired output

First Number     Last Number     Count

100000001      100000004          4

100000010      100000012          3

100000101      100000105          5

100000107      100000107          1

Super User
Posts: 19,772

Re: I am trying to count and group a sequence of numbers from the Input and produce the output.

How do you know what's a group?

Occasional Contributor
Posts: 10

Re: I am trying to count and group a sequence of numbers from the Input and produce the output.

A group is defined as as a consdecutive string of sequential numbers until there is a break in the sequence and then a new group begins.

Respected Advisor
Posts: 3,156

Re: I am trying to count and group a sequence of numbers from the Input and produce the output.

Try this:

data have;

     input number;

     cards;

100000001

100000002

100000003

100000004

100000010

100000011

100000012

100000101

100000102

100000103

100000104

100000105

100000107

;

data want;

     merge have have(firstobs=2 rename=number=_num) end=last;

     count+1;

     if _num-number>1 or last then

           do;

                output;

                count=.;

           end;

     drop _num;

run;

Respected Advisor
Posts: 3,156

Re: I am trying to count and group a sequence of numbers from the Input and produce the output.

Oops, forgot one element:

data have;

input number;

cards;

100000001

100000002

100000003

100000004

100000010

100000011

100000012

100000101

100000102

100000103

100000104

100000105

100000107

;

data want;

retain first_num;

merge have have(firstobs=2 rename=number=_num) end=last;

      count+1;

      if count=1 then first_num=number;

if _num-number>1 or last then

           do;

                output;

                count=.;

           end;

drop _num;

run;

Occasional Contributor
Posts: 10

Re: I am trying to count and group a sequence of numbers from the Input and produce the output.

Thanks, this should work.

Respected Advisor
Posts: 3,156

Re: I am trying to count and group a sequence of numbers from the Input and produce the output.

Another option, without typical 'look ahead':

data have;

     input number;

     cards;

100000001

100000002

100000003

100000004

100000010

100000011

100000012

100000101

100000102

100000103

100000104

100000105

100000107

;

data want;

do while (1);

set have end=last;

if number-_num>1 then do;output;count=.;end;

count+1;

if count=1 then first_num=number;

_num=number; 

if last then output;

end;

rename _num=last_num;

drop number;

run;

Occasional Contributor
Posts: 10

Re: I am trying to count and group a sequence of numbers from the Input and produce the output.

Thanks, this will work as well.

Super User
Posts: 10,023

Re: I am trying to count and group a sequence of numbers from the Input and produce the output.

Assuming your data has already been ordered.

 
 
data have;
     input number ;
     cards;
100000001
100000002
100000003
100000004
100000010
100000011
100000012
100000101
100000102
100000103
100000104
100000105
100000107
;
run;
data temp;
 set have;
 if dif(number) ne 1 then position+1;
run;
data want;
 set temp;
 by position ;
 length first last $ 20;
 retain first;
 if first.position then do;count=0;first=number;end;
 count+1;
 if last.position then do;last=number;output;end;
 keep  first last  count;
run;
 

Xia Keshan

Occasional Contributor
Posts: 10

Re: I am trying to count and group a sequence of numbers from the Input and produce the output.

Thanks, this works great.

Super User
Super User
Posts: 7,942

Re: I am trying to count and group a sequence of numbers from the Input and produce the output.

Cant check at the moment, but is it not just working out the group, then simple group functions:

proc sql;

create table counts as

select MIN(NUM) as MIN_NUM,

  MAX(NUM) as MAX_NUM,

  COUNT(NUM) as CNT_NUM

from    (select NUM,

   floor((NUM-100000)/5) as GRP

   from HAVE)

group by GRP;

quit;

Occasional Contributor
Posts: 10

Re: I am trying to count and group a sequence of numbers from the Input and produce the output.

Thanks for responding.

Ask a Question
Discussion stats
  • 11 replies
  • 376 views
  • 0 likes
  • 5 in conversation