style/formatting disappears when using tagsets.excelxp

[Mgarret]

Hi all--

I had put together a series of frequency tables using ods MSOFFICE2K and proc tabulate. I now have to incorporate the frequency tables into a workbook with several other sheets. The new workbook is produced tagsets.excelxp.  Each of the other sheets uses proc report to generate outputs – outputs different than frequency tables.  When I add in the additional sheet, all the style/formatting, which worked when I used MSOFFICE2K, disappears. Is there a way to preserve the look of the sheet now that I’ve incorporated it into a workbook which is generated using tagsets.excelxp?  

Thanks for any help!

odstagsets.excelxp file=" Z:\SAS\SASCODE\Test.xls"

style=minimal

options(sheet_interval='none'  ORIENTATION='Landscape'

AUTOFIT_HEIGHT='yes'FROZEN_HEADERS='5' embedded_titles='yes'

PAGE_ORDER_ACROSS='yes'COLUMN_REPEAT='7' ROW_REPEAT='5'sheet_name="Sheet1");

Proc report, Proc report Proc report Proc report …………….

odstagsets.excelxp style=minimal options(sheet_interval='none'  ORIENTATION='Landscape'

AUTOFIT_HEIGHT='yes'FROZEN_HEADERS='4' embedded_titles='yes'

PAGE_ORDER_ACROSS='yes'COLUMN_REPEAT='7'ROW_REPEAT='4'  sheet_name=" Sheet2");

Proc report Proc report Proc report Proc report Proc report …………….

And then a few more sheets….

....and then new sheet

FOOTNOTE;

odstagsets.excelxp

options(sheet_interval='none'  ORIENTATION= "portrait"

AUTOFIT_HEIGHT='yes'

FROZEN_HEADERS='no'

embedded_titles='yes'

sheet_name="Sheet3"

Print_Footer="Page: &P"  ods escapechar='^';

None ofthe style/formatting is generating

proc tabulate data=have

(where=(questionin('Q16')))

style=[background=whiteforeground=black  font_face='ArialNarrow' font_size=10.5pt just=c cellwidth=.6in];

title1 "^S={font_face='ArialNarrow' font_weight=bold font_size=12pt}

SAFETYINDICATOR";

title2 "^S={font_face='ArialNarrow' font_weight=bold font_size=12pt}

SAFETYSub-indicator A: Immediate/Impending Child Safety  Concerns";

classquestion program_name

/ style={font_face='ArialNarrow' font_weight=bold font_size=11pt cellheight=50 };

classResponce1 / order=formatted

style={font_face='ArialNarrow' font_weight=bold font_size=11pt cellwidth=.6in};

classlevquestion

/style=[font_face='ArialNarrow' font_size=11pt];

classlev  program_name

/style={font_face='ArialNarrow' font_size=10pt cellwidth=.6in cellheight=.4injust=l};

classlev

Responce1

/style={font_face='ArialNarrow' font_size=11pt  };

tableQuestion=' ',

program_name=' '

all='OverallAgency Totals',

Responce1=''*(n='Count'*f=best8. pctn='%'*f=p.)

n='CountTotal' pctn='% Total' *f=p.

/style={font_face='ArialNarrow' font_size=11pt cellwidth=.6in}

row=float 

box={label='Count^{newline1}Overall^_%'

style={font_face='ArialNarrow' font_size=10pt font_weight=bold cellwidth=.6in}};

formatquestion $ques.;

formatresponce1 $resp.;

keywordn pctn  /

style=[font_weight=boldforeground=black  font_face='ArialNarrow' font_size=10pt just=c];

keywordall/ style=[font_weight=bold foreground=black font_face='Arial Narrow' font_size=10pt just=l];

run;

title;

and several more tables…

[Cynthia_sas]

HI:

  I notice you seem to have some cut and paste issues (missing spaces, etc), so I don't know whether this is your actual code or a paste error. This statement seems wrong:

odstagsets.excelxp

options(sheet_interval='none' ORIENTATION= "portrait"

AUTOFIT_HEIGHT='yes'

FROZEN_HEADERS='no'

embedded_titles='yes'

sheet_name="Sheet3"

Print_Footer="Page: &P" ods escapechar='^';

because ODS ESCAPECHAR is not one of the TAGSETS.EXCELXP option -- and, I don't see a close parentheses for the options list.

I think you should have something like this:

ods tagsets.excelxp

      options(sheet_interval='none' ORIENTATION= "portrait"

                 AUTOFIT_HEIGHT='yes'

                 FROZEN_HEADERS='no'

                 embedded_titles='yes'

                 sheet_name="Sheet3"

                 Print_Footer="Page: &P" );

ods escapechar='^'; /* this should be a separate statement */

Can you verify that you have a separate ODS ESCAPECHAR statement? And that you have the proper closing parentheses for your OPTIONS list??

cynthia

[Mgarret]

Hi Cynthia--

yes that was a cut and paste issue. I do have this:

ods tagsets.excelxp

      options(sheet_interval='none' ORIENTATION= "portrait"

                 AUTOFIT_HEIGHT='yes'

                 FROZEN_HEADERS='no'

                 embedded_titles='yes'

                 sheet_name="Sheet3"

                 Print_Footer="Page: &P" );

ods escapechar='^';

I don't know what the deal is.

[Cynthia_sas · Accepted solution]

Hi:

When I used a (very) simplified version of your code (below), the PROC TABULATE output does show the modified style attribute changes..even with the Minimal style as the base style (created with SAS 9.3 and using Excel 2010). I have a hard time discerning font changes, so I threw in some color changes just so I could quickly see what was happening and if the style changes were being used.

Since you have a PAGE dimension in your TABLE, you cannot "touch" the PAGE variable using CLASSLEV -- you'd have to move the PAGE variable into the BOX area and then change the style of the BOX area -- generally, that is how the BOX area is used (with BOX=_PAGE_) for 3 dimensional tables. (For example, in my example, SEX is the PAGE dimension variable, but there would be no point using a CLASSLEV statement for that variable in Sheet 3. If you look at the code for Sheet4, you will see how I made the PAGE dimension text bold and gray on a purple background.) Compare Sheet 3 with Sheet 4 and 5 -- you will see that the CLASSLEV style override in Sheet5 for the page variable has no impact.

If you run my code and you do not see style changes, then you probably should open a track with Tech Support.

cynthia

***the code;

proc format;

  picture pct

    low-high="009.0%";

run;

ods tagsets.excelxp file="c:\temp\Test_min.xml"

    style=minimal

    options(sheet_interval='none'

            ORIENTATION='Landscape'

            embedded_titles='yes'

            sheet_name="Sheet1");

  proc report data=sashelp.shoes nowd

       style(header)={background=pink foreground=black};

    where product contains 'Dress' and

          region contains 'Europe' and stores gt 10;

  run;

  proc report data=sashelp.shoes nowd

       style(header)={background=yellow  foreground=black};

    where product contains 'Casual' and

          region contains 'Europe' and stores gt 10;

  run;

ods tagsets.excelxp style=minimal

    options(sheet_interval='none'

            ORIENTATION='Landscape'

            embedded_titles='yes'

            sheet_name="Sheet2");

  proc report data=sashelp.shoes nowd

       style(header)={background=yellow foreground=black};

    where product contains 'Boot' and

          region contains 'Europe' and stores gt 10;

  run;

title; FOOTNOTE;

ods tagsets.excelxp

    options(sheet_interval='none'  ORIENTATION= "portrait"

            embedded_titles='yes'

            sheet_name="Sheet3"

            Print_Footer="Page: &P"  );

ods escapechar='^';

proc tabulate data=sashelp.class

     style=[background=white foreground=black

            font_face='Arial Narrow' font_size=10.5pt

            just=c];

title1 bold f="Arial Narrow" h=12pt "SAFETY INDICATOR";

title2 bold f="Arial Narrow" h=12pt "SAFETY Sub-indicator A";

class age sex /

      style={font_face='Arial Narrow' font_weight=bold

             font_size=11pt};

classlev age

        /style=[font_face='Arial Narrow' font_size=11pt

               background=yellow foreground=black ];

table sex=' ',

      age=' ' all='Overall Agency Totals',

      n='Count Total' pctn='% Total' * f=pct.

     /style={font_face='Arial Narrow' font_size=11pt}

      box={label='Count ^{newline 1} Overall^_%'

      style={font_face='Arial Narrow' font_size=10pt

             font_weight=bold

             background=pink foreground=black}};

keyword n pctn  /

        style=[font_weight=bold foreground=black

               font_face='Arial Narrow' font_size=10pt

               background=lightgreen just=c];

keyword all/ style=[font_weight=bold foreground=black

                    font_face='Arial Narrow'

                    font_size=10pt

                    background=cyan just=l];

run;

title;

ods tagsets.excelxp

    options(sheet_interval='proc'

            ORIENTATION= "portrait"

            embedded_titles='yes'

            sheet_name="Sheet4");

proc tabulate data=sashelp.class;

title 'See Change in Page Dimension';

  class age sex;

  var height;

  table sex='Gender',age,height/

        box={label=_page_ s={background=purple font_face='Courier New'

                             foreground=cxdddddd font_weight=bold

                             font_size=14pt}};

run;

ods tagsets.excelxp

    options(sheet_interval='proc'

            ORIENTATION= "portrait"

            embedded_titles='yes'

            sheet_name="Sheet5");

proc tabulate data=sashelp.class;

title 'CLASSLEV does not work for PAGE var';

  class age sex;

  classlev sex / s={background=yellow font_face='Courier New'

                    font_weight=bold font_size=14pt};

  var height;

  table sex='Gender',

        age,

        height/

        box={label='Other' s={background=purple font_face='Courier New'

                             foreground=cxdddddd font_weight=bold

                             font_size=14pt}};

run;

ods _all_ close;

[Mgarret]

Thanks Cynthia! This is great. You have been incredibly helpful.