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;
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.