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

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