4.4. Interfacing PROC SQL with the Macro Language
Many software vendors’ SQL implementations permit SQL to be interfaced with a host language. The SAS System’s SQL implementation is no different. The SAS macro language lets you customize the way SAS software behaves and in particular extend the capabilities of the SQL procedure. PROC SQL users can apply the macro facility’s many powerful features by interfacing the SQL procedure with the macro language to provide a wealth of programming opportunities.
From creating and using user-defined macro variables and automatic (SAS-supplied) variables, reducing redundant code, performing common and repetitive tasks, to building powerful and simple macro applications, the macro language has the tools PROC SQL users can take advantage of to improve efficiency. The best part is that you do not have to be a macro language heavyweight to begin reaping the rewards of this versatile interface between two powerful Base SAS software languages.
This section will introduce you to a number of techniques that, with a little modification, could be replicated and used in your own programming environment. You will learn how to use the SQL procedure with macro programming techniques, as well as explore how dictionary tables (see Chapter 2 for details) and the SAS macro facility can be combined with PROC SQL to develop useful utilities to inquire about the operating environment and other information. For more information about the SAS Macro Language, readers are referred to Carpenter’s Complete Guide to the SAS Macro Language by Art Carpenter; SAS Macro Programming Made Easy by Michele M. Burlew; and SAS Macro Language: Reference, Version 8.
4.4.1. Exploring Macro Variables and Values
Macro variables and their values provide PROC SQL users with a convenient way to store text strings in SAS code. Whether you create your own macro variables or use SAS-supplied automatic macro variables, macro variables can improve a program’s efficiency and usefulness. A number of techniques are presented in this section to illustrate the capabilities afforded users when interfacing PROC SQL with macro variables.
4.4.1.1. Creating a Macro Variable with %LET
The %LET macro statement creates a single macro variable and assigns or changes a text string value. It can be specified inside or outside a macro and used with PROC SQL. In the next example, a macro variable called PRODTYPE is created with a value of SOFTWARE assigned in a %LET statement. The PRODTYPE macro variable is referenced in the TITLE statement and enclosed in quotes in the PROC SQL WHERE clause. This approach of assigning macro variable values at the beginning of a program makes it easy and convenient for making changes, if necessary, because the values are all at the beginning of the program.
SQL Code
%LET PRODTYPE=SOFTWARE;
TITLE "Listing of &PRODTYPE Products";
PROC SQL;
SELECT PRODNAME,
PRODCOST
FROM PRODUCTS
WHERE UPCASE(PRODTYPE) = "&PRODTYPE"
ORDER BY PRODCOST;
QUIT;Results
Listing of SOFTWARE Products
Product
Product Name Cost
____________________________________
Wordprocessor Software $299.00
Spreadsheet Software $299.00
Graphics Software $299.00
Database Software $399.00 |
In the next example, a macro named VIEW creates a macro variable called NAME and assigns a value to it with a %LET statement. When VIEW is executed, a value of PRODUCTS, MANUFACTURERS, or INVENTORY is substituted for the macro variable. The value supplied for the macro variable determines what view is referenced. If the value supplied to the macro variable is not one of these three values, then a program warning message is displayed in the SAS log. Invoking the macro with %VIEW(Products) produces the following results.
SQL Code
%MACRO VIEW(NAME);
%IF %UPCASE(&NAME) ^= %STR(PRODUCTS) AND
%UPCASE(&NAME) ^= %STR(MANUFACTURERS) AND
%UPCASE(&NAME) ^= %STR(INVENTORY) %THEN %DO;
%PUT A valid view name was not supplied and no output
will be generated!;
%END;
%ELSE %DO;
PROC SQL;
TITLE "Listing of &NAME View";
%IF %UPCASE(&NAME)=%STR(PRODUCTS) %THEN %DO;
SELECT PRODNAME,
PRODCOST
FROM &NAME._view
ORDER BY PRODCOST;
%END;
%ELSE %IF %UPCASE(&NAME)=%STR(MANUFACTURERS) %THEN %DO;
SELECT MANUNAME,
MANUCITY,
MANUSTAT
FROM &NAME._view
ORDER BY MANUCITY;
%END;
%ELSE %IF %UPCASE(&NAME)=%STR(INVENTORY) %THEN %DO;
SELECT PRODNUM,
INVENQTY,
INVENCST
FROM &NAME._view
ORDER BY INVENCST;
%END;
QUIT;
%END;
%MEND VIEW;Results
Listing of Products View
Product
Product Name Cost
___________________________________
Analog Cell Phone $35.00
Office Phone $130.00
Digital Cell Phone $175.00
Spreadsheet Software $299.00
Graphics Software $299.00
Wordprocessor Software $299.00
Database Software $399.00
Dream Machine $3,200.00
Business Machine $3,300.00
Travel Laptop $3,400.00 |
In the previous example, if a name is supplied to the macro variable &NAME that is not valid, the user-defined program warning message appears in the SAS log. Suppose we invoked the VIEW macro by entering %VIEW(Customers).
SQL Code
%VIEW(Customers);
SAS Log Results
%VIEW(Customers); A valid view name was not supplied and no output |
will be generated!4.4.1.2. Creating a Macro Variable from a Table Row Column
A macro variable can be created from a column value in the first row of a table in PROC SQL by specifying the INTO clause. The macro variable is assigned using the value of the column specified in the SELECT list from the first row selected. A colon (:) is used in conjunction with the macro variable name being defined. In the next example, output results are suppressed with the NOPRINT option, while two macro variables are created using the INTO clause and their values displayed in the SAS log.
SQL Code
PROC SQL NOPRINT;
SELECT PRODNAME,
PRODCOST
INTO :PRODNAME,
:PRODCOST
FROM PRODUCTS;
QUIT;
%PUT &PRODNAME &PRODCOST;SAS Log Results
PROC SQL NOPRINT;
SELECT PRODNAME,
PRODCOST
INTO :PRODNAME,
:PRODCOST
FROM PRODUCTS;
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.38 seconds
%PUT &PRODNAME &PRODCOST;
Dream Machine $3,200.00 |
In the next example, two macro variables are created using the INTO clause and a WHERE clause to control what row is used in the assignment of macro variable values. Using the WHERE clause enables a row other than the first row to always be used in the assignment of macro variables. Their values are displayed in the SAS log.
SQL Code
PROC SQL NOPRINT;
SELECT PRODNAME,
PRODCOST
INTO :PRODNAME,
:PRODCOST
FROM PRODUCTS
WHERE UPCASE(PRODTYPE) IN ('SOFTWARE'),
QUIT;
%PUT &PRODNAME &PRODCOST;SAS Log Results
PROC SQL NOPRINT;
SELECT PRODNAME,
PRODCOST
INTO :PRODNAME,
:PRODCOST
FROM PRODUCTS
WHERE UPCASE(PRODTYPE) IN ('SOFTWARE'),
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.04 seconds
%PUT &PRODNAME &PRODCOST;
Spreadsheet Software $299.00 |
4.4.1.3. Creating a Macro Variable with Aggregate Functions
Turning data into information and then saving the results as macro variables is easy with aggregate (summary) functions. The SQL procedure provides a number of useful aggregrate functions to help perform calculations, descriptive statistics, and other aggregating computations in a SELECT statement or HAVING clause. These functions are designed to summarize information and not display detail about data. In the next example, the MIN summary function is used to determine the least expensive product from the PRODUCTS table with the value stored in the macro variable MIN_PRODCOST using the INTO clause. The results are displayed in the SAS log.
SQL Code
PROC SQL NOPRINT; SELECT MIN(PRODCOST) FORMAT=DOLLAR10.2 INTO :MIN_PRODCOST FROM PRODUCTS; QUIT; %PUT &MIN_PRODCOST;
SAS Log Results
PROC SQL NOPRINT;
SELECT MIN(PRODCOST) FORMAT=DOLLAR10.2
INTO :MIN_PRODCOST
FROM SQL.PRODUCTS;
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.05 seconds
%PUT &MIN_PRODCOST;
$35.00 |
4.4.1.4. Creating Multiple Macro Variables
PROC SQL lets you create a macro variable for each row returned by a SELECT statement. Using the PROC SQL keyword THROUGH or hyphen (-) with the INTO clause, you can easily create a range of two or more macro variables. This is a handy feature for creating macro variables from multiple rows in a table. For example, suppose we wanted to create macro variables for the three least expensive products in the PRODUCTS table. The INTO clause creates three macro variables and assigns values from the first three rows of the PRODNAME and PRODCOST columns. The ORDER BY clause is also specified to perform an ascending sort on product cost (PRODCOST) to assure that the data is in the desired order from least to most expensive. The results are displayed on the SAS log.
PROC SQL NOPRINT; SELECT PRODNAME, PRODCOST INTO :PRODUCT1 – :PRODUCT3, :COST1 – :COST3 FROM PRODUCTS ORDER BY PRODCOST; QUIT; %PUT &PRODUCT1 &COST1; %PUT &PRODUCT2 &COST2; %PUT &PRODUCT3 &COST3;
SAS Log Results
PROC SQL NOPRINT;
SELECT PRODNAME,
PRODCOST
INTO :PRODUCT1 - :PRODUCT3,
:COST1 - :COST3
FROM PRODUCTS
ORDER BY PRODCOST;
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.26 seconds
%PUT &PRODUCT1 &COST1;
Analog Cell Phone $35.00
%PUT &PRODUCT2 &COST2;
Office Phone $130.00
%PUT &PRODUCT3 &COST3;
Digital Cell Phone $175.00 |
4.4.1.5. Creating a List of Values in a Macro Variable
Concatenating values of a single column into one macro variable lets you create a list of values that can be displayed in the SAS log or output to a SAS data set. Use the INTO clause with the SEPARATED BY option to create a list of values. For example, suppose we wanted to create a blank-delimited list containing manufacturer names (MANUNAME) from the MANUFACTURERS table. We create a macro variable called &MANUNAME and assign the manufacturer names to a blank-delimited list with each name separated with two blank spaces. The WHERE clause restricts the list’s contents to only those manufacturers located in San Diego.
SQL Code
PROC SQL NOPRINT; SELECT MANUNAME INTO :MANUNAME SEPARATED BY ' ' FROM MANUFACTURERS WHERE UPCASE(MANUCITY)='SAN DIEGO'; QUIT; %PUT &MANUNAME;
SAS Log Results
PROC SQL NOPRINT;
SELECT MANUNAME
INTO :MANUNAME SEPARATED BY ' '
FROM MANUFACTURERS
WHERE UPCASE(MANUCITY)='SAN DIEGO';
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.00 seconds
%PUT &MANUNAME;
Global Comm Corp KPL Enterprises San Diego PC PlanetIn the next example, a similar list containing manufacturers from San Diego is created. But instead of each name being separated with two blanks as in the previous example, the names are separated by commas.
SQL Code
PROC SQL NOPRINT; SELECT MANUNAME INTO :MANUNAME SEPARATED BY ', ' FROM MANUFACTURERS WHERE UPCASE(MANUCITY)='SAN DIEGO'; QUIT; %PUT &MANUNAME;
PROC SQL NOPRINT;
SELECT MANUNAME
INTO :MANUNAME SEPARATED BY ', '
FROM MANUFACTURERS
WHERE UPCASE(MANUCITY)='SAN DIEGO';
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.00 seconds
%PUT &MANUNAME;
Global Comm Corp, KPL Enterprises, San Diego PC
|
4.4.1.6. Using Automatic Macro Variables to Control Processing
Three automatic macro variables supplied by SAS are assigned values during SQL processing for the purpose of providing process control information. SQL users can determine the number of rows processed with the SQLOBS macro variable, assess whether a PROC SQL statement was successful or not with the SQLRC macro variable, or identify the number of iterations the inner loop of an SQL query processes with the SQLOOPS macro variable. To inspect the values of all automatic macro variables at your installation, use the _AUTOMATIC_ option in a %PUT statement.
SQL Code
%PUT _AUTOMATIC_;
SAS Log Results
%PUT _AUTOMATIC_; AUTOMATIC AFDSID 0 AUTOMATIC AFDSNAME AUTOMATIC AFLIB AUTOMATIC AFSTR1 AUTOMATIC AFSTR2 AUTOMATIC FSPBDV AUTOMATIC SYSBUFFR AUTOMATIC SYSCC 0 AUTOMATIC SYSCHARWIDTH 1 AUTOMATIC SYSCMD AUTOMATIC SYSDATE 10JUN04 AUTOMATIC SYSDATE9 10JUN2004 AUTOMATIC SYSDAY Thursday AUTOMATIC SYSDEVIC AUTOMATIC SYSDMG 0 AUTOMATIC SYSDSN WORK INVENTORY AUTOMATIC SYSENDIAN LITTLE AUTOMATIC SYSENV FORE AUTOMATIC SYSERR 0 AUTOMATIC SYSFILRC 0 AUTOMATIC SYSINDEX 3 AUTOMATIC SYSINFO 0 AUTOMATIC SYSJOBID 3580 AUTOMATIC SYSLAST WORK.INVENTORY AUTOMATIC SYSLCKRC 0 AUTOMATIC SYSLIBRC 0 AUTOMATIC SYSMACRONAME AUTOMATIC SYSMAXLONG 2147483647 AUTOMATIC SYSMENV S AUTOMATIC SYSMSG AUTOMATIC SYSNCPU 1 AUTOMATIC SYSPARM AUTOMATIC SYSPBUFF AUTOMATIC SYSPROCESSID |
4.4.2. Building Macro Tools and Applications
The macro facility, combined with the capabilities of the SQL procedure, enables the creation of versatile macro tools and general-purpose applications. A principal design goal when writing macros is that they are useful and simple to use. A macro that has little applicability to user needs or has complicated and hard to remember macro variable names is best avoided.
As tools, macros should be designed to serve the needs of as many users as possible. They should contain no ambiguities, consist of distinctive macro variable names, avoid the possibility of naming conflicts between macro variables and data set variables, and not try to do too many things. This utilitarian approach to macro design helps gain widespread approval and acceptance by users.
4.4.2.1. Creating Simple Macro Tools
Macro tools can be constructed to perform a variety of useful tasks. The most effective ones are usually those that are simple and perform a common task. Before constructing one or more macro tools, explore what processes are currently being performed, then identify common users’ needs with affected personnel by addressing gaps in the process. Once this has been accomplished, you will be in a better position to construct simple and useful macro tools that will be accepted by user personnel.
Suppose during an informal requirements analysis phase that you identified users who, in the course of their jobs, use a variety of approaches and methods to create data set and variable cross-reference listings. To prevent unnecessary and wasteful duplication of effort, you decide to construct a simple macro tool that can be used by all users to retrieve information about the columns in one or more SAS data sets.
4.4.2.2. Cross-Referencing Columns
Column cross-reference listings come in handy when you need to quickly identify all the SAS library data sets a column is defined in. Using the COLUMNS dictionary table (see Chapter 2, “Working with Data in PROC SQL,” for more details) a macro can be created that captures column-level information including column name, type, length, position, label, format, informat, indexes, as well as a cross-reference listing containing the location of a column within a designated SAS library. In the next example, macro COLUMNS consists of a PROC SQL query that accesses any single column in a SAS library. If the macro was invoked with a user-request consisting of %COLUMNS(WORK,CUSTNUM), the macro would produce a cross-reference listing on the user library WORK for the column CUSTNUM in all DATA types.
SQL Code
%MACRO COLUMNS(LIB, COLNAME);
PROC SQL;
SELECT LIBNAME, MEMNAME, NAME, TYPE, LENGTH
FROM DICTIONARY.COLUMNS
WHERE LIBNAME='&LIB' AND
UPCASE(NAME)='&COLNAME' AND
MEMTYPE='DATA';
QUIT;
%MEND COLUMNS;
%COLUMNS(WORK,CUSTNUM);It is worth noting that multiple matches could be found in databases containing case-sensitive names. This would allow both “employee” and “EMPLOYEE” to be displayed as matches. This is not very likely to occur too often in practice but is definitely a possibility.
Results
Library Column |
4.4.2.3. Determining the Number of Rows in a Table
Sometimes it is useful to know the number of observations (or rows) in a table without first having to read all the rows. Although the number of rows in a table is available for true SAS System tables, they are not for DBMS tables using a library engine. In the next example, the TABLES dictionary table is accessed (refer to Chapter 2 for more details) in a user-defined macro called NOBS. Macro NOBS is designed to accept and process two user-supplied values: the library reference and the table name. Once these values are supplied, the results are displayed in the Output window.
SQL Code
%MACRO NOBS(LIB, TABLE);
PROC SQL;
SELECT LIBNAME, MEMNAME, NOBS
FROM DICTIONARY.TABLES
WHERE UPCASE(LIBNAME)="&LIB" AND
UPCASE(MEMNAME)="&TABLE" AND
UPCASE(MEMTYPE)="DATA";
QUIT;
%MEND NOBS;
%NOBS(WORK,PRODUCTS);Results
The SAS System Library |
4.4.2.4. Identifying Duplicate Rows in a Table
Sometimes it is handy to be able to identify duplicate rows in a table. In the next example, the SELECT statement with a COUNT summary function and HAVING clause are used in a user-defined macro called DUPS. Macro DUPS is designed to accept and process three user-supplied values: the library reference, table name, and column(s) in a GROUP BY list. Once these values are supplied by submitting macro DUPS, the macro is executed with the results displayed in the Output window.
SQL Code
%MACRO DUPS(LIB, TABLE, GROUPBY);
PROC SQL;
SELECT &GROUPBY, COUNT(*) AS Duplicate_Rows
FROM &LIB..&TABLE
GROUP BY &GROUPBY
HAVING COUNT(*) > 1;
QUIT;
%MEND DUPS;
%DUPS(WORK,PRODUCTS,PRODTYPE);Results
The SAS System
Duplicate_
Product Type Rows
_____________________________
Phone 3
Software 4
Workstation 2 |