How to remove a group of observations based on certain date condition

[Spintu]

I want to delete complete group records based on by group ID Pract_name Hospital_name. if the date value is less than 01/01/2018.

ID	Pract_Name	Hospital_Name	Date
1	XYZ	ABC	24-Jun-18
1	XYZ	ASD
2	XYA	TVX
2	XYA	TVX	25-Jun-17
2	XYA	TRF
3	WED	DEC	25-Jun-19

Required Output:

ID	Pract_Name	Hospital_Name	Date
1	XYZ	ABC	24-Jun-18
1	XYZ	ASD
2	XYA	TRF
3	WED	DEC	25-Jun-19

[PaigeMiller]

What do you mean by "complete group"?

Show us the desired output.

[Spintu]

I have mentioned as required output

[PaigeMiller]

I don't understand "complete group". Please explain.

Also explain in more detail the logic that allows you to go from input data set to obtain output data set. Don't make me guess.

[novinosrin · Accepted solution]

data have;
input ID	Pract_Name $	Hospital_Name $	Date :date9.;
format date date9.;
cards;
1	XYZ	ABC	24-Jun-18
1	XYZ	ASD	 .
2	XYA	TVX	 .
2	XYA	TVX	25-Jun-17
2	XYA	TRF	 .
3	WED	DEC	25-Jun-19
;

proc sql;
  create table want as
  select *
  from have
  group by id, Pract_name, Hospital_name
  having not max (.< date < '01jan2018'd);
quit;

[novinosrin]

data have;
input ID	Pract_Name $	Hospital_Name $	Date :date9.;
format date date9.;
cards;
1	XYZ	ABC	24-Jun-18
1	XYZ	ASD	 .
2	XYA	TVX	 .
2	XYA	TVX	25-Jun-17
2	XYA	TRF	 .
3	WED	DEC	25-Jun-19
;

proc sort data=have out=_have;
  by id Pract_name Hospital_name;
run;
/*Datastep*/
data want;
  do until(last.Hospital_name);
    set _have;
	by id Pract_name Hospital_name notsorted;
	if .< date < '01jan2018'd then _n_=0;
  end;
  do until(last.Hospital_name);
    set _have;
	by id Pract_name Hospital_name notsorted;
	if _n_ then output;
  end;
run;

/*OR*/

data want;
  merge _have _have(where=(.< _date < '01jan2018'd) keep= id Pract_name Hospital_name date rename=date=_date in=a);
  by id Pract_name Hospital_name;
  if not a;
  drop _date;
run;
/*OR*/
data want;
  if _n_=1 then do;
    dcl hash h(dataset:"have(where=(.< date < '01jan2018'd))");
	h.definekey('id', 'Pract_name', 'Hospital_name');
	h.definedone();
  end;
  set have;
  if h.check() ne 0;
run;

[novinosrin]

data have;
input ID	Pract_Name $	Hospital_Name $	Date :date9.;
format date date9.;
cards;
1	XYZ	ABC	24-Jun-18
1	XYZ	ASD	 .
2	XYA	TVX	 .
2	XYA	TVX	25-Jun-17
2	XYA	TRF	 .
3	WED	DEC	25-Jun-19
;

proc sql;
  create table want as
  select *
  from have
  where cats(id,Pract_name,Hospital_name) not in 
  (select cats(id,Pract_name,Hospital_name) from have where .< date < '01jan2018'd);
quit;

[Spintu]

Thank you!