let’s see the limitation of SAS SQL with an example.

 

If my original code was a text or a character value like Recipe name, and I want to compare it with a data value that’s a character, then it must include the macro in double quotes. So let’s take it to code now. And by the way, these are the dishes that have a heat scale less than the average for the entire set of dishes. Let’s see this play out in code, which recipes have a moderate heat scale.

Now, I have no idea at this time what is a moderate number. So I’m going to target somewhere in the middle, and I’m going to say give me all rows when the heat scale is less than or equal to 3, and grab the danger from the Spices table. So this query is going to result in six rows of data. Six dishes have a heat scale less than or equal to 3. Now that was all fine, but was that truly hitting the average mark? I was guesstimating. So to get to a true average, I need to go and grab this from the data itself bypassing the average function to this query.

What is the average heat scale? And then, I can use it average to grab rows of data that kind of match the average. And the average is going to be 2.25. I’ll copy this over, and then I’ll bring it back to code to paste it in the space where I’m asking for a heat scale less than the average. Earlier, I had supplied less than or equal to 3. Now, I am going to the actual average. And that gives me those five rows of data. Up until now, we have not done any marching of our code. We’ve been hard coding this value. The practical use of the macro is to reduce, reuse, recycle.

In the first step, I’m going to select the average heat scale, and I’m going to store it INTO. Isn’t the SQL language so similar to the English language? Stored into a macro variable. So INTO followed by a colon. And then the name of the macro variable. What is the name? It can be user-defined. Can it be anything? Well, anything as in acceptable, by SAS, meaning rules. And I’ve also turned the NOPRINT option because this is a single value that I want to be stored in a macro variable. There’s no need for me to see it in the Results window. Also, at the very end, I want %put statement. A cool little statement. It allows me to see the value of a macro variable. Avg is a macro variable. And if I’m telling SAS, please print the value of a macro variable, I need to precede that macro variable with the symbol amper. The query is executed. And in the log, I’m going to see the value 2.25. Quite nice, but still missing something. Because in the log, I have row numbers. And this 2.25 somehow gets lost in a sea of numbers. I like to see it preceded by the name of the macro variable. And how do I do this? By a little tweak to that %put statement.

I’ll still have %put, and then I’ll saw amper equals. By now, SAS is expecting that I’m requesting a macro variable. And then after that, I give the name of the macro variable. So what this produces for me in the log is something you might be happier with. Avgheat equals 2.25. Very clear, very articulate, and there’s no confusion. Let’s head back to code. So we have a macro variable now, and we want to use it. We have Avgheat, and we know it’s holding a value of 2.25. How do I use it in code? I’m going to use it in two separate spaces. And that really is the value of a macro variable. Reduce, reuse, recycle.

What are the two places I can use it in? In the TITLE statement, I can ask SAS to print the value of this macro variable, great. And a rule that the macro language has, goes something like this. If the original code, the TITLE statement is using a macro, and if the original code is using quotations, and you have a macro in there, then replace the quotations with double quotations. Same principle in the WHERE clause. Is the original code heat scale numeric? I would have actually typed in less than 2.25. There are no code quotations in the original code, so my macro is typed as-is. No quotations. At this step, I macro this query, and I’m going to see that value of 2.5 appears in two places.

One in the TITLE, second in the WHERE clause, filtering only those rows that the average is less than 2.25. So this is the first form of storing a query in a macro value. Up until now, we only see a single value. Let’s take it to real-time business where you have multiple values. There are three forms of syntax of that INTO class. Up until now, we’ve been seeing the first form. Let’s see the first form in another business scenario. I’d like to see all the recipe names where the heat scale is less than or equal to 3.

I’m going to store a recipe name into a macro variable. And I’m going to run this query and check out the value of this macro variable. When the query runs, I get six rows of data. Remember, earlier we had said keep the number six in mind. Six rows of data where the heat scale is less than or equal to 3, all right? And the first row, let’s pay attention to the first row. Chana masala. Go to the log. The macro variable LOWHEATRECIPE has only grabbed the first row of data and held it there. The INTO clause is syntax 1. It’s only going to store the first row in a macro.

Now, you’re probably thinking, I have six rows. I want six macro variables. Not one. We are here to solve that question as well. To store multiple rows in multiple macro variables, I’ll still have my SELECT sequence. I still have an INTO and then the colon followed by the name of the first macro variable, hyphen, colon, the name of the last macro variable. So you get the idea. I’m going to create six macro variables.

Six rows of data are going to be stored in six variables. And I had a no print option turned on this time. Only the log with a %put statement will provide the values of the six macro variables. So this is syntax 2, which is storing multiple rows in multiple macro variables. Well, one last syntax. The syntax 3. Syntax 3 is going to store a list of values. So, say I want to get to know all the recipe names where the heat scale is less than or equal to 3, so that I can pass it to a WHERE clause, to the Chef table and get to know who the chefs are and the country of origin for these dishes. Let’s begin first by storing the list in a macro variable.

Leave a Comment