Help with variable construction

[shalmali]

Dear All,

I am trying to create the following variables: tenure of auditor (Aud_Ten). The data I have is as follows:

Firm Year     Change

A     2001     0

A     2002     0

A     2003     1

A     2004     0

A     2005     0

A     2006     0

A     2007     0

A     2008     1

A     2009     1

A     2010     0

Change is 1 for the firm-year when the current auditor leaves the company. I am trying to get an output as follows:

Firm Year     Change     AUD_TEN

A     2001     0

A     2002     0

A     2003     1               3

A     2004     0

A     2005     0

A     2006     0

A     2007     0

A     2008     1               5

A     2009     1               1

A     2010     0

I would appreciate if someone help me with the construction of these variables.

Thank you,

S.

[ballardw]

Something like this may work (assumes that the data is sorted by Firm and Year )

data want;

     set have;

     by firm;

     retain tempten 0;

     if first.firm then tempten=0;

     tempten+1;

     if change = 1 then do ;

          AUD_TEN= tempten;

          tempten=0;

     end;

     drop tempten;

run;

[stat_sas]

data want;
AUD_TEN=0;
do until (change=1);
set have;
by firm;
AUD_TEN + 1;
end;
run;

data need;
merge have want;
by firm year;
run;