Long titles and small table

MiMe — Fri, 11 May 2012 06:33:35 GMT

Hi!

I have a problem with titles in Excel output. I have a narrow (small) table and I would like to have title that is wider than the table. Here is example of my problem:

%let title1="I would like to have this title in a one row...";

ods tagsets.excelxp file='C:\testing titles.xls';

ods tagsets.excelxp options(embedded_titles='yes' gridlines='yes' autofit_height='yes' sheet_name='Testing');

title j=left font='Arial' height=12pt &title1 ;

proc print data=sashelp.class (keep=name);

run;

ods tagsets.excelxp close;

In SAS output it's ok, but when I open it in Excel the title is only as wide as the table and therefore it will split on two rows.

Is it possible to have this in one row with this font size (it would have to be wider that the table)?

TIA,

Miia

Re: Long titles and small table

Cynthia_sas — Fri, 11 May 2012 19:35:41 GMT

Hi:

I don't observe the behavior you describe using SAS 9.3 and Office 2010/Excel 2010. Using the code below, I get the same results in both worksheets. I did change a few things in your code, but they would not have impacted the TITLEs. First, I used NOOBS to make the table even narrower and a VAR statement for just NAME. Next, I ran 2 PROC PRINTS -- getting 1 sheet for each PROC PRINT. You can see in the screenshot the 2 outputs are the same. Then, I used ODS ESCAPECHAR for the second title in order to explicitly alter the output width of the title. Since I didn't see any problem with the first title for the first PROC PRINT, I don't really know whether the second title in the second PROC PRINT was even necessary.

The other changes I made also were unrelated to the title. Instead of 2 ODS TAGSETS.EXCELXP statements, I just used 1. It cleaned up the invocaiton. All of those suboptions can be specified at the first invocation and then if you want to change them, you can, but there was no need to change them here. Oh, I also used the SASWEB style, I like the white background. I got all the same results using these styles as well: ANALYSIS, EGDEFAULT, DEFAULT, STATISTICAL.

Last, I feel strongly that it is a bad idea to "pre-quote" macro variables -- or put quotes around a macro variable value in the %LET statement. Call me old-fashioned. That's the way I was always taught to define macro variables -- sometimes placing quotes prematurely could get you into trouble or mis-matched "quote-land" in your programs--so I was taught that quotes do not necessarily belong to the macro variable definition, they belong to the statement where you use the macro variable. It is a technique that I use very sparingly,and, since I could not see a reason for the quotes in your %LET statement, I used a different technique. Using %STR allows me to have punctuation (if I need it) such as semi-colons without prematurely ending the %LET statement.

I'd recommend that you consider updating your EXCELXP tagset template and/or work with Tech Support on this issue.

cynthia

ods escapechar='^';
%let title1 %str(1. I would like to have this title in one row...);
%let title2=%str(^S={outputwidth=5in}2. And this is another title that I would like to have in one row...);

ods tagsets.excelxp file='C:\temp\testing titles.xls' style=sasweb
    options(embedded_titles='yes' gridlines='yes'
            autofit_height='yes' sheet_name='Testing');

proc print data=sashelp.class noobs;
title j=left font='Arial' height=12pt "&title1" ;
var name;
run;
title;

proc print data=sashelp.class noobs;
title j=left font='Arial' height=12pt "&title2" ;
var name;
run;
title;

ods tagsets.excelxp close;

Re: Long titles and small table

MiMe — Mon, 14 May 2012 06:25:49 GMT

Thank you Cynthia for your fast answer. I have SAS 9.2 and Office 2010. I tried running your code, but I still have the same problem in both worksheets as shown in the picture that is attached. I'll keep on searching the solution.

BR,

Miia

Re: Long titles and small table

shivas — Mon, 14 May 2012 06:41:40 GMT

Hi,

I have used same code given by Cynthia and it works fine in sas 9.2 and office 2010.

Just expand the column A it is coming fine.

topic Re: Long titles and small table in ODS and Base Reporting

Long titles and small table

Re: Long titles and small table

Re: Long titles and small table

Re: Long titles and small table