topic Re: Help with variable construction in SAS Programming

Help with variable construction

shalmali — Tue, 18 Nov 2014 18:10:02 GMT

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,

ballardw — Tue, 18 Nov 2014 18:27:23 GMT

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 — Tue, 18 Nov 2014 20:06:31 GMT

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;