BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ciro
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
ciro
Quartz | Level 8

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

Ksharp
Super User

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;
novinosrin
Tourmaline | Level 20
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 869 views
  • 2 likes
  • 4 in conversation