Does a macro variable created in PROC SQL become local or global

Question

When I write:

proc sql;
    select count(*) into :out from sashelp.cars;
quit;

Does the macro variable out become a global or local variable?

Answer 1

It depends. Let's put together a test macro and see what happens

%macro test();
proc sql noprint;
select count(*) into :x from sashelp.cars;
quit;
%put IN MACRO: &x;
%mend;

options nosource nonotes;

%symdel x;

%test();

%put Out MACRO: &x;

%let x=2;

%put Out MACRO: &x;

%test();

%put Out MACRO: &x;

Creates:

IN MACRO:      428
WARNING: Apparent symbolic reference X not resolved.
Out MACRO: &x
Out MACRO: 2
IN MACRO:      428
Out MACRO:      428

So at the beginning, there was no X macro variable. The %test() macro populated a local variable. It was not available outside the macro.

After that, we set %let x=2 making X be a global variable. Then we execute the %test() macro and see that X keeps its value outside of the %test() macro.

So if it exists globally, it continues to exist and is overwritten. If it doesn't exist globally, it continues to not exist globally.

Answer 2

Let's take a look at the documentation to answer this compeltely.

The documentation for SQL INTO clause in the Macro language dictionary says:

Macro variables created with INTO follow the scoping rules for the %LET statement. For more information, see Scopes of Macro Variables.

So, we click on that link, and find this page - How Macro Variables are Assigned and Resolved and this page - Examples of Macro Variable Scopes.

The first page has a nice tree diagram showing the decisions SAS makes when assigning or creating a macro variable, and that might be sufficient to understand this.

The second page has some good examples and explanations:

When the macro processor executes a macro program statement that can create a macro variable (such as a %LET statement), the macro processor attempts to change the value of an existing macro variable rather than create a new macro variable. The %GLOBAL and %LOCAL statements are exceptions.

There are some very good examples on that page; what it boils down to, though, is simple. If there is already an existing macro variable with that name, it will assign the value to that macro variable, wherever that variable is - in the scope tree of the current macro, anyway. (So, a single macro executing has two symbol tables to look in: local, and global. A macro called by another macro has three: local, local to the calling macro, and global. Etc.)

However, there are a pair of related exceptions: when you specify the table to use, with %local or %global. Either of those statements referencing a macro variable before your %let or select into will cause any following statements to set that particular version - local or global - and then you're safe for sure.