Let's Check The Power Of SAS SQL With The Basic Example. » My Blogging

Let’s check the power of SAS SQL with the basic example.

It has no resemblance to the original apples. And that’s a distinction I wanted you to take away. The difference between the WHERE and the HAVING. The WHERE acts on detailed data on unprocessed data, on the original rows. And the HAVING acts on your summarized data. It’s able to cut through and filter your summarized data. So here we have the HAVING. I’ll submit it. And there we go. These are the four SpiceQuotients that have an average oil value greater than or equal to 4.

Using the HAVING clause, we were able to effectively cut through the group data and grab only those rows that met our condition. Adding to our building blocks now, we would like to see the results ordered in a certain way. So while the results gave us the average oil, it’s not really ordered in a manner that makes it easy on the eye. A nice order might be, well, show me the results in order of descending average oil or ascending average oil, so that I can make some calculated decisions. To add to this is the ORDER BY clause. Here’s the business question I want to ask. What SpiceQuotients have the maximum amount of fat? So the query hasn’t really changed.

We’re still selecting SpiceQuotient, summarizing average OilInTbsp. From the Ingredients dataset, we are grouping by SpiceQuotient. We use the HAVING clause to cut through and get only those rows if the summarized column, average oil, is less than or equal to 4. To add the ORDER BY statement, which is the last statement in our query, I just stick it there after the HAVING. Interesting ways in which I can use the ORDER BY. I can either list the column name from the SELECT.

Now, it so happens that the column name has an alias here. This is very handy. If I didn’t have an alias for this new column name, guess what? I would have to retype Avg brackets OilInTbsp every single time I needed to reuse it in my query. But I’m still not that big a fan of typing so much. I think I’m a smart programmer, so I want to reduce the amount of typing I do. And the ORDER BY gives me a possibility. It says you don’t have to type in column names. You can even type in an integer value representing the column position of that column on the SELECT. So let’s back up there. Long statement. I want the column position on the SELECT. Happens to be the second column, so I’ll stick ORDER BY 2 in descending order.

I’m trying to see the results to see what SpiceQuotients have the maximum amount of fat using the ORDER BY. And this gives me a descending order. So I can see recipes and dishes that use the most fat and try to correlate in my brain the SpiceQuotient, and make important decisions about. If SpiceQuotient of seaweed has an average oil of 4, can I dummy down the amount of oil? Can I maybe reduce the oil there? Because the recipe is already sweet, and it looks like fat can counteract spice. So it’s not needed as much for that recipe. Further down, if I have insanity using 4 tablespoons of oil, perhaps I can up the amount of oil if I’m not so keen on a very hot dish.

Let’s do a quick recap of section one on crafting an efficient query. These are the statements that SQL needs, and in this order. Two things that are always mandatory are the SELECT and the FROM. Everything else is optional. Let’s move on to section two. Joins, subqueries, inline views. Three ways in which I’m going to show you how to grow your table wide because that’s basically what a join is. Stacking your tables beside each other to grow them wide. Let’s understand the concept of an inner join. I have my Chef table and my Spices table. So why do I need data from both the datasets? Well, the Chef table has the names of the chef, and the Spices table has information about the spice ingredients. And I really want to strike a midway mark. I want to find those chefs that are going to make dishes that are kind of a moderate heat rating. About a number three.

I’m going to basically stack these tables beside each other in an inner join and provide a condition that says, give me those rows where the heat rating is less than or equal to 3. And that condition is really important for a join. If I didn’t put the condition in, then SQL is going to take the two datasets, slap them together and give you all possible combinations. And that is called a Cartesian product. And we don’t want to be building that monster. So we are using an inner join. So that’s conceptually the idea of an inner join. You can replicate the same action that an inner join does, which is growing the data wide through a subquery. Here is a list of recipes or dishes that are low in heat. And this is going to be my subquery. This data is coming from the Spices table.

I’m going to pass this list to the outer query to match it up and see which are the chefs that are making these dishes. So the outer query is going to be the Chef table. Under the covers, SQL may decide to do a join while you provide a subquery and query on the outside. But the concept is basically the same. You’re growing the data wide by grabbing more information. Best practices for a subquery indicate that the subquery must be standalone, must be independent.

You really want the subquery to provide values to the outer query. And this is called a non-correlated subquery. You don’t want the outer query to pass values to the inner query. That becomes a correlated subquery. It can be messy, and it can lead to a very expensive query, which runs over days, perhaps weeks. We saw the concept of a join, now how does it play out in code? Here is the syntax. On the SELECT, I’m going to list all the columns from all the tables that I’m grabbing on the FROM. For the first time now, we are seeing, on the FROM clause, two tables listed. The Chef table and the Spices table. And we stack them together using the inner join syntax. And that’s not all. We need to supply the join condition. That goes on the ON clause, where you specify the table name followed by the column name. Chef.name=spices. Recipe name. So that is your inner join. And then to that, we also want to filter through those rows of stacked data. We want to grab only those rows where the heat scale is less than or equal to 3. Kind of hitting that moderate heat mark. And so these are the chefs and these are their recipes.

What I’m seeing here is amazingly useful. On the left side, the first four columns are coming from the left table, the Chef table. The remaining columns are coming from the Spices table. So you have a lot of information and a lot of possibilities to mix and match. A thought struck me, though, as I was writing this code. I had to spell out the chef. Name on the ON clause and spices.Recipename. And I already mentioned, I believe in being a smart programmer. So how can I modify this program to make it a little smarter, is by providing aliases? We saw aliases in section one for column names. Well, we can certainly apply aliases to table names as well. And you can make it as short as a single character. So dinner. Chef, going forward, like C, provides SQL the information that any time I use columns from this table, I am going to qualify it with C.name. So it does do things here for me, this handy little tip.

Related Posts

14 thoughts on “Let’s check the power of SAS SQL with the basic example.

  1. I was recommended this blog by my cousin. I’m not sure whether this post is written by him as no one else know such detailed about my problem.
    You’re incredible! Thanks!

  2. Hi exceptional website! Does running a blog such as this take a great deal of work?
    I’ve no knowledge of programming however I was hoping to start
    my own blog in the near future. Anyways, if you have any recommendations or
    tips for new blog owners please share. I understand this is
    off subject nevertheless I just wanted to ask.
    Many thanks!

  3. Hi, Neat post. There’s a problem together with your website in internet explorer, may
    check this? IE still is the market leader and a big element of other people will omit your fantastic writing
    due to this problem.

  4. Excellent blog here! Also your site loads up very fast! What web host are you using?
    Can I get your affiliate link to your host?
    I wish my website loaded up as fast as yours lol

  5. I’m really enjoying the design and layout of your website.
    It’s a very easy on the eyes which makes it much more enjoyable for me to come here and visit more often. Did you hire out a designer to create your theme?
    Superb work!

  6. Do you mind if I quote a couple of your posts as long as I
    provide credit and sources back to your website? My blog is in the very same area of
    interest as yours and my visitors would really benefit
    from some of the information you provide here. Please
    let me know if this okay with you. Cheers!

  7. You actually make it appear so easy with your presentation but I in finding this matter to be really something that I feel I might by no means understand.

    It seems too complex and extremely large for me.

    I’m taking a look forward on your subsequent submit, I’ll try to get the dangle
    of it!

Leave a Reply

Your email address will not be published.