Need to add Yes/No if based on the Last.ID

[karthigao]

Hi, 

I need to add new variables Nextcycle, indicating if the subject has moved to next cycle or not, see my data below, if the EOT end of treatment is not null, then the row above the end of treatment should have the nextcycle value as 'No' or else its Yes. the row before the last.Id Should be 'No' if the end of treatment is present. The red column must be added to the new table (which I have manually added)

Subject	Visit	Visit_date	Nextcycle
A001	Screening	10/27/2021	Yes
A001	cycle1	12/1/2021	Yes
A001	cycle2	1/17/2022	Yes
A001	cycle3	2/28/2022	Yes
A001	cycle4	4/19/2022	Yes
A001	cycle5	5/30/2022	Yes
A001	cycle6	7/11/2022	Yes
A001	cycle7	8/22/2022	Yes
A001	cycle8	10/3/2022	Yes
A001	cycle9	11/17/2022	Yes
A001	cycle10	1/2/2023	Yes
A001	cycle11	2/15/2023	Yes
A001	cycle12	3/27/2023	Yes
A002	Screening	11/22/2022	Yes
A002	cycle1	1/14/2023	Yes
A002	cycle2	2/28/2023	Yes
A003	Screening	9/30/2022	Yes
A003	cycle1	11/2/2022	Yes
A003	cycle2	11/23/2022	Yes
A003	cycle3	1/2/2023	No
A003	EOT	1/9/2023
A004	Screening	9/1/2022	Yes
A005	cycle1	9/23/2022	Yes
A005	cycle2	10/31/2022	No
A005	EOT	11/2/2022

[PaigeMiller]

/* UNTESTED CODE */

data want;
    merge have have(firstobs=2 rename=(visit=visit1) keep=visit);
    if visit1='EOT' then nextcyle=0;
    else nextcycle=1;
run;

[Tom · Accepted solution]

You could try some method to look ahead to see if there is an EOT visit or not.

But here is a method will let you just use the FIRST/LAST processing of BY statement.

Introduce a new variable that indicates if this is an EOT visit or not.  You can do that as a view so you don't have to read the data twice.

Then the NO records as for the last non-EOT visit that is not also the last SUBJECT visit.

data for_analysis / view=for_analysis;
  set have ;
  eot=visit='EOT';
run;

data want;
  set for_analysis;
  by subject eot ;
  want='Yes';
  if last.eot and not last.subject then want='No';
  if eot then want=' ';
  drop eot;
run;

Obs    Subject    Visit        Visit_date    Nextcycle    want

  1     A001      Screening    2021-10-27       Yes       Yes
  2     A001      cycle1       2021-12-01       Yes       Yes
  3     A001      cycle2       2022-01-17       Yes       Yes
  4     A001      cycle3       2022-02-28       Yes       Yes
  5     A001      cycle4       2022-04-19       Yes       Yes
  6     A001      cycle5       2022-05-30       Yes       Yes
  7     A001      cycle6       2022-07-11       Yes       Yes
  8     A001      cycle7       2022-08-22       Yes       Yes
  9     A001      cycle8       2022-10-03       Yes       Yes
 10     A001      cycle9       2022-11-17       Yes       Yes
 11     A001      cycle10      2023-01-02       Yes       Yes
 12     A001      cycle11      2023-02-15       Yes       Yes
 13     A001      cycle12      2023-03-27       Yes       Yes
 14     A002      Screening    2022-11-22       Yes       Yes
 15     A002      cycle1       2023-01-14       Yes       Yes
 16     A002      cycle2       2023-02-28       Yes       Yes
 17     A003      Screening    2022-09-30       Yes       Yes
 18     A003      cycle1       2022-11-02       Yes       Yes
 19     A003      cycle2       2022-11-23       Yes       Yes
 20     A003      cycle3       2023-01-02       No        No
 21     A003      EOT          2023-01-09
 22     A004      Screening    2022-09-01       Yes       Yes
 23     A005      cycle1       2022-09-23       Yes       Yes
 24     A005      cycle2       2022-10-31       No        No
 25     A005      EOT          2022-11-02

[HenryKobus]

data Treatment1;
format Subject $4. Visit $10. Visit_date date10. NextCycleManuallyAdded $3.;
input  Subject $8.	Visit $12.	Visit_date MMDDYY10.	NextCycleManuallyAdded $5.;
cards;
A001	Screening	10/27/2021	Yes
A001	cycle1		12/1/2021	Yes
A001	cycle2		1/17/2022	Yes
A001	cycle3		2/28/2022	Yes
A001	cycle4		4/19/2022	Yes
A001	cycle5		5/30/2022	Yes
A001	cycle6		7/11/2022	Yes
A001	cycle7		8/22/2022	Yes
A001	cycle8		10/3/2022	Yes
A001	cycle9		11/17/2022	Yes
A001	cycle10		1/2/2023	Yes
A001	cycle11		2/15/2023	Yes
A001	cycle12		3/27/2023	Yes
A002	Screening	11/22/2022	Yes
A002	cycle1		1/14/2023	Yes
A002	cycle2		2/28/2023	Yes
A003	Screening	9/30/2022	Yes
A003	cycle1		11/2/2022	Yes
A003	cycle2		11/23/2022	Yes
A003	cycle3		1/2/2023	No
A003	EOT			1/9/2023	 
A004	Screening	9/1/2022	Yes
A005	cycle1		9/23/2022	Yes
A005	cycle2		10/31/2022	No
A005	EOT			11/2/2022
;	 
run;

proc sort data = Treatment;
	by subject descending visit_date;
run;

data Treatment2;
set Treatment;
format NextCyle _TempNextCyle $3.;
retain _TempNextCyle;
by Subject;
select;
	when(first.Subject and Visit eq 'EOT' ) do;
		   									NextCyle = ''; 
											_TempNextCyle = 'No';end;
	when(first.Subject) 					do;
		   									NextCyle = 'Yes'; 
											_TempNextCyle = 'Yes';end;
	otherwise 								do;	
											NextCyle = _TempNextCyle; 
										    _TempNextCyle = 'Yes';end;
end;
/* select;
	when(NextCyle eq NextCycleManuallyAdded) CorrectResult = 1;
	otherwise 								 CorrectResult = 0;
end; */
drop _: ;
run;

proc sort data = Treatment2;
	by subject visit_date;
run;