General SAS Programming

[shalmali]

Dear All,

I am trying to create the following variables: tenure of auditor (Aud_Ten) and a dummy to indicate whether the previous auditor resigned or dismiss (Pre_Aud_Dis). The data I have is as follows:

Firm     Year     Auditor_Change     Curr_Aud_Dis

A          2000     0                              .

A          2001     0                              .

A          2002     1                              1

A          2003     0                              .

A          2004     0                              .

A          2005     0                              .

A          2006     1                              0

A          2007     0                              .

A          2008     0                              .

A          2009     0                              .

A          2010     0                              .

A          2011     1                              .

A          2012     0                              .

Auditor_Change is 1 for the firm-year when the current auditor leaves the company. Curr_Aud_Dis is 1 if the current auditor is dismiss, 0 if the auditor resigns, and missing or . for years when there is no auditor change. I am trying to get an output as follows:

Firm     Year     Auditor_Change     Curr_Aud_Dis Aud_Ten      Pre_Aud_Dis

A          2000     0                              .

A          2001     0                              .

A          2002     1                              1          .                      .   .                   

A          2003     0                              .

A          2004     0                              .

A          2005     0                              .

A          2006     1                              0          4                    1

A          2007     0                              .

A          2008     0                              .

A          2009     0                              .

A          2010     0                              .

A          2011     1                              1          5                    0

A          2012     0                              .

Aud_Ten and Pre_Aud_Dis is missing for 2002 because there is no information on when and why there was a change in the auditor.

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

Thank you,

S.

[PGStats · Accepted solution]

One way of doing this:

data want;

do until (last.Firm);

    set have; by Firm;

    if Auditor_Change then do;

        if change_year > 0 then Aud_Ten = Year - Change_Year;

        Change_Year = Year;

        if Aud_Dis >= 0 then Pre_Aud_Dis = Aud_Dis;

        Aud_Dis = Curr_Aud_Dis;

        end;

    output;

    call missing(Aud_Ten, Pre_Aud_Dis);

    end;

drop Change_Year Aud_Dis;

run;

PG

[PGStats · Accepted solution]

One way of doing this:

data want;

do until (last.Firm);

    set have; by Firm;

    if Auditor_Change then do;

        if change_year > 0 then Aud_Ten = Year - Change_Year;

        Change_Year = Year;

        if Aud_Dis >= 0 then Pre_Aud_Dis = Aud_Dis;

        Aud_Dis = Curr_Aud_Dis;

        end;

    output;

    call missing(Aud_Ten, Pre_Aud_Dis);

    end;

drop Change_Year Aud_Dis;

run;

PG

[shalmali]

Thank you for your prompt reply.

[Ksharp]

How about :

data have;
input Firm $    Year     Auditor_Change     Curr_Aud_Dis ;
cards;
A          2000     0                              .
A          2001     0                              .
A          2002     1                              1
A          2003     0                              .
A          2004     0                              .
A          2005     0                              .
A          2006     1                              0
A          2007     0                              .
A          2008     0                              .
A          2009     0                              .
A          2010     0                              .
A          2011     1                              1
A          2012     0                              .
;
run;

data want;
 set have;
 by firm;
 retain _year _curr;
 if first.firm then call missing(_year,_curr);
 if Auditor_Change=1 then do;
  Aud_Ten=year-_year;Pre_Aud_Dis=_curr; 
  _year=year;_curr=Curr_Aud_Dis;
 end;
 drop _: ;
run;

Xia Keshan

Message was edited by: xia keshan