Using SAS SQL to Create Data-Driven Macro Variables
If you are looking for a way to assign data values to macro variables in SAS, a PROC SQL step is one of the easiest and most readable ways to do it. In this article, you will learn how to assign data values to macro variables in a PROC SQL step, making your code more dynamic and self-sufficient.
We will be working with SAS SQL and SAS macro language throughout this article. If you need a quick refresher, feel free to visit: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/titlepage.htm and https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/n1oihmdy7om5rmn1aorxui3kxizl.htm].
So, let us say we have just come into a sizable amount of money and are hoping to purchase a car from our favourite catalogue (sashelp.cars, of course). The catch is, we are hoping to spend at least $100,000.
We can pretty easily subset sashelp.cars to reflect our price range as follows:
proc sql;
select *
from sashelp.cars
where MSRP > 100000;
quit;
Now, just for fun, we’d like to assign each of these car model names to their own macro variable.
At the most basic level, we can manually assign values to macro variables then plug these macros into our code later. This can be really helpful to make our code more readable and customisable.
%let expensive_model1 = CL600 2dr;
%let expensive_model2 = SL55 AMG 2dr;
%let expensive_model3 = SL600 convertible 2dr;
%let expensive_model4 = 911 GT2 2dr;
proc sql;
select *
from sashelp.cars
where Model in ("&expensive_model1",
"&expensive_model2",
"&expensive_model3",
"&expensive_model4");
quit;
This works well enough for a static dataset but if we had a catalogue that updated regularly, we’d have to repeat this step every time, refreshing the data set then manually typing the name of each car mode to update the macro variables. This would be both time consuming and quite frankly unbecoming of a programmer of our means. This is where PROC SQL comes in. We can use the INTO statement to read the names of cars over $100,000 and assign them each to their own macro variable. Here’s how:
We will add an INTO statement to our code, which takes the form:
SELECT column-name
INTO :macro_var1-
So now our programme looks like this:
proc sql;
select Model
into :expensive_model1-
from sashelp.cars
where MSRP > 100000;
quit;
proc sql;
select *
from sashelp.cars
where Model in ("&expensive_model1",
"&expensive_model2",
"&expensive_model3",
"&expensive_model4");
quit;
This is a far more streamlined, and dare I say luxurious method of assigning macro variables. We could even assign these values to a list of comma separated, quoted values to streamline the subsetting WHERE statement:
proc sql;
select Model
format = $quote40.
into :expensive_models separated by ", "
from sashelp.cars
where MSRP > 100000;
quit;
proc sql;
select *
from sashelp.cars
where Model in (&expensive_models);
quit;
The INTO statement can do some pretty cool things, and this article barely scratches the surface. As an exercise, you might want to try using the INTO statement to assign a summary statistic (such as the average) to its own macro variable.
And as we drive off into the sunset, let’s take satisfaction in knowing our code is on its way to becoming as streamlined as our hypothetical > $100,000 convertible.
