BookmarkSubscribeRSS Feed
BigPete
Obsidian | Level 7

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

10 REPLIES 10
Reeza
Super User

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.

jakarman
Barite | Level 11

The tags ets have different versions aside the sas version.

You should do a release management approach on that part

---->-- ja karman --<-----
BigPete
Obsidian | Level 7

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?

Cynthia_sas
SAS Super FREQ

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

Reeza
Super User

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;

    ;

BigPete
Obsidian | Level 7

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.

Cynthia_sas
SAS Super FREQ

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

Reeza
Super User

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.

BigPete
Obsidian | Level 7

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?

Reeza
Super User

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;

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
  • 10 replies
  • 2333 views
  • 0 likes
  • 4 in conversation