The SAS Output Delivery System and reporting techniques

can't get column widths and heights to come out

Reply
Contributor
Posts: 73

can't get column widths and heights to come out


I am trying to output to excel...is there a way to get the column widths and heights to come out?

I'm using tagsets.excelxp and the options row_heights= and absolute_column_width=

Super User
Posts: 11,343

Re: can't get column widths and heights to come out

Posted in reply to wkossack_nspirehealth_com

Sample data and attempted code would help diagnosis issues.

Contributor
Posts: 73

Re: can't get column widths and heights to come out

for some stupid reason I was not able to paste in my first window.

am I leaving something out?

ods tagsets.ExcelXP path="c:\temp\" file='Technician_Quality_Report.xml' style=sasweb;

   ods tagsets.excelxp options(embedded_footnotes='yes' embedded_titles='yes'

                              sheet_label='Country Summary               ' ROW_HEIGHTS='10,10,10,20,10'

                             orientation='landscape'

                              absolute_column_width='25,15'

                              );

title1 'quality report';

proc print data=test;

var a b c d;

label

     a='var a'

     b='var b';

run; quit;

SAS Super FREQ
Posts: 8,868

Re: can't get column widths and heights to come out

Posted in reply to wkossack_nspirehealth_com

Hi, in addition to sample data and code, have you searched the forum for previous postings? I know that there have been previous posts, with code examples on this issue. I generally use cellwidth as my first approach and then absolute_column_width as my next approach. When I use cellwidth, I don't necessarily have to adjust row heights. See the attached screen shot. I created this output using PROC REPORT. Did not use absolute_cell_width or row_height and the small column adjusted automatically.

cynthia

data class;

  length newname $80;

  set sashelp.class;

  newname = catx(' ',name,repeat(substr(name,1,2),7));

  newname = propcase(newname);

run;

  

** ODS TAGSETS.EXCELXP is making an XML file;

** Only control for cellwidth and use;

** NEWNAME twice on the report, 1 time with 2.5 in width;

** and second time with .5in width;

ods tagsets.excelxp file='c:\temp\xp.xml' style=sasweb;

  

proc report data=class nowd

  style(header)={background=pink foreground=black};

  column name newname newname=nnsmall age height weight;

  define newname /style(column)={cellwidth=2.5in};

  define nnsmall/style(column)={cellwidth=.5in};

run;

  

ods _all_ close;


use_cellwidth_xp.png
Contributor
Posts: 73

Re: can't get column widths and heights to come out

Posted in reply to Cynthia_sas

ah but your using proc report

I'm using proc print.  I don't see any examples using proc print.

I'm trying not to change the report too much from what users are used to seeing.  The proc print works fine the old server which is failing so I have to port everything to a new server

SAS Super FREQ
Posts: 8,868

Re: can't get column widths and heights to come out

Posted in reply to wkossack_nspirehealth_com

Hi:

  The syntax is a bit different for PROC PRINT, but the results are the same for me. See the attached screenshot. Altered code is below.

cynthia

data class;

  length newname $80;

  set sashelp.class;

  newname = catx(' ',name,repeat(substr(name,1,2),7));

  newname = propcase(newname);

  newsmall = newname;

run;

  

** ODS TAGSETS.EXCELXP is making an XML file;

** Only control for cellwidth;

ods tagsets.excelxp file='c:\temp\xp.xml' style=sasweb

    options(embedded_titles='yes');

  

proc report data=class nowd

  style(header)={background=pink foreground=black};

  title '1) PROC REPORT';

  column name newname newname=nnsmall age height weight;

  define newname /style(column)={cellwidth=2.5in};

  define nnsmall/style(column)={cellwidth=.5in};

run;

  

  

proc print data=class noobs

  style(header)={background=pink foreground=black};

  title '2) PROC PRINT';

  var name;

  var newname /style(data)={cellwidth=2.5in};

  var newsmall /style(data)={cellwidth=.5in};

  var age height weight;

run;

  

ods _all_ close;


same_technique_print.png
Contributor
Posts: 73

Re: can't get column widths and heights to come out

Posted in reply to Cynthia_sas

with of the column has not been the big problem it is the width of the rows

I've been trying ROW_HEIGHTS='30,30,30,20,20,20' but it does not seem to have an effect

SAS Super FREQ
Posts: 8,868

Re: can't get column widths and heights to come out

Posted in reply to wkossack_nspirehealth_com

Hi, As my code and screenshots above show, the height of the rows is automatically adjusted when I set the width of the columns. However, I don't understand why you are changing every possible value for ROW_HEIGHTS. Are you using PARSKIP? does your code have BYLINES, why bother to change those heights if you don't have them in your code? For example, in the code below, I change the table headers, table rows, title and footnote rows. I am changing ALL the title rows and ALL the footnote rows to be the same. I am changing ALL the "body/data" rows to be the same.

If you are still having issues with Row Height, then I suggest you open a track with SAS Tech Support.

 

cynthia

title;

ods listing close;

/*

  For the ROW_HEIGHTS suboption, the default values are:

     Table_head  : 0

     Table  : 0

     Byline : 0

     Title  : 0

     Footer : 0

     PageBreak : 0

     Parskip : 0

 

row_heights='table_head,table,byline,title,footer, pagebreak,parskip'

*/

     

ods _all_ close;

ods tagsets.excelxp file='c:\temp\heights.xml'

    options(doc='help' embedded_titles='yes'

            embedded_footnotes='yes'

            row_heights='30,20,0,50,70,0,0')

    style=sasweb;

 

proc report data=sashelp.class(obs=3) nowd;

  title  j=l 'Title1:';

  title2 j=l 'Title2:';

  title3 j=l 'Title3:';

  footnote 'The Footnote';

  column  age sex name height weight;

  define age / order style(header)={cellwidth=2in};

  define name / style(header)={cellwidth=2in};

  define weight / style(header)={cellwidth=2in};

run;

      

ods tagsets.excelxp close;

Ask a Question
Discussion stats
  • 7 replies
  • 431 views
  • 0 likes
  • 3 in conversation