Identify treatment regimen

[SSK_011523]

Hi all, 

I'm interested in BR-based regimens in the following code. I wish to keep all patient ids.

I'm not sure how to proceed

ID	REGIMEN	LOT	regimen12
1	A	1L	Chemo monotherapy
1		2L
1		3L
2	B	1L	Chemo monotherapy
2		2L
2		3L
3	C+D	1L	BR-based
3		2L
3		3L
4	C+D	1L	BR-based
4		2L
4		3L
5	E	1L	Chemo monotherapy
5	F	2L	Chemo monotherapy
5	E+F	3L	Other chemotherapy
5	G	1L	onco
5		2L
5		3L
6	C+D	1L	BR-based
6		2L
6		3L

Data want - 

ID	REGIMEN	LOT	regimen12
3	C+D	1L	BR-based
3		2L
3		3L
4	C+D	1L	BR-based
4		2L
4		3L
6	C+D	1L	BR-based
6		2L
6		3L

[Ksharp · Accepted solution]

CODE NOT TESTED:

proc sql;
create table want as
select * from have
where ID in (select ID from have where regimen12='BR-based')
;
quit;

[SSK_011523]

Thank you! this code works. 

I QC'ed it too. 

[yabwon]

Two more options along with the one proposed by @Ksharp 

data have;
infile cards dlm="|";
input ID REGIMEN :$3. LOT $2. regimen12 :$30.;
cards;
1|A|1L|Chemo monotherapy
1| |2L| 
1| |3L| 
2|B|1L|Chemo monotherapy
2| |2L| 
2| |3L| 
3|C+D|1L|BR-based
3| |2L| 
3| |3L| 
4|C+D|1L|BR-based
4| |2L| 
4| |3L| 
5|E|1L|Chemo monotherapy
5|F|2L|Chemo monotherapy
5|E+F|3L|Other chemotherapy
5|G|1L|onco
5| |2L| 
5| |3L| 
6|C+D|1L|BR-based
6| |2L| 
6| |3L| 
;
run;
proc print;
run;

/* option 1 */
data want;
  do _N_=1 by 1 until(last.ID);
    set have;
    by ID notsorted;
    output+(regimen12="BR-based");
  end;

  do _N_=1 to _N_;
    set have;
    if output then output;
  end;
  output=0;
  drop output;
run;
proc print;
run;

/* option 2 */
data want2;
  declare hash H(dataset:"have(where=(regimen12='BR-based'))");
  H.defineKey("ID");
  H.defineDone();

  do until(eof);
    set have end=eof;
    if 0=H.check() then output;
  end;
stop;
run;
proc print;
run;

Bart

[yabwon]

and since IDs are numerical in "limited" range, one more:

/* option 3 */
data want3;
  
  array IDs[1:100] _temporary_;
  do until(eof1);
    set have(keep=ID regimen12) end=eof1;
    where regimen12='BR-based';
    IDs[ID]=1;
  end;

  do until(eof2);
    set have end=eof2;
    if IDs[ID] then output;
  end;
stop;
run;
proc print;
run;