topic Re: remove the observation when the date is within 5 days and retain the earliest date record among in SAS Programming

remove the observation when the date is within 5 days and retain the earliest date record among them

Vk_2 — Thu, 06 Sep 2018 20:50:57 GMT

DATA table1;
infile datalines DELIMITER=','; 
INFORMAT id 2. type $10. date date2 MMDDYY10. ; 

 INPUT id  date type date2;

format date date9.
       date2 date9.;
DATALINES;
1,02/09/2012,BIG,02/09/2012
2,05/16/2012,BIG,05/18/2012
2,06/18/2012,BIG,06/18/2012
2,06/18/2012,SMALL,	
3,08/08/2011,BIG,08/08/2012
3,09/13/2011,BIG,09/13/2012
4,06/08/2016,BIG,06/12/2016	
5,08/16/2012,BIG,08/16/2012
5,08/15/2012,SMALL,	
6,09/05/2012,BIG,09/06/2012
7,09/05/2012,BIG,09/05/2012
7,02/13/2013,BIG,02/13/2013
7,08/03/2011,BIG,08/03/2011
7,05/09/2012,BIG,05/09/2012
7,04/24/2013,SMALL,	
8,03/31/2017,BIG,04/01/2017
8,03/06/2017,SMALL,	
9,02/17/2016,SMALL,	

;
run;

I want to remove the observation if in our example of id 5 date is 8/16/2012 for BIG type while for the same id type SMALL is 8/15/2012 the difference in date is 1 thus I would like to remove that observation which is greater and less than 5 days apart. So my dataset has to give only one record for id 5

5,08/15/2012,SMALL, but not 5,08/16/2012,BIG,08/16/2012

only between different type SMALL and BIG but not between themselves.

Sas EG-7.12

Re: remove the observation when the date is within 5 days and retain the earliest date record among

ChrisNZ — Thu, 06 Sep 2018 21:38:51 GMT

> only between different type SMALL and BIG but not between themselves.

What does this mean? Can you add an example in your data showing this case?

Re: remove the observation when the date is within 5 days and retain the earliest date record among

PGStats — Thu, 06 Sep 2018 21:41:08 GMT

proc sort data=table1; by id date; run;

data want;
do until(last.id);
    set table1; by id;
    if missing(lastDate) or intck("day", lastDate, date) > 5 then do;
        output;
        lastdate = date;
        end;
    end;
drop lastDate;
run;

Re: remove the observation when the date is within 5 days and retain the earliest date record among

Vk_2 — Fri, 07 Sep 2018 00:31:28 GMT

If for an id there are 2 SMALL type or 2 BIG within 5 days I dont want to remove but if they are different type within 5 days then I would just want the earliest one

Re: remove the observation when the date is within 5 days and retain the earliest date record among

Vk_2 — Fri, 07 Sep 2018 01:01:53 GMT

I dont want to remove both of these like eg
7,09/05/2012,BIG,09/05/2012
7,09/07/2013,BIG,09/08/2013
though they are 2 days apart but not different type

Re: remove the observation when the date is within 5 days and retain the earliest date record among

Vk_2 — Fri, 07 Sep 2018 01:37:29 GMT

This is not considering the type condition. If for an id there are 2 SMALL type or 2 BIG within 5 days I dont want to remove but if they are different type within 5 days then I would just want the earliest one. eg
7,09/05/2012,BIG,09/05/2012
7,09/07/2013,BIG,09/08/2013
though they are 2 days apart but not different type. Hence I dont want to remove these records

Re: remove the observation when the date is within 5 days and retain the earliest date record among

ChrisNZ — Fri, 07 Sep 2018 02:22:03 GMT

Like this?

proc sort data=HAVE out=SORTED; 
  by ID DATE TYPE;
run;
data WANT; 
  set SORTED; 
  if ID=lag(ID) and TYPE ne lag(TYPE) and . < dif(DATE) < 5 then delete;
run;

Re: remove the observation when the date is within 5 days and retain the earliest date record among

PGStats — Fri, 07 Sep 2018 03:21:23 GMT

Is this it?

DATA table1;
infile datalines dsd; 
INPUT id  date :mmddyy10. type :$10. date2 :MMDDYY10.;
format date date2 date9.;
DATALINES;
1,02/09/2012,BIG,02/09/2012
2,05/16/2012,BIG,05/18/2012
2,06/18/2012,BIG,06/18/2012
2,06/18/2012,SMALL,.          < drop : same date, different type
3,08/08/2011,BIG,08/08/2012
3,09/13/2011,BIG,09/13/2012
4,06/08/2016,BIG,06/12/2016	
5,08/16/2012,BIG,08/16/2012   < drop : one day apart, different type
5,08/15/2012,SMALL,.	
6,09/05/2012,BIG,09/06/2012
7,09/05/2012,BIG,09/05/2012
7,09/07/2012,BIG,09/07/2012   < Keep : two days apart but same type
7,02/13/2013,BIG,02/13/2013
7,08/03/2011,BIG,08/03/2011
7,05/09/2012,BIG,05/09/2012
7,04/24/2013,SMALL,.	
8,03/31/2017,BIG,04/01/2017
8,03/06/2017,SMALL,.	
9,02/17/2016,SMALL,.	
;

proc sort data=table1; by id date; run;

data want;
do until(last.id);
    set table1; by id;
    if  missing(lastDate) or 
        intck("day", lastDate, date) > 5  or 
        lastType = type then do;
            output;
            lastdate = date;
            lasttype = type;
            end;
    end;
drop last: ;
run;