The SAS Output Delivery System and reporting techniques

Quick Formatting Problem with ExcelXP

Reply
Super Contributor
Posts: 358

Quick Formatting Problem with ExcelXP

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?
SAS Super FREQ
Posts: 8,864

Re: Quick Formatting Problem with ExcelXP

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
Super Contributor
Posts: 358

Re: Quick Formatting Problem with ExcelXP

Posted in reply to Cynthia_sas
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!")
SAS Super FREQ
Posts: 8,864

Re: Quick Formatting Problem with ExcelXP

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
Super Contributor
Posts: 358

Re: Quick Formatting Problem with ExcelXP

Posted in reply to Cynthia_sas
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?
SAS Super FREQ
Posts: 8,864

Re: Quick Formatting Problem with ExcelXP

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]
Super Contributor
Posts: 358

Re: Quick Formatting Problem with ExcelXP

Posted in reply to Cynthia_sas
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?
SAS Super FREQ
Posts: 8,864

Re: Quick Formatting Problem with ExcelXP

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]
Super Contributor
Posts: 358

Re: Quick Formatting Problem with ExcelXP

Posted in reply to Cynthia_sas
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.
Contributor
Posts: 24

Re: Quick Formatting Problem with ExcelXP

Posted in reply to Cynthia_sas
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
New Contributor
Posts: 4

Re: Quick Formatting Problem with ExcelXP

Posted in reply to Cynthia_sas

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?

SAS Super FREQ
Posts: 8,864

Re: Quick Formatting Problem with ExcelXP

Posted in reply to AlexHuang

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;

New Contributor
Posts: 4

Re: Quick Formatting Problem with ExcelXP

Posted in reply to Cynthia_sas

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?

SAS Super FREQ
Posts: 8,864

Re: Quick Formatting Problem with ExcelXP

Posted in reply to AlexHuang

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

New Contributor
Posts: 4

Re: Quick Formatting Problem with ExcelXP

Posted in reply to Cynthia_sas

Thanks a lot for your specificationsSmiley Happy

Ask a Question
Discussion stats
  • 16 replies
  • 1024 views
  • 0 likes
  • 4 in conversation