BookmarkSubscribeRSS Feed
ChrisNZ
Tourmaline | Level 20

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
Super User

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
Tourmaline | Level 20

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

JohnHoughton
Quartz | Level 8

@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
Calcite | Level 5

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!

Kurt_Bremser
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 37 replies
  • 2785 views
  • 38 likes
  • 6 in conversation