The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

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

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;

Accepted Solutions
Solution
‎01-05-2018 08:44 AM
Super Contributor
Posts: 320

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

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;

View solution in original post


All Replies
Super User
Posts: 22,823

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

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

Use the macro variable in a FOOTNOTE statement.

Contributor
Posts: 25

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

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'?
Super Contributor
Posts: 320

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

Just put the macro variable inside the line.
Super User
Posts: 22,823

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

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'?

 

Super Contributor
Posts: 320

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

Solution
‎01-05-2018 08:44 AM
Super Contributor
Posts: 320

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

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;
Super User
Posts: 22,823

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

Posted in reply to snoopy369

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.

Super Contributor
Posts: 320

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

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).
Contributor
Posts: 25

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

Posted in reply to snoopy369

Great! It works, thank you so much

SAS Super FREQ
Posts: 9,254

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

Hi:

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

copyright.png

 

  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

Contributor
Posts: 25

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

Posted in reply to Cynthia_sas

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 590 views
  • 7 likes
  • 4 in conversation