topic Re: Quick Formatting Problem with ExcelXP in ODS and Base Reporting

Quick Formatting Problem with ExcelXP

OS2Rules — Thu, 26 Jun 2008 19:28:30 GMT

Hi All:

I have a pesky problem with formatting of a field using ExcelXP.

Here is the code I run to create the report:

PROC TABULATE DATA=sasdata.work05 missing order=data format=comma18.2;
by eff_dt;
class curc_name crncy_cde loc_cde db_cr_ind to_date;
var calc_int_amt cad_eq db_sum cr_sum db_sum_cad cr_sum_cad;
table curc_name * crncy_cde * loc_cde all='Total',
(db_cr_ind=' ' all='Net') *
(calc_int_amt='Local'*f=comma17.2 * sum=' '
cad_eq='CAD'*f=dollar18.2 * sum=' '
) to_date=' ' *
(cr_sum='Credit to-date'*f=dollar18.2*sum=' '
cr_sum_cad='CAD'*f=dollar18.2*sum=' '
db_sum='Debit to-date'*F=comma17.2 *sum=' '
db_sum_cad='CAD'*F=dollar18.2 *sum=' '
)
/ rts=22;

When I run this I get the dollar values in the spreadsheet, but not the comma fields. The fields are there, but unformatted The commas are in the listing output.

Any ideas?

Re: Quick Formatting Problem with ExcelXP

Cynthia_sas — Fri, 27 Jun 2008 16:27:12 GMT

Hi, When you're using ODS to send your output report from SAS to Excel, generally Excel does not honor many of the SAS formats. There are 2 workarounds and they depend on which piece of ODS you're using to send your output from SAS to a file that Excel can open (HTML or SpreadsheetML XML).

If you are using HTML-based methods to send your output from SAS to an HTML file that Excel can open, then you can use the HTMLSTYLE attribute to send a Microsoft format from SAS to Excel. If you are using XML-based methods to send your output from SAS to an XML (Spreadsheet ML) file that Excel can open, then you can use the TAGATTR style attribute to send a Microsoft format from SAS to Excel. If you search the forum for the string
TAGATTR ExcelXP
you should find some examples of sending a Microsoft format to Excel.

cynthia

Re: Quick Formatting Problem with ExcelXP

OS2Rules — Fri, 27 Jun 2008 18:55:45 GMT

Cynthia:

Thanks for the reply.

Just for the record, I'm using the ExcelXP tagset to write the data to the spreadsheet.

I just find it odd that Excel will accept the $dollar format and not the commaW.D format.

I would use the tagattr, but the tabulate statement is so complex, I wouldn't know where to put it. I just hope my client will accept my explanation ("You can't get there from here!")

Re: Quick Formatting Problem with ExcelXP

Cynthia_sas — Sat, 28 Jun 2008 03:30:32 GMT

Hi,
Let's say that you want to change this snippet to use tagattr:
[pre]
calc_int_amt='Local'*f=comma17.2 * sum=' '
[/pre]

You now have to add the STYLE= override to the snippet.

[pre]
calc_int_amt='Local'*f=comma17.2 * sum=' '*{s={tagattr="Format:???????"}}
[/pre]

In this case, the value for the tagattr attribute is the Microsoft format. I don't know exactly whether this would be the correct Microsoft format to use, but it works for this example:
[pre]
ods tagsets.excelxp file='c:\temp\usecomma.xls';

proc tabulate data=sashelp.shoes;
class region product;
var sales;
table region*product,
sales*sum*f=comma12.2*{s={tagattr="format:###,###,###"}}; ;
run;
ods _all_ close;
[/pre]

cynthia

Re: Quick Formatting Problem with ExcelXP

OS2Rules — Thu, 10 Jul 2008 19:25:17 GMT

Thanks Cynthia - I made those changes and the work great.

One more issue I hope you can help me with - using the same code as above, I would like to create a column total only for the "CAD_EQ" variable. As it stands now, I get column totals for that and the "CALC_INT_AMT" varialble using the ALL tag.

code line(from above): table curc_name * crncy_cde * loc_cde all='Total', of the code.

is there any way to do this?

Re: Quick Formatting Problem with ExcelXP

Cynthia_sas — Thu, 10 Jul 2008 20:16:31 GMT

Hi:
Well, just guessing...PROC TABULATE is making buckets based on the table operators that you are using. In this case, the space and the parentheses are considered table operators. so you have this table statement:
[pre]
(db_cr_ind=' ' all='Net') *
(calc_int_amt='Local'*f=comma17.2 * sum=' ' cad_eq='CAD'*f=dollar18.2 * sum=' ' )

[/pre]

which indicates that CAD_EQ is being CROSSED with DB_CR_IND and ALL in the COLUMN dimension...and is having row levels set by the crossing of
[pre]
curc_name * crncy_cde * loc_cde all='Total',
[/pre]

in the ROW dimension. So depending on how you got to this table statement and whether you -want-CAD_EQ crossed with DB_CR_IND and ALL you might try restructuring your TABLE statement. Either take ALL out of the DB_CR_IND crossing or move CAD_EQ out of the crossing.

The placement of the ALL and the parentheses can have a significant impact on your table. A simpler example which illustrates different ways of using ALL in a crossing with 2 numeric variables is shown in the program below. It uses SASHELP.PRDSALE, so you should be able to run in and see which one of the arrangements is close to the one you want.

cynthia

[pre]
ods listing close;
ods html file='c:\temp\crossexamp.html'
style=sasweb;
proc tabulate data=sashelp.prdsale;
title 'tabulate examples';
class region division country prodtype;
var actual predict;
table country*division*region all,
(prodtype all)*(predict*sum actual*sum)
/box='1) cross predict and actual';

table country*division*region all,
(prodtype all)*(predict*sum) actual*sum
/box='2) only cross predict';

table country*division*region all,
(prodtype)*(predict*sum actual*sum)
/box='3) Have ALL only in ROW';

table country*division*region all,
(prodtype)*(predict*sum actual*sum)
all*actual*sum
/box='4) Have ALL only for Actual';

table country*division*region all,
(prodtype all)*(predict*sum)
prodtype*actual*sum
/box='5) PRODTYPE and ALL for PREDICT, only PRODTYPE for Actual';
run;

ods html close;
[/pre]

Re: Quick Formatting Problem with ExcelXP

OS2Rules — Fri, 11 Jul 2008 14:06:51 GMT

Cynthia:

You are brilliant (as usual), but ....

Using the first table that is created from your example (which closely relates to what I have), here is what I need -

For the 'Predicted Sales' column, I would like a total, but not for the 'Actual Sales' column.

Any ideas?

Re: Quick Formatting Problem with ExcelXP

Cynthia_sas — Fri, 11 Jul 2008 20:05:18 GMT

Oh, got it. You want the columns to stay where they are, you just don't want the TOTAL at the bottom of all the columns.

Well, when PROC TABULATE uses ALL, it summarizes the statistics for ALL the columns. It's very hard to get TABULATE to do otherwise and the way you would have to do it if you absolutely, positively NEEDED to use tabulate is messy and inelegant. Possible, but inelegant.

However, PROC REPORT comes to the rescue...because of the ability to do a COMPUTE AFTER, you can selectively "turn off" the total at the end of the report (produced by the RBREAK statement in PROC REPORT). See the program below.

The one wrench in the works is the fact that in order to figure out the correct absolute column name, you either have to KNOW the number of values for the ACROSS variable (in this case, PRODTYPE) or you have to figure it out ahead of time (either manually or with a SAS Macro) so you can generate the correct column numbers.

Here's how PROC REPORT figures absolute column numbers for the report below...taken from the COLUMN statement:

[pre]
column ("REPORT solution" country division region)
prodtype,(predict actual)
('Total' predict=ptot actual=atot);
[/pre]

In this case, country is column 1, division is column 2 and region is column 3 (even if any of them were NOPRINT variables, they'd get assigned a number).
Then
1st PRODTYPE value: predict = _c4_ actual = _c5_
2nd PRODTYPE value: predict = _c6_ actual = _c7_

ptot is column 8 and atot is column 9

( know for sure that there are only 2 PRODTYPES in SASHELP.PRDSALE, so I didn't have to go any further than that and could hard code the absolute column numbers in the code below. In my COMPUTE block, the only variables that I am required to address by absolute column numbers are the variables under the across. Since PTOT and ATOT are aliases for predict and actual and since they're NOT under the ACROSS variable, I can refer to them by name.

However, let's say I had 4 values for PRODTYPE... then my assignment of absolute column numbers would happen like this:

Country, Division and Region would still be columns 1,2, and 3
Then
1st PRODTYPE value: predict = _c4_ actual = _c5_
2nd PRODTYPE value: predict = _c6_ actual = _c7_
3rd PRODTYPE value: predict = _c8_ actual = _c9_
4th PRODTYPE value: predict = _c10_ actual = _c11_

ptot is column 12 and atot is column 13

So you can see how the number of values for the across variable will have an impact on what you put in your compute block. However, using this method, you can then "blank" out the total underneath ACTUAL:
[pre]
compute after ;
if _break_ = '_RBREAK_' then do;
_c5_ = .;
_c7_ = .;
atot = .;
end;
endcomp;
[/pre]

cynthia
[pre]
ods listing close;
ods html file='c:\temp\crossexamp.html'
style=sasweb;
proc tabulate data=sashelp.prdsale;
title 'tabulate examples';
class region division country prodtype;
var actual predict;
table country*division*region all,
(prodtype all)*(predict*sum actual*sum)
/box='1) cross predict and actual';

run;

options missing = ' ';
proc report data=sashelp.prdsale nowd;
title 'proc report suppress total';
column ("REPORT solution" country division region)
prodtype,(predict actual)
('Total' predict=ptot actual=atot);
define country /group
style(column) = Header;
define division / group
style(column) = Header;
define region / group
style(column) = Header;
define prodtype / across;

define predict / sum;
define actual / sum;
define ptot / sum;
define atot / sum;
rbreak after / summarize;
compute country;
if _break_ = '_RBREAK_' then do;
country = 'Total';
call define(_col_,'style',
'style=Header');
end;
endcomp;
compute division;
if _break_ = '_RBREAK_' then
call define(_col_,'style',
'style=Header');
endcomp;
compute region;
if _break_ = '_RBREAK_' then
call define(_col_,'style',
'style=Header');
endcomp;
compute after ;
if _break_ = '_RBREAK_' then do;
_c5_ = .;
_c7_ = .;
atot = .;
end;
endcomp;
run;
options missing = .;
ods html close;

[/pre]

Re: Quick Formatting Problem with ExcelXP

OS2Rules — Mon, 14 Jul 2008 12:53:04 GMT

Cynthia:

I figured that I would have to convert the code to a PROC REPORT, but the data was not correctly set-up for it. After a little "fudging of the figures" I managed to fit the data to the report (rather than vise-versa).

The PROC REPORT also fixes a little column header problem that I had.

Thanks again.

Re: Quick Formatting Problem with ExcelXP

JenHarper — Thu, 31 Jul 2008 00:42:24 GMT

Cynthia,

THANK YOU!!!!! I've just spent 2 hours trying to get different formats on different tables in one Tabulate output via ExcelXP. I was really close, but I was confused about where the tagattr option went. This just made me day!

Jen

Re: Quick Formatting Problem with ExcelXP

AlexHuang — Tue, 25 Dec 2012 14:11:36 GMT

Hi Cynthia,

According to the method "f=comma12.2*{s={tagattr="format:###,###,###"}}; ;"

you suggested to use in Excelxp tabulate formatting.

I did get the right result in the first tabulate of my code, but failed in the second tabulate.

These 2 tabulate are almost the same.

Do you have any idea what is the reason accounts for this issue?

Re: Quick Formatting Problem with ExcelXP

Cynthia_sas — Tue, 25 Dec 2012 15:27:04 GMT

Hi:

Without seeing your code, it is just a guess about what the issue is. If the TAGATTR works in one TABULATE and not the other, some of the possibilities might be:

1) the TAGATTR string is applied in the ROW dimension, for example and the COL dimension format is overriding it (the fix for this is to use STYLE_PRECEDENCE=ROW); or

2) the TAGATTR format is wrong (have you made sure it is correct? In the back of this paper, I show how to "reverse engineer" finding out whether you are using the correct value for TAGATTR. http://support.sas.com/resources/papers/proceedings11/266-2011.pdf) or

3) you are applying the style override to the wrong variable, wrong crossing or wrong piece of the table (such as using TAGATTR in a CLASS statement instead of a TABLE statement) or

4) "almost the same" is just different enough to cause your issue, or

5) Excel has problems using your format (related to #2, but you are asking for a custom format that Excel doesn't like), or

6) something else....depending on your data or your code.

Can you post a simplified version of your code, perhaps using SASHELP.SHOES, as shown in one of the earlier posts? If you can't replicate your issue using SASHELP.SHOES, then the problem might be with your data. If you can replicate your issue using SASHELP.SHOES, then you've provided code that someone might be able to run in order to see whether they can help you or you've got code that you can send to Tech Support.

cynthia

**Sample code to start with;

** SASHELP.SHOES has 3 numeric vars: sales, inventory and returns;

** with region, product and subsidiary as all possible CLASS variables.;

** Table below uses only region, product and sales.;

ods listing close;

ods tagsets.excelxp file='c:\temp\usetagattr.xml'

style=sasweb;

proc tabulate data=sashelp.shoes;

where region in ('Canada', 'United States');

class region product;

var sales;

table region*product,

sales*sum*f=comma12.2*{s={tagattr="format:###,###,###"}}

/box='TABLE stmt' ;

run;

ods tagsets.excelxp close;

Re: Quick Formatting Problem with ExcelXP

AlexHuang — Wed, 26 Dec 2012 02:19:35 GMT

Cynthia,

Thanks for your quick reponse.

I solved the problem after including "excltags.tpl" downloaded from sas website.

libname tagslib "/DIR_HOME";

ods path(prepend) tagslib.templat(update);

%inc "/DIR_HOME/excltags.tpl";

But I do not really understand the reason.

Could you give me an insight into this issue?

Re: Quick Formatting Problem with ExcelXP

Cynthia_sas — Wed, 26 Dec 2012 03:59:41 GMT

Hi:

Excltags.tpl is the underlying PROC TEMPLATE code that 1) builds the tagset template which 2) instructs ODS how to write the XML markup language, as dictated by the Microsoft specification. Spreadsheet Markup Language is what Microsoft invented in 2002/2003 of Office in order to have an XML definition for a workbook with spreadsheets. Apparently the issue you ran into involved an older version of the tagset template and was fixed by installing/downloading/updating the version to be the most current version of the tagset template.

cynthia

Re: Quick Formatting Problem with ExcelXP

AlexHuang — Thu, 27 Dec 2012 02:29:28 GMT

Thanks a lot for your specifications

Re: Quick Formatting Problem with ExcelXP

AlexHuang — Thu, 27 Dec 2012 03:09:21 GMT

Hi Cynthia,

I have another problem with the row header alignment.

the number fields in the row header is always aligned right.

How can I align them left.

I have tried these methods

replace RowHeader from Header /

just = center

proc tabulate data= missing order=formatted style=[just=center] ;

table &row * {style=[just=left]}

but none of above works.

Please help. Thanks a lot!

Re: Quick Formatting Problem with ExcelXP

Cynthia_sas — Thu, 27 Dec 2012 16:49:28 GMT

Hi:

It's hard to say. The REPLACE statement went away in SAS 9.2, so your style code is suspect unless you are still running SAS 9.1.3. But, you didn't show your whole template, and besides, what you specify in a style template would apply to ALL the row headers the same way. That's why I prefer to use style overrides in my CLASS and CLASSLEV statements, so I can possibly apply one set of style attributes to one variable and another set of style attributes to another variable.

Also, any style adjustments you make on the PROC TABULATE statement or on the TABLE statement ONLY apply to the calculated table cells, shown in pink in the #1 output below. What you specify in the TABULATE or TABLE statements would have no impact on the row title (or row header) area in TABULATE output. Next, you show a macro variable &ROW -- but where is your CLASS statement or statements??? It would be those statements where your overrides would be specified (or the CLASSLEV, depending on whether you wanted to style the headers for the CLASS variables or the class levels for the variables.

Take a look at the attached code and screenshots. BTW, this was a really OLD post that you piggy-backed your question onto, and it was already a long post with several back and forth questions. Your tagging onto the old post made it even longer. I generally recommend that you start a new post and then either reference the older post or just let us know that you've tried a solution posted in an earlier post. Certainly, this second question about row headers was off-topic from the first, older post, which started out as a PROC TABULATE post, but then turned into being about PROC REPORT. I would recommend that if you have another question, that you start a new forum post instead of tacking your question onto an older post.

cynthia

ods tagsets.excelxp file='c:\temp\align_hdr.xml'

style=sasweb;

proc tabulate data=sashelp.class style={background=pink};

class age sex;

var height;

table age*sex all,

height*(min max) / box='1) Override on TABULATE Stmt';

run;

proc tabulate data=sashelp.class;

class age / style={just=c background=pink color=black};

class sex / style={just=c background=yellow color=black};

classlev age / style={just=c};

classlev sex / style={just=l};

var height/style={just=r};

table age*sex all,

height*(min*{s={just=l}} max*{s={just=c}})

/box="2) With Overrides Other Stmts";

keyword all / style={just=c color=cyan};

keyword min max / style={just=l color=red};

run;

ods tagsets.excelxp close;