In SAS you can create macro variables in a variety of ways. In this article, we discuss how to create macro variables with the SELECT INTO clause. The SELECT INTO clause is useful when the values you want to assign to the macro variables are stored in a table. We cover the syntax of this clause and provide examples with SAS code.
The SELECT INTO Syntax
The SELECT INTO clause needs to be written within the PROC SQL procedure. With this clause, you can create one or multiple macro variables simultaneously. It is important that you write a colon (:) before each macro variable.
The general syntax is shown below. However, we provide more clear examples in the remainder of this article.
INTO : macro_variable_1 <, : macro_variable_2 …>
The SELECT INTO Example
The Example Data
We use a subset of the CARS data set to show how to create macro variables with the SELECT INTO clause. We select the first 10 rows and the columns Make, Model, Type, and Horsepower.
data work.ds; set sashelp.cars (obs=10); keep make model type horsepower; run;
Create one macro variable with SELECT INTO
The simplest way of using the SELECT INTO clause is to create 1 macro variable. The example below shows how to save the value of the Make column in the first row as a macro variable. We use the noprint option to prevent that SAS creates a report.
proc sql noprint; select make into :macro_make from work.ds; quit; %put &=macro_make.;
Create two macro variables with SELECT INTO
If you want to create a second macro variable that contains the value of the Model, you could reuse the code above. However, the code below shows a more elegant way.
proc sql noprint; select make, model into :macro_make, :macro_model from work.ds; quit; %put &=macro_make.; %put &=macro_model.;
Create different instances of different macro variables with SELECT INTO
Until now the macro variables that have been created contained all values of the first row of our data set. If you want to create, for example, 3 macro variables that contain the values of the first 3 rows, then you can use the following code.
proc sql noprint; select make, model into :macro_make1 - :macro_make3, :macro_model1 - :macro_model3 from work.ds; quit; %put &=macro_make1.; %put &=macro_make2.; %put &=macro_make3.; %put &=macro_model1.; %put &=macro_model2.; %put &=macro_model3.;
Use the separated by keyword in the SELECT INTO clause
Now, suppose you want to store all the different values of a column in one macro variable. You can achieve this with the use of the keyword separated by. This is useful when you want to create a list of items.
For example, the code below stores all types of cars in one variable. To use this marco variable later as input for the IN clause of a WHERE statement, the items are separated by a comma and nested in quotes.
proc sql noprint; select distinct quote(trim(type)) into :macro_type separated by ',' from work.ds; quit; %put &=macro_type.;
More examples of the SELECT INTO clause
In the previous examples, we created the macro variables reading directly from the input data set. However, you can use more complex code to create the value of the macro variable. For example, with the code below we create 2 macro variables where each variable contains the average Horsepower per Make for the Sedan Type.
proc sql noprint; select mean(horsepower) into :avg_hp1 - :avg_hp2 from work.ds where type = "Sedan" group by make; quit; %put &=avg_hp1.; %put &=avg_hp2.;
You can find all the official documentation on the SAS website.