Solved
Contributor
Posts: 50

# Create counter in increments of i by subgroup

Dear community,

I would like to ask for your support on the following issue, illustrated via an example:
Let the database contain multiple observations for each year (multiple rows with the same value for the "year" variable). For each year in the database, I would like to create a variable which counts forward.
For 2000, it starts with 1, proceeds to 2 and so on.

This procedure shall be restarted the next year. Thus, in 2001, the first observation of 2001 would get a 1, the second a 2, and so on...

But now, I do not want the counter to add a 1 in each increment. It shall stand still for e.g. 5 steps.

I.e., the first five observations of year 2000 would all get a 1. The next five a 2. Then, there may be only three observations left for year 2000, but they still get "their" 3.

This data gives an example of what I am thinking of.

Spoiler
``````data want;
infile datalines dlm=',' truncover;
input year count_up_by_five;
datalines;
2000,1
2000,1
2000,1
2000,1
2000,1
2000,2
2000,2
2000,2
2000,2
2000,2
2000,3
2000,3
2000,3
2001,1
2001,1
2001,1
2001,1
2001,1
2001,2
2001,2
2001,2
2001,2
2001,2
2001,3
;
run;``````

I would be glad if you could help me with this issue of mine.

Yours sincerely,

Sinistrum

Accepted Solutions
Solution
‎08-25-2016 05:26 AM
Super User
Posts: 13,583

## Re: Create counter in increments of i by subgroup

Try

``````data want;
set have;
by year;
retain count_up_by_five rowcount 0;
if first.year then do;
count_up_by_five=1;
rowcount=1;
end;
else rowcount+1;
output;
if mod(rowcount,5)=0 then count_up_by_five+1;
drop rowcount;
run; ``````

Change the 5 in the Mod(rowcount,5) to do other size groups. DO not use 1 or 0.

All Replies
Solution
‎08-25-2016 05:26 AM
Super User
Posts: 13,583

## Re: Create counter in increments of i by subgroup

Try

``````data want;
set have;
by year;
retain count_up_by_five rowcount 0;
if first.year then do;
count_up_by_five=1;
rowcount=1;
end;
else rowcount+1;
output;
if mod(rowcount,5)=0 then count_up_by_five+1;
drop rowcount;
run; ``````

Change the 5 in the Mod(rowcount,5) to do other size groups. DO not use 1 or 0.

Contributor
Posts: 50

## Re: Create counter in increments of i by subgroup

Dear ballardw,

Thank you very much for this clever solution.
It works smoothly in the "real setting" with different number at a huge scale, too.

It is astonishing, which great kind of a help I do receive from this forum.

☑ This topic is solved.