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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.