BookmarkSubscribeRSS Feed
wkossack_nspirehealth_com
Calcite | Level 5


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=

7 REPLIES 7
ballardw
Super User

Sample data and attempted code would help diagnosis issues.

wkossack_nspirehealth_com
Calcite | Level 5

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;

Cynthia_sas
SAS Super FREQ

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
wkossack_nspirehealth_com
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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
wkossack_nspirehealth_com
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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;

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
  • 7 replies
  • 1225 views
  • 0 likes
  • 3 in conversation