DATA have;
infile datalines DELIMITER=',';
INFORMAT id 2. type $1. date1 date2 MMDDYY10. ;
INPUT id date1 type date2;
format date1 date9.
date2 date9.;
DATALINES;
1,02/09/2012,W,02/09/2012
2,05/16/2012,W,05/18/2012
2,06/18/2012,W,06/18/2012
2,06/18/2012,T,. < drop: same date
3,08/08/2011,W,08/08/2012
3,09/13/2011,W,09/13/2012
4,06/08/2016,W,06/12/2016
4,06/11/2016,T,. < drop: between 6/8 and 6/12
5,08/16/2012,W,08/16/2012
5,08/16/2012,W,08/30/2012
5,08/24/2012,T,. < drop: btw 8/16 and 8/22
6,09/05/2012,W,09/06/2012
7,09/05/2012,W,09/05/2012
7,09/07/2012,W,09/08/2012
7,08/03/2011,W,08/03/2011
7,05/01/2012,W,05/09/2012
7,04/30/2012,T,. <keep: as not between
8,03/31/2017,W,04/01/2017
8,03/06/2017,T,.
8,03/06/2017,L,.
8,07/03/2018,T,.
9,02/17/2016,T,. < drop same day
9,02/18/2016,L,. < drop between 2/17 and 2/22 day
9,02/17/2016,W,02/22/2016
;
run;
There are 3 type W,T,L 1step-if any type other than W is between the date1 and date2 for a same id then the record must not appear but include a newtype as W & T or W & L depending on the type record removed.
PROC SORT DATA= have;
BY ID DATE1 TYPE ;
RUN;
proc sql;
create table comb2 as
Select id, date1, type,date2 from have t
Where type = "W" or
not exists(select date1 from have
where id = t.id and t.date1 BETWEEN date1 and date2 and
type ="W")
;
run;
but this does not append the type that is removed like if type T is removed then the record of the corresponding W should indicate W & T as the new type.
2nd step depending on the value mentioned %let days_btw=5; then depending on the macro &days_btw should be used to remove any records between the days_btw limit and on the earliest record should be preserved for an id and the rest of the type should be appended but dont want to append if the dates are of the same type.
%Let DAYS_BTW=5;
proc sort data=have; by id date1; run;
data want;
drop date2;
do until(last.id);
set have; by id;
if missing(lastDate) or
intck("day", lastDate1, date1) > &DAYS_BTW or
lastType = type then do;
output;
lastdate1 = date1;
lasttype = type;
end;
end;
drop last: ;
run;
this gives the records as wanted but does not append to type as newtype. How do I create a field new_type indicating the records that are removed (eg W & T)
