Cellwidth in Excel with msoffice2k_x

[jshaik]

Hi All,

Above is a sample report I am trying to create.As you can see the columns are very cramped. I am not able to increase the width of the excel cells.Can some one please suggest an option.Thanks.

Below is sample code I have written.

Options noQuoteLenMax;

       ods listing close;

         ods tagsets.msoffice2k_x file= "sample.xls"    style=normal 

         options (rotate_headers="-90"  height="100"  fittopage='yes' EMBEDDED_TITLES = 'YES' /*Center_Horizontal = 'yes' Center_Vertical='yes' */ gridlines = 'yes' blackandwhite = 'yes');

       proc report data=final_report nowd split= '*' STYLE(header)=[ BACKGROUND = silver foreground=black /* font_size=3 */ font = ("calibri",10pt) FONT_WEIGHT = BOLD ]

        style(column)={ font = ("calibri",10pt) background=white foreground=black }

        style(report)={ font = ("calibri",10pt) };

       title1 "<table border ='1'>

         <tr>

           <th align=left colspan=14><font size=2.5 face=calibri><b>xxxxxxxxxx</b></font></th>

           <th bgcolor='silver' align=left colspan=1><font size=2.5 face=calibri>Report Revised :</font></th><th colspan = 2><font size=2.5 face=calibri></font></th>

         </tr>

         <tr>

             <td align=left colspan=17><font size=2.5 face=calibri><b>MCO Report # 107: Behavioral Health Network Capacity</b></font></td></th>

         </tr>     

         <tr>

           <td align=left colspan=1><font size=2.5 face=calibri>MCO Name:</font></td> <td></td> <td align=right colspan=13><font size=2.5 face=calibri>DMS/DBHDID Use Only</font></td>

         </tr> 

         <tr>

           <td align=left colspan=1><font size=2.5 face=calibri>Report Run Date:</font></td> <td <font size=2.5 face=calibri>&rundate.</font></td> <td align=right colspan=13 ><font size=2.5 face=calibri>Received Date :</font></td>

           <td align=right colspan=2></td>

         </tr>

         <tr>

           <td align=left colspan=1><font size=2.5 face=calibri>Report Period From:</font></td> <td <font size=2.5 face=calibri>%sysfunc(PutN(%sysfunc(datepart(&begmvar)),MMDDYYS10.))</font></td><td align=right colspan=13><font size=2.5 face=calibri>ReviewedDate : </font></td>

           <td align=right colspan=2></td>

         </tr>

         <tr>

           <td align=left colspan=1><font size=2.5 face=calibri>Report Period To:</font></td> <td <font size=2.5 face=calibri>%sysfunc(PutN(%sysfunc(datepart(&endmvar)),MMDDYYS10.))</font></td><td align=right colspan=13><font size=2.5 face=calibri>Reviewer :</font></td>

           <td align=right colspan=2></td>

         </tr>

         <tr></tr>

       </table>";

       footnote;

        column    BH_Descipline

           Total

                  total_with_claims                 

                  ('Provider Type' provtype02 provtype04 provtype13 provtype21 provtype24 provtype25 provtype27 provtype29 provtype30

                provtype31 provtype40 provtype45 provtype64 provtype82 provtype89 provtype92 other )

           ('Region' region01 region02 region03 region04 region05 region06 region07 region08 region09)

           ;

           define BH_Descipline  /display 'Behavioral Health Discipline'       ;

           define Total   /display 'Total Number of Professionals by Discipline During the Reporting Period' ;

           define total_with_claims /display 'Total Number with at least 1 Claim Filed During the Report Period'   ;

           define provtype02  /display '02 Mental Hosp'               ;

           define provtype04  /display '04 - PRTF'         ;

           define provtype13  /display '13 Specialized Ch Clinic'       ;

           define provtype21  /display '21 School Based health Srvs'       ;

    define provtype24  /display '24 First Steps'        ;

    define provtype25  /display '25 Targeted Case Mgmt'       ;

    define provtype27  /display '27 & 28 Adult and Child TCM '       ;

    define provtype29  /display '29 IMPACT Plus'        ;

    define provtype30  /display '30 CMHC'         ;

    define provtype31  /display '31 Primary Care'        ;

    define provtype40  /display '40 EPSDT Prev Srvs'        ;

    define provtype45  /display '45 EPSDT Special Services/Grp/PA'      ;

    define provtype64  /display '64 & 65 & 95- Physician Ind '       ;

    define provtype82  /display '82 Clinical Social Worker'       ;

    define provtype89  /display '89 - Psychologist'        ;

    define provtype92  /display '92 & 93 Psych /Rehab'        ;

           define other                 /display 'Other'          ;

           define region01  /display '1'   ;

           define region02  /display '2'        ;

           define region03  /display '3' ;

           define region04  /display '4' ;

           define region05  /display '5' ;

           define region06  /display '6'  ;

           define region07  /display '7' ;

           define region08  /display '8'      ;

           define region09  /display 'Out of state' ;

          RUN;

         ods tagsets.msoffice2k_x close;

         ods listing;

Thanks in advance.

[Cynthia_sas]

Hi:

   So I'm not sure what your asking about. Are you asking about the TITLE statement syntax? Or, are you asking about the header cells in your screenshot? To increase the width of the header and data cells in your output, you only need to change the DEFINE statement or the PROC REPORT statement. In the code below, I set a default cellwidth (or width) of .5in for all the headers and data columns in the PROC REPORT statement and then override that default to be different for selected columns, as shown in the screen shot.

  In my test program, I eliminated your TITLE statement in favor of showing only the cellwidth change.

cynthia

Options noQuoteLenMax;
title; footnote;
ods listing close;
ods tagsets.msoffice2k_x file= "c:\temp\sample_MSO2K_X.xls"    style=normal 
    options (rotate_headers="-90"  height="100" 
             fittopage='yes' EMBEDDED_TITLES = 'YES'
             gridlines = 'yes' blackandwhite = 'yes'
             doc='help');
  
proc report data=sashelp.class nowd split= '*'
     STYLE(header)=[BACKGROUND=silver foreground=black
                     font=("calibri",10pt)
                     FONT_WEIGHT=BOLD width=.5in]
     style(column)={font=("calibri",10pt) background=white
                    foreground=black width=.5in}
     style(report)={font=("calibri",10pt) };
  column    name age sex height weight;
  define name  /display 'Behavioral Health Discipline'      
         style(header)={width=.75in}
         style(column)={width=.75in};
  define age   /display 'Total Number of Professionals by Discipline During the Reporting Period'
         style(header)={width=1.25in}
         style(column)={width=1.25in};
  define sex  /display '04 - PRTF'         ;
  define height /display 'Total Number with at least 1 Claim Filed During the Report Period'  
         style(header)={width=1.0in}
         style(column)={width=1.0in};
  define weight  /display '02 Mental Hosp'               ;
RUN;

ods tagsets.msoffice2k_x close;
ods listing;