topic Re: Create counter in increments of i by subgroup in SAS Programming

Create counter in increments of i by subgroup

Sinistrum — Wed, 24 Aug 2016 21:46:40 GMT

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.

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

Re: Create counter in increments of i by subgroup

ballardw — Wed, 24 Aug 2016 23:02:28 GMT

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.

Re: Create counter in increments of i by subgroup

Sinistrum — Thu, 25 Aug 2016 09:28:33 GMT

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.