Search by category

[Frex]

Hi Everyone,

I have been struggling with a search algo, the data looks as follows:

category start_date end_date

red     10jan2018    15feb2018

red     02jan2018    10jan2018

red     09jan2018    10jan2018

red     01jan2018    15feb2018

red     10jan2018    15feb2018

blue   01jan2018     10jan2018

What i'm trying to do:

Where the category is red, compare every start date to every other end date where they are equal. 

hope that makes sense.

Pseudo code is fine 🙂 

run;

[PaigeMiller]

Show us the desired output.

[Frex]

I’m basically trying to find instances where other events in the category start on the same day a certain event ends.

[Frex]

so say for example I start looping through, the first category will be red and the start date 10JAN2018:

I expect it to find:

category start_date end_date

red     02jan2018    10jan2018

red     09jan2018    10jan2018

hope that helps...

[novinosrin · Accepted solution]

Let us know if this helps, if not i will switch to hash solution. 

data have;
input category $ (start_date end_date) (:date9.);
format start_date end_date date9.;
cards;
red     10jan2018    15feb2018
red     02jan2018    10jan2018
red     09jan2018    10jan2018
red     01jan2018    15feb2018
red     10jan2018    15feb2018
blue   01jan2018     10jan2018
;

proc sql;
create table want as
select distinct a.category,b.start_date as start_date,b.end_Date
from have a, have b
where a.category=b.category and a.start_date=b.end_date;
quit;

[Frex]

Thanks man, this worked with some slight modification to skip missing values. 

[PaigeMiller]

---

@Frex wrote:

so say for example I start looping through, the first category will be red and the start date 10JAN2018:

I expect it to find:

category start_date end_date

red     02jan2018    10jan2018

red     09jan2018    10jan2018

hope that helps...

---

Nope, doesn't help a bit. You have end date of 15feb2018, and start date of 01jan2018, why aren't those used? 

Can you show us and give us a thorough and clear explanation of what you want?

[novinosrin]

@PaigeMiller Good morning,  My understanding is 

Look up startdate <=>enddate , if there is a match, fetch the startdate for those matches

So in OP's example, only 10 jan2018 exisits in enddate and 2nd and 9th are the corresponding startdate for those matches,

[Larrihoover]

data test;
input color $5. start_date date9. @16 end_date date9.;
informat start_date date9. end_date date9.;
format start_date date9. end_date date9.;
cards;
red 10jan2018 15feb2018
red 02jan2018 10jan2018
red 09jan2018 10jan2018
red 01jan2018 15feb2018
red 10jan2018 15feb2018
red 10dec2018 10dec2018
blue 01jan2018 10jan2018
;
proc print data=test; run;

Data test2;
set test;

if color ='red' and start_date = end_date then flag = 1;
else flag = 0;
proc print data=test2; run;
run;