When
you submit a PROC SQL step, the PROC SQL program code is placed into
the input stack, and word scanning is performed for macro triggers
in the same process as in other SAS programs.
In the following code,
the macro variable reference
&crsid
is
resolved during the creation of the PROC SQL view, resulting in a
constant value whenever the view is used. For example, if the value
of
crsid
is
C003
when
this code is submitted, the view Subcrsid is based on the course code
C003.
proc sql;
create view subcrsid as
select student_name, student_company,paid
from sasuser.all
where course_code="&crsid";
quit;
A better approach would
be to use the SYMGET function to enable the view to look up the macro
variable value. In the following example, the view Subcrsid is based
on the value of
crsid
when the view is used:
proc sql;
create view subcrsid as
select student_name,student_company,paid
from sasuser.all
where course_code=symget('crsid');
quit;
%let crsid=C003;
proc print data=subcrsid noobs;
title "Status of Students in Course Code &crsid";
run;
%let crsid=C004;
proc print data=subcrsid noobs;
title "Status of Students in Course Code &crsid";
run;
PROC SQL does not perform
automatic data conversion. You must use the INPUT function to convert
the macro variable value to numeric if it is compared to a numeric
variable.
The following code performs
a query that is based on the numeric equivalent of the current value
of the macro variable
crsnum
. The INPUT function
is necessary in this WHERE statement because the value of the data
set variable
Course_number
is numeric, but
crsnum
has
a character value because it is a macro variable.
proc sql;
create view subcnum as
select student_name, student_company, paid
from sasuser.all
where course_number=input(symget('crsnum'),2.);
quit;
%let crsnum=4;
proc print data=subcnum noobs;
title "Status of Students in Course Number &crsnum";
run;