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
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;
> 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?
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;
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;
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.