What is type of subquery is called a non-correlated subquery? And how do I know that this is a non-correlated subquery, and is it best practice, are probably a couple of questions you might be thinking about. Yes, a non-correlated, generally, is best practice. And how do I know? Second question. How do I know if it is a non-correlated subquery? I’ll head back and look at my inner query. And that inner query must not be dependent on the outer query to supply values. In fact, it’s an inside out query the inner query is what is supplying values to the outer queries for a match, right? Same here.
The inner query is supplying data values to match with the outer table. So that’s an example of best practices in a non-correlated subquery. And these are two ways of growing the data wide. Possibly, you’ve worked in a joint. Possibly, you’ve worked with subqueries. But it’s also possible that perhaps it didn’t strike us that these are both techniques to grow the data wide. But I’m not going to stop here. I’m going to share one more technique to grow the data wide. And this technique is called an inline view. Now, the minute anyone heard here is the word inline view, chances are they start thinking that this is a view. But you are smart.
You’re not going to think that way because I’m going to share with you exactly what an inline view is. It is not a view. So we’ve already set the record straight on that. What it really is is a query that is going to sit on your FROM clause. So if you rewind and you think about what goes in the FROM clause, well, tables go on the FROM clause. So if I replace a table with a query, what is that doing? Basically, it’s creating an intermediate table. And that, you can join with another table. So in effect, it is like an inner join. And if the inline view is basically a query on the FROM, it acts as an intermediate table. So in this code here, I am grabbing rows from dinner. spices, where the heat scale is less than or equal to 3, combining it with a physical table.
How interesting is that? A virtual table is being combined with a physical table. And because it’s being combined, it becomes an inner join here. And I need to specify a join condition. So back up here a second. If I don’t specify the join condition, I will end in a Cartesian product. Even though it’s a virtual table, I still need to specify the join condition. Table as a table, at the end of a day. Virtual or physical. In terms of SQL. Having this inline view in place, are my result’s going to be any different versus the subquery versus the inner join? And the answer is no. I get the exact same results. In my code, I just want to point out one other factor. Here, the names of the columns are different. So they did not need to be qualified. You know what SQL says.
You can always use the same name column for joins, even if they come from two datasets. I don’t care. The only thing is, if they have the same name, please tell me what table they come from. Here, in our example, the names are different in the two tables. So we don’t need to qualify them. But if they were the same, then you would need to qualify them like so. You would need to give an alias for the first table. And you would need to give an alias for the second table. And then on the WHERE clause, you would specify the join condition using the Table qualifier.
Once again, recap. Only if you had the table names, the tables with the same column names. In our example, it wasn’t a problem. The two tables had different column names, so they never even entered into our consciousness. So there you go. You have three techniques to grow the data wide. We learned an inner join. We learned how to craft a subquery. And we learned to inline view and best practices throughout in learning the power of SQL. This SAS macro language allows us to create variables that we can use over and over again. In my third and final Power of SAS SQL tip, I’ll show you a cool way to store a SQL query result in a macro.
We’re really combining the power of two powerful languages. The macro language, and SQL. So that once the query result is stored in a macro, I can use it over and over again. Let’s take a look at the syntax. SELECT, you provide the name of the column. And then the INTO clause colon creates the name of the macro variable. And then the FROM clause provides the table name. Pretty simple. The only addition was the INTO clause. How about a business example? Well, let’s consider. I’d like to see which of my dishes, among all of the eight dishes, have a heat rating that is less than the average for all the dishes.
Here is the code. Select Avgheat scale Into Avgheat From Dinner. Spices. So that average value that I calculated is now being stored in a macro variable called Avgheat. And that, I can use over and over again. I can plug it into my title statement. I can stick it in a WHERE clause. I can use it in multiple locations. So that was step one, creating the macro. Step two, we want to use this macro. Wasn’t that the purpose of creation? And here’s how I use it. A standard query. Select * From Dinner. Spices where heat scale is less than that macro variable. How do you tell SAS it’s a macro variable? You insert an amper before the name of the variable, so SAS can make the distinction and see this as a macro variable. Not just a regular variable.
A couple of other things to bear in mind when you’re building these queries that are stored in a macro, and you want to use that macro. Firstly, that WHERE clause principle, like has to match like. If I’m comparing for a numeric column heat scale, the comparison on the right side should be a numeric value. And how does SAS know that this is numeric? And what about quotations? Well, if my original code used quotations, and I want to replace that value with a macro, then I must use double quotations.
Here, let’s take a look at this example. The heat scale was numeric. I would have typically said less than 2.25 if I was hard coding the value. Since I’m replacing it with a macro, I don’t need quotations, because what was the principle? If the original code used quotations and I want to use a macro, then I replace that with double-quotes.