ODS Tagset.TableEditor option Issues discussion

[jay_q]

Hello all,

I'm new to sas tagset table editor and I'm amazed with what it can perform. Just some issues when running options below. Open for any discussions and suggestions. the sample code is in the end.

Problem1: when use option sheet_name=" Excel ", can't name raw data, pivot chart sheets separately.

Problem2: when use option  pivotdata_fmt="[blue] $#,###.##~$#,###.##",
the output excel data doesn't return the desired color and $ format.

Problem3: When use option image_path="C:\logo.jpg" url="none" ,
it won't show the logo picture in html email file.

Problem4: When use option web_tabs="tab1,tab2" with option pivotcharts="yes"
It will only create the pivot chart for output in tab1 but not tab2 after export.

Problem5: When use option autofilter="yes", it conflicts with option PRINT_DIALOG="yes" and PRINT_ZOOM="yes".
Plus, any way we can hide the autofilter when print the html output because it looks messy in the printout?

Samle code:

ods tagsets.tableeditor   file="//prod/test2222.html"
style=sasweb 
options(
highlight_color="pink"
scrollbar_color="beige"
title_style="normal"
image_path="C:\logo.jpg" url="none"
image_just="left"
zoom_toggle="yes"
sort="yes" sort_arrow_color="brown"
fit2page="yes"
header_bgcolor="darkblue"
header_fgcolor="white"
rowheader_bgcolor="white"
data_bgcolor="white"
frozen_headers="yes"
title_size="10pt"       
PRINT_DIALOG="yes"
PRINT_ZOOM="yes"
button_text="Excel Pivot Export"
pivotrow = "1"
pivotcol = "3"
pivotdata = "13"
pivotdata_stats = "sum"

autofilter="yes"
sheet_name=" Pivot"
pivotdata_fmt="[blue] $#,###.##~$#,###.##"
pivot_format="table10"
web_tabs="tab1,tab2"
chart_type="linemarkers"

excel_autofilter="yes"
pivotcharts="yes"
chart_title="Pivot chart"
auto_format="color2"
);

proc means data=sashelp.class;
run;

proc report data=sashelp.orsales ;
title "Sorting data";
define year / display;
rbreak after / summarize;
run;

ods tagsets.tableeditor close;

[Chevell_sas · Accepted solution]

Jay,

Take a look at the below.

Problem1: when use option sheet_name=" Excel ", can't name raw data, pivot chart sheets separately

A)    A) You are correct, the pivot table currently uses the prefix of the raw data sheet. This is something that can be added.

     Problem2: when use option  pivotdata_fmt="[blue] $#,###.##~$#,###.##",
the output excel data doesn't return the desired color and $ format.

B)   A) This occurs when you use the column numbers to represent the columns rather than the column names when using the pivot table options. This should work the same using either method. The below is an example which uses the  column names should provide a short term workaround.                                                                                                                           

ods tagsets.tableeditor file="c:\temp.html"

style=sasweb

options(

button_text="Excel Pivot Export"

pivotrow = "Age"

pivotcol = "Sex"

pivotdata = "Weight"

pivotdata_stats = "sum"

sheet_name=" Pivot"

pivotdata_fmt="[blue]$#,###.##"

pivot_format="table10"

chart_type="linemarkers"

excel_autofilter="yes"

pivotcharts="yes"

chart_title="Pivot chart"

auto_format="color2"

);

proc report data=sashelp.class nowd ;

column name age sex height weight;

title "Sorting data";

define year / display;

rbreak after / summarize;

run;

ods tagsets.tableeditor close;

Problem3: When use option image_path="C:\logo.jpg" url="none" ,
it won't show the logo picture in html email file.

A)    A) In the current release images are not embedded into the HTML file, therefore it is looking for the image on your C:\  drive. You can store the images on a web server and refer to it using HTTP addressing.

Problem4: When use option web_tabs="tab1,tab2" with option pivotcharts="yes"
It will only create the pivot chart for output in tab1 but not tab2 after export.

A)    The readme file for the tagset download  should mention some of the interactions between the options which it does not currently  do. Some options don't  really work well together. For instance this Web_tabs= option will export only the tab that has focus which is why you see the current behavior.

.

Problem5: When use option autofilter="yes", it conflicts with option PRINT_DIALOG="yes" and PRINT_ZOOM="yes".  Plus, any way we can hide the autofilter when print the html output because it looks messy in the printout?

A)    A) These items have been noted and will be addressed.

S

[jay_q]

Thanks for your detailed reply , Chevell.

Since the column name in SAS EG is  different than one in Base SAS, the column name such as: pivotdata = "Weight loss" will not work in the option either except I change the name to weigh_loss  which is not really optimal solution.

plus , It did not work using

image_path="C:\logo.jpg"  url="http://upload.wikimedia.org/wikipedia/en/thumb/d/de/Capital_One_Financial_logo.svg/249px-Capital_One..."

instead of image_path="C:\logo.jpg" url="none" ,

[Chevell_sas]

The names that you specify in the pivot table options need to match the column names in the output. If they are the same, then this should work fine. The pivot table options allow for spaces in the field name for either the variable or label. As along as the options match the output field name this should work. Please let me know if this is not the case.

For the other issue, the path that you have displayed with the URL= need to be added to the image_path= option such as the below.

image_path="http://upload.wikimedia.org/wikipedia/en/thumb/d/de/Capital_One_Financial_logo.svg/249px-Capital_One..."

[jay_q]

Hi Chevell,

Thanks for the suggestion on image_path, it worked. However, I'm still getting following error when I use the column names with space(such as "employee name" in SAS EG) instead of column numbers.  

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E; MS-RTC LM 8; InfoPath.1)

Timestamp: Fri, 24 May 2013 20:16:15 UTC

Message: Syntax error

Line: 4344

Char: 67

Code: 0

URI: file:///C:/Documents%20and%20Settings/GYB420/Local%20Settings/Temp/scp55752/prod/user2/bank/consumer/non_npi/GYB420/Incentives/access%20output/test4.html

[Chevell_sas]

You might want to open a tracking entry with technical support and send the supporting files regarding the errors received when the columns has embedded spaces.