O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required