Footnote created by proc report ods excel (sas 9.4 M3)

[nnl3256]

By the codes below, 
1. how can I replace footnote with content in 'note' variable? 
2. how can I put the footnote in first column of the displayed table in spreadsheet (col 3), and left justified and font in red color?


 data class; set sashelp.class;
 length sym $1 line1 note $40;
 sym= 'AE'x;
 line1=" Copy Right: The ABC Company ";
 note=catt(line1, sym);  /* first line of footnote*/
 drop sym line1;
 run;

ods excel file="&output\footnote.xlsx" style=snow						
	options(start_at="3,3"    
	frozen_headers="5"			
	autofilter="1-5"					
	sheet_name="Student List"
	embedded_titles="yes"	
	contents="no");	

title "Student List ";
	
proc report data=class;
	column name sex age height weight note; 
	define name/display;
	define sex/display format=$3.;
	define age/order;
	define height/format=6.2;
	define weight/format=6.2;
	define note/format=$40. noprint;
	compute after;
		line  ' ';
		line @1 "Replace string in note variable";
		line @1 "Data Source: PC, CSTK, AMI, STK2, STK5";
	endcomp;
run;
ods excel close;

[Reeza]

Create a macro variable using CALL SYMPUTX() in the data step

Use the macro variable in a FOOTNOTE statement.

[nnl3256]

thank you. but for some reason, the footnote statement seem does not work because the content of footnote doesn't show. Is there option like embedded-title='yes'?

[snoopy369]

Just put the macro variable inside the line.

[Reeza]

No, but make sure you use double quotes around the macro variable. If it doesn't work, show your code and what doesn't work means, because that doesn't tell us anything useful.

---

@nnl3256 wrote:
thank you. but for some reason, the footnote statement seem does not work because the content of footnote doesn't show. Is there option like embedded-title='yes'?

---

[snoopy369]

There is actually an embedded_Footnotes option; see http://documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVer...

[snoopy369 · Accepted solution]

I happen to prefer PROC SQL, so put this before the PROC REPORT:

proc sql noprint;
  select distinct note into :note 
  from class;
quit;

Then modify PROC REPORT like so:

proc report data=class;
	column name sex age height weight note; 
	define name/display;
	define sex/display format=$3.;
	define age/order;
	define height/format=6.2;
	define weight/format=6.2;
	define note/format=$40. noprint;
	compute after/style=[textalign=left color=red];  *note the styles here;
		line  ' ';
		line @1 "&note";
		line @1 "Data Source: PC, CSTK, AMI, STK2, STK5";
	endcomp;
run;

[Reeza]

---

@snoopy369 wrote:

I happen to prefer PROC SQL, so put this before the PROC REPORT:

---

 In general, I agree, except the OP is already using a data step, so may as well build it in there. 

@snoopy369 is correct about the embedded option however, I skipped over the fact that you were using ODS EXCEL. The option is on the ODS statement, not the FOOTNOTE statement. And the Compute After within PROC REPORT is a great option.

[snoopy369]

I actually assume that data step exists for the sole purpose of getting that NOTE variable onto the dataset, so it would be easier to just do the PROC SQL from wherever it comes from (or, alternately if it's hardcoded, use %LET).

[nnl3256]

Great! It works, thank you so much

[Cynthia_sas]

Hi:

  That's odd, when I create a macro variable and use it with ODS Excel in SAS 9.4, it works for me:

  Here's the relevant code I changed:

 data class; set sashelp.class;
 length sym $1 line1 note $40;
 sym= 'AE'x;
 line1=" Copyright: The ABC Company ";
 note=catt(line1, sym);  /* first line of footnote*/
 call symputx('fnstring',note);
 drop sym line1;
 run;
 %put -----> <-----;
 %put &fnstring;

ods excel file="c:\temp\footnote.xlsx" style=snow						
	options(start_at="3,3"    
	frozen_headers="5"			
	autofilter="1-5"					
	sheet_name="Student List"
	embedded_titles="yes"	
	contents="no");	

title "Student List ";
	
proc report data=class;
**... rest of code same ...;
	compute after;
		line  ' ';
		line @1 "&fnstring";
		line @1 "Data Source: PC, CSTK, AMI, STK2, STK5";
	endcomp;
run;
ods excel close;

Your @1 attempt in the LINE statement will ONLY work with the LISTING output. It won't work to put anything in column 1 in the Excel output. The LINE statement output is written inside the boundary of the report table. So the @1 is essentially ignored.

  If you want an actual footnote in a FOOTNOTE statement, then you'd still use &fnstring in a FOOTNOTE statement, but then I believe you would also have to use embedded_footnotes='yes', as shown in the doc: http://go.documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docset... -- just scroll down to the section that lists the suboptions.

cynthia

[nnl3256]

Thank you so much.  I used footnote and footnotes option. It works and the footnotes are separated from the displayed table.