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 |
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;
What do you mean by "complete group"?
Show us the desired output.
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.
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.