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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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