Creating Macro Variables during PROC SQL Step Execution
INTO Clause and the NOPRINT Option Syntax
You
can create or update macro variables during the execution of a PROC
SQL step. Remember that the SELECT statement in a PROC SQL step retrieves
and displays data. The INTO clause in a SELECT statement enables you
to create or update macro variables.
When you create or update macro variables
during execution of a PROC SQL step, you might not want any output
to be displayed. The PRINT and NOPRINT options specify whether a SELECT
statement's results are displayed in output. PRINT is the default
setting.
|
Syntax, PROC SQL with
the NOPRINT option and the INTO clause:
PROC SQL NOPRINT;
SELECT column1<,column2,...>
INTO :macro-variable-1<,:macro-variable-2,...> <TRIMMED>
FROM table-1
| view-1
<WHERE expression>
<other
clauses>;
QUIT;
column1, column2,...
specifies one or more
columns of the SQL table specified by table-1 |
view-1.
:macro-variable-1, :macro-variable-2,...
names the macro variables
to create.
expression
produces a value that
is used to subset the data.
other clauses
are other valid clauses
that group, subset, or order the data.
|
Note: Macro variable names are
preceded by a colon.
This form of the INTO
clause does not automatically trim leading or trailing blanks. Use
the TRIMMED modifier to explicitly remove leading and trailing blanks
before storing the results in your macro variable. Also, the INTO
clause cannot be used when you create a table or a view.
Example: Using the INTO Clause and the NOPRINT Option
You can create a macro
variable named TotalFee that contains the total of all course fees.
In addition, you can use the NOPRINT option to suppress the output
from the PROC SQL step.
proc sql noprint;
select sum(fee) format=dollar10.
into :totalfee trimmed
from certadv.all;
quit;
proc means data=certadv.all sum maxdec=0;
class course_title;
var fee;
title "Grand Total for All Courses Is &totalfee";
run;
The title in the output
from the PROC MEANS step shows the sum of all course fees in the DOLLAR10.
format.
Output 8.3 PROC MEANS Output of Certadv.All
Example: Creating Variables with the INTO Clause
Suppose you want to
create a series of macro variables that contain the course code, location,
and starting date of all courses that are scheduled in 2019. You do
not know the number of courses. If you name a numbered macro variable
to begin the series followed by a hyphen, SQL determines the number
of macro variables that are required based on the number of rows in
the query results set. A macro variable named SQLOBS is automatically
created to store the number of rows that were read.
proc sql noprint;
select course_code, location, begin_date format=mmddyy10.
into :crsid1- ,
:place1- ,
:date1-
from certadv.schedule
where year(begin_date)=2019
order by begin_date;
quit;
%put There are &sqlobs courses in 2019;
%put _user_;
The SAS log shows that
SQLOBS is assigned a value of
12. The
%PUT statement at the end of the program shows the names and values
of all the macro variables that are created in the SELECT statement. Log 8.14 SAS Log
There are 12 courses in 2019 GLOBAL CRSID1 C003 GLOBAL CRSID10 C006 GLOBAL CRSID11 C001 GLOBAL CRSID12 C002 GLOBAL CRSID2 C004 GLOBAL CRSID3 C005 GLOBAL CRSID4 C006 GLOBAL CRSID5 C001 GLOBAL CRSID6 C002 GLOBAL CRSID7 C003 GLOBAL CRSID8 C004 GLOBAL CRSID9 C005 GLOBAL DATE1 01/08/2019 GLOBAL DATE10 10/01/2019 GLOBAL DATE11 11/12/2019 GLOBAL DATE12 12/03/2019 GLOBAL DATE2 01/22/2019 GLOBAL DATE3 02/26/2019 GLOBAL DATE4 04/02/2019 GLOBAL DATE5 05/21/2019 GLOBAL DATE6 06/11/2019 GLOBAL DATE7 07/16/2019 GLOBAL DATE8 08/13/2019 GLOBAL DATE9 09/17/2019 GLOBAL NUMROWS 12 GLOBAL PLACE1 Boston GLOBAL PLACE10 Seattle GLOBAL PLACE11 Boston GLOBAL PLACE12 Seattle GLOBAL PLACE2 Seattle GLOBAL PLACE3 Dallas GLOBAL PLACE4 Boston GLOBAL PLACE5 Dallas GLOBAL PLACE6 Boston GLOBAL PLACE7 Seattle GLOBAL PLACE8 Dallas GLOBAL PLACE9 Boston GLOBAL SQLEXITCODE 0 GLOBAL SQLOBS 12 GLOBAL SQLOOPS 28 GLOBAL SQLRC 0 GLOBAL SQLXOBS 0 GLOBAL SQLXOPENERRS 0 GLOBAL SYS_SQL_IP_ALL -1 GLOBAL SYS_SQL_IP_STMT
There is a NOPRINT option
in the code. Therefore, a report is not generated.
Example: Creating a Delimited List of Values
You can use the SQL
procedure to create one macro variable named Sites that contains the
names of all training centers that appear in the Certadv.Schedule
data set. The names will be separated by blanks.
proc sql noprint;
select distinct location
into :sites separated by ' '
from certadv.schedule;
quit;
title1 'Total Revenue from Course Sites:';
title2 &sites;
proc means data=certadv.all sum maxdec=0;
var fee;
run;
Output 8.4 PROC MEANS Output of Certadv.All
Last updated: October 16, 2019
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.