topic Re: how splie records based on start and end dates in SAS Programming

how splie records based on start and end dates

vraj1 — Wed, 10 Oct 2018 07:36:23 GMT

I have id's along with num- i need to spliet the same id and num basis on start and end by 2. and if the duration is 5 days than 3 records with start and end in which 2 will have start and end seperate dates and one record with same date as they are odd.

data WORK.TEST;
  infile datalines dsd truncover;
  input id:32. num:32. start:DATE9. end:DATE9.;
datalines4;
101,2,12MAR2018,14MAR2018
101,3,15MAR2018,19MAR2018
101,4,20MAR2018,29MAR2018
102,2,12JAN2018,19JAN2018
102,3,20JAN2018,28JAN2018
102,4,29JAN2018,12FEB2018
102,5,13FEB2018,18FEB2018
102,6,19FEB2018,28FEB2018
;;;;

Need something like this

data WORK.want;
  infile datalines dsd truncover;
  input id:32. num:32. start:DATE9. end:DATE9.;
datalines4;
101,2,12MAR2018,14MAR2018
101,3,15MAR2018,16MAR2018
101,3,17MAR2018,18MAR2018
101,3,19MAR2018,19MAR2018
101,4,20MAR2018,21MAR2018
101,4,20MAR2018,21MAR2018
101,4,22MAR2018,23MAR2018
101,4,24MAR2018,25MAR2018
101,4,26MAR2018,27MAR2018
101,4,28MAR2018,29MAR2018
;;;;

any help please

Re: how splie records based on start and end dates

Kurt_Bremser — Wed, 10 Oct 2018 08:10:21 GMT

Re-think your issue with regards to what constitutes an "odd" or "even" date, and how you want to handle month boundaries, as months can have odd (29,31) and even (28,30) numbers of days.

Re: how splie records based on start and end dates

vraj1 — Wed, 10 Oct 2018 08:22:11 GMT

odd means if duration of start and end is 5 days and i want to split on 2 days each than 2 rows with 2 days duration and one row with 1 day. I am looking only for days and it will be continous start and end for one id

Re: how splie records based on start and end dates

Kurt_Bremser — Wed, 10 Oct 2018 08:31:19 GMT

Ah, it's the durations only that matter. See this:

data have;
infile datalines dsd truncover;
input id num (start end) (:date9.);
format start end date9.;
datalines4;
101,2,12MAR2018,14MAR2018
101,3,15MAR2018,19MAR2018
101,4,20MAR2018,29MAR2018
102,2,12JAN2018,19JAN2018
102,3,20JAN2018,28JAN2018
102,4,29JAN2018,12FEB2018
102,5,13FEB2018,18FEB2018
102,6,19FEB2018,28FEB2018
;;;;
run;

data want;
set have (rename=(start=_start end=_end));
format start end date9.;
if _end - _start le 2
then do;
  start = _start;
  end = _end;
  output;
end;
else do;
  start = _start;
  end = start + 1;
  do while (end le _end);
    output;
    start + 2;
    end = start + 1;
  end;
  if end = _end + 1
  then do;
    start = _end;
    end = _end;
    output;
  end;
end;
drop _start _end;
run;

Re: how splie records based on start and end dates

vraj1 — Wed, 10 Oct 2018 10:06:29 GMT

Sorry kurt i just realised that. it should only split 2 days for first 2 times and the rest should be as it is. I am very sorry for confusion.

if first.id then it should split num 2 times and the rest of dates should be as it is

data have;
infile datalines dsd truncover;
input id num (start end) (:date9.);
format start end date9.;
datalines4;
101,2,12MAR2018,18MAR2018
101,3,15MAR2018,19MAR2018
101,4,20MAR2018,29MAR2018
102,2,12JAN2018,19JAN2018
102,3,20JAN2018,28JAN2018
102,4,29JAN2018,12FEB2018
102,5,13FEB2018,18FEB2018
102,6,19FEB2018,28FEB2018
;;;;
run;

data want;
infile datalines dsd truncover;
input id num (start end) (:date9.);
format start end date9.;
datalines4;
101,2,12MAR2018,13MAR2018
101,2,14MAR2018,15MAR2018
101,2,16MAR2018,18MAR2018
101,3,15MAR2018,19MAR2018
101,4,20MAR2018,29MAR2018
102,2,12JAN2018,19JAN2018
102,3,20JAN2018,28JAN2018
102,4,29JAN2018,12FEB2018
102,5,13FEB2018,18FEB2018
102,6,19FEB2018,28FEB2018
;;;;
run;

very sorry for the confusion

Re: how splie records based on start and end dates

Kurt_Bremser — Wed, 10 Oct 2018 10:10:35 GMT

In your example, only the first observation is split, so you can use by-group processing and apply the whole algorithm only at first.id.

Re: how splie records based on start and end dates

vraj1 — Wed, 10 Oct 2018 10:30:02 GMT

data have;
infile datalines dsd truncover;
input id num (start end) (:date9.);
format start end date9.;
datalines4;
101,2,12MAR2018,18MAR2018
101,3,15MAR2018,19MAR2018
101,4,20MAR2018,29MAR2018
102,2,12JAN2018,19JAN2018
102,3,20JAN2018,28JAN2018
102,4,29JAN2018,12FEB2018
102,5,13FEB2018,18FEB2018
102,6,19FEB2018,28FEB2018
;;;;
run;


proc sort data=have; by id num; run;

data want;
set have (rename=(start=_start end=_end));
by id;
format start end date9.;
if first.id then do;
if _end - _start le 2
then do;
  start = _start;
  end = _end;
  output;
end;
else do;
  start = _start;
  end = start + 1;
  do while (end le _end);
    output;
    start + 2;
    end = start + 1;
  end;
  if end = _end + 1
  then do;
    start = _end;
    end = _end;
    output;
  end;
end;
end;
drop _start _end;
run;

I tried this way but it gave me same result