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
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.