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

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:

counter.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

8 REPLIES 8
novinosrin
Tourmaline | Level 20

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?

Chad_OSU
Obsidian | Level 7
In application, yes I would have a look up table with the limits. And yes you are correct on when the counter starts.
novinosrin
Tourmaline | Level 20

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 

Chad_OSU
Obsidian | Level 7
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:

counter.PNG

novinosrin
Tourmaline | Level 20

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;

Chad_OSU
Obsidian | Level 7

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:

 

Capture11.PNG

 

I would be looking for the counter to restart at line 5.

novinosrin
Tourmaline | Level 20

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;
Chad_OSU
Obsidian | Level 7
Yes, that worked! Thank you very much!!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1987 views
  • 1 like
  • 2 in conversation