topic Re: Starting a counter when value goes to zero in SAS Programming

Starting a counter when value goes to zero

Chad_OSU — Fri, 09 Aug 2019 17:59:30 GMT

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;

Re: Starting a counter when value goes to zero

PaigeMiller — Fri, 09 Aug 2019 18:09:03 GMT

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

Re: Starting a counter when value goes to zero

novinosrin — Fri, 09 Aug 2019 18:11:59 GMT


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;

Re: Starting a counter when value goes to zero

noling — Fri, 09 Aug 2019 18:13:08 GMT

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;

Re: Starting a counter when value goes to zero

novinosrin — Fri, 09 Aug 2019 18:16:25 GMT

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;

Re: Starting a counter when value goes to zero

Chad_OSU — Fri, 09 Aug 2019 18:20:35 GMT

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?

Re: Starting a counter when value goes to zero

PaigeMiller — Fri, 09 Aug 2019 18:23:41 GMT

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

Re: Starting a counter when value goes to zero

novinosrin — Fri, 09 Aug 2019 18:30:01 GMT

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;

Re: Starting a counter when value goes to zero

Chad_OSU — Fri, 09 Aug 2019 18:45:12 GMT

Yes, that should work, thanks!

Re: Starting a counter when value goes to zero

Chad_OSU — Fri, 09 Aug 2019 18:56:21 GMT

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

Re: Starting a counter when value goes to zero

novinosrin — Fri, 09 Aug 2019 19:01:29 GMT


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;

Re: Starting a counter when value goes to zero

Chad_OSU — Fri, 09 Aug 2019 19:04:44 GMT

Perfect, thank you!!

Re: Starting a counter when value goes to zero

hashman — Fri, 09 Aug 2019 20:34:44 GMT

@Chad_OSU:

I like the way @novinosrin thinks - and the DoW loop he uses to achieve the result. However, methinks that in this case, it can be achieved simpler because:

- by the nature of the task, it doesn't require BY

- there's no need to impose any artificial limit on 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 
;                                           
run ;                                       
                                            
data want ;                                 
  set have ;                                
  counter + mw = 0 ;                          
  if mw then counter = . ;                  
run ;

Kind regards

Paul D.

Re: Starting a counter when value goes to zero

Chad_OSU — Fri, 09 Aug 2019 21:35:16 GMT

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?

Re: Starting a counter when value goes to zero

Chad_OSU — Fri, 09 Aug 2019 21:38:23 GMT

Thanks for the reply. In a later response I added that I do actually need to apply a limit to the counter.

Re: Starting a counter when value goes to zero

hashman — Fri, 09 Aug 2019 22:49:13 GMT

@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.

Re: Starting a counter when value goes to zero

Chad_OSU — Fri, 09 Aug 2019 23:01:20 GMT

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:

Re: Starting a counter when value goes to zero

novinosrin — Fri, 09 Aug 2019 23:41:09 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;

data want;
do until(last.group);
 merge have limits;
 by group;
 if mw then _f=1;
 if _f then _c=sum(_c,mw=0);
 counter=_c;
 if mw or  _c>limit then counter=.;
 output;
end;
drop _:;
run;

Re: Starting a counter when value goes to zero

hashman — Sat, 10 Aug 2019 02:58:37 GMT

@Chad_OSU:

Ahhh ... that's a different story: A waaaay better presentation of the task and expected results - but also quite different an arrangement.

The simple approach I've posted before won't cut here for two reasons:

you need to determine the size of each successive collection of records with the same (group, mv) key-value before deciding whether to mark its records as counted or missing
hence, it cannot be done in a purely single pass through the data

Below, is one way of cracking this - pretty general if the limits and the driver data are stored in separate files, as you showed - which, by the way, is a sensible way to structure data processing. Note that I've added a bunch of successive 5's in HAVE, as otherwise we cannot have a proper test case accounting for all eventualities:

data limits ;                                                
  input group $ limit ;                                      
  cards ;                                                    
x 3                                                          
y 5                                                          
run ;                                                        
                                                             
data have ;                                                  
  input group $ MW ;                                         
  cards ;                                                    
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                                                          
y 5                                                          
y 5                                                          
y 5                                                          
y 5                                                          
y 5                                                          
y 5                                                          
y 5                                                          
run ;                                                        
                                                             
data want (drop = _:) ;                                      
  if _n_ = 1 then do ;                                       
    if 0 then set limits (keep = limit) ;                    
    dcl hash h (dataset:"limits") ;                          
    h.definekey  ("group") ;                                 
    h.definedata ("limit") ;                                 
    h.definedone () ;                                        
  end ;                                                      
  do _q = 1 by 1 until (last.mw) ;                           
    set have ;                                               
    by group mw notsorted ;                                  
  end ;                                                      
  h.find() ;                                                 
  do _n_ = 1 to _q ;                                         
    set have ;                                               
    if mw or (_q < limit) or (_n_ > limit) then counter = . ;
    else counter = _n_ ;                                     
    output ;                                                 
  end ;                                                      
run ;

Above, a hash table (H) is used to store the correspondence between GROUP and LIMIT in memory, so that LIMIT could be easily lookup up by GROUP downstream. The first DoW-loop determines the size of each (group,mw) BY group. The second reads the same BY group and selects the proper value for COUNTER based on the specified conditions.

Alternatively, the same can be done by using MERGE to pair the records from the driver with the appropriate LIMIT values (instead of using the hash table). However, it involves two steps since NOTSORTED cannot be coded with MERGE (or UPDATE) statements.

data havelimits ;                                            
  merge have limits ;                                        
  by group ;                                                 
run ;                                                        
                                                             
data want (drop = _:) ;                                      
  do _q = 1 by 1 until (last.mw) ;                           
    set havelimits ;                                         
    by group mw notsorted ;                                  
  end ;                                                      
  do _n_ = 1 to _q ;                                         
    set havelimits ;                                         
    if mw or (_q < limit) or (_n_ > limit) then counter = . ;
    else counter = _n_ ;                                     
    output ;                                                 
  end ;                                                      
run ;

Kind regards

Paul D.

Re: Starting a counter when value goes to zero

hashman — Sat, 10 Aug 2019 03:05:31 GMT

@novinosrin:

Kudos! just what the doctor ordered.

So, in fact it can be done in a purely single pass.

I retract my wrongheaded statement to the contrary.

Kind regards

Paul D.