General form, PROC SQL
with the NOPRINT option and the INTO clause:
PROC SQL NOPRINT;
SELECT column1<,column2,...>
INTO :macro-variable-1<,:macro-variable-2,...>
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.
|
totalfee
that contains the
total of all course fees, and use this macro variable in a later step.
You use the NOPRINT option to suppress the output from the PROC SQL
step. proc sql noprint; select sum(fee) format=dollar10. into :totalfee from sasuser.all; quit; %let totalfee=&totalfee; proc means data=sasuser.all sum maxdec=0; class course_title; var fee; title "Grand Total for All Courses Is &totalfee"; run;
totalfee
.
General form, SELECT
statement with the INTO clause for a range of macro variables:
PROC SQL NOPRINT;
SELECT column1
INTO :macro-variable-1 - :macro-variable-n
FROM table-1
| view-1
<WHERE expression>
<other
clauses>;
QUIT;
column1
specifies the column
of the SQL table specified by table-1 | view-1.
:macro-variable-1 - :macro-variable-n,...
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.
|
crsid1
-crsid3
are
assigned values of the data set variable Course_code
from
each of the first three rows of the PROC SQL result: proc sql; select course_code, location, begin_date format=mmddyy10. into :crsid1-:crsid3, :place1-:place3, :date1-:date3 from sasuser.schedule where year(begin_date)=2002 order by begin_date; quit;
proc sql noprint; select course_code, location, begin_date format=mmddyy10. into :crsid1-:crsid999, :place1-:place999, :date1-:date999 from sasuser.schedule where year(begin_date)=2002 order by begin_date; %let numrows=&sqlobs; %put There are &numrows courses in 2002; %put _user_; quit;
numrows
is
assigned a value of 4
. 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.
114 proc sql noprint; 115 select course_code, location, 116 begin_date format=mmddyy10. 117 into :crsid1-:crsid999, 118 :place1-:place999, 119 :date1-:date999 120 from sasuser.schedule 121 where year(begin_date)=2002 122 order by begin_date; 123 %let numrows=&sqlobs; 124 %put There are &numrows courses in 2002; There are 4 courses in 2002 125 %put _user_; GLOBAL SQLOBS 4 GLOBAL CRSID2 C004 GLOBAL SQLOOPS 20 GLOBAL CRSID3 C005 GLOBAL DATE4 03/25/2002 GLOBAL PLACE1 Dallas GLOBAL CRSID1 C003 GLOBAL PLACE2 Boston GLOBAL PLACE3 Seattle GLOBAL SYS_SQL_IP_ALL -1 GLOBAL SYS_SQL_IP_STMT GLOBAL CRSNUM 3 GLOBAL DATE 01/08/2001 GLOBAL DATE1 01/07/2002 GLOBAL CRSID4 C006 GLOBAL DATE2 01/21/2002 GLOBAL DATE3 02/25/2002 GLOBAL NUMPAID 14 GLOBAL SQLXOBS 0 GLOBAL SQLRC 0 GLOBAL NUMROWS 4 GLOBAL NUMSTU 20 GLOBAL CRSNAME Local Area Networks GLOBAL DUE $3,900 GLOBAL SQLEXITCODE 0 GLOBAL PLACE4 Dallas 126 quit; |
General form, SELECT
statement with INTO clause for combining values into one macro variable:
PROC SQL NOPRINT;
SELECT column1
INTO :macro-variable-1
SEPARATED
BY 'delimiter1'
FROM table-1
| view-1
<WHERE expression>
<other
clauses>;
QUIT;
column1
specifies the column
of the SQL table specified by table-1 | view-1.
:macro-variable-1
names the macro variable
to create.
delimiter1
is enclosed in quotation
marks and specifies the character that is used as a delimiter in the
value of the macro variable.
expression
produces a value that
is used to subset the data.
other clauses
are other valid clauses
that group, subset, or order the data.
|
sites
that
contains the names of all training centers that appear in the Sasuser.Schedule
data set. The names are separated by blanks. proc sql noprint;
select distinct location into :sites separated by ' '
from sasuser.schedule;
quit;
sites
as it is stored
in the global symbol table after this PROC SQL step has run.
proc means data=sasuser.all sum maxdec=0;
var fee;
title1 'Total Revenue';
title2 "from Course Sites: &sites";
run;