ExcelXP Tagset, MultiSheet Works in 9.13 on UNIX, not in 9.2?

[jhixon]

Hi,

I am migrating a large application from SAS 9.13 under UNIX to SAS 9.2.3 under UNIX.  I am having issues with code that runs fine under 9.13 but fails under 9.2.  I am using ExcelXP tagset to create a mutli-sheet XML file using the command (this is embedded in a macro, but this shold be clear?):

ods tagsets.ExcelXP path=odsout file="&xmlFile7" style=JohnSansV;

  %do t=1 %to &nTables;

   data _oneTable;

    set ForTablesSm(where=(nTable=&t)) end=done;

    call symput('TableName',trim(summarytable));

   run;

   proc sort data=_oneTable;

    by ParamDateTime;

   run;

   %put >>>>>t=&t, TableName=&TableName<<<<<;

   title "Batch List for &TableName";

   title1 j=c h=11pt "Product: &product.";

   title2 j=c h=11pt  "&EntSiteAreaText";

   title3 j=c h=10pt  "&DateRangeText";

   title4 j=c h=12pt c=blue  "&TableName";

   %rdt;

   footnote1 h=8pt f=Arial j=l "Printed By [&username] &rdt (PST)" ;

   ods tagsets.ExcelXP options( sheet_name="&TableName" frozen_headers='Yes'

      AutoFit_Height='Yes'  embedded_titles='yes' embedded_footnotes='yes'

       absolute_column_width='22.5,22.5,22.5');

   proc report data=_OneTable nowindows;

    column  lot date time;

    define lot / display "Batch" style=[vjust=c just=c tagattr='format:Text'];

    define date / display "Date" format=is8601da. style=[vjust=c just=c];

    define time / display "Time" format=is8601tm. style=[vjust=c just=c];

   where nTable=&t;

   run;

  %end;

ods tagsets.ExcelXP close;

This works fine in SAS 9.13 under UNIX and creates a multi-sheet XMl Table.

The code *also* works fin under SAS 9.2 on WINDOWS, but, it fails under SAS 9.2 on UNIX.

Has anyone run  into this behavior?

I've used two different versions of the ExcelXP tagset, and they both have the same behavior.

The two versions of the tagsets files that I have used are:

1)  NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.47, 01/10/07 vcd)

2)  NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12)

They both exhibit the same behavior.  Everything is fine in 9.13 in UNIX, or 9.13 and 9.2 on WINDOWS, but, the mutlisheet XML fails in 9.2 on UNIX.

Thanks for any insights.

[jhixon]

OK, I just downloaded the latest version opf the ExcepXP tagsets.  I still have a problem (but it is slightly different).  With the prior versions of the tagsets, (in UNIX SAS 9.2 MR3 only) I would receive an error when the tagset was trying to write the second sheet in the file.  I now see this tasget version iformation:

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.127, 09/26/2011)

And, I do not get an error message in the log, but, rather than create a multi-sheet XML table, I get mutiple XML files.

This is very strange since the code runs fine on SAS 9.13 in UNIX, and runs fine in 9.13 and 9.2 on WINDOWS.

But...I need it to run on SAS 9.2 MR3 on UNIX!  🙂

I'll keep plugging away, but...I can't believe that I'm the only person who had his multi-sheet XML exports stop working when migrating an application from 9.13 to 9.2?

[Cynthia_sas · Accepted solution]

Hi:

  This is a long shot, but you might try:

ods tagsets.ExcelXP path=odsout file="&xmlFile7" style=JohnSansV newfile=none;

The behavior that you describe almost sounds like a bad setting for NEWFILE= -- which I can't imagine how you might have gotten that set globally. But explicitly turning it to NONE should stop multiple XML files from being created.

You do not show what the value for the fileref ODSOUT is, or the value for &xmlFile7 macro variable, and your invocation of %RDT macro might be doing something that would impact the destination. But given that the code used to work and now doesn't work, I'd suggest that you open a track with Tech Support. (especially if the newfile=none has no impact on the behavior).

cynthia

[jhixon]

Thank you so much, Cynthia.

That did the trick!

I tried something similar, using the code snippet below, but what you suggested eliminated the problem!

Thanks again.

Until you sent “The Answer” I was going to open a ticket and send the code snipped below as a way to demo that things worked in WINDOWS and not in UNIX.

You could toggle the defn of odsout between C:\temp or the UNIX path to compare the output.

data Demo;

      length SheetName$6;

      do SheetName='One', 'Two', 'Three';

            nSheet+1;

            do x=1 to 25;

                  y=rannor(-1);

                  output;

            end;

      end;

run;

proc print data=Demo;

run;

*filename odsout "C:\temp";

%include 'PATH TO UNIX TEMPLATE STORE/excltags.tpl';

filename odsout "PATH TO UNIX FOLDER";

ods html newfile=none;    *<-----  Hah! I tried this: No Joy.;

%macro Demo;

ods tagsets.ExcelXP path=odsout file="WhyDoesThis_ONLY_HappenIn_SAS92_On_UNIX.xml" style=sansprinter newfile=none ;  *<-- This did the trick!;

%do i=1 %to 3;

      %let TableName=SheetName_&i;

      ods tagsets.ExcelXP options( sheet_name="&TableName" frozen_headers='Yes' /*doc='help'*/                           

                                          AutoFit_Height='Yes'  embedded_titles='yes' embedded_footnotes='yes'

                                          absolute_column_width='22.5,22.5,22.5');

      proc report data=Demo(where=(nSheet=&i)) nowindows;

                        column  x y ;

                        define x / display "X" ;

                        define y / display "Y" ;

      run; quit;

%end;

ods tagsets.ExcelXP close;

%mend;

%Demo;

Thanks again.

This was my first time trying the SAS Community thing.  Very nice.  A quick answer!

Cheers,

John

[Cynthia_sas]

Ummm...glad it worked, but you did NOT show that ODS HTML snippet in your original post. I wonder why you tried that, at all. You have ODS HTML --outside-- of your macro call and TAGSETS.EXCELXP --inside-- your macro call and usually, this would be OK...but I do NOT see a close for ODS HTML, which is bothersome.

I'm glad it worked out for you, but I am now confused by your code. ODS HTML is totally different from ODS TAGSETS.EXCELXP -- so adding NEWFILE=NONE to the ODS HTML statement would not have any impact on the XML file created by TAGSETS.EXCELXP....just FYI.

cynthia

[jhixon]

Hi Cynthia,

Yes, of course that “ODS HTML NEWFILE=NONE” was not in my original code.  My original code worked fine in 9.13 on UNIX, before moving to UNIX on SAS 9.2.

The insertion of that (admittedly absurd) line of code was just a desperate shot in the dark on my part!  I had no expectation it would do anything, but, it was the only “newfile=none” reference I found in the docs…so, I thought I would give it a try.

I did not see a “newfile=none” option for the ExcelXP tagsets.

So, I’m happy as a clam now.  Problem solved.  Moving on to more productive things.

Thanks again.  Have a good weekend.

John

[Cynthia_sas]

Hi,

  If you look in the doc (in the future), everything "TAGSET" is a member of the "Markup Family" of destinations. Sometimes in the doc you will see a statement that something "is valid only in markup family destinations, printer family destinations, RTF destination, and the Measured RTF destination."

  HTML, TAGSETS.EXCELXP, MSOFFICE2K, CSV, etc, etc, are all "Markup" family destinations. This means that ODS takes the output from your SAS procedure or process and "marks it up" with the appropriate markup language information (such as HTML tags for HTML or commas and quotes for CSV or XML tags for TAGSETS.EXCELXP.)

  In the ODS doc, there is a section for the ODS Tagset Statement

http://support.sas.com/documentation/cdl/en/odsug/62755/HTML/default/viewer.htm#n0jrwo0xyh8nlqn19u6u...

and NEWFILE= is documented there. An "alias" method of invoking tagset templates is to use the ODS MARKUP destination, and NEWFILE= is documented there, as well.

http://support.sas.com/documentation/cdl/en/odsug/62755/HTML/default/viewer.htm#n0onpp2holuauhn1gyb7...

  For example, you can invoke the Excelxp tagset template in  2 different ways:

ods markup tagset=excelxp file='c:\temp\method1.xml'

    style=sasweb;

ods tagsets.excelxp file='c:\temp\method2.xml'

    style=sasweb;

proc print data=sashelp.class(obs=2);

run;

ods _all_ close;

But that is a topic for another post. I just thought you might be interested in knowing a bit more about what a "tagset" means. In fact, the simple invocation ODS HTML is just an alias for ODS TAGSETS.HTML4 - -but at some point there got to be too many aliases, so TAGSETS.EXCELXP didn't get one.

cynthia