ods tagsets.excelxp - v1.116 - Column Height Issue

Smijoss — Tue, 15 Apr 2014 22:34:12 GMT

My Server currently has the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12). All the data and columns in the output sheet look fine, except I am not able to do vertical alignment on cells.

This Version is not supporting the Vjust option to vertically align the cell to middle.

Now when I download the newer tagset from sas site V1.130 or V1.116, I get a wierd issue. the cell height of certain cells that have more text in it have different height. Please see attachment

It works fine when i use autofit_height='yes' , but if i use ( autofit_height='no' row_heights='15,15,0,50,0,0,0' ) to control title height it is not forcing the column height.

Is there and option to force the cell height ?

Re: ods tagsets.excelxp - v1.116 - Column Height Issue

ballardw — Tue, 15 Apr 2014 23:05:12 GMT

Do you have text to either the left or right that is wrapping in a cell or forced to two lines? Excel may be honoring that more than your settings.

And what procedure are you using to create the output? You may have options of inline style settings using tagattr to control things a bit better.

Re: ods tagsets.excelxp - v1.116 - Column Height Issue

Cynthia_sas — Tue, 15 Apr 2014 23:53:03 GMT

Hi:

It is nearly impossible to comment with just a screen shot of the partial output -- no code and no data. Other things on the row could be forcing the wrapping. Since it looks like you have quite a bit of color added to the output, you might be using a STYLE template, you might be using STYLE overrides with PROC PRINT, PROC REPORT or PROC TABULATE and each of those procedures have different methods for working with cells. However, you said to see the attachment, to view how title height was being handled, but your screen shot does not show the SAS title, only the values and summary lines for each row. Do you have the embedded_titles suboption set to 'yes'?

One way to change the row height is actually by changing the cell width, especially for columns with long text strings and the other way to change the row height is to use the ExcelXP sub-options and a third way to impact the row height, which may not work in all destinations, is to use the HEIGHT= style override. Without seeing your code and/or your data, it's impossible to tell whether it is the ExcelXP tagset template or your code or your style or your data that is causing the issue.

cynthia

Re: ods tagsets.excelxp - v1.116 - Column Height Issue

Smijoss — Tue, 15 Apr 2014 23:53:09 GMT

Hi Ballardw,

I did check that i didnt find wrapping on any cell in that row. If that was the issue then autofit_height='yes' would have given the same result.

Attached is the file that was created from the program

Heres the code too -

DATA KPIF_NET_GROWTH;

SET KPIF_NET_GROWTH;

JAN=1; FEB=1; MAR=1; QTR1=1;

APR=1; MAY=1; JUN=1; QTR2=1;

JUL=1; AUG=1; SEP=1; QTR3=1;

OCT=1; NOV=1; DEC=1; QTR4=1;

RUN;

DATA KPIF_REG_VAL ;

SET KPIF_REG_VAL ;

JAN=1; FEB=1; MAR=1;

APR=1; MAY=1; JUN=1;

JUL=1; AUG=1; SEP=1;

OCT=1; NOV=1; DEC=1;

RUN;

%MACRO PROC_REPORT_NET_GROWTH;

ods tagsets.excelxp options(sheet_interval = 'none' sheet_name='NEW GROWTH');

%DO K = 1 %TO &REGION_COUNT.;

%LET REG = %SYSFUNC(SCAN(&REGION_VALS.,&K.));

%LET REGION=%SYSFUNC(SUBSTR(&REG.,1,2));

%LET SUB_REGION = %SYSFUNC(SCAN(&REG.,2,'_'));

%LET Region_Name = %SYSFUNC(PUTC(&REG.,$RGN.));

%IF %LENGTH(&SUB_REGION.) NE 0 %THEN %DO;

%LET Region_Name = %STR(&REGION. &Region_Name.);

%END;

%DO J = 1 %TO &EXCHANGE_COUNT. ;

%LET EXCHANGE = %SYSFUNC(SCAN(&EXCHANGE_VALS.,&J.));

%let Exchange_Desc = %SYSFUNC(PUTC(&EXCHANGE.,$EXG.));

PROC REPORT DATA=KPIF_NET_GROWTH

(WHERE=(REGION="&REG." AND EXCHANGE="&EXCHANGE."))

style(header)={background=LIGHTYELLOW foreground=black font_size=10pt BORDERWIDTH=1.0}

style(column)={font_size=9pt foreground=black background=LIGHTCYAN BORDERWIDTH=1.0};

options missing=0;

COLUMNS (NET_GROWTH_DETAILS )

("First Quarter &year" JAN FEB MAR QTR1)

("Second Quarter &year" APR MAY JUN QTR2)

("Third Quarter &year" JUL AUG SEP QTR3)

("Fourth Quarter &year" OCT NOV DEC QTR4);

DEFINE NET_GROWTH_DETAILS / DISPLAY 'NET GROWTH DETAILS' STYLE(column)={CELLWIDTH=2.2 in};

DEFINE JAN / ANALYSIS STYLE(column)={CELLWIDTH=.7 in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE FEB / ANALYSIS STYLE(column)={CELLWIDTH=.7 in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE MAR / ANALYSIS STYLE(column)={CELLWIDTH=.7 in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE APR / ANALYSIS STYLE(column)={CELLWIDTH=.7 in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE MAY / ANALYSIS STYLE(column)={CELLWIDTH=.7 in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE JUN / ANALYSIS STYLE(column)={CELLWIDTH=.7 in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE JUL / ANALYSIS STYLE(column)={CELLWIDTH=.7 in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE AUG / ANALYSIS STYLE(column)={CELLWIDTH=.7 in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE SEP / ANALYSIS STYLE(column)={CELLWIDTH=.7 in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE OCT / ANALYSIS STYLE(column)={CELLWIDTH=.7 in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE NOV / ANALYSIS STYLE(column)={CELLWIDTH=.7 in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE DEC / ANALYSIS STYLE(column)={CELLWIDTH=.7 in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE QTR1 - QTR4 / ANALYSIS STYLE(column)={CELLWIDTH=.7 in};

Rbreak after / ol summarize

style=[Just=C background=orange BORDERCOLOR=black

height=12pt font_size=10pt font_weight=bold];

compute after;

NET_GROWTH_DETAILS = 'TOTAL';

endcomp;

Title1 justify=C height=13pt color=blue font=Arial bold

"Region: &Region_Name. Exchange : &Exchange_Desc.";

run;quit;

%END;

%MEND PROC_REPORT_NET_GROWTH;

%MACRO PROC_REPORT_REGIONAL(REG,EXCHANGE,CATEGORY);

%Let Exchange_Desc = %SYSFUNC(PUTC(&EXCHANGE.,$EXG.));

PROC REPORT DATA=KPIF_REG_VAL

(WHERE=(REGION="&REG." AND

EXCHANGE="&EXCHANGE." AND

CATG_TYPE = "&CATEGORY.")) box missing SPANROWS

style(header)={background=LIGHTYELLOW foreground=black font_size=10pt fontweight=bold BORDERWIDTH=1.0}

style(column)={font_size=9pt foreground=black BORDERWIDTH=1.0 background=LIGHTCYAN};

options missing=0;

COLUMNS

("MEMBERSHIP ACTUALS FOR &REG. BY &CATEGORY."

CATG_TYPE_VALUE PLAN_NAME)

("YEAR : &YEAR." JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC );

COMPUTE PLAN_NAME ;

if PLAN_NAME = 'TOTAL'

THEN CALL DEFINE(_ROW_, "style", "STYLE=[background=lightgreen fontsize=2 fontweight=bold]");

IF PLAN_NAME = 'TOTAL MEMBERSHIP' THEN DO;

CALL DEFINE(_COL_, "style", "STYLE=[CELLWIDTH=2.5 in ]");

CALL DEFINE(_ROW_, "style", "STYLE=[background=#FFCC99 fontsize=2 fontweight=bold]");

END;

%IF &REG.=ALL %THEN %DO;

If SUBSTR(PLAN_NAME,1,1) = '-' THEN

CALL DEFINE(_COL_, "style", "STYLE=[background=Lightgrey]"); /*#C0C0C0*/

%END;

ENDCOMP;

%IF &REG.=ALL %THEN %DO;

%STYLE_SUBREGION;

%END;

DEFINE CATG_TYPE_VALUE / ORDER=DATA group "&CATEGORY." STYLE(column)={just=C vjust=M CELLWIDTH=1.5 in};

DEFINE PLAN_NAME / ORDER=DATA "PLAN NAME" ;

DEFINE JAN / DISPLAY STYLE(column)={CELLWIDTH=.7in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE FEB / DISPLAY STYLE(column)={CELLWIDTH=.7in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE MAR / DISPLAY STYLE(column)={CELLWIDTH=.7in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE APR / DISPLAY STYLE(column)={CELLWIDTH=.7in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE MAY / DISPLAY STYLE(column)={CELLWIDTH=.7in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE JUN / DISPLAY STYLE(column)={CELLWIDTH=.7in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE JUL / DISPLAY STYLE(column)={CELLWIDTH=.7in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE AUG / DISPLAY STYLE(column)={CELLWIDTH=.7in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE SEP / DISPLAY STYLE(column)={CELLWIDTH=.7in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE OCT / DISPLAY STYLE(column)={CELLWIDTH=.7in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE NOV / DISPLAY STYLE(column)={CELLWIDTH=.7in tagattr='format:#,##0_);[Red](#,##0)'};

DEFINE DEC / DISPLAY STYLE(column)={CELLWIDTH=.7in tagattr='format:#,##0_);[Red](#,##0)'};

Title1 justify=C height=13pt color=blue font=Arial bold "&Exchange_Desc. For &Region_Name. (By &CATEGORY.)";

run; quit;

%MEND PROC_REPORT_REGIONAL;

%MACRO STYLE_SUBREGION;

%LET MONTHS=%STR(JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC);

%DO M = 1 %TO 12;

%LET COL = %SYSFUNC(SCAN(&MONTHS.,&M.));

COMPUTE &COL.;

If SUBSTR(PLAN_NAME,1,1) = '-' THEN

CALL DEFINE(_COL_, "style", "STYLE=[background=Lightgrey]");

ENDCOMP;

%END;

%MEND;

%MACRO REPORT_DATA;

%GLOBAL Region_Name;

%PROC_REPORT_NET_GROWTH;

%DO I = 1 %TO 3; /*&REGION_COUNT.;*/

%LET REG = %SYSFUNC(SCAN(&REGION_VALS.,&I.));

%LET REGION=%SYSFUNC(SUBSTR(&REG.,1,2));

%LET SUB_REGION = %SYSFUNC(SCAN(&REG.,2,'_'));

%LET Region_Name = %SYSFUNC(PUTC(&REG.,$RGN.));

%IF %LENGTH(&SUB_REGION.) NE 0 %THEN %DO;

%LET Region_Name = %STR(&REGION. &Region_Name.);

%END;

ods tagsets.excelxp options(Sheet_Interval="none" sheet_name="&REG.");

%DO J = 1 %TO &EXCHANGE_COUNT. ;

%LET EXCHANGE = %SYSFUNC(SCAN(&EXCHANGE_VALS.,&J.));

%PROC_REPORT_REGIONAL(&REG.,&EXCHANGE.,PRODUCT_TYPE);

%IF "&EXCHANGE." NE 'GF' AND "&EXCHANGE." NE 'GF2' %THEN %DO;

%put "smijo here report reg=&REG. exch=&EXCHANGE. METALTIER";

%PROC_REPORT_REGIONAL(&REG.,&EXCHANGE.,METALTIER);

%END;

%MEND REPORT_DATA;

ods tagsets.excelxp

file="/apps/sas/datasets/data2/sckpif92/data/SmijoKPIF/6500.xml"

options( sheet_interval='none' CENTER_VERTICAL='yes' doc='help'

embedded_titles='yes' embedded_footnotes='yes'

autofit_height='no' row_heights='15,15,0,30,0,0,0' )

style=Style.Smijo2

;

%REPORT_DATA;

RUN;

ods tagsets.excelxp close;

topic ods tagsets.excelxp - v1.116 - Column Height Issue in ODS and Base Reporting

ods tagsets.excelxp - v1.116 - Column Height Issue

Re: ods tagsets.excelxp - v1.116 - Column Height Issue

Re: ods tagsets.excelxp - v1.116 - Column Height Issue

Re: ods tagsets.excelxp - v1.116 - Column Height Issue