PROC TABULATE using ODS tagsets.msoffice2k

Reply
Contributor
Posts: 27

PROC TABULATE using ODS tagsets.msoffice2k

Producing report in excel using PROC TABULATE & ODS tagsets.msoffice2k, but issue is that negative values are always getting in BOTTOM align without WRAP text, but others values are showing with WRAP text in CELL, please I need your valuable suggestion about this solution..... Actually I want output negative value in Center Align vertically in excel sheet.

Attachment
Grand Advisor
Posts: 10,210

Re: PROC TABULATE using ODS tagsets.msoffice2k

Code use to generate the table? Without that, and preferably some data, it is hard to see what might be causing that.

Contributor
Posts: 27

Re: PROC TABULATE using ODS tagsets.msoffice2k

hi ballardw, thanks for your response, please find the below mentions syntax for.....

PROC TABULATE

DATA=WORK.Append_Table1 STYLE={FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=MEDIUM FONT_STYLE=ROMAN}

    ;

   

    VAR NeedAssessed CurrFinYearAchivement PreFinYearAchivement PercentageChange PercentagechangeNeedAssessed PerFill;

    CLASS StateTypeName_V /    ORDER=Data MISSING;

    CLASS StateName_V /    ORDER=UNFORMATTED MISSING;

    CLASS Achievement / missing ;

    classlev  StateTypeName_V / style=[color=vigb bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=bold just=left background=white cellwidth=150];

    classlev  StateName_V / style=[color=vigb bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 background=white just=left cellwidth=150];

classlev  Achievement / style=[color=vigb bordercolor=vigb background=#C0DCA7 FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN];

 

TABLE

        StateTypeName_V={LABEL=' ' STYLE={color=vigb bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#C0DCA7

cellwidth=150 }} *StateName_V={LABEL=' ' STYLE={color=vigb bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#C0DCA7

cellwidth=150 }}

         ,

         (NeedAssessed ={LABEL="Need Assessed  &YEAR1-&YEAR2 " STYLE={color=vigb bordercolor=vigb FONT_FACE='Verdana'

FONT_SIZE=1 FONT_WEIGHT=BOLD VJUST=TOP FONT_STYLE=ROMAN BACKGROUND=#C0DCA7 cellwidth=120 }}*

Sum ={LABEL="(A)" STYLE={color=vigb  bordercolor=vigb FONT_FACE='Verdana'

FONT_SIZE=1 FONT_WEIGHT=BOLD VJUST=TOP FONT_STYLE=ROMAN BACKGROUND=#C0DCA7 cellwidth=120}}*f=TonsNum.*{STYLE=

{bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 foreground=temp_grey. BACKGROUND=temp_grey.

font_weight=datafmt. FONT_WEIGHT=MEDIUM FONT_STYLE=ROMAN JUST=right }} 

Achievement={LABEL=' ' STYLE={color=vigb bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN vJUST=top cellwidth=390

BACKGROUND=#C0DCA7 }}*

(  CurrFinYearAchivement ={LABEL="&YEAR1-&YEAR2" STYLE={color=vigb bordercolor=vigb FONT_FACE='Verdana'

FONT_SIZE=1 FONT_WEIGHT=BOLD

FONT_STYLE=ROMAN BACKGROUND=#C0DCA7 cellwidth=120 }}* Sum ={LABEL='(B)' STYLE={color=vigb  bordercolor=vigb

FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=BOLD VJUST=TOP FONT_STYLE=ROMAN BACKGROUND=#C0DCA7 

cellwidth=120}}*f=TonsNum.* {STYLE=

{bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 FONT_STYLE=ROMAN foreground=temp_white. BACKGROUND=temp_white.

font_weight=datafmt. FONT_WEIGHT=MEDIUM JUST=right }}

PreFinYearAchivement ={LABEL="&YEAR3-&YEAR4" STYLE={color=vigb bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1

FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#C0DCA7 cellwidth=120 }}*

Sum ={LABEL='(C)' STYLE={color=vigb  bordercolor=vigb FONT_FACE='Verdana'

FONT_SIZE=1 FONT_WEIGHT=BOLD VJUST=TOP FONT_STYLE=ROMAN BACKGROUND=#C0DCA7 cellwidth=120}}*f=TonsNum.* {STYLE={bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1

FONT_STYLE=ROMAN foreground=temp_white. BACKGROUND=temp_white. font_weight=datafmt. FONT_WEIGHT=MEDIUM JUST=right }}

PercentageChange  ={LABEL='% Change' STYLE={color=vigb bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=BOLD

FONT_STYLE=ROMAN BACKGROUND=#C0DCA7 cellwidth=120 }}* Sum ={LABEL='(D=((B-C)/C)*100)' STYLE={color=vigb  bordercolor=vigb

FONT_FACE='Verdana'

FONT_SIZE=1 FONT_WEIGHT=BOLD VJUST=TOP FONT_STYLE=ROMAN BACKGROUND=#C0DCA7 cellwidth=150}}*f=12.1*

{STYLE={HTMLSTYLE="VND.MS-EXCEL.NUMBERFORMAT:#0.0" bordercolor=vigb FONT_FACE='Verdana'

FONT_SIZE=1 FONT_WEIGHT=MEDIUM foreground=temp_white. BACKGROUND=temp_white. font_weight=datafmt.

FONT_STYLE=ROMAN JUST=right }})       

/*PercentagechangeNeedAssessed  ={LABEL='% Achvt of need assessed' STYLE={color=vigb bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#C0DCA7 */

/*cellwidth=100 }}* Sum ={LABEL=''}*f=12.1* {STYLE={HTMLSTYLE="VND.MS-EXCEL.NUMBERFORMAT:#0.0" bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1  FONT_WEIGHT=MEDIUM FONT_STYLE=ROMAN JUST=right }}*/

PercentagechangeNeedAssessed  ={LABEL='% Achievement of need assessed'

STYLE={color=vigb bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=BOLD VJUST=TOP FONT_STYLE=ROMAN

BACKGROUND=#C0DCA7 cellwidth=120 }}* Sum ={LABEL='(E=(B/A)*100)' STYLE={color=vigb  bordercolor=vigb FONT_FACE='Verdana'

FONT_SIZE=1  FONT_WEIGHT=BOLD VJUST=TOP FONT_STYLE=ROMAN BACKGROUND=#C0DCA7  cellwidth=120}}*f=TonsNum.* 

{STYLE={HTMLSTYLE="VND.MS-EXCEL.NUMBERFORMAT:#0.0" bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1

foreground=temp_grey. BACKGROUND=temp_grey. FONT_WEIGHT=datafmt. FONT_WEIGHT=MEDIUM

FONT_STYLE=ROMAN JUST=right }}

/ BOX={LABEL='State/UT/Agency' STYLE={color=vigb bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#C0DCA7 VJUST=Center Cellwidth=300}} ;;

RUN;

Grand Advisor
Posts: 10,210

Re: PROC TABULATE using ODS tagsets.msoffice2k

I wonder if part of the cause of your appearance issue is changing back and forth from VJUST=Top to none. I don't see any actual negative values appearing different in your example. Also, since your example doesn't have a label of LABEL="Need Assessed  &YEAR1-&YEAR2" then the code posted didn't create the table example displayed.

One hint for maintaining code with lots of style overrides like this: Use a macro value or two for the common elements so it is easier to see differences. For instance you have many references to :

color=vigb bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN

you could have a macro variable

%let commonstyle=color=vigb bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1 FONT_WEIGHT=BOLD FONT_STYLE=ROMAN;

and then in your style overrides

style={&commonstyle <other style overrides here>}

This approach leads to avoiding some things such as:

TonsNum.* {STYLE={bordercolor=vigb FONT_FACE='Verdana' FONT_SIZE=1

FONT_STYLE=ROMAN foreground=temp_white. BACKGROUND=temp_white. font_weight=datafmt. FONT_WEIGHT=MEDIUM JUST=right }}

where you have two font_weight options.

Ask a Question
Discussion stats
  • 3 replies
  • 207 views
  • 0 likes
  • 2 in conversation