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;
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;
Show us the desired output.
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...
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;
Thanks man, this worked with some slight modification to skip missing values.
@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?
@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,
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.