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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.