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=
Sample data and attempted code would help diagnosis issues.
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;
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;
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
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.