traffic lighting and sheet names with ExcelXP Tagset

[deleted_user]

Once again, I have a large batch of Excel workbooks to generate and I'm trying to automate as much of the process as possible. Therefore, I'm looking for possible solutions/workarounds for the following:

1) Sheet Names. I'm using proc report to generate rosters by a particular variable and I'd love to be able to name each sheet according to that by-variable. Unfortunately, the good old #byval trick inserted into the sheet_name=' ' option doesn't seem to work. Do I have any options at this point, short of giving everyone a list stating what values tabs 1, 2, 3, 4, etc. are equivalent to?

2) Traffic lighting. Essentially, I have a column of percentiles (in other words, values pre-summarized before I reach the report procedure) where I'd like to show anything in the column at or below the 25% with a yellow background and leave the rest white, otherwise. I tried creating a user-defined format, along the lines of...

proc format;
value lowtraff low-<0.255='Yellow'
0.255-1='White';
run;

...and then applying it to the pertinent variable in the proc report in this manner...

define low25r / style(column)=[just=c background=lowtraff. font_weight=bold];

...but when I go to open it up in Excel, it gives me a "Problems came up in the following areas during load:" error, listing "Style".

I didn't think I could use a compute block, because there's nothing to compute. Does the method I'm using have known issues with ExcelXP?

3) Percent formats. Every time I have an ExcelXP spreadsheet with percentages (which is almost 100%) of the time, values are always displayed with two decimal places, even if I define them to show zero or one decimal place.

4) Column alignment. I'm sure this is another one of those "Tagset attributes overrides SAS Proc-defined attributes" situations, but nothing I define as center-justified in my proc ever comes out as centered on the worksheet. Am I overlooking a tagset option?

Direction on any or all of these issues would be greatly appreciated. Thanks so much!

[Eric_SAS]

hello again,

1. You should get sheet names with by values if you set your sheet_interval="bygroup" If that is still not quite what you want we can
make a simple change to the tagset to make it print what you do want.

2. This should work, I don't see anything standing out.
It would be better to define a style to use instead of using attribute over rides. The error indicates that a style definition is possibly missing from
the definition. That could happen if you referenced a style element that
didn't exist. Does the error file say anything more?

For your example I would recommend this. It could fix the problem too.

proc template;
define style styles.test;
parent=styles.default;

style lowtraffic /
foreground=yellow
just = center
font_weight=bold
;

style regtraffic from lowtraff /
foreground=white
;
end;
run;

proc format;
value lowtraff low-<0.255='lowtraffic'
0.255-1='regtraffic';
run;

...

define low25r / style(column)=lowtraff;
...

3. Percent formats always use a percentage format. I believe that 2 decimals is the default. To change it you could do this.
define low25r / style(column)=lowtraff[tagattr='type:Number Format:0.0%'];

4. justification as an over-ride does not work. You can do it through the
style definition the way I did for question 2.

[deleted_user]

Here's what I found.

1) sheet_interval="bygroup" puts everything into one tab, which isn't what I want. What I'm trying to do is to get the actual bygroup values to also serve as tab names. In this particular project, my bygroup is teacher. I'd like for each teacher's info to be on a separate tab and to have the tab name be the teacher's name. That's why I attempted to do sheet_name="#byval", but the option is still read as a literal and each tab actually ends up getting named #byval 1, #byval 2, #byval 3, etc. I run into this issue quite often, so it would be greatly helpful to me if a simple change was made to the tagset.

2) I have a custom style (it's adapted from Printer and mainly controls fonts, sizes, and a few colors) that I've used countless times, so I'm not sure how I could have used a nonexistent style definition. Here's what happened when I used your code:

First, I used your code exactly as is, making sure to change my style delcaration in my ODS options to the new style, Test. I received an error of "Unable to find the "LOWTRAFF" style element. Default style attributes will be used."

Next, I realized there seemed to be a typo in your code. "lowtraff" is a format name, and in your style delcaration you said "regtraffic from lowtraff", which looks like it should have been "regtraffic from lowtraffic". I made the change and received the same error.

As a last ditch effort, I changed "define low25r / style(column)=lowtraff" to "define low25r / style(column) = lowtraffic" to see if that worked. It runs and produces a report without errors, but they're all yellow, which is wrong. I expected that result from this particular change.

I'm not strong enough with conditionally referencing styles through formats in this manner to troubleshoot anymore. Help!

3) I undid everything from #2, so the line would become define pctrankr / style(column)=[tagattr='type:Number Format:0.0%'];. Subsequently, Excel gave me an error upon opening, similar to the style error issue. It's a box that pops up that says "Problems During Load", and then says "Problems came up in the following areas during load:". Inside a white box is a clickable line called Table (in this case), and outside the white box it says "This file cannot be opened because of errors. Errors are listed in: (filepath)". The filepath points to a .log file that I can never find on my computer.

4) When I fix #2, I'm sure #4 will also work.

Any suggestions?

[deleted_user]

I just want to look the source code of some procedure. for example, proc sort, i want to look the source code of the procedure 'sort'. Does somebody know where I can look it? Thanks a lot.