06-09-2014 11:48 AM
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?
06-09-2014 12:00 PM
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.
06-09-2014 12:54 PM
The tags ets have different versions aside the sas version.
You should do a release management approach on that part
06-09-2014 02:56 PM
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?
06-09-2014 03:42 PM
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.
06-09-2014 03:50 PM
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"|
06-09-2014 04:49 PM
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.
06-09-2014 05:09 PM
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).
06-09-2014 05:24 PM
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.
06-10-2014 04:18 PM
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?
06-10-2014 04:21 PM
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.
retain i 0;
if first.a then i+1;