ODS Excel Options to insert page numbers

[Jeff_DOC]

Good morning.

I'm hoping some can help because I'm out of ideas.

I'm attempting to use ODS Excel to place the page number and number of pages in the footer at a right justify. I've tried the three different methods in the example below.  I've done some searching and found some information but can't seem to get it to work.

I get warning messages such as: 

WARNING: Apparent symbolic reference R not resolved.
WARNING: Apparent symbolic reference P not resolved.
WARNING: Apparent symbolic reference N not resolved.

Can anyone give me a hint as to what I may be doing wrong here?

                    options(sheet_interval = 'none'

                    sheet_name = 'Sheet Name'

                    frozen_headers = '2'

row_repeat = '1-2'

                    suppress_bylines = 'yes'

autofilter = 'all'

                    pages_fitwidth = '1'

                    pages_fitheight = '50'

/* print_footer = '&R &P & of &N'*/

/* print_footer = 'Page &P & of &N'*/

print_footer = "Page &R &P of &N"

/* print_footer = "&;LInstitutional Research and Assessment &R Page &P of &N &Z &F"*/

absolute_column_width = '12, 30, 30, 18, 10, 10, 12, 50');

[ballardw]

The warning messages like

WARNING: Apparent symbolic reference R not resolved.
WARNING: Apparent symbolic reference P not resolved.
WARNING: Apparent symbolic reference N not resolved.

are from using code like &R &P or &N. SAS expects &R (etc) to reference a macro variable created some where.

If you place the part referencing the & inside single quotes instead of double quotes then SAS will not attempt to resolve the value as a macro variable:

print_footer = 'Page &R &P of &N'

[Jeff_DOC]

Thank you. That removed the warning. However, it still does not include the page numbers.

[Cynthia_sas]

Hi: Make sure you go into Print Preview because I do not believe the page numbers or headers and footers are visible in the worksheet.
Cynthia

[Jeff_DOC]

Thank you Cynthia.

I did use print preview and still don't see it. I even changed my bottom margin to  1.75 in to be sure it wasn't buried behind the table somehow.

[Cynthia_sas · Accepted solution]

Hi: This is what I get:

When I use this code:

proc sort data=sashelp.shoes out=newshoes;
  where region in ('Asia', 'Canada', 'Pacific');
  by region;
run;
title; footnote;

options topmargin=1.5in bottommargin=1.5in leftmargin=1in rightmargin=1in;

ods excel file='c:\temp\hdr_footr.xlsx'
            options(sheet_name = 'Sheet Name'
			sheet_interval='None'
			orientation='portrait'
            suppress_bylines = 'yes'
			print_header = '&C&BThis is the Header'
            print_footer = '&LInstitutional Research &RPage &P of &N' );
 
  proc print data=newshoes;
    by region;
  run;
  ods excel close;

I simplified your suboptions some since the column width and repeating rows and autofilters didn't have any bearing on whether headers/footers were used. Since you had suppress_bylines turned off, that implied the use of a BY statement in your procedure, so I just did a quick PROC SORT to get 3 regions from SASHELP.SHOES. I also did not mess with the pages suboptions because my main interest was just verifying the PRINT_HEADERS and PRINT_FOOTERS suboptions. My recommendation would be to simplify your code and trim it back until you can see the headers and footers being used and then one by one introduce some of the other suboptions and make sure that none of the other suboptions are breaking the PRINT_HEADERS and PRINT_FOOTERS.

Cynthia

[Jeff_DOC]

Thank you so much. That worked perfectly. I must have been using a very old example for my attempt. 

You've saved me (again)!