In the example below, group x has a counter limit of 3 and group y has a counter limit of 5. In each group, the counter starts when the MW value goes from >0 to 0. Any ideas on how to code this? In the real application of this, I have multiple "groups" all with different "counter" limits. Any help is much appreciated!
Here is an example dataset and the result I am trying to return:
Sorry @Chad_OSU Do you mean this?
data want;
do until(last.group);
merge have limits;
by group;
if mw then do; _f=1; _c=.;end;
if _f then _c=sum(_c,mw=0);
counter=_c;
if mw or _c>limit then counter=.;
output;
end;
drop _:;
run;
Hello @Chad_OSU Do you have a look up table that has limits for each group. Also, my understanding is that counter values start for the set of zeros that follows the first non zero value, is this accurate?
So why not post a sample of the HAVE's
1. your table
2. The look tables with groups and associated limits
3. Of course the expected output
data limits;
input group$ limit;
datalines;
x 3
y 5
run;
data have;
input group$ MW;
datalines;
x 0
x 0
x 5
x 5
x 0
x 0
x 0
x 0
x 0
x 0
x 0
y 0
y 0
y 5
y 0
y 0
y 0
y 0
y 0
y 0
y 0
run;
Expected output:
Hello @Chad_OSU
data limits;
input group$ limit;
datalines;
x 3
y 5
;
run;
data have;
input group$ MW;
datalines;
x 0
x 0
x 5
x 5
x 0
x 0
x 0
x 0
x 0
x 0
x 0
y 0
y 0
y 5
y 0
y 0
y 0
y 0
y 0
y 0
y 0
run;
data want;
do until(last.group);
merge have limits;
by group;
if _f then _c=sum(_c,mw=0);
counter=_c;
if mw then _f=1;
if mw or _c>limit then counter=.;
output;
end;
drop _:;
run;
Thank you very much for the response. This functions correctly with the given dataset. Although, if the first x value is changed to 5, this is the output:
I would be looking for the counter to restart at line 5.
Sorry @Chad_OSU Do you mean this?
data want;
do until(last.group);
merge have limits;
by group;
if mw then do; _f=1; _c=.;end;
if _f then _c=sum(_c,mw=0);
counter=_c;
if mw or _c>limit then counter=.;
output;
end;
drop _:;
run;
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.