> row 1 and row 2 are concurrent jobs to row 3 and both start in the same month
No they're not. ROW 1 start on 1 and ROW 3 start on 3.
Unsure what exactly you want, as some details are still unclear, but his should be pretty close, and you can tweak to match your exact requirements.
data HAVE;
input INDIVIDUAL $ FIRST_ONE LAST_ONE TOTAL_LENGTH SECTOR $;
ROW=_N_;
cards;
A 1 72 72 Sports
A 3 60 58 Care
A 50 55 6 Sports
A 51 71 21 Teaching
B 5 40 36 Unknown
B 7 47 41 Teaching
B 30 47 11 Unknown
B 49 52 4 Care
C 50 52 3 Sports
C 59 66 8 Sports
;
proc sql;
create table JOIN as
select a.*
,b.FIRST_ONE as F, b.LAST_ONE as L, b.TOTAL_LENGTH as T, b.SECTOR as S
,(a.FIRST_ONE between b.FIRST_ONE and b.LAST_ONE) as OVERLAP
,(a.FIRST_ONE-b.LAST_ONE between 1 and 3) as THREE_MONTH_GAP
,a.FIRST_ONE-b.LAST_ONE as DIFF
,b.ROW as R
from HAVE a
left join
HAVE b
on a.FIRST_ONE between 49 and 60
and a.INDIVIDUAL eq b.INDIVIDUAL
and a.ROW ne b.ROW
order by a.INDIVIDUAL, a.FIRST_ONE, a.LAST_ONE
, calculated OVERLAP descending
, b.FIRST_ONE descending, b.TOTAL_LENGTH descending %* order for overlap;
, calculated THREE_MONTH_GAP descending
, b.LAST_ONE descending, b.TOTAL_LENGTH descending %* order 3-month gap;
;
quit;
data WANT;
set JOIN;
by ROW;
if first.ROW;
CATEGORY=ifc(DIFF eq . , '- '
,ifc(OVERLAP & SECTOR =S, 'Gap less than 3 months - same sector '
,ifc(OVERLAP & SECTOR^=S, 'Gap less than 3 months - different sector'
,ifc(THREE_MONTH_GAP & SECTOR =S, 'Gap less than 3 months - same sector '
,ifc(THREE_MONTH_GAP & SECTOR =S, 'Gap less than 3 months - different sector'
,ifc(DIFF > 0 , 'Gap longer than 3 months '
, 'No previous employment '))))));
* keep FIRST_ONE LAST_ONE TOTAL_LENGTH CATEGORY;
run;
... View more
