Create variable by ID and based on another variable

[blackandwhite]

Hi,

I would like to create the variable Z which takes the value 1 up until (and including when) Y=1 for the first time. After that, Z takes the value of 2.

For the same ID, Y can take the value of 1 at max two times.

Please see attached an example of what I need.

Thanks very much!

[novinosrin · Accepted solution]

data have;
input ID	ID_sub $	Y;*	Z;
cards;
1	A	0	1
1	B	0	1
1	C	1	1
2	A	1	1
2	B	0	2
2	C	0	2
2	D	1	2
3	A	0	1
3	B	0	1
3	C	0	1
3	D	0	1
3	E	0	1
3	F	0	1
3	G	0	1
4	A	0	1
4	B	1	1
4	C	1	2
5	A	1	1
5	B	1	2
;
data want;
if 0 then set have;
 z=1;
 do until(last.id);
 set have;
 by id;
 output;
 if z<2 then z+y;
 end;
run;

[blackandwhite]

Thank you!!

[hashman]

@blackandwhite:

Looks like a backtracking task. So, one approach is to read every group of records ending in Y=1 twice, initializing Z=0 at the beginning of each ID by-group and incrementing Z before each second pass:

data have ;                 
  input ID ID_sub :$1. Y ;  
  cards ;                   
1  A  0                     
1  B  0                     
1  C  1                     
2  A  1                     
2  B  0                     
2  C  0                     
2  D  1                     
3  A  0                     
3  B  0                     
3  C  0                     
3  D  0                     
3  E  0                     
3  F  0                     
3  G  0                     
4  A  0                     
4  B  1                     
4  C  1                     
5  A  1                     
5  B  1                     
run ;                       
                            
data want ;                 
  do until (Y) ;            
    set have ;              
    by id ;                 
    if first.id then Z = 0 ;
  end ;                     
  Z + 1 ;                   
  do until (Y) ;            
    set have ;              
    output ;                
  end ;                     
run ;                       

Kind regards

Paul D.