BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Vk_2
Obsidian | Level 7
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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

7 REPLIES 7
ChrisNZ
Tourmaline | Level 20

> 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?

Vk_2
Obsidian | Level 7
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
Vk_2
Obsidian | Level 7
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
PGStats
Opal | Level 21

 

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;
PG
Vk_2
Obsidian | Level 7
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
ChrisNZ
Tourmaline | Level 20

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;

 

PGStats
Opal | Level 21

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1586 views
  • 0 likes
  • 3 in conversation