The SAS Output Delivery System and reporting techniques

Different SAS versions = different outcomes of "ODS tagsets.excelxp"?

Reply
Occasional Contributor
Posts: 17

Different SAS versions = different outcomes of "ODS tagsets.excelxp"?

Hi,

I borrowed the ODS tagsets.excelxp portion of a coworker's SAS code since my project involves very similar sources and objectives.  If I realized earlier that my XML files will appear different than her's using those same ODS statements before she left the company, I wouldn't even be asking here.

Refer to the attachments to get an idea what I'm talking about.  I can only provide a dummy dataset here to help you to help me.  I ran the latest tagsets update first off of https://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl like the coworker did before running the SAS code attached.  Not sure what version she used.  I used 9.2 (TS level 2M3) at work and 9.4 at home.

Even though values are properly distributed into separate sheets by zip code like I'd like to see, the labeling for the sheets and headers are different in the coworker's, my work, and home version of the XML/Excel output.  Coworker's sheet names would beautifully show as "Zip 00001", "Zip 00002", "Zip 00003", etc. but mine would show "00001 2", "00002 2", "00003 2", etc.  Her header label would show "Table 1:...", "Table 2:...", "Table 3:...", etc. but mine wouldn't show the index # after "Table".  Not to mention in 9.2_test_work.xml, you'll see the three header rows repeating after every hundred rows.  And these are using the same "ODS tagsets.excelxp" statements as the coworker's.

Any idea what gives?

Thanks,

Pete

Grand Advisor
Posts: 17,464

Re: Different SAS versions = different outcomes of "ODS tagsets.excelxp"?

Do you have the log from any of her runs? That would show the version of Tagsets used.
Can you post your log? Or at least the first section that indicates that the options were correctly assigned.


She's also using a custom style, styles.minimal I'm assuming you're running this as well.

Valued Guide
Posts: 3,206

Re: Different SAS versions = different outcomes of "ODS tagsets.excelxp"?

The tags ets have different versions aside the sas version.

You should do a release management approach on that part

---->-- ja karman --<-----
Occasional Contributor
Posts: 17

Re: Different SAS versions = different outcomes of "ODS tagsets.excelxp"?

So I found out from her supervisor that they also been using SAS 9.2 at work.  When I barely talked to her before leaving the company, she mentioned she used the latest tagset version 1.13.  That's what I also downloaded and ran off of https://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl.

Reeza, yes I used styles.minimal too like she did.  Was there another SAS code out there to make that work?

Jaap, it'd be nice if we did have more components like Release Management but we only have the base 9.2 and EG 4.3 at work.

Any alternative ideas anyone?

SAS Super FREQ
Posts: 8,721

Re: Different SAS versions = different outcomes of "ODS tagsets.excelxp"?

Hi:

  My suggestion is to open a track with Tech Support. There was work done on TAGSETS.EXCELXP between releases of SAS and during  releases of SAS. Some sub-options were added to improve sheet naming based on BY groups. This is the kind of thing that I would recommend you pursue with Tech Support because they can replicate your environment in order to help you come to a good resolution. For example, there would be no point in my running any code, since I only have 9.4 to work with EG 6.1.

cynthia

Grand Advisor
Posts: 17,464

Re: Different SAS versions = different outcomes of "ODS tagsets.excelxp"?

I'm going to assume that your colleague was simply using a earlier version of tagsets, as I've seen this issue when switching versions. The version of SAS is not related to the version of the tagsets as mentioned by Jaap.

You only need to run the tagsets update once by the way, and then its 'stuck' so you don't need to call it every time.

In the code you have #byval(i). I haven't seen this before so I don't know if its valid and SAS will generate an automatic i, or if there was something else in your colleagues code that generated the i.

I suspect the latter.

This gets closer, minus the table i ...

ods tagsets.ExcelXP file="&odir\9.2_test_work.xml"
   options
   (
   sheet_interval='bygroup'
   sheet_label='Zip '
   suppress_bylines='yes'
   Row_Repeat='3'
   embedded_titles='yes'
   absolute_column_width='9,15,13'
   width_fudge='0.75'
   autofit_height='yes'
  

frozen_headers='3' )

styles=styles.minimal;

    ;

Occasional Contributor
Posts: 17

Re: Different SAS versions = different outcomes of "ODS tagsets.excelxp"?

Yup the variable i was user-generated and was incremented WRT each zip: i=1 for zip 00001, i=2 for zip 00002, etc.  It seems the colleague's point of that was to put in a table # on those header labels.  It puzzles me that #byval(zip) worked but not #byval(i) from my end but did so for hers.

SAS Super FREQ
Posts: 8,721

Re: Different SAS versions = different outcomes of "ODS tagsets.excelxp"?

Hi, I have seen #byval(1) or #byval(2) work or #byval(name_of_var), but not #byval(i) -- I might expect to see #byval(&i) if this was all inside a huge macro %DO loop, but it didn't look like that in your posted code. When I run a test using #byval(i) I don't get an error message, but the value resolves to a "blank" in the title (in SAS 9.4).

cynthia

Grand Advisor
Posts: 17,464

Re: Different SAS versions = different outcomes of "ODS tagsets.excelxp"?

Either 1 - The original code has something you haven't included in your example

2 - The by statement in the code also had an i, which was included as a variable in the source data set to drive the results.

Occasional Contributor
Posts: 17

Re: Different SAS versions = different outcomes of "ODS tagsets.excelxp"?

As you can see in the latest SAS code, I figured out mixing in some CALL SYMPUT and macro looping did the trick.  I suspected the BY and PAGEBY statements under PROC PRINT were the culprits for the extra header rows every hundred rows, so I took those out and add the WHERE= option.  My XML file appeared just the way I'd like.  When I tested this code on a much bigger input dataset though, it seemed the tradeoff was a significantly longer run.  Any suggestions on what to tweak to still achieve the same XML file(s) but on faster run times?

Attachment
Grand Advisor
Posts: 17,464

Re: Different SAS versions = different outcomes of "ODS tagsets.excelxp"?

Skip the macro and use my #2 suggestion above, create an i variable in the data that has the value you want to put in the title and add it into your By statement.

data reporting;

set have;

retain i 0;

by a;

if first.a then i+1;

return;

Ask a Question
Discussion stats
  • 10 replies
  • 1440 views
  • 0 likes
  • 4 in conversation