Calculating monthly employee count from start and end date

[Steffy_SAS]

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

[PeterClemmensen]

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

[ballardw · Accepted solution]

---

@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.

[Steffy_SAS]

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

[rudfaden]

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;