topic Re: continuous period in SAS Programming

continuous period

lillymaginta — Fri, 02 Nov 2018 21:24:02 GMT

data have;
informat start end mmddyy10.;
format start end date9.;
input id drug start end;
cards;
1  a  1/1/2005  2/17/2005
1  a  2/7/2005  3/3/2005
1  a  3/7/2005  5/5/2005
1  a  5/30/2005 8/9/2005
;run;

I want to create a continuous period allowing only 7 days gap between the end date of the period and the start of the next one

output

id start end

1 1/1/2005 5/15/2005

Re: continuous period

AMSAS — Fri, 02 Nov 2018 18:56:02 GMT

Can you provide more information - When I look at the input data you have a gap from 3/3/05 to 4/4/05 between obs 2 & 3, which is more than 7 days. Yet the output suggests you want to go from 01/01/05 to 05/05/05. Thanks

Re: continuous period

lillymaginta — Fri, 02 Nov 2018 21:26:16 GMT

Thank you for the response. I adjusted the dates. I want to allow a gap of 7 days but if there is overlap in the dates such as in the first and the second row (there is an overlap of 10 days) I want to add the 10 days to the end date as refected in the output.

Re: continuous period

mkeintz — Sat, 03 Nov 2018 04:31:17 GMT

You didn't show multiple ID's, but I presume the dataset is sorted by ID/START, so this program checks for a change in ID:

data have;
informat start end mmddyy10.;
format start end date9.;
input id drug :$1. start end;
cards;
1  a  1/1/2005  2/17/2005
1  a  2/7/2005  3/3/2005
1  a  3/7/2005  5/5/2005
1  a  5/30/2005 8/9/2005
;run;

data want (drop=_:);
  set have (keep=id);
  by id;

  merge have 
        have (firstobs=2 keep=start rename=(start=_nxtstart));

  retain _start;
  if first.id or start > lag(end)+7 then _start=start;
  if last.id or _nxtstart>end+7;

  start=_start;
run;

I want to test for first.id and last.id, but I couldn't do
MERGE HAVE HAVE (firstobs=2 keep=start rename=(start=_nxtstart));
BY ID;
because the 2nd ID group and all subsequent ID groups, would not have _NXTSTART one observation after the current obs. So the solution is to
1. Use SET HAVE (keep=id); BY ID;
  which sets up the dummy first.id and last.id as needed,
  and
2. MERGE HAVE HAVE (firstobs=2 keep=start rename=(start=_nxtstart); WITHOUT a following BY ID statement. This keeps _nxtstart one obs ahead of start, even when changing ID groups.