Chapter 11 Building a Library of Utilities
Writing a Macro Program to Behave Like a Function
Chapter 10 showed ways of saving your macro program code and compiled macro programs. With these tools, you can create and organize your own libraries of macro programs that you and your coworkers frequently use. This chapter introduces the concept of building your own library of macro programs that perform routine or frequent tasks.
Because your work requirements are very different from that of another reader, your library of utility macro programs is likely to be different from his or hers. As you read through the chapter, keep in mind the routine and commonly performed programming tasks that you could include in your own library of utility routines. The SAS support website, SAS Community website, SAS conference proceedings, and SAS Press books are useful sources of code that you can adapt and add to your own libraries of tools.
You can write a macro program to return a value as though it was a macro function. These values can be used to test conditions in your macro program code. These types of macro programs may be useful when you need to customize actions similar to those produced by existing macro functions that you frequently use, as Examples 11.1 and 11.2 demonstrate.
Example 11.1: Examining Specific Data Set Characteristics
Example 11.1 demonstrates that you can call a macro program and have it return a value that you can test to determine the next processing step to take. This action is similar to the way you reference a function and obtain a return code that can be tested. The example is illustrated in two steps. The first step shows how a function can be tested. The second step replaces the function call with a macro program call.
The SAS language function EXIST determines if a data set exists, and it sets a return code based on its determination. It returns 1 if the data set exists and 0 if the data set does not exist. Macro program LISTSAMPLE shows how you could test for the existence of a data set with this function. If the data set exists, PROC PRINT lists the first ten observations of the data set specified in the parameter DSNAME. Otherwise, the macro program LISTSAMPLE writes an error message to the SAS log that the data set does not exist.
%macro listsample(dsname);
%if %sysfunc(exist(&dsname)) %then %do;
proc print data=&dsname(obs=10);
title “First 10 Observations of &dsname”;
run;
%end;
%else %put ERROR: ***** Data set &dsname does not exist.;
%mend listsample;
%listsample(books.ytdsales)
The program is modified below to replace the EXIST function with a call to macro program MULTCOND. Macro program MULTCOND checks four conditions of a data set, including whether it exists. MULTCOND defines a macro variable RC whose values can be 0 or 1 depending on the macro program’s evaluations of the data set. In this manner, MULTCOND acts like a function. When the returned value is 0, the data set should not be processed. When the returned value is 1, the data set can be processed.
Macro program MULTCOND returns a value by applying the PUTN SAS language function to macro variable RC. The %SYSFUNC macro function is required to execute the PUTN function.
Note that all the macro variables used in macro program MULTCOND are defined as local macro variables on the %LOCAL statement. Since this macro program could be accessed in different situations, declaring these macro variables as local prevents conflicts in macro variable resolution if these macro variables had previously been defined in open code or by a macro program that called MULTCOND.
The four conditions that macro program MULTCOND examines are:
1. Data set existence with the EXIST SAS language function
2. Data set can be opened for input with the OPEN SAS language function
3. Data set has at least one undeleted observation as determined by the ATTRN SAS language function and NLOBS argument
4. Data set has a read access password as determined by the ATTRN SAS language function and READPW argument.
The macro program branches to label SETRC when a test fails. The %LET statement that follows this label sets the value of RC to zero. If a data set passes all tests, the value of RC is 1.
Note that the only modification to LISTSAMPLE is to replace %sysfunc(exist) with %multcond(&dsname).
%macro multcond(dsname);
%local rc dsid exist nlobs readpw;
%*----Initialize return code to 1;
%let rc=1;
%*----Initialize data set id;
%let dsid=0;
%*----Does data set exist (condition 1);
%let exist=%sysfunc(exist(&dsname));
%*----Data set does not exist;
%if &exist=0 %then %goto setrc;
%let dsid=%sysfunc(open(&dsname,i));
%*----Data set cannot be opened (condition 2);
%if &dsid le 0 %then %goto setrc;
%*----Any obs to list from this data set? (condition 3);
%let nlobs=%sysfunc(attrn(&dsid,nlobs));
%*----No obs to list;
%if &nlobs le 0 %then %goto setrc;
%*----Read password set on this data set? (condition 4);
%let readpw=%sysfunc(attrn(&dsid,readpw));
%*----READPW in effect, do not list;
%if &readpw=1 %then %goto setrc;
%*----Data set okay to list, skip over section
that sets RC to 0;
%goto exit;
%*----Problems with data set, set RC to 0;
%setrc:
%let rc=0;
%exit:
%if &dsid ne %then %let closerc=%sysfunc(close(&dsid));
%*----Return the value of macro variable RC;
%sysfunc(putn(&rc,1.))
%mend;
%macro listsample(dsname);
%if %multcond(&dsname)=1 %then %do;
proc print data=&dsname(obs=10);
title “First 10 Observations of &dsname”;
run;
%end;
%else %put ERROR: ***** Data set &dsname cannot be listed.;
%mend listsample;
*----First call to LISTSAMPLE;
%listsample(books.ytdsales)
*----Second call to LISTSAMPLE;
%listsample(books.ytdsaless)
In the first call to LISTSAMPLE, assume BOOKS.YTDSALES exists and passes the four tests in MULTCOND. Therefore, the PROC PRINT step lists the first ten observations.
In the second call to LISTSAMPLE, the data set name is misspelled in order to cause MULTCOND to assign a value of 0 to macro variable RC. The value that %MULTCOND returns is a 0 and the %ELSE statement in LISTSAMPLE executes. Macro program LISTSAMPLE writes to the SAS log the following error message after submission of the second call.
ERROR: ***** Data set books.ytdsaless cannot be listed.
Example 11.2: Editing Character Data for Comparisons
Character data such as names, titles, and addresses can be stored various ways. When you want to select observations from a data set based on a character data value, you may have to edit the value so that you can find as many matching observations as possible in the data set. From your text value, you may need to remove extra blanks and punctuation and convert the value to a specific case. If you commonly edit a type of value the same way, you may want to create a utility macro program that does this task for you, which you can later reference when needed.
Example 11.2 submits a PROC TABULATE step that lists sales by quarter and book format of all titles by an author. It defines a macro program, TRIMNAME, that edits the author’s name to remove extra blanks and all punctuation, except for commas, and converts the author’s name to uppercase. Macro program TRIMNAME is called twice. The first time it is referenced is on the WHERE statement of the PROC TABULATE step. The resolved value returned by TRIMNAME is supplied to the WHERE statement. The second reference is inserted in the title.
Using multiple SAS language and macro functions, TRIMNAME edits the parameter value it receives. Note there are no macro or SAS language statements in TRIMNAME. All that TRIMNAME does is apply the series of functions to the parameter value that it receives.
Note the usage of the quoting functions %QUPCASE and %SUPERQ. In this example, the author’s name can contain a comma and without using these functions, the comma is interpreted as a separator between arguments to functions %CMPRES and COMPRESS, respectively. Omitting the quoting functions generates errors.
%macro trimname(namevalue);
%cmpres(%qupcase(%sysfunc(
compress(%superq(namevalue),%str(, ),kA))))
%mend trimname;
proc tabulate data=books.ytdsales
(where=(upcase(author)=
“%trimname(%str(wright, LINDA))”))
;
title “Title list for %trimname(%str(wright, linda))”;
class booktitle datesold bookfmt;
tables booktitle=' ',
datesold=“Quarter Sold” all='**Total Books Sold',
all=“Books Sold”*(bookfmt all='Total')*n=' '*f=3. /
misstext='0';
format datesold qtr.;
run;
Output 11.1 shows the results of the PROC TABULATE step, including the editing of the author’s name for insertion in the title.
Output 11.1 Output from Example 11.2
In your SAS programming, you may need to program the same process in different applications. For example, perhaps your company requires that all reports have the same dimensions, a title written a certain way, and a footnote identifying program name and programmer. It is often useful to save these routine, frequently used tasks as macro programs in a library of utilities.
Example 11.3: Standardizing RTF Output
Example 11.3 defines two macro programs that manage production of reports sent to the ODS RTF destination and applies these to the production of a report by PROC REPORT. The tasks that these two macro programs accomplish are examples of the kinds of routine tasks you might want to consider adding to your library of utility routines.
The first macro program, RTF_START, initializes settings when sending a report to the ODS RTF destination. The second macro program, RTF_END, resets options and closes the RTF destination after the report or reports are produced. Macro program RTF_START does the following tasks:
• closes the HTML destination
• changes the orientation to that specified by the value of the ORIENTATION parameter
• turns off the SAS option DATE
• specifies an ODS style to use in producing the report
• specifies TITLE1 and FOOTNOTE1 statements.
Macro program RTF_END does the following tasks:
• closes the RTF destination
• opens the HTML destination
• resets the orientation to PORTRAIT
• turns on SAS option DATE
• clears the TITLE1 and FOOTNOTE1 statements.
Example 11.3 first submits and compiles the two macro programs. Then macro program RTF_START executes, followed by a PROC REPORT step. Last, macro program RTF_END executes.
The call to RTF_START specifies the ODS style MONEY that is found in SASUSER.TMPLMST.
%macro rtf_start(style=,orientation=);
%* This macro program initializes settings to send reports
to ODS RTF destination;
ods html close;
options orientation=&orientation nodate;
ods rtf style=&style;
title1 justify=center “Bookstore”;
footnote justify=right “Report Prepared &sysdate9”;
%mend rtf_start;
%macro rtf_end;
%* This macro program resets options and closes the RTF
destination after sending a report to the ODS RTF
destination;
ods rtf close;
ods html;
options orientation=portrait date;
title;
footnote1;
%mend rtf_end;
%rtf_start(style=money,orientation=landscape)
proc report data=books.ytdsales nowd;
column section saleprice;
define section / group;
define saleprice / sum analysis format=dollar11.2;
rbreak after / summarize;
compute after;
section='** Totals **';
endcomp;
run;
%rtf_end
A copy of the report follows in Output 11.2.
Output 11.2 Output from Example 11.3
Example 11.4: Documenting Characteristics of a Data Set
As a programmer, you might frequently want to list the same specific information about a data set in a specific order to document your work. You could submit multiple steps and review the output to do this. Alternatively, you could write a macro program to accomplish all the steps and save the macro program in your library of utilities that you can reference when needed.
Macro program FACTS in Example 11.4 determines specific information about a SAS data set, lists this information, and lists the first five observations of the data set. It saves the output in a PDF file. The information presented is available in several procedures. The goal of this macro program is to list only specific pieces of information in a specific order to produce customized documentation of the data set.
The only parameter to FACTS is DSNAME, which is the name of the data set that FACTS should examine. Macro program FACTS saves the data set characteristics in a data set and displays the information with PROC PRINT. Output from this program is directed to the ODS PDF destination. Temporary data sets created by FACTS are deleted at the conclusion of the macro program.
The program is long, but it does just a few tasks. Macro program FACTS creates several macro variables with PROC SQL and accesses the data set descriptive information from dictionary tables.
The data set that contains the information for the report has two variables, ATTRIBUTE and VALUE. The information obtained by PROC SQL and saved in macro variables is assigned to these two variables. The macro variables created by PROC SQL are in bold and underlined in Example 11.4.
Macro program FACTS could be improved with error checking. For example, the first task that could be done is to determine if the data set exists. If not, a different report could be produced. Actions could also be specified based on the results obtained from the dictionary tables. Different ODS destinations and further enhancements of the report could be made.
Note that all the macro variables created in macro program FACTS are defined as local macro variables on the %LOCAL statement. This action prevents conflicts in macro variable resolution if these macro variables had previously been defined in open code or by a macro program that calls FACTS.
%macro facts(dsname);
%local dslib dsmem varpos varalpha dslabel crdate modate
nobs nvar;
%let dsname=%upcase(&dsname);
%*----Extract each part of data set name;
%let dslib=%scan(&dsname,1,.);
%let dsmem=%scan(&dsname,2,.);
proc sql noprint;
create table npos as
select npos,name
from dictionary.columns
where libname=“&dslib“and memname=“&dsmem”
order by npos;
select name into :varpos separated by ', ' from npos;
select name
into :varalpha separated by ', '
from dictionary.columns
where libname=“&dslib“and memname=“&dsmem”
order by name;
select memlabel,crdate,modate,nobs,nvar
into :dslabel,:crdate,:modate,:nobs,:nvar
from dictionary.tables
where libname=“&dslib“and memname=“&dsmem”;
quit;
data temp;
length attribute $ 35
value $ 500;
*----Create an observation for each characteristic of the
data set;
attribute='Creation Date and Time';
value=“&crdate”;
output;
attribute='Last Modification Date and Time';
value=“&modate”;
output;
attribute='Number of Observations';
value=“&nobs”;
output;
attribute='Number of Variables';
value=“&nvar”;
output;
attribute='Variables by Position';
value=“&varpos”;
output;
attribute='Variables Alphabetically';
value=“&varalpha”;
output;
run;
ods html close;
ods pdf style=statistical;
title “Data Set Report for &dsname %trim(&dslabel)”;
proc print data=temp noobs label;
var attribute value;
label attribute='Attribute'
value='Value';
run;
proc print data=&dsname(obs=5);
title2 “First 5 Observations”;
run;
ods pdf close;
ods html;
proc datasets library=work nolist;
delete temp npos;
run;
quit;
%mend facts;
%facts(books.ytdsales)
Output 11.3 presents the results of applying macro program FACTS to data set BOOKS.YTDSALES as specified in the last statement in Example 11.4.
Output 11.3 Output from Example 11.4
