To the clever SAS users of the world, I need your help. The solution might come to me on Monday but nothing was coming to mind by the end of a long week.
My problem: I have HR data with records showing an employees’ duration in a given position. Typically a person is assigned the role for a long or indefinite period, but when the person is away, another employee might be assigned the position for a short period.
HAVE:
WANT:
start_date
end_date
position
employee
start_date
end_date
position
employee
1-Sep-14
31-Dec-99
348
574
1-Sep-14
20-Mar-16
348
574
21-Mar-16
6-Jun-16
348
635
21-Mar-16
6-Jun-16
348
635
7-Jun-16
31-Dec-99
348
574
1-Aug-13
31-Dec-99
554
963
1-Aug-13
20-Jul-14
554
963
21-Jul-14
8-Jul-16
554
580
21-Jul-14
1-Nov-15
554
580
2-Nov-15
27-Nov-15
554
78
2-Nov-15
27-Nov-15
554
78
28-Nov-15
8-Jul-16
554
580
9-Jul-16
31-Dec-99
554
963
3-Sep-09
4-Apr-15
742
830
3-Sep-09
4-Apr-15
742
830
21-May-15
24-Jun-15
742
38
21-May-15
10-Jun-15
742
38
11-Jun-15
31-Dec-99
742
313
11-Jun-15
31-Dec-99
742
313
23-Nov-15
11-Dec-15
751
44
23-Nov-15
11-Dec-15
751
44
12-Dec-15
31-Dec-99
751
125
12-Dec-15
7-Apr-16
751
125
8-Apr-16
22-Apr-16
751
44
8-Apr-16
22-Apr-16
751
44
23-Apr-16
31-Dec-99
751
125
10-Mar-16
23-Mar-16
231
67
10-Mar-16
23-Mar-16
231
67
24-Mar-16
6-Apr-16
231
723
24-Mar-16
6-Apr-16
231
723
7-Apr-16
31-Dec-99
231
32
7-Apr-16
20-Apr-16
231
839
7-Apr-16
20-Apr-16
231
839
21-Apr-16
31-Dec-99
231
32
I want to convert these overlapping records into a single time series so it’s clear who was/is in a position on a given date.
The situations can become complicated. And ambiguous. The example data illustrates the appropriate assumptions.
I didn't think it would be that difficult but I soon realised first. last. processing probably won't do. Using the LAG function looked impractical, and dangerous, when mixed with too many IF-THEN-ELSE statements. Look-ahead processing with extra SET statements might be the way. And maybe a little array processing could be handy. Perhaps Proc FCMP and a recursive routine. I can even imagine a SQL solution - from an SQL guru.
Is anyone up to the challange? I'm taking a break.
TIA
... View more