BookmarkSubscribeRSS Feed
Smijoss
Fluorite | Level 6

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 ?


excelxp_correct.JPGexcelxp_incorrect.JPG
3 REPLIES 3
ballardw
Super User

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.

Smijoss
Fluorite | Level 6

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;

  %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;

  %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;



Cynthia_sas
SAS Super FREQ

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1608 views
  • 0 likes
  • 3 in conversation