DATA Step, Macro, Functions and more

Search by category

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Search by category

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 Smiley Happy 

 

run;

 


Accepted Solutions
Solution
4 weeks ago
PROC Star
Posts: 1,836

Re: Search by category

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,065

Re: Search by category

Show us the desired output.

--
Paige Miller
New Contributor
Posts: 4

Re: Search by category

Posted in reply to PaigeMiller
I’m basically trying to find instances where other events in the category start on the same day a certain event ends.
New Contributor
Posts: 4

Re: Search by category

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

Solution
4 weeks ago
PROC Star
Posts: 1,836

Re: Search by category

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;
New Contributor
Posts: 4

Re: Search by category

Posted in reply to novinosrin

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

Respected Advisor
Posts: 3,065

Re: Search by category


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

--
Paige Miller
PROC Star
Posts: 1,836

Re: Search by category

Posted in reply to PaigeMiller

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

Occasional Contributor
Posts: 17

Re: Search by category

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 98 views
  • 0 likes
  • 4 in conversation