The SAS Output Delivery System and reporting techniques

ExcelXP with Proc Print

Reply
Contributor
Posts: 21

ExcelXP with Proc Print

I am using the latest ExcelXP tagset with good success, but am trying to center a couple of columns in my Excel tables by using style overrides in the Proc Print, but I can't get it to work. Can this be done, or do I need to wrestle with my favorite Proc Template?

PROC PRINT DATA=ALLEVT NOOBS LABEL SPLIT='/';
WHERE FSTATRES = "US&RPTST" AND EVENT = "&EVT";
VAR YEAR / style={just=c};
VAR FSTATOCC;
VAR FILENO / style={just=c tagattr="format:000000"};
VAR FSTATRES FCNTYRES;
VAR DATE / style={just=c};
SAS Super FREQ
Posts: 8,864

Re: ExcelXP with Proc Print

Hi:
One of the Tech Support folks can correct me if I'm wrong, but I thought that if all you had was a format, then ExcelXP would let you get away with a simple format reference for the TAGATTR value:
[pre]
style(column)={tagattr="00000"}; (REPORT)
style(data)={tagattr="00000"}; (PRINT)
OR
style(column)={tagattr="$#,####.00"}; (REPORT)
style(data)={tagattr="$#,####.00"}; (PRINT)
[/pre]
BUT, if you needed a formula or a formula AND a format, then you needed the Formula: and Format:

The simple invocation works for me with the updated tagset. Proc REPORT uses style(column), as shown above. Proc PRINT uses style(data) and Proc TABULATE does not use a component (what's in the parens). With PRINT and REPORT, I have always found it better to be explicit in the style= override as far as providing a component..because that number format would be inappropriate for the column HEADER (I believe your override applies to ALL components unless you're specific.)

cynthia
SAS Employee
Posts: 95

Re: ExcelXP with Proc Print

I have an updated tagset that will allow this to work. But justification has
been an issue, as are style attribute over rides as you show them.

To simplify usage, and the XML generated, the way to make this work, with the tagset you have is to create a style element.

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

style data_c from data/
just=c
;

end;
run;

Then in your proc print,

PROC PRINT DATA=ALLEVT NOOBS LABEL SPLIT='/';
WHERE FSTATRES = "US&RPTST" AND EVENT = "&EVT";
VAR YEAR / style=data_c;
VAR FSTATOCC;
VAR FILENO / style=data_c{tagattr="format:000000"};
VAR FSTATRES FCNTYRES;
VAR DATE / style=data_c;

You could create yet another style element with the tagattr="format:000000" in it as well.

Over-rides like this, except for tagattr, and just, vjust, can cause the style section of the Excel XML to grow to tremendous size. To keep that under control, just and vjust is ignored. tagattr settings of format, formula and rotate are treated differently since they are more manageable.

Creating style elements with the settings you need is the best way to work
with the excel tagset. The proc print code benefits from being a little simpler since it is always a style name you are referencing.

Just and vjust are particularly difficult since it can't be known if they are an over ride or just the current setting. The latest version of the tagset works around this problem by creating alternate styles for almost everything,
data_c, data_l, data_r, header_c, header_l, header_r, etc. It is verbose, but
it is manageable.

The new tagset should be available on the web next thursday.
Contributor
Posts: 21

Re: ExcelXP with Proc Print

Thanks for the code; I was able to use the style attribute to get those columns centered, along with their headers, which is fine, but now I'd like to get the other column headers centered as well.

I attempted to modify my style with just=c for the RowHeader element, but that didn't work. Which element controls the placement of the column headers, or am I heading down the wrong path altogether here?
SAS Super FREQ
Posts: 8,864

Re: ExcelXP with Proc Print

The RowHeader element controls the header-like column headings that go down the rows (as in a PROC FREQ, for example, where the variable values go down the side and have the same formatting as the column headers).

The RowHeader element inherits style attributes from the Header style element. So if you're doing this in a style template, you'd change the Header style element.

If you're doing this in a style template, then change the Header style element to alter the column headers' attributes.

The potentially confusing thing is that for PROC PRINT and PROC REPORT, when you say: style(header), you might be changing either column headers or row headers, depending on your report -- but essentially, PROC PRINT and PROC REPORT are letting you cut out the style template because they are overriding what's in the style template. (well, PROC TABULATE, too.)

cynthia
Ask a Question
Discussion stats
  • 4 replies
  • 192 views
  • 0 likes
  • 3 in conversation