BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sinistrum
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

2 REPLIES 2
ballardw
Super User

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.

 

Sinistrum
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 9588 views
  • 2 likes
  • 2 in conversation