topic Table and Charts -ODS TAGSETS.MSOFFICE2K_X in ODS and Base Reporting

Table and Charts -ODS TAGSETS.MSOFFICE2K_X

Aman4SAS — Wed, 06 Aug 2014 13:24:07 GMT

Hi can we have table and charts in below pattern?

Table Chart1

Chart2

I am getting result as below:

Table - Chart - Chart

Re: Table and Charts -ODS TAGSETS.MSOFFICE2K_X

Cynthia_sas — Wed, 06 Aug 2014 23:07:26 GMT

HI:

Without seeing your code, it is nearly impossible to comment on what you're getting and/or how to fix it. Can you make a sample program that uses SASHELP datasets to generate a TABLE and 2 Charts and show ALL your code, including the ODS statements that you're using? Have you checked the documentation for MSOFFICE2K_X, particularly, the section on creating paneled output? Base SAS: The MSOffice2K_x Tagset Adds Options to the MSOffice2K Tagset For example, how are you using the PANELCOLS= suboption? It seems to me that what you describe would be PANELCOLS=2 to start out with. Also important to see will be whether you are using SAS/GRAPH or ODS GRAPHICS and the size constraints you specify for each chart.

Or, you can open a track with Tech Support, they can look at ALL your code and you can send them your data and they can help you figure out the resolution.

cynthia

Re: Table and Charts -ODS TAGSETS.MSOFFICE2K_X

Aman4SAS — Fri, 08 Aug 2014 10:19:51 GMT

Hi,

I have attached my sample code and dataset along with this post.

I have to create 50 file along with multi sheet , along with related charts.

i using this code but now its creating two separate files one for chart and one for table. how can i resolve this problem by using SAS or SAS has its own limitation?

Re: Table and Charts -ODS TAGSETS.MSOFFICE2K_X

Cynthia_sas — Fri, 08 Aug 2014 15:28:09 GMT

Hi:

I am getting ready to teach, so I don't have a lot of time to devote to this. But right off the bat, I see an issue. In your code, you have:

ods tagsets.msoffice2k file='I:\anuj\temp folder\show_line_mso1k.xls' style=analysis ;

ods tagsets.msoffice2k_x options(panelcols="2") ;

which is incorrect. you can't open MSOFFICE2K and then only use PANELCOLS for ODS MSOFFICE2K_X, they are 2 different destinations ODS MSOFFICE2K does NOT have the same suboptions as any other destination. First, you have to decide what destination you're using and then you have to invoke the destination and use the correct suboption designed for that destination. You should have:

ods tagsets.msoffice2k_x file='I:\anuj\temp folder\show_line_mso2k_x.xls' style=analysis ;

ods tagsets.msoffice2k_x options(panelcols="2") ;

You might want to consider opening a track with Tech Support on this.

cynthia

also, to get rid of the . for missing, you do not need a custom format, you can simply use an OPTIONS statement:

odptions missing=' ';

Re: Table and Charts -ODS TAGSETS.MSOFFICE2K_X

Cynthia_sas — Fri, 08 Aug 2014 15:31:43 GMT

Also, HEADLINE and HEADSKIP are ignored by ALL ODS destinations except for the LISTING window, they are totally ignored in your code so you may as well not use those options.

And, if you are using an HTML-based destination, then your use of TAGATTR will no longer work. You need to use HTMLSTYLE= methods for HTML destinations. TAGATTR method of supplying a format only applies to TAGSETS.EXCELXP. (Here's a reference that discusses the differences between HTMLSTYLE and TAGATTR http://support.sas.com/resources/papers/proceedings11/266-2011.pdf )

And, since you are making an HTML file, then you will also probably need PATH= and GPATH= in your code because HTML files are created with IMG tags in the HTML and those must have the right value in order to display in the output:

ods tagsets.msoffice2k_x path='c:\temp' (url=none)

gpath='c:\temp' (url=none)

file='show_line_mso2kx.xls' style=analysis ;

ods tagsets.msoffice2k_x options(panelcols="2") ;

cynthia

Re: Table and Charts -ODS TAGSETS.MSOFFICE2K_X

Aman4SAS — Mon, 11 Aug 2014 12:59:17 GMT

HI Cynthia,

I have tried all as u suggested but still i m not getting desired output.

Please ignore proc report and proc gplot contents , i m only worried about output. need to creat creat chart beside table and multiple sheet.

Please find attached code , table and output file.

Re: Table and Charts -ODS TAGSETS.MSOFFICE2K_X

Cynthia_sas — Mon, 11 Aug 2014 14:09:21 GMT

Hi:

I am teaching today and unable to look at your code. However, here's a test I ran based on your previous posting that produced the following results (see screen shot) -- with the table next to the graph. If you find that you need more help, then I would suggest that you open a track with Tech Support.

Cynthia

ods tagsets.msoffice2k_x path='c:\temp' (url=none)
                        gpath='c:\temp' (url=none)
    file='show_line_mso2kx.xls' style=analysis ;
ods tagsets.msoffice2k_x options(panelcols="2") ;
proc report data = anuj.TableA nowd   split= "*"
style(report)=[cellspacing=2 borderwidth=1 bordercolor=blue frame=void ]
style(header)=[color=black
               fontsize =3 textalign=l fontweight=bold fontfamily = 'Times New Roman']
style(column)=[color=black
               fontfamily='Times New Roman' just=center fontsize=2 ]
style(lines)=[color=black backgroundcolor=cxdddddd
               font_size=8pt just=l ]
style(summary)=[color=cx3e3d73 backgroundcolor=cxaeadd9
                fontfamily='Times New Roman' fontsize=3 textalign=r];
column ("Table A: State Labor Demand, Selected States, Seasonally Adjusted" (Area SA_tot_undup&currmob. Mom_tot_undup sd_tot_undup&prevmob. blank ));
DEFINE AREA / "**LOCATION" style(Header)=[just=center cellwidth=2 in] style(column)=[just=l];
define sa_tot_undup&currmob./ "Total ads~{super 1}*(Thousands)* currmon"
       style(Header)=[just=center fontweight= bold cellwidth=1 in] style(column)=[htmlstyle="mso-number-format:'#,##0.0'"];
define mom_tot_undup/ "M-O-M*Change*currmon - prevmon"
       style(Header)=[just=center fontsize=2 cellwidth=1.2 in] style(column)=[htmlstyle="mso-number-format:'#,##0.0'"];
Define sd_tot_undup&prevmob./"Supply/*Demand Rates~{super 2}*status"
       style(Header)=[just=center cellwidth=1.2 in] style(column)=[htmlstyle="mso-number-format:'#.00'"];
define blank/ computed "Recent**Trend~{super 3}"
       style(Header)=[just=center cellwidth=.8 in] style(column)=[just=center cellwidth=.8 in backgroundcolor=yellow];
compute blank;
blank=.;
if Area = "United States" and _break_=' ' then
      call define(_col_, "style",
                  "style=[backgroundcolor=yellow]");

endcomp;
compute Area;
      if Area in ("NORTHEAST","SOUTH","MIDWEST","WEST") and _break_=' ' then
      call define(_row_, "style",
                  "style=[backgroundcolor=yellow
                          fontfamily='Times New Roman'
                          fontweight=bold]");
if Area = "United States" and _break_=' ' then
      call define(_row_, "style",
                  "style=[backgroundcolor=cxaeadd9
                          fontfamily='Times New Roman'
                          fontweight=bold]");
   endcomp;

compute after ;
line "(*ESC*)S={ textalign= left Font_weight=bold fontsize=8pt fontfamily='times new roman'}The Conference Board - All rights reserved. (*ESC*)S={}";
line "(*ESC*)S={indent=1in font_size=1 fontfamily='times new roman'}1. Total ads are all unduplicated ads appearing during the reference period. This figure includes ads from the previous months that have been reposted as well as new ads.(*ESC*)S={}";
line "(*ESC*)S={indent=1in font_size=1 fontfamily='times new roman'}2. Supply/Demand rate is the number of Unemployed persons divided by the number of total ads and reflects the latest month for which unemployment data is available.(*ESC*)S={}";
line "(*ESC*)S={indent=1in font_size=1 fontfamily='times new roman'}3. Recent trend is The Conference Board Economists' indication of the direction of the overall trend in online job demand from the date indicated (month/year).(*ESC*)S={}";
line "~{style[color=purple fontfamily='Courier New']Line 1 &l1 &l1 &l1 (END1)}";

      line "~{style[color=red font_style=roman fontfamily='Helvetica' height=1in]Line 2 &l2 &l2 &l2 (END2)}";
endcomp;
* title 'Table A: State Labor Demand, Selected States, Seasonally Adjusted';
         title;
   FORMAT SA_TOT_UNDUP&currmob. 7.1 MOM_TOT_UNDUP 7.1 SD_TOT_UNDUP&prevmob. 7.2;
run;
quit

goptions htext=8pt hsize=5pct vsize=5pct htitle=15pt gsfname=chrt;
axis1 label=("States") value=(angle=45 h=8pt);
axis2 label=('HWOL Ads' justify=center);

/* Add a title to the graph */
title1 'HWOL Ads - States';

/* Create the graph */

proc gchart data=anuj.tablea;

vbar area / sumvar=sa_tot_undup201407 maxis=axis1 raxis=axis2 nostats outside=sum ;
where areatype not in ("nation","region");
format sa_tot_undup201407 comma8.;
run;

quit;
ods _all_ close;

Re: Table and Charts -ODS TAGSETS.MSOFFICE2K_X

Aman4SAS — Tue, 12 Aug 2014 06:24:00 GMT

Thanks. I m sure that code will work. Its something wrong with my side.

my getting "WARNING: Tagset TAGSETS.MSOFFICE2K_X not found; the default tagset will be used instead."

in this case i m getting currupted output file which i attached in my previous post.

Can i get rid of this problem at my end i need to purchase something add-on?

Thanks

Re: Table and Charts -ODS TAGSETS.MSOFFICE2K_X

Aman4SAS — Tue, 12 Aug 2014 07:03:49 GMT

Hi , Thanks , Problem has been resolved.

Thanks a lot.