ods excelxp filename with macro

[daisy6]

Hi SAS guys,

I run  into a problem. When I use ods excelxp to output my table to xml file, the file name can not be resovled.

Here is part of my code:

...........

ODS noresults;
ODS listing close; /*Turn off the standard line printer destination*/
ods tagsets.ExcelXP path="&dir./&&examdegree&i."
file="&&abexamd&i._&&degree&k._&mon..xml"
style=seaside /*Styles to control appearance of output*/;
ods tagsets.ExcelXP
options ( sheet_name= "Directory" embedded_titles='yes' autofit_height= 'yes' frozen_headers= '1' autofilter= 'yes' frozen_rowheaders="2" absolute_column_width= '20,20,4,4,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8');
options missing=" ";
title j=l "&&examdegree&i. ";
title1 j=l "Breakout: &&degree&k.";
title2 j=l "Reporting Window: &mon.";
proc Report data=uall NOWD
style(header)=[foreground=white background=CX4D7EBF];
Column form summary roster;
define form /Center display "Form Name";
define summary / center display "Summary";
define roster / center display "Roster" ;
compute form;
row+1;
if (mod(row,2)) then do;
call define(_row_,"style","style=[background=CXECEDEC]");
end;
endcomp;

run;

the log window shows:

WARNING: Apparent symbolic reference ABEXAMD1_ not resolved.
WARNING: Apparent symbolic reference DEGREE3_2017_08 not resolved.
NOTE: Writing TAGSETS.EXCELXP Body file: &abexamd1_&degree3_2017_08.xml
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.130, 05/01/2011). Add
options(doc='help') to the ods statement for more information.

Those &&abexamd&i. and &&degree&k. have values when I use %put &&abexamd&i. &&degree&k.  command.

The titles which have macro values are resolved.

Thanks,

[ballardw]

Did you try your %put for the problem variables in code immediately following the ods tagsets.ExcelXP statement that is generating the error?

If your test the value elsewhere you may have something defined that is not available (yet) at that point in your code.

If your %put test was in a separate macro it may be a scope issue.

If I have code (a very skeleton description) such as

%macro one ();

<does something that creates macro variables "&&abexamd&I>

%mend;

And call that macro elsewhere:

%macro othermacro ();

%one;

<my excelxp output>

%mend;

then the variables created in macro one may not be available in Othermacro if they are not declared as global when created.

[daisy6]

Thanks for the reply. I tried several times you mentioned. The macro has values. It only can not be resolved when I put it to the file name of  ODS excelxp output. I want to know what is the rules of ods tagsets excelxp path file name ? 

[PaigeMiller]

file="&&abexamd&i._&&degree&k._&mon..xml"

I think you need two dots after &&abexamd&i and two dots after &&degree&k

[Tom · Accepted solution]

You have

file="&&abexamd&i._&&degree&k._&mon..xml"

So if I set some values for I,K, and MON.  And also create ABEXAMD(i) and DEGREE(k) variables that I assume you are trying to resolve.

%let i=1 ;
%let k=2 ;
%let mon=JAN ;
%let abexamd1=XXX ;
%let degree2=YYY;

And try your string I get errors that macro variables are not found.

130   %put file="&&abexamd&i._&&degree&k._&mon..xml" ;
WARNING: Apparent symbolic reference ABEXAMD1_ not resolved.
WARNING: Apparent symbolic reference DEGREE2_JAN not resolved.
file="&abexamd1_&degree2_JAN.xml"

So you need to add another period since the SAS macro processor is making two passes because of the && included in the expression.

Try this:

file="&&abexamd&i.._&&degree&k.._&mon..xml"

Testing

137   %put file="&&abexamd&i.._&&degree&k.._&mon..xml" ;
file="XXX_YYY_JAN.xml"

[daisy6]

Thank Tom very much. You help works and  I appreciate! You save my day!