topic Re: Search by category in SAS Programming

Search by category

Frex — Tue, 26 Jun 2018 14:15:37 GMT

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;

Re: Search by category

PaigeMiller — Tue, 26 Jun 2018 14:36:04 GMT

Show us the desired output.

Re: Search by category

Frex — Tue, 26 Jun 2018 14:41:26 GMT

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

Re: Search by category

Frex — Tue, 26 Jun 2018 14:49:15 GMT

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

Re: Search by category

novinosrin — Tue, 26 Jun 2018 14:58:55 GMT

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;

Re: Search by category

Larrihoover — Tue, 26 Jun 2018 15:10:12 GMT

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;

Re: Search by category

PaigeMiller — Tue, 26 Jun 2018 15:29:50 GMT

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

Re: Search by category

novinosrin — Tue, 26 Jun 2018 15:38:23 GMT

@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,

Re: Search by category

Frex — Wed, 27 Jun 2018 08:06:46 GMT

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