calculating annual turnover rate

[Angi]

Hi,

I have a dataset with the following variables: school name, employee name, job title, and employment year. I need to calculate annual turnover rates for a) all employees within a school, and b) teachers within a school from 2009 to 2010. I’d appreciate any help you can provide in this matter.

Job title: 1 = principal, 2 = teacher, 3 = other employees.

I'm using SAS 9.4.

Thank you,

Angi

data WORK.EMPLOYEE_DATA_SAMPLE(label='employee_data_sample dataset written by Stat/Transfer Ver. 11.2.2106.0521       ');

infile datalines dsd truncover;

input School:$7. Name:$17. Job:32. Year:32.;

datalines;

SchoolA Smith, John 1 2009

SchoolA Doe, Jane 2 2009

SchoolA Cooper, Laura 3 2009

SchoolA Adams, Charlie 2 2009

SchoolA Anderson, Joyce 2 2009

SchoolA Bell, Anne 2 2009

SchoolA Abbott, Sharon 3 2009

SchoolB Lane, Gary 1 2009

SchoolB Nelson, Elizabeth 2 2009

SchoolB Bumpus, Toby 3 2009

SchoolB Doe, John 3 2009

SchoolB Smith, Jane 2 2009

SchoolB Richards, Simon 2 2009

SchoolB Tate, Lori 2 2009

SchoolB Bates, Linda 2 2009

SchoolB Pitt, Joel 3 2009

SchoolA Smith, John 1 2010

SchoolA Doe, Jane 2 2010

SchoolA Hill, Claire 3 2010

SchoolA Adams, Charlie 2 2010

SchoolA Arnold, Gayle 2 2010

SchoolA Bell, Anne 2 2010

SchoolA Abbott, Sharon 3 2010

SchoolA Bond, Connie 2 2010

SchoolB Lane, Gary 1 2010

SchoolB Nelson, Elizabeth 2 2010

SchoolB Bumpus, Toby 3 2010

SchoolB Doe, John 3 2010

SchoolB Anderson, Gilbert 2 2010

SchoolB Gately, Ronan 2 2010

SchoolB Tate, Lori 2 2010

SchoolB Bates, Linda 2 2010

SchoolB Pitt, Joel 3 2010

SchoolB Jeffords, Jim 2 2010

;;;;

[Jagadishkatam]

when you say annual turn over rate, what is the definition. Here we have only the employees details and the  year. We could get the count of employees within the school or count of teachers within the school in that year. Please let me know if you are expecting the count.

[Satish_Parida · Accepted solution]

Please check the bellow code

data WORK.EMPLOYEE_DATA_SAMPLE(label='employee_data_sample dataset written by Stat/Transfer Ver. 11.2.2106.0521       ' drop=
fname lname);
infile datalines dsd dlm=' ' truncover;
input School:$7. FName:$17. LName:$17. Job:32. Year:32.;
Name=strip(FName)||' '||strip(LName);
datalines;
SchoolA Smith, John 1 2009
SchoolA Doe, Jane 2 2009
SchoolA Cooper, Laura 3 2009
SchoolA Adams, Charlie 2 2009
SchoolA Anderson, Joyce 2 2009
SchoolA Bell, Anne 2 2009
SchoolA Abbott, Sharon 3 2009
SchoolB Lane, Gary 1 2009
SchoolB Nelson, Elizabeth 2 2009
SchoolB Bumpus, Toby 3 2009
SchoolB Doe, John 3 2009
SchoolB Smith, Jane 2 2009
SchoolB Richards, Simon 2 2009
SchoolB Tate, Lori 2 2009
SchoolB Bates, Linda 2 2009
SchoolB Pitt, Joel 3 2009
SchoolA Smith, John 1 2010
SchoolA Doe, Jane 2 2010
SchoolA Hill, Claire 3 2010
SchoolA Adams, Charlie 2 2010
SchoolA Arnold, Gayle 2 2010
SchoolA Bell, Anne 2 2010
SchoolA Abbott, Sharon 3 2010
SchoolA Bond, Connie 2 2010
SchoolB Lane, Gary 1 2010
SchoolB Nelson, Elizabeth 2 2010
SchoolB Bumpus, Toby 3 2010
SchoolB Doe, John 3 2010
SchoolB Anderson, Gilbert 2 2010
SchoolB Gately, Ronan 2 2010
SchoolB Tate, Lori 2 2010
SchoolB Bates, Linda 2 2010
SchoolB Pitt, Joel 3 2010
SchoolB Jeffords, Jim 2 2010
;;;;
run;

proc sort data=work.employee_data_sample;
by School Job Name Year;
run;

data want;
set employee_data_sample;
by School Job Name Year;
if first.name then year_count=0;
year_count+1;
if last.name then output;
run;

proc sql;
create table turnover_rate as
select a.job, round(sum(a._total, -1*b._remains)/a._total*100,0.01) as _remains from
(select Job,count(*) as _total from want group by Job) a left join
(select Job,count(*) as _remains from want where year_count=1 and Year=2009 group by Job) b
on a.job=b.job;
quit;

Please let us know if it worked for you.

[Angi]

Thank you for your help!