topic Re: How to apply a counter with limits for specific groups? in SAS Programming

How to apply a counter with limits for specific groups?

Chad_OSU — Fri, 09 Aug 2019 22:26:05 GMT

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:

Re: How to apply a counter with limits for specific groups?

novinosrin — Fri, 09 Aug 2019 22:38:53 GMT

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?

Re: How to apply a counter with limits for specific groups?

Chad_OSU — Fri, 09 Aug 2019 22:41:51 GMT

In application, yes I would have a look up table with the limits. And yes you are correct on when the counter starts.

Re: How to apply a counter with limits for specific groups?

novinosrin — Fri, 09 Aug 2019 22:43:57 GMT

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

Re: How to apply a counter with limits for specific groups?

Chad_OSU — Fri, 09 Aug 2019 22:55:25 GMT

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:

Re: How to apply a counter with limits for specific groups?

novinosrin — Fri, 09 Aug 2019 23:31:45 GMT

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;

Re: How to apply a counter with limits for specific groups?

Chad_OSU — Fri, 09 Aug 2019 23:49:19 GMT

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.

Re: How to apply a counter with limits for specific groups?

novinosrin — Fri, 09 Aug 2019 23:55:27 GMT

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;

Re: How to apply a counter with limits for specific groups?

Chad_OSU — Fri, 09 Aug 2019 23:57:27 GMT

Yes, that worked! Thank you very much!!