SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

proc sql;

create table one(pw=red, col1 num, col2 num, col3 num);

quit;

proc sql;

insert into one(pw=red, col1, col3)

values(1, 3);

quit;

proc sql;

update one(pw=red)

set col2 = 22

where col2 = . ;

quit;

You cannot use SAS data set options with DICTIONARY tables because DICTIONARY

tables are read-only objects.

The only SAS data set options that you can use with PROC SQL views are data set

options that assign and provide SAS passwords: READ=, WRITE=, ALTER=, and PW=.

For more information about SAS data set options, see SAS Data Set Options: Reference.

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” on page

162.

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.

Using PROC SQL with the SAS Macro Facility 157

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;

Log 5.5 Creating Macro Variables from the First Row of a Query Result

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';

158 Chapter 5 • Programming with the SQL Procedure