topic Re: ods excelxp filename with macro in ODS and Base Reporting

ods excelxp filename with macro

daisy6 — Fri, 03 Nov 2017 20:27:29 GMT

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,

Re: ods excelxp filename with macro

ballardw — Fri, 03 Nov 2017 20:46:26 GMT

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 ();

%mend;

And call that macro elsewhere:

%macro othermacro ();

%one;

%mend;

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

Re: ods excelxp filename with macro

daisy6 — Mon, 06 Nov 2017 15:17:10 GMT

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 ?

Re: ods excelxp filename with macro

PaigeMiller — Mon, 06 Nov 2017 18:10:57 GMT

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

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

Re: ods excelxp filename with macro

Tom — Mon, 06 Nov 2017 18:46:48 GMT

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"

Re: ods excelxp filename with macro

daisy6 — Mon, 06 Nov 2017 20:01:53 GMT

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