Perhaps not the cleanest way of doing it, but does this work?
data have;
input id$ 1-6 job 7-8 jobyrin 9-11 jobyrout 18-20 icode$ 23-27 lung$ 29-35;
datalines;
OSa13 3 73 78 6191 cacase
OSa30 1 39 46 7181 cacase
OSa30 3 56 64 6191 cacase
OSa73 1 23 31 7181 popcon
OSa73 2 31 42 5130 popcon
OSf26 2 70 70 6191 popcon
OSj17 3 60 75 6191 cacase
OSj17 4 65 70 6191 cacase
;
run;
data codes;
input icode $ code $;
datalines;
6191 janitor
7181 baker
5130 plumber
;
run;
proc sql;
create table have_with_codes as
select h.*
,c.code
,jobYrOut - jobYrIn as duration
from have h
left join codes c
on h.icode = c.icode
order by id, job, jobYrIn;
quit;
data have_modify_duration;
set have_with_codes;
by id;
retain in_prev out_prev;
if duration = 0
then duration = 0.5;
if first.id
then do;
in_prev = jobYrIn;
out_prev = jobYrOut;
end;
else
do;
if (in_prev < JobYrIn and JobYrOut < out_prev)
then duration = .;
end;
if duration ne .;
drop in_prev out_prev lung job jobYrIn jobYrOut;
run;
proc transpose data=have_modify_duration
out=transposed (drop=_name_) suffix=_dur;
by id;
id code;
run;
proc sql;
create table want as
select id
,coalesce(janitor_dur,0) as janitor_dur
,case when janitor_dur = . then 0 else 1 end as janitor_ever
,coalesce(baker_dur,0) as baker_dur
,case when baker_dur = . then 0 else 1 end as baker_ever
,coalesce(plumber_dur,0) as plumber_dur
,case when plumber_dur = . then 0 else 1 end as plumber_ever
from transposed;
quit;
... View more
