topic Re: change front of 'by variable' for ods excel output in ODS and Base Reporting

change front of 'by variable' for ods excel output

Mindy_Su — Wed, 20 Sep 2017 19:43:00 GMT

Hi,

I used the following code to get SAS output in Excel. Variable" Location " is already formatted. "By location" is used to create a seperate table for each location. Attached is the example for one of the tables created.You can see everything else (title, data, headers, ect) are in the right style (Times New Roman, 10pt) except for the text "LOCATION=Distance Education". Is there any way to change the style of "LOCATION=Distance Education" so it has Times New Roman as Font_Face and 10pt as Font_Size?

proc template;

define style styles.newstyle;
parent = styles.minimal;

style data/
background = white
foreground = black
font_style = Roman
font_weight = medium
font_size = 10pt
font_face = "Times New Roman"
textalign=center
verticalalign=middle;

style header/
background = white
foreground = black
font_style = Roman
font_weight = medium
font_size = 10pt
font_face = "Times New Roman";

style systemtitle/
background = white
foreground = black
font_style = Roman
font_weight = medium
font_size = 10pt
font_face = "Times New Roman"

style table/
cellspacing=0
cellpadding=7
FRAME=BOX
RULES=all
borderwidth = 1pt

END;
RUN;

ODS TAGSETS.EXCELXP FILE='C:\xxx\ODSOUTPUT.XML'
STYLE=newstyle OPTIONS (sheet_interval='none' sheet_name='TEST' embedded_titles='on' FITTOPAGE='YES' ORIENTATION='LANDSCAPE');

PROC TABULATE DATA=xxx MISSING ORDER=FORMATED;
CLASS FP TERM_CODE location;
TABLE FP=' ' ALL='Total',
TERM_CODE=' '*(N='#'*F=COMMA6. PCTN<FP ALL>='%'*F=COMMA6.1);
FORMAT LOCATION LOCATION. TERM_CODE $TERM.;
BY LOCATION;
run;

ODS TAGSETS.EXCELXP CLOSE;

Re: change front of 'by variable' for ods excel output

ballardw — Wed, 20 Sep 2017 20:03:10 GMT

In Proc tabulate I would not use BY for most things. Instead use that variable in the Page position of the table and style overrides instead of trying to find the esoteric items that may be needed for BY lines in a style

PROC TABULATE DATA=xxx MISSING ORDER=FORMATED;
   CLASS FP TERM_CODE location;
   TABLE Location *style=[style options like font and size],
         FP=' ' ALL='Total',
         TERM_CODE=' '*(N='#'*F=COMMA6. PCTN<FP ALL>='%'*F=COMMA6.1)
   ;
   FORMAT  LOCATION LOCATION. TERM_CODE $TERM.;

run;

Re: change front of 'by variable' for ods excel output

Mindy_Su — Thu, 21 Sep 2017 13:35:59 GMT

Thanks Ballardw for helping. I modified the code based on your suggestion,
please see below. It still does not change the font_face to Times New
Roman. Can you tell what is wrong with the code?

Also is there a way to center the text of location(text in red) above the
table? The table can be found below the code?

Thanks to anyone who can help!

ODS TAGSETS.EXCELXP FILE='C:xxx.XML'
STYLE=newstyle OPTIONS (sheet_interval='none' sheet_name='TEST'
embedded_titles='on' FITTOPAGE='YES' ORIENTATION='LANDSCAPE');
PROC TABULATE DATA=SHARE8 MISSING ORDER=FORMATED;
CLASS FP TERM_CODE LOCATION;
TABLE LOCATION*[STYLE=[FONT_FACE="TIMES NEW ROMAN"]],
FP=' ' ALL='Total',
TERM_CODE=' '*(N='#'*F=COMMA6. PCTN='%'*F=COMMA6.1);
FORMAT GENDER $SEX. AGE OLD. COUNTY2 $COUNTY. CIP_CODE $DEGREE. ATTENDANCE
NEW. LOCATION LOCATION. TERM_CODE $TERM.;
run;
ODS TAGSETS.EXCELXP CLOSE;

LOCATION Distance Education
Fall 2012 Fall 2013 Fall 2014 Fall 2015 Fall 2016 Fall 2017
# % # % # % # % # % # %
Full-time 51 3.2 42 2.5 46 2.9 45 3.2 32 2.1 33 2.2
Part-time 1565 96.8 1606 97.5 1519 97.1 1365 96.8 1457 97.9 1461 97.8
Total 1616 100 1648 100 1565 100 1410 100 1489 100 1494 100

Re: change front of 'by variable' for ods excel output

ballardw — Thu, 21 Sep 2017 15:13:11 GMT

I don't use the page options often so originally supplied something based on anothe example I have.

You would place the options for the Page variable Location in the Class statement

Class location / style=[fontfamily='Times New Roman' just=c];

and remove Location from the other class statement an the style override from the table statement.

I am not sure if you are requesting to have the text for location in red or not. If so add Color=red to the style elements in the class statement.

Notice that the options are Fontfamily FontSize Fontstyle Fontweight and Fontwidth to address font characteristics.