How to count the number of observations between two events and create a variable conditional on

[user95]

Yes, here it is, 

so far the code worked well but just didnt take under account missing values like the following

Year	ID	occurrence_event	Have_with current code	Var_Want
1992	14264	.	0	.
1993	14264	.	0	.
1994	14264	.	0	.
1995	14264	1	1	1
1996	14264	1	1	1
1992	15110	.	1	.
1993	15110	.	1	.
1994	15110	0	1	0
1995	15110	0	1	0
1996	15110	0	1	0
1997	15110	0	1	0
1998	15110	0	1	0
1999	15110	0	1	0
2000	15110	0	1	0
2001	15110	0	1	0
2002	15110	0	1	0
2003	15110	0	1	0
2004	15110	0	1	0
2005	15110	0	1	0
2006	15110	.	1	.
2007	15110	0	1	0

if the data starts with a missing value for the event_occurrence variable, it is set in the var_want to either 0 or 1, and the following results get affected by that .

In the second example, because the first two rows of missing values are set to 1 and the  rest of the data of event_occurence is =0, following the logic of the code, the remaining values of want_var are then set to 1. But it is wrong, because an event never occurred for that firm throughout those years. So I am looking for a way to take under consideration the missing values, before they affect the values of the following want_var.

Thank you

[novinosrin]

Okay Thank you @user95 . A good representative sample always helps and also avoid any assumptions plus going back and forth. The fix is very minor though. Try-

data want;
do until(last.id);
 set have;
 by id Event_occurrence notsorted;
 array t(999999) _temporary_;
 if first.Event_occurrence then _n=0;
 if Event_occurrence then do; _f=1;y=year;end;
 if _f then  _n+(Event_occurrence=0);
 if last.Event_occurrence and Event_occurrence=0 and _f then do;
 _i+1;
 t(_i)=_n;
 end;
end;
call missing(of _:);
if not missing(y) then _y=year ne y;
do until(last.id);
 set have;
 by id Event_occurrence notsorted;
 want_var=Event_occurrence;
 if Event_occurrence then _f=1;
 if first.Event_occurrence then _n=0;
 if first.Event_occurrence and Event_occurrence=0 and _f then _i+1;
 if _f then do; _n+(Event_occurrence=0);want_var=1;end;
 if Event_occurrence=0 and _f then do;
  if  t(_i)<3 then want_var=1;
  else if t(_i)=3 then want_var=0;
  else if t(_i)>3 then want_var=_n<=t(_i)-3;
 end;
 if _y then want_var=ifn(year>=y,1,want_var);
 output;
end;
call missing(of t(*),of _:);
drop y _:;
run;

[user95]

Greetings, 

Thank you , it was helpful, one detail I noticed is that if the last line of data for a certain company's event_occurrence variable is missing the var_want is not =., instead it follows the previous rule of having all consecutive years =1 if the last.event_occurrence year is before the last year of data.

Concretely, this is what I get

Year	ID	occurrence_event	Want	Have_with current code
2003	15110	1	1	1
2004	15110	1	1	1
2005	15110	0	1	1
2006	15110	1	1	1
2007	15110	0	1	1
2008	15110	.	.	1

Any suggestions on how to fix it?

[novinosrin · Accepted solution]

Okay @user95   Again as you review your data thoroughly it appears the missings goes as missings and your sample contains a lot of missing values. However all these changes are very minor IF-THENs if you follow the code to make changes. While you asked for one step solution which is fine, I really wonder whether you are able to follow and understand the code to edit/update should you consider maintaining in a production environment. Of course I hope you do. Nonetheless, it's easier to have it split into steps to modify/edit/update if you do not understand the code I am afraid.  I leave that up to you.

data want;
do until(last.id);
 set have;
 by id Event_occurrence notsorted;
 array t(999999) _temporary_;
 if first.Event_occurrence then _n=0;
 if Event_occurrence then do; _f=1;y=year;end;
 if _f then  _n+(Event_occurrence=0);
 if last.Event_occurrence and Event_occurrence=0 and _f then do;
 _i+1;
 t(_i)=_n;
 end;
end;
call missing(of _:);
if not missing(y) then _y=year ne y;
do until(last.id);
 set have;
 by id Event_occurrence notsorted;
 want_var=Event_occurrence;
 if Event_occurrence then _f=1;
 if first.Event_occurrence then _n=0;
 if first.Event_occurrence and Event_occurrence=0 and _f then _i+1;
 if _f and Event_occurrence>. then do; _n+(Event_occurrence=0);want_var=1;end;
 if Event_occurrence=0 and _f then do;
  if  t(_i)<3 then want_var=1;
  else if t(_i)=3 then want_var=0;
  else if t(_i)>3 then want_var=_n<=t(_i)-3;
 end;
 if _y and Event_occurrence>.  then want_var=ifn(year>=y,1,want_var);
 output;
end;
call missing(of t(*),of _:);
drop y _:;
run;

[user95]

Thank you, 

It worked as intended, I am slowly getting used to working with SAS and I deeply appreciate your time and effort.

[novinosrin]

Hello @user95  The pleasure is all mine rather ours(including @PaigeMiller ). I get the impression you are speeding in your learning which is a good attitude to have. However take the learning in piecemeal, play with simple approaches, see the brevity in code and execution and then consolidate into one step. Like Sir said, we appreciate your well written question considering you seem  new here.  My hope is while it's fun for us to give solutions and treat SAS programming as a video game, it benefits your learning too. Cheers!