topic Re: Title and Footnote could not be shown in the Output Excel file? in SAS Programming

Title and Footnote could not be shown in the Output Excel file?

ybz12003 — Wed, 18 Dec 2024 16:44:03 GMT

Hello,

I have the following codes for the Excel file output. However, the title and footnote aren't shown in the Excel file. Please help.

%let bdate1=2004_01_14; /* The most updated date to extracted LIVD table */
%let bdate2=2014_02_17; /* The most updated date to extracted LOOKUP table */
%let footie=%str(LIVD as of &bdate1. & LOOKUP as of &bdate2.);
%let tdate=%sysfunc(today(), yymmddn8.); /* Generate today's date for filename */

ods excel file="Pathway\LIVD_LOOKUP_check_&tdate..xlsx";
  	options(sheet_interval="none" embedded_titles="yes" embedded_footnotes="yes" flow="tables");

ods excel options(sheet_name="LIVD");
Proc print data=LIVD_;
   Title "LIVD - &bdate1.";
run;

ods excel options(sheet_name="LOOPUP");
Proc print data=test_lookup; 
	title "LOOKUP - &bdate2.";;
run;

ods excel options(sheet_name="Unmatched_LIVD");
Proc print data=Unmatched_LIVD;
	title "Codes in LIVD but not in LOOPUP";
	footnote "&footie";
run;

ods excel options(sheet_name="Unmatched_LOOPUP");
Proc print data=Unmatched_Lookup;
	title "Codes in LOOPUP but not in LIVD";
	footnote "&footie";
run;

ods excel close;

Re: Title and Footnote could not be shown in the Output Excel file?

Tom — Wed, 18 Dec 2024 16:55:15 GMT

Did you get an error message when you ran that code? What you posted has an extra semicolon that splits the first ODS EXCEL statement into two separate statements. An ODS statement and an OPTIONS statement.

514  ods excel file="Pathway\LIVD_LOOKUP_check_&tdate..xlsx";
WARNING: Apparent symbolic reference TDATE not resolved.
515      options(sheet_interval="none" embedded_titles="yes" embedded_footnotes="yes" flow="tables")

-----------------------------------
                                                                                                   2
515! ;

ERROR 2-12: Invalid option name.

Re: Title and Footnote could not be shown in the Output Excel file?

ybz12003 — Wed, 18 Dec 2024 17:02:34 GMT

It's not like that, I fixed it by adding the option to each sheet output option.

Re: Title and Footnote could not be shown in the Output Excel file?

Tom — Wed, 18 Dec 2024 17:29:22 GMT

If it wasn't because the options did not run then it was because you told it NOT to make a new sheet.

ods excel 
  file="c:\downloads\embedded_titles.xlsx"
	options(sheet_interval="none"
          embedded_titles="yes"
          embedded_footnotes="yes" 
          flow="tables")
;

ods excel options(sheet_name="MALES");
proc print data=sashelp.class;
  where sex='M';
  Title "Males Only";
run;

ods excel options(sheet_name="FEMALES");
proc print data=sashelp.class;
  where sex='F';
  Title "Females Only";
run;

ods excel close;

When you change the sheet name you need also tell it to start a new sheet. So either set the SHEET_INTERVAL option to "PROC" or tell it start and new page when you change the name.