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.
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;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.