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
;;;;
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.
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.
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.
Thank you for your help!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.