Help with SAS Macros

[xliu1]

I set up a simple macro definition, but keep getting warning in the log. Attached are macro variables and corresponding values, SAS warning messages, and complete SAS program. I hope someone can help me solve the issue. Thanks.

%macro cagoal;
proc sql noprint;
  select distinct trim(college) as college,
                  Global_College
		into :college1-,
		     :Global_College1-
  from caa.usf_college_sort;
  quit;

       %do i=1 %to &sqlobs;

	   %let college_id=%superq(college&i);
proc export data=caa.usf_college_sort(where=(college=%bquote("&college_id"))
                                      keep=college global_college cohort_yr usf_pell_6yr_actual
                                           Pell_6YR_Actual Pell_6YR_Forecast l95 u95)
            outfile="&path\&&Global_College&i.._Goals.xlsx"
			dbms=xlsx
			replace;
run;
    %end;
%mend cagoal;
%cagoal

[ballardw]

The warning is coming because you have a value of your macro variable College_id of "A&S" in this line:

proc export data=caa.usf_college_sort(where=(college=%bquote("&college_id"))

I am not sure that the picture of the log that you show actually represents a run from the code shown. There is a bit of text in the picture that reads: " MPRINT (CAGOAL) :   WXLX;  " just before the Run statement for the Proc Export. There is nothing that you show in the CODE that would generate any such. 

So perhaps you have a timing issue of the shown log and the code executed where this picture is from a previous run OR the macro wasn't recompiled after a change.

I might suggest closing SAS and restarting and then running the code, possibly  with a reduced input data set to just include the A&S. If you still get the warning then show us the entire log, pasted into a text box just like the code was, including where the macro is compiled. The suggestion of a reduced input data set is to reduce the amount of log needed. 

[Kathryn_SAS · Accepted solution]

WXLX is a statement that PROC EXPORT generates when you are writing data to Excel using DBMS=XLSX. This is expected.

You can prevent the macro warning by resolving the macro variable in single quotes using the %TSLIT function as follows:

proc export data=caa.usf_college_sort(where=(college=%tslit(&college_id))
                                      keep=college global_college cohort_yr usf_pell_6yr_actual
                                           Pell_6YR_Actual Pell_6YR_Forecast l95 u95)
            outfile="&path\&&Global_College&i.._Goals.xlsx"
			dbms=xlsx
			replace;
run;

[xliu1]

Thanks. This removes warning message. I really appreciate it!

[Tom]

This issue is that you generated this code:

college="A&S"

But there was no S macro variable found.

To avoid the macro processor examining the values embed the strings in single quotes, not double quotes.  You can include the quotes into the value of the macro variables you generate by using the QUOTE() function in your SQL query.

%macro cagoal;
%local i;
proc sql noprint;
  select distinct 
         quote(trim(college),"'")
       , quote(cats("&path\",Global_College,"_Goals.xlsx"),"'")
  into :college1-
     , :Global_College1-
  from caa.usf_college_sort
;
quit;

%do i=1 %to &sqlobs;
proc export dbms=xlsx replace
  data=caa.usf_college_sort
  (
    where=(college=&&college&i)
    keep=college global_college cohort_yr usf_pell_6yr_actual
         Pell_6YR_Actual Pell_6YR_Forecast l95 u95
  )
  outfile=&&Global_College&i 
;
run;
%end;
%mend cagoal;

[xliu1]

Thank you.