Create Macro Variables with SELECT INTO

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.