Big Data - Cartesian

[ChrisNZ]

Mmm indeed, the embedded brand could be at either end of the longer brand.

So the test should tolerate equalities but only at one end. Like this maybe:

    and b.START <= a.START    
    and a.END   <= b.END    

    and not (b.START = a.START & a.END = b.END )     

or this:

    and ( (b.START <= a.START & a.END < b.END )   

           | (b.START < a.START & a.END <= b.END )     )

[Kurt_Bremser]

As a Super User, I'd recommend for one of you two @JohnHoughton@ChrisNZ to put the findings of this thread into an article. Rarely has there been such a fruitful discussion of performance optimisation in SAS around here.

This is a fine source of food for thought for beginners and advanced users alike.

[ChrisNZ]

I can probably give it go if you want.

I want to add a new chapter in

https://www.amazon.com/High-Performance-SAS-Coding-Christian-Graffeuille/dp/1512397490

about table joins, and examples like this one are interesting.

I've always resisted it as there are so may ways to joins, so many reasons, so may table sizes and structures, so many join criteria and cardinalities. Yet I am at the point where I can't avoid it as it is typically such an important part of a SAS job, and I can at least give benchmarks, examples and ideas to trigger the reader's brain into thinking about options.

Unless @JohnHoughton  volunteers of course. 🙂

[Kurt_Bremser]

No matter how you do it, I'd just hate for this thread to simply vanish in the depths of the internet.

[JohnHoughton]

@ChrisNZ brilliant; both of your new options to check for embedded brands work with my made-up data to leave just one match.

I tested on dataset with 25million brands and 400,000 phrases of 25 words (on SAS OnDemand) and it ran in 6 minutes!!

Can't wait to hear how it works with @pietro342 's real data. Will need some refinements to deal with punctuation marks in the phrases.

I will leave the article to you. 

[pietro342]

Hi JohnHoughton,

I'm sorry I did not reply to messages. I tested your solution 15 minutes after your post and I could not believe. It works excellent! Unfortunately, I had to go back to my dictionary. I'm just finishing it. I will tested  your solution today. I'll put log 🙂

ChrisNZ, JohnHoughton,

Thank you very much. You are brilliant!

[ChrisNZ]

@pietro342 So what's the new run time?

[Kurt_Bremser]

---

@Walker_cabay wrote:

'Big data' is the new science of understanding and predicting human behavior by studying large volumes of unstructured data. Big data is also known as 'predictive analytics'.

---

This discussion was not about "Big Data" in the Data Science sense, but about dealing with big datasets in a technical SAS sense.