topic Re: Prevent title wrapping in ODS Excel in SAS Programming

Prevent title wrapping in ODS Excel

Batman — Tue, 09 May 2023 16:24:06 GMT

The following code produces titles that wrap and a merge cells. Can I prevent both?

35 ods excel options (Sheet_name="Table" sheet_interval='NOW' row_heights='40,0'
36 ABSOLUTE_COLUMN_WIDTH = '10,30,30' flow="header,data");
37
38 proc report data=Daily3;
39 col Date Tot_Avg Adult_Cov;
40 define Date / display 'Date';
41 define Tot_Avg / display format=comma8. 'Number of patients hospitalized with confirmed COVID-19 (7 day average)';
42 define Adult_Cov / display format=comma8. 'Number of hospitals reporting on adult patients with confirmed COVID';
43 /*AP implies data should start on August 1, 2020*/
44 where Date ge mdy(8,1,2020);
45 Title 'Analysis of COVID-19 Hospitalizations Data';
46 Title2 'Through May 2023';
47 Title3 "Median number of patients hospitalized with confirmed COVID-19 (7 day average):&median1 (thru &max_date1)";
48 Title4 "Median number of patients hospitalized with confirmed COVID-19 (7 day average):&median2 (thru &max_date2)";
49 Title5 "Median number of patients hospitalized with confirmed COVID-19 (7 day average):&median3 (thru &max_date3)";
50 run;

Re: Prevent title wrapping in ODS Excel

Cynthia_sas — Tue, 09 May 2023 18:36:05 GMT

Hi:
Are you showing all of your code? I don't see any embedded_titles suboption, so I'm not sure where your titles are wrapping or not wrapping.
Cynthia

Perhaps you are trying to over-control the cellwidths and row heights. When I simplify all the suboptions and include the embedded_titles suboption, I am not seeing any wrapping, as shown below:

As you can tell, I used SASHELP.PRDSALE for my example because it has a date variable that I can use for the first column. I made fake values for the macro variables in the TITLE statements.

Re: Prevent title wrapping in ODS Excel

Batman — Tue, 09 May 2023 20:03:09 GMT

Here is the full code between the ods statements

ods excel file=[output URL];
options (absolute_column_width='80'
embedded_titles='yes' embedded_footnotes='yes' sheet_name='Info'
sheet_interval='none' flow='tables');

%info

ods excel options (Sheet_name="Table" sheet_interval='NOW' row_heights='40,0'
ABSOLUTE_COLUMN_WIDTH = '10,30,30' flow="header,data");

proc report data=Daily3;
col Date Tot_Avg Adult_Cov;
define Date / display 'Date';
define Tot_Avg / display format=comma8. 'Number of patients hospitalized with confirmed COVID-19 (7 day average)';
define Adult_Cov / display format=comma8. 'Number of hospitals reporting on adult patients with confirmed COVID';
/*AP implies data should start on August 1, 2020*/
where Date ge mdy(8,1,2020);
Title 'Analysis of COVID-19 Hospitalizations Data';
Title2 'Through May 2023';
Title3 "Median number of patients hospitalized with confirmed COVID-19 (7 day average):&median1 (thru &max_date1)";
Title4 "Median number of patients hospitalized with confirmed COVID-19 (7 day average):&median2 (thru &max_date2)";
Title5 "Median number of patients hospitalized with confirmed COVID-19 (7 day average):&median3 (thru &max_date3)";
run;

ods excel close;

Re: Prevent title wrapping in ODS Excel

Cynthia_sas — Tue, 09 May 2023 21:56:39 GMT

Hi: As I show in my example, my titles are NOT wrapping. So I would recommend that you remove all the suboptions and width controls out of your syntax and start with code more like the code I show. Then introduce only one change at a time until you find the one that's causing the wrapping. Without data, it is impossible to run your code. And since I can show that the TITLES don't wrap with taking all the defaults on width and row heights, there's something in your options that is conflicting and causing the issue.
Cynthia