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
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-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.
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
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;
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
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.
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
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.