Hi
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 ?
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.
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 ®ION_COUNT.;
%LET REG = %SYSFUNC(SCAN(®ION_VALS.,&K.));
%LET REGION=%SYSFUNC(SUBSTR(®.,1,2));
%LET SUB_REGION = %SYSFUNC(SCAN(®.,2,'_'));
%LET Region_Name = %SYSFUNC(PUTC(®.,$RGN.));
%IF %LENGTH(&SUB_REGION.) NE 0 %THEN %DO;
%LET Region_Name = %STR(®ION. &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="®." 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;
%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="®." 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 ®. 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 ®.=ALL %THEN %DO;
If SUBSTR(PLAN_NAME,1,1) = '-' THEN
CALL DEFINE(_COL_, "style", "STYLE=[background=Lightgrey]"); /*#C0C0C0*/
%END;
ENDCOMP;
%IF ®.=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; /*®ION_COUNT.;*/
%LET REG = %SYSFUNC(SCAN(®ION_VALS.,&I.));
%LET REGION=%SYSFUNC(SUBSTR(®.,1,2));
%LET SUB_REGION = %SYSFUNC(SCAN(®.,2,'_'));
%LET Region_Name = %SYSFUNC(PUTC(®.,$RGN.));
%IF %LENGTH(&SUB_REGION.) NE 0 %THEN %DO;
%LET Region_Name = %STR(®ION. &Region_Name.);
%END;
ods tagsets.excelxp options(Sheet_Interval="none" sheet_name="®.");
%DO J = 1 %TO &EXCHANGE_COUNT. ;
%LET EXCHANGE = %SYSFUNC(SCAN(&EXCHANGE_VALS.,&J.));
%PROC_REPORT_REGIONAL(®.,&EXCHANGE.,PRODUCT_TYPE);
%IF "&EXCHANGE." NE 'GF' AND "&EXCHANGE." NE 'GF2' %THEN %DO;
%put "smijo here report reg=®. exch=&EXCHANGE. METALTIER";
%PROC_REPORT_REGIONAL(®.,&EXCHANGE.,METALTIER);
%END;
%END;
%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;
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.