BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
daisy6
Quartz | Level 8

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,

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

5 REPLIES 5
ballardw
Super User

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
Quartz | Level 8

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
Diamond | Level 26

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

 

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

--
Paige Miller
Tom
Super User Tom
Super User

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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1799 views
  • 1 like
  • 4 in conversation