The Select Into Clause in Proc SQL is a great tool to create macro variables in SAS. In this post, I will demonstrate a few simple examples of how to use it. Furthermore, I will show how to read multiple values into a single macro variable. And how to create multiple macro variables. Even without knowing how many in advance. In the examples below, I will use the sashelp.class data set.
Use the Select Into Clause: A Simple Example in SAS
First, let us revise how to use the Select Into Clause in Proc SQL. In the code snippet below, we create a single macro variable n. A simple SQL query with the into :n syntax added to the Select Clause. N determines the name of the macro variable. Notice that this create a single macro variable with a single value in it. Because only one observation in the input data satisfies the Where Condition.
proc sql noprint; select name into :n from sashelp.class where name = 'Alfred'; quit; %put &=n.; |
Next, let us create two macro variables from two different variables. We can select multiple columns in the Select Clause by separating with a comma. Likewise, we can use mulitple Into Variable and separate them by a comma. The example below creates two macro variables with a single value in each. Notice that we use the separated by ‘ ‘ option on h. We do so to left-justify the value in the macro variable. Try removing it at see what the log prints.
proc sql noprint; select name, height into :n , :h separated by ' ' from sashelp.class where name = 'Alfred'; quit; %put &=n. &=h.; |
Select Multiple Values Into a Single SAS Macro Variables
Now, let us consider a single macro variable with multiple values in it. In the example below, we create a list of names in the macro variable n. We do so with the Separated by Clause. This results in 19 comma separated names in the macro variable n. Run the code and verify in the log.
proc sql noprint; select name into : n separated by ', ' from sashelp.class; quit; %put &n.; |
Select Into Multiple Macro Variables in Proc SQL
Finally, let us create multiple macro variables in a single Select Into Clause. In the code below, I use the Select Into syntax just like above. However, instead of a single macro variable, I specify n1 – . Notice that we have a dash and nothing else. This tells Proc SQL to create as many macro variables as the SQL query processes. In this case, the query processes 19 observations (the entire data set) since we do not have any where clause.
The result is 19 macro variables n1, n2, n3 … n19. We can use the automatic variable &sqlobs to keep track of the number of macro variables that we create. For example if we want to loop over these somehow. I do so in the small macro %putem. This simply puts all the macro variables in the log.
proc sql noprint; select name into : n1 - from sashelp.class; %let t = &sqlobs.; quit; %put &=n1.; %put &=n19.; %put &=t.; %macro putem; %do i = 1 %to &t.; %put &&n&i; %end; %mend; %putem; |
Summary
In this post, we investigate the Select Into Clause in Proc SQL. This is a highly flexible way to create macro variables in SAS. We can easily create multiple macro variables. Or create a single variable with multiple values in it. If you want to learn more, I recommend reading Carpenter’s Complete Guide to the SAS Macro Language by Art Carpenter.
As a related post, read Investigating the Macro Array in SAS.
You can download the entire code from this post here.