Using PROC SQL with the SAS Macro Facility

Overview of Using PROC SQL with the SAS Macro Facility

The macro facility is a programming tool that you can use to extend and customize SAS software. The macro facility reduces the amount of text that you must enter to perform common or repeated tasks and improves the efficiency and usefulness of your SQL programs.

The macro facility enables you to assign a name to character strings or groups of SAS programming statements. Thereafter, you can work with the names rather than with the text itself. For more information about the SAS macro facility, see SAS Macro Language: Reference .

Macro variables provide an efficient way to replace text strings in SAS code. The macro variables that you create and name are called user-defined macro variables. The macros variables that are defined by SAS are called automatic macro variables. PROC SQL produces six automatic macro variables (SQLOBS, SQLRC, SQLOOPS, SQLEXITCODE, SQLXRC, and SQLXMSG) to help you troubleshoot your programs. For more information, see Using the PROC SQL Automatic Macro Variables .

Creating Macro Variables in PROC SQL

Overview of Creating Macro Variables in PROC SQL

Other software vendors' SQL products allow the embedding of SQL into another language. References to variables (columns) of that language are termed host-variable references. They are differentiated from references to columns in tables by names that are prefixed with a colon. The host-variable stores the values of the object-items that are listed in the SELECT clause.

The only host language that is currently available in SAS is the macro language, which is part of Base SAS software. When a calculation is performed on a column's value, its result can be stored, using :macro-variable, in the macro facility. The result can then be referenced by that name in another PROC SQL query or SAS procedure. Host-variable can be used only in the outer query of a SELECT statement, not in a subquery. Host-variable cannot be used in a CREATE statement.

If the query produces more than one row of output, then the macro variable will contain only the value from the first row. If the query has no rows in its output, then the macro variable is not modified. If the macro variable does not exist yet, it will not be created. The PROC SQL macro variable SQLOBS contains the number of rows that are produced by the query.

Note: The SQLOBS automatic macro variable is assigned a value after the SQL SELECT statement executes.

Creating Macro Variables from the First Row of a Query Result

If you specify a single macro variable in the INTO clause, then PROC SQL assigns the variable the value from the first row only of the appropriate column in the SELECT list. In this example, &country1 is assigned the value from the first row of the Country column, and &barrels1 is assigned the value from the first row of the Barrels column. The NOPRINT option prevents PROC SQL from displaying the results of the query. The %PUT statement writes the contents of the macro variables to the SAS log.

libname sql 'SAS-library'; proc sql noprint; select country, barrels into :country1, :barrels1 from sql.oilrsrvs; %put &country1 &barrels1;

4 proc sql noprint; 5 select country, barrels 6 into :country1, :barrels1 7 from sql.oilrsrvs; 8 9 %put &country1 &barrels1; Algeria 9,200,000,000 NOTE: PROCEDURE SQL used: real time 0.12 seconds

Creating a Macro Variable from the Result of an Aggregate Function

A useful feature of macro variables is that they enable you to display data values in SAS titles. The following example prints a subset of the WORLDTEMPS table and lists the highest temperature in Canada in the title:

libname sql 'SAS-library'; proc sql outobs=12; reset noprint; select max(AvgHigh) into :maxtemp from sql.worldtemps where country = 'Canada'; reset print; title "The Highest Temperature in Canada: &maxtemp"; select city, AvgHigh format 4.1 from sql.worldtemps where country = 'Canada';

Note: You must use double quotation marks in the TITLE statement to resolve the reference to the macro variable.

Creating Multiple Macro Variables

You can create one new macro variable per row from the result of a SELECT statement. Use the keywords THROUGH, THRU, or a hyphen (-) in an INTO clause to create a range of macro variables.

Note: When you specify a range of macro variables, the SAS macro facility creates only the number of macro variables that are needed. For example, if you specify :var1-:var9999 and only 55 variables are needed, only :var1-:var55 is created. The SQLOBS automatic variable is useful if a subsequent part of your program needs to know how many variables were actually created. In this example, SQLOBS would have a value of 55.

This example assigns values to macro variables from the first four rows of the Name column and the first three rows of the Population column. The %PUT statements write the results to the SAS log.

libname sql 'SAS-library'; proc sql noprint; select name, Population into :country1 - :country4, :pop1 - :pop3 from sql.countries; %put &country1 &pop1; %put &country2 &pop2; %put &country3 &pop3; %put &country4;

4 proc sql noprint; 5 select name, Population 6 into :country1 - :country4, :pop1 - :pop3 7 from sql.countries; 8 9 %put &country1 &pop1; Afghanistan 17070323 10 %put &country2 &pop2; Albania 3407400 11 %put &country3 &pop3; Algeria 28171132 12 %put &country4; Andorra

Concatenating Values in Macro Variables

You can concatenate the values of one column into one macro variable. This form is useful for building a list of variables or constants. Use the SEPARATED BY keywords to specify a character to delimit the values in the macro variable.

This example assigns the first five values from the Name column of the COUNTRIES table to the &countries macro variable. The INOBS option limits PROC SQL to using the first five rows of the COUNTRIES table. A comma and a space are used to delimit the values in the macro variable.

libname sql 'SAS-library'; proc sql noprint inobs=5; select Name into :countries separated by ', ' from sql.countries; %put &countries;

4 proc sql noprint inobs=5; 5 select Name 6 into :countries separated by ', ' 7 from sql.countries; WARNING: Only 5 records were read from SQL.COUNTRIES due to INOBS= option. 8 9 %put &countries; Afghanistan, Albania, Algeria, Andorra, Angola

The leading and trailing blanks are trimmed from the values before the macro variables are created. If you do not want the blanks to be trimmed, then add NOTRIM to the INTO clause. Here is the previous example with NOTRIM added:

libname sql 'SAS-library'; proc sql noprint inobs=5; select Name into :countries separated by ',' NOTRIM from sql.countries; %put &countries;

1 proc sql noprint inobs=5; 2 select Name 3 into :countries separated by ',' NOTRIM 4 from sql.countries; WARNING: Only 5 records were read from SQL.COUNTRIES due to INOBS= option. 5 6 %put &countries; Afghanistan ,Albania ,Algeria ,Andorra ,Angola

Defining Macros to Create Tables

Macros are useful as interfaces for table creation. You can use the SAS macro facility to help you create new tables and add rows to existing tables.

The following example creates a table that lists people to serve as referees for reviews of academic papers. No more than three people per subject are allowed in a table. The macro that is defined in this example checks the number of referees before it inserts a new referee's name into the table. The macro has two parameters: the referee's name and the subject matter of the academic paper.

libname sql 'SAS-library'; proc sql; create table sql.referee (Name char(15), Subject char(15)); /* define the macro */ %macro addref(name,subject); %local count; /* are there three referees in the table? */ reset noprint; select count(*) into :count from sql.referee where subject="&subject"; %if &count ge 3 %then %do; reset print; title "ERROR: &name not inserted for subject – &subject.."; title2 " There are 3 referees already."; select * from sql.referee where subject="&subject"; reset noprint; %end; %else %do; insert into sql.referee(name,subject) values("&name","&subject"); %put NOTE: &name has been added for subject – &subject..; %end; %mend;

Submit the %ADDREF() macro with its two parameters to add referee names to the table. Each time you submit the macro, a message is written to the SAS log.

%addref(Conner,sailing); %addref(Fay,sailing); %addref(Einstein,relativity); %addref(Smythe,sailing); %addref(Naish,sailing);

34 %addref(Conner,sailing); NOTE: 1 row was inserted into SQL.REFEREE. NOTE: Conner has been added for subject - sailing. 35 %addref(Fay,sailing); NOTE: 1 row was inserted into SQL.REFEREE. NOTE: Fay has been added for subject - sailing. 36 %addref(Einstein,relativity); NOTE: 1 row was inserted into SQL.REFEREE. NOTE: Einstein has been added for subject - relativity. 37 %addref(Smythe,sailing); NOTE: 1 row was inserted into SQL.REFEREE. NOTE: Smythe has been added for subject - sailing. 38 %addref(Naish,sailing);

The output has a row added with each execution of the %ADDREF() macro. When the table contains three referee names, it is displayed in SAS output with the message that it can accept no more referees.

Using the PROC SQL Automatic Macro Variables

PROC SQL sets up macro variables with certain values after it executes each statement. These macro variables can be tested inside a macro to determine whether to continue executing the PROC SQL step.

After each PROC SQL statement has executed, the following macro variables are updated with these values:

contains the highest return code that occurred from some types of SQL insert failures. This return code is written to the SYSERR macro variable when PROC SQL terminates.

contains the number of rows that were processed by an SQL procedure statement. For example, the SQLOBS macro variable contains the number of rows that were formatted and displayed in SAS output by a SELECT statement or the number of rows that were deleted by a DELETE statement.

When the NOPRINT option is specified, the value of the SQLOBS macro variable depends on whether an output table, single macro variable, macro variable list, or macro variable range is created:

If no output table, macro variable list, or macro variable range is created, then SQLOBS contains the value 1.

If a macro variable list or macro variable range is created, then SQLOBS contains the number of rows that are processed to create the macro variable list or range.

Note: The SQLOBS automatic macro variable is assigned a value after the SQL SELECT statement executes.

contains the number of iterations that the inner loop of PROC SQL processes. The number of iterations increases proportionally with the complexity of the query. For more information, see Limiting Iterations with the LOOPS= Option and LOOPS= in the Base SAS Procedures Guide .