SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Jeff_DOC
Pyrite | Level 9

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');

 

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi: This is what I get:

Cynthia_sas_1-1716049264447.png

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

View solution in original post

6 REPLIES 6
ballardw
Super User

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
Pyrite | Level 9

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

 

 

Cynthia_sas
SAS Super FREQ
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
Pyrite | Level 9

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
SAS Super FREQ

Hi: This is what I get:

Cynthia_sas_1-1716049264447.png

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
Pyrite | Level 9

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

 

You've saved me (again)!

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1439 views
  • 3 likes
  • 3 in conversation