DATA Step, Macro, Functions and more

Create counter in increments of i by subgroup

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

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: 11,343

Re: Create counter in increments of i by subgroup

Posted in reply to Sinistrum

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.

 

View solution in original post


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

Re: Create counter in increments of i by subgroup

Posted in reply to Sinistrum

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: 42

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 225 views
  • 1 like
  • 2 in conversation