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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.