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 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 …>
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;
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.;
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.;
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.;
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.;
create a list of macro variables with the select into clause" width="" height="" />
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.