Lines of treatment, start date and stop date

[cuan]

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.

[hhinohar]

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;

[cuan]

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.

[hhinohar]

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;

[r_behata · Accepted solution]

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;

[cuan]

Hi r_behata thank you for this.

Brilliant.

cheers.

cuan.