Creating a date spine

abar228 — Wed, 23 Sep 2020 15:55:33 GMT

I am trying to join two sets of data relating to Staff Data specifically Start and End Dates and Total Available Positions with in the Department.

I need a graph to show the number of employees vs. number of headcount for the department in a stacked bar graph.

As a first step -

I am looking to create a “date spine” for the relevant time period that simply has one record for each date in the period. keeping only the dates that represent the first of the month (I don’t need to chart this stuff for each day, so this will reduce the amount of data I have).

The Staff data file has all information including start and end date for each employee over the last 10 years.

The total available positions file has a start and end date for each time there was an increase or decrease in headcount given to the department by the company.

Ex. Start Date End Date Total FTE allotted
01May2014 31Mar2015 20

01Apr2015 09Dec2018 22

Knowing this information I would want a numeric value of employees that worked in this data range to populate.

This is my first SAS project in 8 months and I am struggling to format my data in a way that returns to me this information. Any pointers appreciated.

Re: Creating a date spine

Reeza — Wed, 23 Sep 2020 16:04:07 GMT

Something like this should work for you:

data want;
set have;

nMonths = intck('month', start, end, 'c') + 1;

do i=1 to nMonths;
	date = intnx('month', start, i-1, 'b');
	output;
end;

format date date9.;
run;

FYI - if you're checking how many people are active at each date there are other ways.

topic Re: Creating a date spine in New SAS User

Creating a date spine

Re: Creating a date spine