topic Re: How to remove a group of observations based on certain date condition in SAS Programming

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

Spintu — Fri, 28 Jan 2022 18:40:47 GMT

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

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

PaigeMiller — Fri, 28 Jan 2022 18:41:14 GMT

What do you mean by "complete group"?

Show us the desired output.

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

Spintu — Fri, 28 Jan 2022 18:48:11 GMT

I have mentioned as required output

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

PaigeMiller — Fri, 28 Jan 2022 18:53:07 GMT

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.

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

novinosrin — Fri, 28 Jan 2022 22:20:18 GMT

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;

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

novinosrin — Fri, 28 Jan 2022 20:33:13 GMT



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;

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

novinosrin — Fri, 28 Jan 2022 22:22:50 GMT


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;

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

Spintu — Sat, 29 Jan 2022 16:06:56 GMT

Thank you!