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

submit sql immediate;
validate &viewdef;
end submit;
if symget('SQLRC') gt 4 then
do;
... the view is not valid ...
end;
else do;
submit sql immediate;
create view &viewname as &viewdef;
end submit;
end;
The following example retrieves the data from the Countries table, but does not display
the table because the NOPRINT option is specified in the PROC SQL statement. The
%PUT macro language statement displays the three automatic macro variable values in
the SAS log. For more information about the %PUT statement and the SAS macro
facility, see SAS Macro Language: Reference.
libname sql 'SAS-library';
proc sql noprint;
select * from sql.countries;
%put SQLOBS=*&sqlobs* SQLOOPS=*&sqloops* SQLRC=*&sqlrc*;
Log 5.10 Using the PROC SQL Automatic Macro Variables
SQLOBS=*1* SQLOOPS=*11* SQLRC=*0*
Notice that the value of SQLOBS is 1. When the NOPRINT option is used and no table
or macro variables are created, SQLOBS returns a value of 1 because only one row is
processed.
Note: You can use the _AUTOMATIC_ option in the %PUT statement to list the values
of all automatic macro variables. The list depends on the SAS products that are
installed at your site.
Formatting PROC SQL Output By Using the
REPORT Procedure
SQL provides limited output formatting capabilities. Some SQL vendors add output
formatting statements to their products to address these limitations. SAS has reporting
tools that enhance the appearance of PROC SQL output.
For example, SQL cannot display only the first occurrence of a repeating value in a
column in its output. The following example lists cities in the USCityCoords table.
Notice the repeating values in the State column.
libname sql 'SAS-library';
proc sql outobs=10;
title 'US Cities';
select State, City, latitude, Longitude
Formatting PROC SQL Output By Using the REPORT Procedure 165
from sql.uscitycoords
order by state;
Output 5.6 USCityCoords Table Showing Repeating State Values
The following code uses PROC REPORT to format the output so that the state codes
appear only once for each state group. A WHERE clause subsets the data so that the
report lists the coordinates of cities in Pacific Rim states only. For more information
about PROC REPORT, see the Base SAS Procedures Guide.
libname sql 'SAS-library';
proc sql noprint;
create table sql.cityreport as
select *
from sql.uscitycoords
order by state;
proc report data=sql.cityreport
headline nowd
headskip;
title 'Coordinates of U.S. Cities in Pacific Rim States';
column state city ('Coordinates' latitude longitude);
define state / order format=$2. width=5 'State';
define city / order format=$15. width=15 'City';
define latitude / display format=4. width=8 'Latitude';
define longitude / display format=4. width=9 'Longitude';
where state='AK' or
state='HI' or
state='WA' or
state='OR' or
state='CA';
run;
166 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