topic Re: Calculating monthly employee count from start and end date in SAS Programming

Calculating monthly employee count from start and end date

Steffy_SAS — Wed, 28 Oct 2020 12:41:14 GMT

Hi,

I need some help to find out if it is possible in SAS to program a monthly head count of employees.

I have the start dates and the end date like this example:

Linda	11-02-2020	30-06-2022
Jimmie	01-01-2021	31-08-2022
Sean	01-01-2019	31-12-2021
Maya	01-03-2019	31-10-2020

Hope you can help

Re: Calculating monthly employee count from start and end date

PeterClemmensen — Wed, 28 Oct 2020 13:47:12 GMT

What do you want the final result to look like? And is this for reporting purposes or do you want a SAS data set?

Re: Calculating monthly employee count from start and end date

ballardw — Wed, 28 Oct 2020 14:15:27 GMT

@Steffy_SAS wrote:

Hi,

I need some help to find out if it is possible in SAS to program a monthly head count of employees.

I have the start dates and the end date like this example:

Linda

11-02-2020

30-06-2022

Jimmie

01-01-2021

31-08-2022

Sean

01-01-2019

31-12-2021

Maya

01-03-2019

31-10-2020

Hope you can help

If I understand your need then one approach would be create one record for each month for each employee and then count the records by date.

Assumes that you actually have SAS dates as numeric with a date format applied:

data have;
  input name $ start :ddmmyy10. end :ddmmyy10.;
  format start end ddmmyy10.;
datalines;
Linda  11-02-2020 30-06-2022
Jimmie 01-01-2021 31-08-2022
Sean 01-01-2019 31-12-2021
Maya 01-03-2019 31-10-2020
;

data need;
   set have;
   monthyear = (intnx('month',start,0,'b'));
   do while (monthyear le (intnx('month',end,0,'b')) );
      output;
      monthyear = intnx('month',monthyear,1,'b');
   end;
   format monthyear monyy.;
run;

proc freq data=need;
   tables monthyear;
run;

The intnx function is used to set the start of the period at the beginning of the month, not needed but may be a good idea, and then in the loop increases the value to the beginning of the next month.

Re: Calculating monthly employee count from start and end date

rudfaden — Wed, 28 Oct 2020 14:39:10 GMT

Maybe somemthin like this

data have;
	input name $ start: ddmmyy10. end: mmddyy10.;
	format start end date9.;
	datalines;
Linda 11/02/2020 01/01/2022
Jimmie 10/01/2019 01/10/2021
Sean 11/01/2018 01/11/2020
Maya 12/01/2020 01/12/2023
;
run;

data temp;
	set have;
	temp_month = intck('MONTH',start,end);

	%*Kopierer unikke records til de manglende måneder;
	do i= 0 to temp_month;
		periode = intnx('MONTH', start, i, 'B' );
		output;
	end;
run;

proc  sql;
select year(periode) as year, month(periode) as month, count(1)
from temp
group by  year, calculated month
;
quit;

Re: Calculating monthly employee count from start and end date

Steffy_SAS — Thu, 29 Oct 2020 09:35:49 GMT

This is just what I needed, thank you so much 😊