As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Customizing PDF By Variable Bookmarks

From sasCommunity
Jump to: navigation, search

Overview

When producing a PDF using ODS and a BY statement, SAS will create PDF bookmarks for each by group making it easy to quickly jump to the report for any given by group. Unfortunately the format of the bookmark text is fixed as:

by variable name = by variable value

Quite often the report users would prefer a different format for the bookmarks (e.g., just include the value of the by variable). And while the PDF destination does not support (easily) changing the format, it is possible to customize the generated output by combining some ODS features with the macro language.

A Single By Variable

ods listing close;
ods pdf file='\SingleByVarBookmarks.pdf';

First, as seen at right,

  • close the LISTING destination; and
  • use the PDF destination and specify the name of the PDF file to create.


proc sql noprint;
 select distinct product into :p1-:p999
 from sashelp.shoes;
 %let numberOfProducts = &sqlobs;
quit;

The next step is to uniquely extract the the list of by values into macro variables. The macro variable values can be created by the SQL code shown at right.

  • the into:p1-:p999 says to create up to 999 macro variables with the by values where the number of macro variables created is limited to the number of by groups.
  • the %let statement uses &sqlobs to obtain the number of by groups.


%do i= 1 %to &numberOfProducts;
ods proclabel="&&p&i";
proc report data=sashelp.shoes nowd contents='';
 where product = "&&p&i";
 title "&&p&i Data";
run;
%end;

We then use the macro facility to loop thru each by group and run a separate PROC REPORT for each by group. Note how:

  • ods proclabel is used to provide the bookmark text, using just the value of the by variable
  • the contents option is used on the PROC REPORT statement to suppress it from generating an additional bookmark.


ods pdf close;

The last step is to close the PDF destination which produces the desired PDF.

Accomodating Multiple By Variables

Handling multiple by variables is straightforward and involved just a few code changes.


proc sql noprint;
 select distinct region, subsidiary
 into :r1-:r999, :s1-:s999 from sashelp.shoes;
 %let numberOfByGroups = &sqlobs;
quit;

First, modify the SQL statement to get the unqiue values of multiple (in this case 2) variables.

  • note that we have two expressions in the into clause, one for each variable
    • :r1-:r999; and
    • :s1-:s999
  • like above, the %let statement uses &sqlobs to obtain the number of by groups. Here we use a more generic name for the macro variable.


%do i= 1 %to &numberOfByGroups;
ods proclabel="&&r&i - &&s&i";
proc report data=sashelp.shoes nowd contents='';
 where region = "&&r&i" and subsidiary="&&s&i";
 title "&&s&i, &&r&i";
run;
%end;

The only change needed to the PROC REPORT step is to use both the region and subsidiary macro variables in the:

  • ods proclabel
  • where
  • title

statements.



When this code is run, it produces the desired PDF.

Complete Code

The complete code, for the two by variable example is included below. Note that it is packaged inside a macro since the %do - %end loop is not allowed in open code.

The code below is for illustrative purposes only and can be generalized/optimized as needed.

%macro customByVarBookMarks;
 
ods listing close;
ods pdf file='\MulipleByVarBookmarks.pdf';
 
proc sql noprint;
 select distinct region, subsidiary
 into :r1-:r999, :s1-:s999 from sashelp.shoes;
 %let numberOfByGroups = &sqlobs;
quit;
 
%do i= 1 %to &numberOfByGroups;
ods proclabel="&&r&i - &&s&i";
proc report data=sashelp.shoes nowd contents='';
 where region = "&&r&i" and subsidiary="&&s&i";
 title "&&s&i, &&r&i";
run;
%end;
 
ods pdf close;
%mend customByVarBookMarks;

See also