Dear all,
I would like to created different lines of treatment and calculate the start and stop date
of every line of treatments I have.
I appreciate your help. thank you.
data have;
input id date :date9. treat;
format date date9.;
DATALINES;
1 08MAR2018 1
1 23MAR2018 1
1 26APR2018 1
1 10MAY2018 1
1 24MAY2018 1
1 24MAY2018 2
1 26MAY2018 2
1 27MAY2018 2
1 28MAY2018 2
1 29MAY2018 2
1 07JUN2018 3
1 21JUN2018 3
1 21JUN2018 4
1 22JUN2018 4
1 23JUN2018 4
1 24JUN2018 4
1 25JUN2018 4
2 05JUL2018 5
2 19JUL2018 5
2 19JUL2018 6
2 20JUL2018 6
2 21JUL2018 6
2 22JUL2018 6
2 23JUL2018 6
3 01AUG2018 7
3 16AUG2018 7
3 16AUG2018 8
3 17AUG2018 8
3 18AUG2018 8
3 25OCT2018 9
;
run;
Dataset I want to :
want:
treat start stop
1 08mar2018 23may2018
2 24may2018 29may2018
3 07jun2018 20jun2018
4 21jun2018 25jun2018
5 05jul2018 18jul2018
6 19jul2018 23jul2018
7 01aug2018 15aug2018
8 16aug2018 18aug2018
9 19aug2018 25oct2018
Thank you very much.
Cuan.
data want;
set have;
by treat;
if eof1=0 then
set have(firstobs=2 keep=date rename= date=date_) end=eof1;
else date_=.;
ldate=lag(date);
format start stop ldate date9.;
retain start;
if first.treat then start =date;
if sum(first.treat,last.treat) =2 then start=ldate+1;
if last.treat then
do;
stop=date;
if date=date_ then stop=date-1;
else stop=date;
output;
end;
drop ldate date_;
run;
data have;
input id date :date9. treat;
format date date9.;
DATALINES;
1 08MAR2018 1
1 23MAR2018 1
1 26APR2018 1
1 10MAY2018 1
1 24MAY2018 1
1 24MAY2018 2
1 26MAY2018 2
1 27MAY2018 2
1 28MAY2018 2
1 29MAY2018 2
1 07JUN2018 3
1 21JUN2018 3
1 21JUN2018 4
1 22JUN2018 4
1 23JUN2018 4
1 24JUN2018 4
1 25JUN2018 4
2 05JUL2018 5
2 19JUL2018 5
2 19JUL2018 6
2 20JUL2018 6
2 21JUL2018 6
2 22JUL2018 6
2 23JUL2018 6
3 01AUG2018 7
3 16AUG2018 7
3 16AUG2018 8
3 17AUG2018 8
3 18AUG2018 8
3 25OCT2018 9
;
run;
data want;
set have end=eof;
by id treat;
format start_date stop_date date9.;
*lag date;
_iorc_=lag(date);
*look ahead using point= dataset option;
pt=_N_+1;
set have(rename=(id=_id date=_date treat=_treat)) point=pt;
*keep start_date retained for final output;
retain start_date;
*logic;
if first.treat then do;
if not last.treat then start_date=date;
else if last.treat then start_date=_iorc_+1;
end;
if last.treat then do;
if ^eof then do;
if date=_date then stop_date=date-1;
else stop_date=date;
end;
else stop_date=date;
end;
*output last group;
if last.treat;
drop _: id date;
run;
Hi Hhinoir,
thank you for this.
I have an error when trying using your code in my SAS:
ERROR: The POINT= data set option is not valid for the data set WORK.have, the data set must be accessible by observation number for
POINT= processing.
My apologies. I forgot the eof flag.Let me know if this works for you.
data want;
set have end=eof;
by id treat;
format start_date stop_date date9.;
*lag date;
_iorc_=lag(date);
*look ahead using point= dataset option;
if not eof then do;
pt=_N_+1;
set have(rename=(id=_id date=_date treat=_treat)) point=pt;
end;
*keep start_date retained for final output;
retain start_date;
*logic;
if first.treat then do;
if not last.treat then start_date=date;
else if last.treat then start_date=_iorc_+1;
end;
if last.treat then do;
if ^eof then do;
if date=_date then stop_date=date-1;
else stop_date=date;
end;
else stop_date=date;
end;
*output last group;
if last.treat;
drop _: id date;
run;
data want;
set have;
by treat;
if eof1=0 then
set have(firstobs=2 keep=date rename= date=date_) end=eof1;
else date_=.;
ldate=lag(date);
format start stop ldate date9.;
retain start;
if first.treat then start =date;
if sum(first.treat,last.treat) =2 then start=ldate+1;
if last.treat then
do;
stop=date;
if date=date_ then stop=date-1;
else stop=date;
output;
end;
drop ldate date_;
run;
Hi r_behata thank you for this.
Brilliant.
cheers.
cuan.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.