The SAS Output Delivery System and reporting techniques

ods excelxp filename with macro

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

ods excelxp filename with macro

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,

 

 


Accepted Solutions
Solution
‎11-06-2017 03:01 PM
Super User
Super User
Posts: 7,260

Re: ods excelxp filename with macro

[ Edited ]

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"

 

 

 

View solution in original post


All Replies
Super User
Posts: 11,810

Re: ods excelxp filename with macro

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.

Frequent Contributor
Posts: 82

Re: ods excelxp filename with macro

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 ? 

 

 

Respected Advisor
Posts: 2,054

Re: ods excelxp filename with macro

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

 

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

--
Paige Miller
Solution
‎11-06-2017 03:01 PM
Super User
Super User
Posts: 7,260

Re: ods excelxp filename with macro

[ Edited ]

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"

 

 

 

Frequent Contributor
Posts: 82

Re: ods excelxp filename with macro

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 243 views
  • 0 likes
  • 4 in conversation