hello,
I have subjects over several months (up to 12). sometime there is an ending date that may signal the end of treatment or just a suspension. in the first case in the months ahead I do not find a (new) beginning date, while in the second case I find one.
I would like to flag the months that are a suspension.
here is an example. dataset have:
id | month | date_beg | date_end |
---|---|---|---|
1 | 1 | . | . |
1 | 2 | . | . |
1 | 3 | . | 13MAR2017 |
1 | 4 | . | . |
1 | 5 | . | . |
1 | 6 | 30JUN2017 | . |
1 | 7 | . | . |
1 | 8 | . | . |
1 | 9 | . | . |
1 | 10 | . | . |
1 | 11 | . | . |
1 | 12 | . | . |
2 | 1 | . | . |
2 | 2 | . | . |
2 | 3 | . | 13MAR2017 |
2 | 4 | . | . |
2 | 5 | . | . |
2 | 6 | . | . |
2 | 7 | . | . |
2 | 8 | . | . |
2 | 9 | . | . |
2 | 10 | . | . |
2 | 11 | . | . |
2 | 12 | . | . |
3 | 1 | . | . |
3 | 2 | . | . |
3 | 3 | . | . |
3 | 4 | . | . |
3 | 5 | . | . |
3 | 6 | 30JUN2017 | . |
3 | 7 | . | . |
3 | 8 | . | . |
3 | 9 | . | . |
3 | 10 | . | . |
3 | 11 | . | . |
3 | 12 | . | . |
4 | 1 | . | . |
4 | 2 | . | 20FEB2017 |
4 | 3 | . | . |
4 | 4 | 15APR2017 | . |
4 | 5 | . | 10MAY2017 |
4 | 6 | 20JUN2017 | . |
4 | 7 | . | . |
4 | 8 | . | 02AUG2017 |
4 | 9 | . | . |
4 | 10 | 20OCT2017 | . |
4 | 11 | . | . |
4 | 12 | . | . |
dataset want:
id | month | date_beg | date_end | suspension |
---|---|---|---|---|
1 | 1 | . | . | 0 |
1 | 2 | . | . | 0 |
1 | 3 | . | 13MAR2017 | 0 |
1 | 4 | . | . | 1 |
1 | 5 | . | . | 1 |
1 | 6 | 30JUN2017 | . | 0 |
1 | 7 | . | . | 0 |
1 | 8 | . | . | 0 |
1 | 9 | . | . | 0 |
1 | 10 | . | . | 0 |
1 | 11 | . | . | 0 |
1 | 12 | . | . | 0 |
2 | 1 | . | . | 0 |
2 | 2 | . | . | 0 |
2 | 3 | . | 13MAR2017 | 0 |
2 | 4 | . | . | 0 |
2 | 5 | . | . | 0 |
2 | 6 | . | . | 0 |
2 | 7 | . | . | 0 |
2 | 8 | . | . | 0 |
2 | 9 | . | . | 0 |
2 | 10 | . | . | 0 |
2 | 11 | . | . | 0 |
2 | 12 | . | . | 0 |
3 | 1 | . | . | 0 |
3 | 2 | . | . | 0 |
3 | 3 | . | . | 0 |
3 | 4 | . | . | 0 |
3 | 5 | . | . | 0 |
3 | 6 | 30JUN2017 | . | 0 |
3 | 7 | . | . | 0 |
3 | 8 | . | . | 0 |
3 | 9 | . | . | 0 |
3 | 10 | . | . | 0 |
3 | 11 | . | . | 0 |
3 | 12 | . | . | 0 |
4 | 1 | . | . | 0 |
4 | 2 | . | 20FEB2017 | 0 |
4 | 3 | . | . | 1 |
4 | 4 | 15APR2017 | . | 0 |
4 | 5 | . | 10MAY2017 | 0 |
4 | 6 | 20JUN2017 | . | 0 |
4 | 7 | . | . | 0 |
4 | 8 | . | 02AUG2017 | 0 |
4 | 9 | . | . | 1 |
4 | 10 | 20OCT2017 | . | 0 |
4 | 11 | . | . | 0 |
4 | 12 | . | . | 0 |
Any advice is greatly appreciated.
Thank you very much in advance.
If I understood what you mean.
data have;
input id month date_beg : date9. date_end : date9.;
format date_beg date_end date9.;
cards;
1 1 . .
1 2 . .
1 3 . 13MAR2017
1 4 . .
1 5 . .
1 6 30JUN2017 .
1 7 . .
1 8 . .
1 9 . .
1 10 . .
1 11 . .
1 12 . .
2 1 . .
2 2 . .
2 3 . 13MAR2017
2 4 . .
2 5 . .
2 6 . .
2 7 . .
2 8 . .
2 9 . .
2 10 . .
2 11 . .
2 12 . .
3 1 . .
3 2 . .
3 3 . .
3 4 . .
3 5 . .
3 6 30JUN2017 .
3 7 . .
3 8 . .
3 9 . .
3 10 . .
3 11 . .
3 12 . .
4 1 . .
4 2 . 20FEB2017
4 3 . .
4 4 15APR2017 .
4 5 . 10MAY2017
4 6 20JUN2017 .
4 7 . .
4 8 . 02AUG2017
4 9 . .
4 10 20OCT2017 .
4 11 . .
4 12 . .
;
run;
data temp;
set have;
if n(date_beg ,date_end );
flag=not missing(date_end);
run;
data id;
set temp;
by id;
if first.id and flag then output;
run;
proc sql;
create table temp1 as
select *
from temp
where id in (select id from id)
group by id
having count(*) ne 1
order by id,month;
quit;
data temp1;
set temp1;
if flag then group+1;
run;
data temp2;
merge temp1 temp1(firstobs=2 keep=group month rename=(group=_group month=_month));
output;
if group=_group then do;
do i=month+1 to _month-1;
month=i;suspension=1;output;
end;
end;
keep id month suspension;
run;
proc sort data=temp2;by id month;run;
data want;
merge have temp2;
by id month;
run;
Post test data in the form of a datastep. As such this code is untested, but retain should work:
data want; set have; retain suspension 0; if date_end ne . then do; output; suspension=1; end; else if date_beg ne . then do; suspension=0; output; end; else output; run;
apologies for not having posted the datastep. I do it now at the end of the message.
thank you for the suggestion.
unfortunately the code posted signal as interruption also a case where the ending date is not followed, in any of the next months, by a beginiing date.
any other idea?
data have;
format date_end date9.;
format date_beg date9.;
do id=1 to 4;
do month=1 to 12;
if id=1 then do;
if month=3 then date_end='13mar2017'd;
else if month=6 then date_beg='30jun2017'd;
else do;
date_end=.;
date_beg=.;
end;
end;
else if id=2 then do;
if month=3 then date_end='13mar2017'd;
else do;
date_end=.;
date_beg=.;
end;
end;
else if id=3 then do;
if month=6 then date_beg='30jun2017'd;
else do;
date_end=.;
date_beg=.;
end;
end;
else if id=4 then do;
if month=2 then date_end='20feb2017'd;
else if month=4 then date_beg='15apr2017'd;
else if month=5 then do; date_beg=.; date_end='10may2017'd; end;
else if month=6 then do; date_beg='20jun2017'd; date_end=.; end;
else if month=8 then date_end='2aug2017'd;
else if month=10 then date_beg='20oct2017'd;
else do;
date_end=.;
date_beg=.;
end;
end;
output;
end;
end;
run;
Sorry, I do not understand what you mean. Post test data in the form of a datastep which shows what you have, and then show the output you expect from that test data. From the data you posted, what I suggest should work, if not, show the data where this doesn't.
If I understood what you mean.
data have;
input id month date_beg : date9. date_end : date9.;
format date_beg date_end date9.;
cards;
1 1 . .
1 2 . .
1 3 . 13MAR2017
1 4 . .
1 5 . .
1 6 30JUN2017 .
1 7 . .
1 8 . .
1 9 . .
1 10 . .
1 11 . .
1 12 . .
2 1 . .
2 2 . .
2 3 . 13MAR2017
2 4 . .
2 5 . .
2 6 . .
2 7 . .
2 8 . .
2 9 . .
2 10 . .
2 11 . .
2 12 . .
3 1 . .
3 2 . .
3 3 . .
3 4 . .
3 5 . .
3 6 30JUN2017 .
3 7 . .
3 8 . .
3 9 . .
3 10 . .
3 11 . .
3 12 . .
4 1 . .
4 2 . 20FEB2017
4 3 . .
4 4 15APR2017 .
4 5 . 10MAY2017
4 6 20JUN2017 .
4 7 . .
4 8 . 02AUG2017
4 9 . .
4 10 20OCT2017 .
4 11 . .
4 12 . .
;
run;
data temp;
set have;
if n(date_beg ,date_end );
flag=not missing(date_end);
run;
data id;
set temp;
by id;
if first.id and flag then output;
run;
proc sql;
create table temp1 as
select *
from temp
where id in (select id from id)
group by id
having count(*) ne 1
order by id,month;
quit;
data temp1;
set temp1;
if flag then group+1;
run;
data temp2;
merge temp1 temp1(firstobs=2 keep=group month rename=(group=_group month=_month));
output;
if group=_group then do;
do i=month+1 to _month-1;
month=i;suspension=1;output;
end;
end;
keep id month suspension;
run;
proc sort data=temp2;by id month;run;
data want;
merge have temp2;
by id month;
run;
data have;
input id month date_beg : date9. date_end : date9.;
format date_beg date_end date9.;
cards;
1 1 . .
1 2 . .
1 3 . 13MAR2017
1 4 . .
1 5 . .
1 6 30JUN2017 .
1 7 . .
1 8 . .
1 9 . .
1 10 . .
1 11 . .
1 12 . .
2 1 . .
2 2 . .
2 3 . 13MAR2017
2 4 . .
2 5 . .
2 6 . .
2 7 . .
2 8 . .
2 9 . .
2 10 . .
2 11 . .
2 12 . .
3 1 . .
3 2 . .
3 3 . .
3 4 . .
3 5 . .
3 6 30JUN2017 .
3 7 . .
3 8 . .
3 9 . .
3 10 . .
3 11 . .
3 12 . .
4 1 . .
4 2 . 20FEB2017
4 3 . .
4 4 15APR2017 .
4 5 . 10MAY2017
4 6 20JUN2017 .
4 7 . .
4 8 . 02AUG2017
4 9 . .
4 10 20OCT2017 .
4 11 . .
4 12 . .
;
run;
data want;
if _n_=1 then do;
if 0 then set have;
dcl hash H (ordered: "A") ;
h.definekey ("id",'month') ;
h.definedata ('suspension') ;
h.definedone () ;
call missing(suspension);
end;
do until(last.id);
set have end=last;
by id month;
if missing(date_beg) and date_end then _k=month;
else if date_beg and missing(date_end) and _k then do;
do _month=_k+1 to month-1;
suspension=1;
h.add(key:id,key:_month,data:suspension);
end;
end;
end;
do until(last.id);
set have;
by id month;
suspension=0;
rc=h.find();
output;
end;
drop _: rc;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.