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…
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;
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
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.
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;
Thanks Cynthia! This is great. You have been incredibly helpful.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.