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:
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: