Starting a counter when value goes to zero

[Chad_OSU]

Here is an output I'm after:

Anytime the MW value changes from >0 to 0 I want to start a counter and reset the counter when MW >0, as shown above.

I have achieved this with the code below.  I'm looking for a way to do this more efficiently, as there are times when I may need to count to up to 72+.

data test;
set data;
if MW = 0 and lag(MW) > 0 
then counter = 1;
if lag(counter)=1 and MW=0
then counter=2;
if lag(counter)=2 and MW=0
then counter=3;
if lag(counter)=3 and MW=0
then counter=4;
if lag(counter)=4 and MW=0
then counter=5;
if lag(counter)=5 and MW=0
then counter=6;
if lag(counter)=6 and MW=0
then counter=7;
if lag(counter)=7 and MW=0
then counter=8;
if lag(counter)=8 and MW=0
then counter=9;
if lag(counter)=9 and MW=0
then counter=10;
if lag(counter)=10 and MW=0
then counter=11;
run;

[PaigeMiller]

data want;
    set have;
    prev_mw=lag(mw);
    if prev_mw>=0 and mw=0 then counter+1;
run;

[novinosrin]

data have;
input mw;
cards;
5
5
5
0
0
0
5
0
0
0
0
0
;

data want;
set have;
by mw notsorted;
if first.mw and mw=0  then count=1;
else if  mw=0 then count+1;
else count=.;
run;

[noling]

Same idea as @PaigeMiller except this resets your counter to missing if mw isn't 0

data want;
	set have;
	retain counter . ;
	mw_lag= lag(mw);
	if mw_lag > 0 and mw= 0 then counter = 1;
	else if mw_lag =0 and mw = 0 then counter +1;
	else call missing(counter);
	drop mw_lag;
run;

[PaigeMiller]

Good point, @noling, I seem to have missed that.

[novinosrin]

data have;
input mw;
cards;
5
5
5
0
0
0
5
0
0
0
0
0
;

data want;
if 0 then set have;/*get the variable order as-is*/
do count=1 by 1 until(last.mw);
set have;
by mw notsorted;
if mw ne 0 then count=.;
output;
end;
run;

[Chad_OSU]

Thank you for the responses!  How would I stop the counter based on a parameter?  For example, once it hit 10, stop counting until the condition of >0 to 0 happens again?

[novinosrin]

Hello @Chad_OSU  Do you mean this by any chance?

data have;
input mw;
cards;
5
5
5
0
0
0
0
0
0
0
0
0
0
0
0
0
5
0
0
0
0
0
;

data want;
if 0 then set have;/*get the variable order as-is*/
retain limit 10;
do count=1 by 1 until(last.mw);
set have;
by mw notsorted;
if mw ne 0 then count=.;
if count>limit then count=limit;
output;
end;
run;

[Chad_OSU]

Yes, that should work, thanks!

[Chad_OSU]

How can I make the duplicated 10 values null without messing up the counter?

[novinosrin · Accepted solution]

data have;
input mw;
cards;
5
5
5
0
0
0
0
0
0
0
0
0
0
0
0
0
5
0
0
0
0
0
;

data want;
retain _limit 10;
do _c=1 by 1 until(last.mw);
set have;
by mw notsorted;
count=_c;
if mw ne 0 or _c>_limit then count=.;
output;
end;
drop _:;
run;

[Chad_OSU]

Perfect, thank you!!

[Chad_OSU]

Any advice on how to apply this same logic with 2 different groupings? If the group x limit was say 5, and the group y limit was say 3?

[hashman]

@Chad_OSU:

Frankly, I'm at a loss as to what you mean by "limit". If it's a specific MW threshold value, I don't see 3 in your sample input data set.

Please clarify; or, better yet, show how your output data set is supposed to look like according to your logic.

[Chad_OSU]

Sorry for the confusion, by "limit" I am referring to the counter limit.

Here's what I have:

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;

Here's the expected output: