BookmarkSubscribeRSS Feed
MiMe
Calcite | Level 5


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

3 REPLIES 3
Cynthia_sas
SAS Super FREQ

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;


testing_titles.png
MiMe
Calcite | Level 5

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

excel_output.png

shivas
Pyrite | Level 9

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.excel.png

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