BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nnl3256
Obsidian | Level 7
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;
1 ACCEPTED SOLUTION

Accepted Solutions
snoopy369
Barite | Level 11

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

11 REPLIES 11
Reeza
Super User

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

Use the macro variable in a FOOTNOTE statement.

nnl3256
Obsidian | Level 7
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
Barite | Level 11
Just put the macro variable inside the line.
Reeza
Super User

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
Barite | Level 11

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
Super User

@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
Barite | Level 11
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
Obsidian | Level 7

Great! It works, thank you so much

Cynthia_sas
SAS Super FREQ

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

nnl3256
Obsidian | Level 7

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

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
  • 11 replies
  • 5169 views
  • 7 likes
  • 4 in conversation