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;
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;
data want;
set have;
prev_mw=lag(mw);
if prev_mw>=0 and mw=0 then counter+1;
run;
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;
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;
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
Good point, @noling, I seem to have missed that.
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;
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?
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;
How can I make the duplicated 10 values null without messing up the counter?
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;
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?
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.
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:
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.