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;