Chapter 9 Interfaces to the Macro Facility
Understanding DATA Step Interfaces to the Macro Facility
Understanding the SYMGET Function
Understanding the SYMPUT and SYMPUTX Call Routines
Understanding the CALL EXECUTE Routine
Understanding the RESOLVE Function
Using Macro Facility Features in PROC SQL
Creating and Updating Macro Variables with PROC SQL
Using the Macro Variables Created by PROC SQL
Displaying Macro Option Settings with PROC SQL and Dictionary Tables
The interfaces described in this chapter provide you with a dynamic communication link between the SAS language and the macro facility. Until now, the discussion of the macro facility has emphasized the distinction between when macro language statements are resolved and when SAS language statements are resolved, and how the macro language can build SAS code and control SAS processing. With the interfaces described in this chapter, your SAS language programs can direct the actions of the macro processor.
The interfaces described in this chapter include SAS language functions and PROC SQL.
The following interfaces are not described in this chapter and are beyond the scope of this book. For further information about these topics, see Documentation in the Knowledge Base, at http://support.sas.com.
• SAS Component Language (SCL) functions and routines
• %SYSLPUT and %SYSRPUT, two macro functions that provide an interface with SAS/CONNECT
Five functions and four call routines in the SAS language can interact with the macro processor during execution of a DATA step. Table 9.1 lists these nine tools.
Table 9.1 DATA step interface tools
|
Tool |
Description |
|
CALL SYMDEL(macro-variable<,’NOWARN’) |
Deletes macro-variable from the global symbol table. The NOWARN option suppresses the SAS warning in the SAS log if macro-variable does not exist. |
|
SYMEXIST(argument) |
Determines if the macro variable specified as argument exists. SYMEXIST returns a 0 if the macro variable does not exist, and a 1 if it does. |
|
SYMGET(argument) |
Obtains the value of a macro variable specified as argument and returns this value as a character value. |
|
SYMGLOBL(argument) |
Determines if the macro variable specified as argument is global to the DATA step during execution of the DATA step. SYMGLOBL returns a 0 if the macro variable is not in the global symbol table, and a 1 if it is. |
|
SYMLOCAL(argument) |
Determines if the macro variable specified as argument is local to the DATA step during execution of the DATA step. SYMLOCAL returns a 0 if the macro variable is not in an existing local symbol table during DATA step execution, and a 1 if it is. |
|
CALL SYMPUT(macro-variable, value) |
Assigns value to macro-variable. This routine does not trim leading and trailing blanks. |
|
CALL SYMPUTX(macro-variable, value <,symbol-table>) |
Assigns value to macro-variable. This routine removes both leading and trailing blanks. Optionally, this routine can direct the macro processor to store the macro variable in a specific symbol table (G=global, L=local, and F=most local if it exists). |
|
CALL EXECUTE(argument) |
Executes the resolved value of argument. Arguments that resolve to a macro facility reference execute immediately. Arguments that resolve to SAS language statements execute after the DATA step that includes CALL EXECUTE ends. |
|
RESOLVE(argument) |
Macro facility resolves argument during DATA step execution where argument can be an expression that includes macro variables and macro program calls. |
The SYMGET SAS language function retrieves macro variable values from the macro symbol tables during execution of a DATA step. The SYMGET function returns a character value. With this function, you can create and update data set variables with information that the macro processor retrieves from macro variables.
A macro variable that you reference with SYMGET must exist before you apply it in a DATA step. If you create a macro variable in the same DATA step with CALL SYMPUT or CALL SYMPUTX, you can retrieve the macro variable value with SYMGET if it follows the CALL SYMPUT or CALL SYMPUTX call.
By default, SYMGET creates a character variable with a length of 200 bytes. You can specify a different length with either the LENGTH or ATTRIB statement. If the DATA step variable is defined as numeric, SAS attempts to convert the value that SYMGET retrieves to a number and writes a warning message to the SAS log.
The SYMGET function accepts three types of arguments:
• the name of a macro variable that is enclosed in single quotation marks and without the leading ampersand. In the following example, assume X is a macro variable that was defined earlier in the SAS session.
y=symget('x'),
• the name of a DATA step character variable whose value is the name of a macro variable. (See Example 9.1 for a discussion of this code.)
%let certific=CRT283817;
%let networki=NET3UD697;
%let operatin=OPSI18375;
%let programm=PRG8361WQ;
%let software=SFT3521P8;
%let webdevel=WBD188377;
data temp;
set books.ytdsales;
attrib compsect length=$8 label='Section'
sectionid length=$9 label='Section ID';
*----Construct macro variable name by compressing
section name and taking the first 8 characters.
e.g. section=Programming, then COMPSECT=“Programm”;
compsect=substr(compress(section),1,8);
sectionid=symget(compsect);
run;
proc print data=temp;
title “Defining the Section Identification Code”;
var section compsect sectionid;
run;
• a DATA step character expression. The resolution of the character expression is the name of a macro variable. (See Example 9.2 for a discussion of similar code.)
%let factor1=1.10;
%let factor2=1.23;
%let factor3=1.29;
data projections;
set books.ytdsales;
array factor{3} factor1-factor3;
array newprice{3} newprice1-newprice3;
format newprice1-newprice3 dollar10.2;
drop i;
do i=1 to 3;
factor{i}=input(symget(cats('factor',put(i,1.))),best8.);
newprice{i}=factor{i}*saleprice;
end;
run;
The next three examples illustrate the three types of arguments that SYMGET can receive.
Example 9.1: Using a Data Set Variable Name as the Argument to the SYMGET Function
Example 9.1 shows how the value of a data set variable can be used to specify the macro variable whose value SYMGET obtains. The open code %LET statements and the DATA step were presented earlier in this section.
Preceding the DATA step, %LET statements create six global macro variables, one for each of the six sections in the BOOK.YTDSALES data set. As the DATA step processes each observation in BOOK.YTDSALES, the SYMGET function extracts a value from one of the six macro variables based on the current observation’s value of the data set variable SECTION, and it stores the extracted value in DATA step variable SECTIONID. The value that the SYMGET function returns is a character value. The ATTRIB statement assigns a length of 9 bytes to SECTIONID, which overrides the default length of 200 bytes.
The data set variable COMPSECT that the data set creates stores the name of the macro variable that contains the specific section’s identification code. COMPSECT equals the first eight characters of the section name after blanks in those first eight characters have been removed.
%let certific=CRT283817;
%let networki=NET3UD697;
%let operatin=OPSI18375;
%let programm=PRG8361WQ;
%let software=SFT3521P8;
%let webdevel=WBD188377;
data temp;
set books.ytdsales;
attrib compsect length=$8 label='Section'
sectionid length=$9 label='Section ID';
*----Construct macro variable name by compressing
section name and taking the first 8 characters.
e.g. section=Programming, then COMPSECT=“Programm”;
compsect=substr(compress(section),1,8);
sectionid=symget(compsect);
run;
proc print data=temp;
title “Defining the Section Identification Code”;
var section compsect sectionid;
run;
Output 9.1 presents a partial listing of the PROC PRINT report produced by Example 9.1. The output shows the values assigned to SECTIONID by SYMGET.
Output 9.1 Partial output from Example 9.1
Example 9.2: Retrieving Macro Variable Values and Creating Numeric Data Set Variables with SYMGET
Example 9.2 directly references two macro variables with the SYMGET function. The two macro variables are defined in open code preceding the DATA step in which they are referenced. On each iteration of the DATA step, SAS determines which macro variable value to retrieve based on the current observation’s value for data set variable SECTION. The DATA step selects specific
observations from the data set and then creates a new numeric variable whose value is the product of a variable in the data set and the value of a macro variable.
%let webfctr=1.20;
%let sftfctr=1.35;
data temp;
set books.ytdsales(where=(
section in ('Web Development', 'Software')));
if section='Web Development' then
costfctr=input(symget('webfctr'),best8.);
else if section='Software' then
costfctr=input(symget('sftfctr'),best8.);
newprice=costfctr*cost;
run;
proc print data=temp;
title “Prices based on COSTFCTR”;
var booktitle section cost costfctr newprice;
format newprice dollar8.2;
run;
Output 9.2 presents a partial listing of the PROC PRINT report produced by Example 9.2. The output shows that a value was assigned to COSTFCTR depending on the value of SECTION.
Output 9.2 Partial output from Example 9.2
Example 9.3: Using the Resolution of a Character Expression As an Argument to SYMGET
The DATA step in Example 9.3 resolves SAS language character expressions to obtain the names and values of macro variables. The goal of the program is to obtain the manager’s initials for the quarter in which a book was sold. Preceding the DATA step, four %LET statements create four macro variables, one for the manager’s initials in each quarter.
As the DATA step processes each observation in BOOK.YTDSALES, the SYMGET function extracts a value from one of the four macro variables based on the current observation’s value of the data set variable DATESOLD. This value is assigned to data set variable MANAGERINITS. The quarter of the sale date is determined and the value of quarter (1, 2, 3, or 4) determines from which macro variable the SYMGET function retrieves a value.
The DATA step assigns a length of 3 bytes to MANAGERINITS, which overrides the default length of 200 bytes.
%let managerquarter1=LPL;
%let managerquarter2=EMB;
%let managerquarter3=EMB;
%let managerquarter4=ADL;
data managers;
set books.ytdsales;
length managerinits $ 3;
managerinits=
symget(cats('managerquarter',put(qtr(datesold),1.)));
run;
proc print data=managers;
title “Sale Dates and Managers”;
var datesold managerinits;
run;
Output 9.3 presents a partial listing of the PROC PRINT report produced by Example 9.3. The output shows the values assigned to MANAGERINITS by SYMGET.
Output 9.3 Partial output from Example 9.3
The SYMPUT and SYMPUTX SAS language call routines create macro variables during execution of a DATA step. If the macro variable already exists, these routines update the value of the macro variable.
CALL SYMPUTX. The syntax of the CALL SYMPUTX routine is:
CALL SYMPUTX(macro-variable,text<,symbol-table>)
CALL SYMPUT. The syntax of the SYMPUT routine is:
CALL SYMPUT(macro-variable,text)
The two functions differ in that CALL SYMPUTX trims leading and trailing blanks from the value assigned to the macro variable while CALL SYMPUT does not. Additionally, the CALL SYMPUTX allows you to specify the symbol table in which you want to store the macro variable.
In most situations, SAS recommends CALL SYMPUTX, and the examples in this section use only CALL SYMPUTX. CALL SYMPUTX is a newer function than CALL SYMPUT.
The first two arguments to CALL SYMPUTX and the two arguments to CALL SYMPUT can each be specified in one of three ways:
• as literal text. The following SAS language statement creates or updates the macro variable BOOKSECT with the value Software. Since CALL SYMPUTX is a SAS language routine, you must enclose literal text arguments in quotation marks.
call symputx('booksect','Software'),
• as the name of a data set character variable whose value is a SAS variable name. The current value of the data set variable NHIGH is assigned to the macro variable N30. The name of the macro variable, N30, is saved in DATA step character variable RESULTVAR.
resultvar='n30'; call symputx(resultvar,nhigh);
• as a character expression. The first argument to CALL SYMPUT below defines a macro variable name where the first part of the name is equal to the text AUTHORNAME. The second part of the macro variable name is equal to the automatic variable _N_. The second argument resolves to a text string. The literal text in the first part of the string and the current observation’s value for AUTHOR are concatenated. During the fifth iteration of the DATA step that contains this statement, CALL SYMPUT defines a macro variable named AUTHORNAME5.
call symputx(cats('authorname',put(_n_,4.)), cat('Author Name: ',author));
The third argument to CALL SYMPUTX, which tells the macro processor the symbol table where to store the macro variable, is optional. This third argument can be specified as a character constant, data set variable, or expression. The first non-blank letter in this optional argument determines where the macro processor stores the macro variable. Valid values for this optional argument can be one of three values:
• G, which specifies that the macro processor store the macro variable in the global symbol table even if the local symbol table exists.
• L, which specifies that the macro processor store the macro variable in the most local symbol table. If a macro program is not executing when this option is specified, there will be no local symbol table. In such a situation, the most local symbol table is actually the global symbol table, which is where the macro processor will store the macro variable.
• F, which specifies that if the macro variable exists in any symbol table, CALL SYMPUTX should update the macro variable’s value in the most local symbol table in which it exists. If it does not exist in any symbol table, CALL SYMPUTX stores the macro variable in the most local symbol table.
Each of these two call routines updates the value of an existing macro variable. A macro variable can have only one value. Even though your DATA step might cause the call routine to be executed with each pass of the DATA step, the macro variable that the routines reference can still have only one value. When the DATA step ends, the value of the macro variable being updated has the last value that was assigned by SYMPUT or SYMPUTX.
Example 9.4: Saving the Sum of a Variable in a Macro Variable by Executing CALL SYMPUTX Once at the End of a DATA Step
In Example 9.4, CALL SYMPUTX creates macro variable N30 whose value is the total number of books that sold for at least $30.00. The program then places this tally in the title of a PROC MEANS report.
The program directs that CALL SYMPUTX execute once when the DATA step reaches the end of the data set and that it store the formatted value of data set variable NHIGH in macro variable N30. The PUT function formats the value assigned to N30 with the COMMA format.
If the DATA step was written so that CALL SYMPUTX executed with each pass of the DATA step, the macro variable value would be updated with each observation. Since the goal is to obtain the total number of books that sold for more than $30.00, it is necessary to execute CALL SYMPUTX only once after the tally is complete. The second IF statement directs that the CALL SYMPUTX routine execute only when the DATA step reaches the end of data set BOOKS.YTDSALES.
data _null_;
set books.ytdsales end=eof;
if saleprice ge 30 then nhigh+1;
if eof then call symputx('n30',put(nhigh,comma.));
run;
proc means data=books.ytdsales n mean min max sum maxdec=2;
title “All Books Sold”;
title2 “Number of Books Sold for More Than $30: &n30”;
var saleprice;
run;
Output 9.4 presents the PROC MEANS report. The title includes the total number of books that sold for at least $30.00.
Output 9.4 Output from Example 9.4
If you used CALL SYMPUT instead of CALL SYMPUTX, you might need to do additional processing to remove leading and trailing blanks. When you assign a numeric variable value to a macro variable with CALL SYMPUT, the numeric value is converted to character by default, and then this character value is stored in the macro variable. The default width of the character field passed to the macro variable is 12 characters and a numeric value is right aligned. Depending on the output destination of your report, you may have leading blanks before the value.
Leading blanks are removed when the output is sent to the HTML destination. If the output goes to the PDF or LISTING destinations, leading blanks are not removed.
Consider what happens when the CALL SYMPUT routine is used instead of CALL SYMPUTX, the PUT function is removed, and the destination is PDF.
if eof then call symput('n30',nhigh);
The title now looks like the following, and eight leading blanks precede the four-digit numeric value.
Number of Books Sold for More Than $30: 1326
Execution of this version of the CALL SYMPUT function causes the following note to be written to the SAS log:
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
Example 9.5: Executing CALL SYMPUTX Multiple Times in a DATA Step
In Example 9.5, CALL SYMPUTX executes with each pass of the DATA step, which is once for each record in the data lines. The value of the macro variable at the end of the DATA step is the value from the last observation read from the raw data.
data newbooks;
input booktitle $ 1-40;
call symputx('lasttitle',booktitle);
datalines;
Hello Java Programming
My Encyclopedia of Networks
Strategic Computer Programming
Everyday Email Etiquette
run;
%put The value of macro variable LASTTITLE is &lasttitle..;
The %PUT statement writes the following to the SAS log.
The value of macro variable LASTTITLE is Everyday Email Etiquette.
Example 9.6: Creating Several Macro Variables with CALL SYMPUTX
CALL SYMPUTX creates multiple macro variables in this example. The DATA _NULL_ step creates two macro variables for each section in the output data set PROC FREQ created. PROC FREQ saves six observations in the SECTNAME output data set, one for each section in the BOOKS.YTDSALES data set. Therefore, the DATA step creates 12 macro variables. Six macro variables hold the names of the six sections. The other six macro variables hold the frequency counts for each of the sections. A %PUT _USER_ following the DATA step lists the 12 macro variables created in the DATA step.
proc freq data=books.ytdsales noprint;
tables section / out=sectname;
run;
data _null_;
set sectname;
call symputx(cats('name',put(_n_,1.)),section);
call symputx(cats('n',put(_n_,1.)),count);
run;
%put _user_;
The following %PUT _USER_ output displays the values of the macro variables defined in the DATA step.
GLOBAL NAME1 Certification and Training
GLOBAL N1 524
GLOBAL NAME2 Networking
GLOBAL N2 479
GLOBAL NAME3 Operating Systems
GLOBAL N3 578
GLOBAL NAME4 Programming
GLOBAL N4 337
GLOBAL NAME5 Software
GLOBAL N5 857
GLOBAL NAME6 Web Development
GLOBAL N6 571
Example 9.7: Creating a Macro Variable with CALL SYMPUTX and Specifying Its Symbol Table
Example 9.7 computes statistics on a subset of a data set and assigns the values of the statistics to global macro variables. The goal is to make these macro variables global so that they are available for subsequent processing.
Macro program STATSECTION computes with PROC MEANS the mean, minimum, and maximum sale price for a specific section in BOOKS.YTDSALES; the program saves the statistics in output data set SECTIONRESULTS. The parameter SECTION passes to STATSECTION the name of the section for which to compute the statistics.
The PROC MEANS step does not print a report, but it does save the three statistics in an output data set. A DATA step processes the output data set. It uses CALL SYMPUTX to create three macro variables to hold the three statistics and to assign values to the macro variables. Additionally, CALL SYMPUTX specifies that the macro variables be stored in the global symbol table.
The three global macro variables created by this program are AVERAGE, MIN, and MAX. Three CALL SYMPUTX statements store the formatted values of the statistics in these macro variables.
If you did not specify that the macro processor store the macro variables in the global symbol table, the macro processor stores them in the local symbol table defined by macro program STATSECTION. Once STATSECTION completed processing, its local symbol table is deleted, and the values of the three macro variables are lost.
Macro program STATSECTION includes a %PUT _LOCAL_ statement to show that the only macro variable stored in the STATSECTION local macro symbol table is SECTION. (You could also use the %SYMGLOBL and %SYMLOCAL macro variable attribute functions described in Chapter 6 to determine whether a macro variable was stored globally or locally.)
Three TITLE statements follow the call to STATSECTION. These TITLE statements include references to the three macro variables created in STATSECTION.
%macro statsection(section);
proc means data=books.ytdsales noprint;
where section=“§ion”;
var saleprice;
output out=sectionresults mean=avgsaleprice
min=minsaleprice max=maxsaleprice;
run;
data _null_;
set sectionresults;
call symputx('average',put(avgsaleprice,dollar8.2),'G'),
call symputx('min',put(minsaleprice,dollar8.2),'G'),
call symputx('max',put(maxsaleprice,dollar8.2),'G'),
run;
%* Submit this statement to see the variables stored in the
STATSECTION local symbol table;
%put _local_;
%mend;
%statsection(Software)
title “Section Results for Average Sale Price: &average”;
title2 “Minimum Sale Price: &min”;
title3 “Maximum Sale Price: &max”;
Execution of the %PUT _LOCAL_ statement writes the following to the SAS log. The text “STATSECTION” refers to the name of the local symbol table.
STATSECTION SECTION Software
After executing %STATSECTION and the subsequent TITLE statements, the titles become:
Section Results for Average Sale Price: $34.28
Minimum Sale Price: $28.04
Maximum Sale Price: $39.06
If you dropped the third argument in the three CALL SYMPUTX calls, the macro processor stores the three macro variables in the STATSECTION local symbol table. Then when STATSECTION ends, the macro processor deletes the STATSECTION local symbol table; the values of the three macro variables are not available for insertion into the titles.
The three DATA step statements would be rewritten as follows.
call symputx('average',put(avgsaleprice,dollar8.2));
call symputx('min',put(minsaleprice,dollar8.2));
call symputx('max',put(maxsaleprice,dollar8.2));
The %PUT _LOCAL_ statement would now produce the following output in the SAS log.
STATSECTION MIN $28.04
STATSECTION MAX $39.06
STATSECTION SECTION Software
STATSECTION AVERAGE $34.28
The references to the three macro variables in the TITLE statements cannot be resolved because the macro variables do not exist in the global symbol table. With the three macro variables stored in the local macro symbol table, the three titles become:
Section Results for Average Sale Price: &average
Minimum Sale Price: &min
Maximum Sale Price: &max
Note that if you want to try out the code that does not specify that the macro processor save the macro variables in the global symbol table and you want to see the unresolved macro variable results in the titles, make sure you delete the three macro variables from the global symbol table. You can delete the three macro variables with the %SYMDEL statement.
%symdel average min max;
The CALL EXECUTE SAS language routine in a DATA step takes as its argument a character expression or constant text that SAS resolves to a macro program invocation or SAS statements. With CALL EXECUTE, you can conditionally call a macro program using SAS language logic statements such as IF-THEN-ELSE. Additionally, you can assign a DATA step variable’s value as a parameter to the macro program that CALL EXECUTE references.
The argument that you supply to CALL EXECUTE does not only have to resolve to a macro program reference. As mentioned above, the argument to CALL EXECUTE can be SAS language statements. For detailed information on how to use CALL EXECUTE in this context, refer to SAS documentation.
When the argument to CALL EXECUTE is a macro program reference, that macro program executes immediately during execution of the DATA step. However, if that macro program generates a DATA step or PROC step, those steps execute after the DATA step finishes.
The syntax of the CALL EXECUTE routine is
call execute('argument')
The three types of arguments that can be supplied to the routine are:
• a text string enclosed in quotation marks. Single quotation marks and double quotation marks are handled differently. Single quotation marks cause the argument to be resolved when the DATA step executes. Double quotation marks cause the argument to be resolved by the macro processor during construction of the DATA step, before compilation and execution of the DATA step.
• the name of a data set character variable. This variable’s value can be a text expression or a SAS language statement. Do not enclose the variable name in quotation marks.
• a text expression that the DATA step can resolve to a SAS language statement or to a macro variable, macro language statement, or macro program reference.
Example 9.8: Illustrating the Timing of CALL EXECUTE When It Invokes a Macro Program That Submits Macro Statements
Example 9.8 is a simple example that demonstrates how a macro program invoked by CALL EXECUTE executes immediately within the DATA step. The macro program LISTAUTOMATIC issues a %PUT statement that lists the automatic variables. The DATA step that follows contains a CALL EXECUTE statement that explicitly invokes LISTAUTOMATIC.
%macro listautomatic;
%put **** Start list of automatic macro variables;
%put _automatic_;
%put **** End list of automatic macro variables;
%mend listautomatic;
data _null_;
call execute('%listautomatic'),
run;
The SAS log for this program shows that the macro program executes during execution of the DATA step. The results of the %PUT _AUTOMATIC_ statement appear before the notes that indicate the DATA step has ended. SAS informs you when your CALL EXECUTE does not generate any SAS language statements, as shown by the last note.
48 %macro listautomatic;
49 %put **** Start list of automatic macro variables;
50 %put _automatic_;
51 %put **** End list of automatic macro variables;
52 %mend listautomatic;
53
54 data _null_;
55 call execute('%listautomatic'),
56 run;
**** Start list of automatic macro variables
AUTOMATIC AFDSID 0
AUTOMATIC AFDSNAME
AUTOMATIC AFLIB
AUTOMATIC AFSTR1
AUTOMATIC AFSTR2
AUTOMATIC FSPBDV
AUTOMATIC SYSBUFFR
.
.
.
AUTOMATIC SYSTIME 08:20
AUTOMATIC SYSUSERID My Userid
AUTOMATIC SYSVER 9.4
.
.
.
**** End list of automatic macro variables
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: CALL EXECUTE routine executed successfully, but no SAS statements were generated.
Example 9.9: Illustrating the Timing of CALL EXECUTE When It Invokes a Macro Program That Submits Macro Statements and a PROC Step
Example 9.9 demonstrates that macro statements execute during execution of a DATA step and that SAS statements generated by the macro program execute after the DATA step finishes. It defines and invokes macro program LISTLIBRARY that submits two %PUT statements and a PROC DATASETS step. Two SAS language PUT statements illustrate the timing of SAS language statements within the DATA step that submits CALL EXECUTE.
The macro program LISTLIBRARY issues a PROC DATASETS for the BOOKS library. Within LISTLIBRARY, a %PUT precedes the PROC step code, and a second %PUT statement follows the PROC step code. Within the DATA step, one PUT statement precedes and a second PUT statement follows CALL EXECUTE.
%macro listlibrary;
%put
**** This macro statement in LISTLIBRARY precedes the PROC step code.;
proc datasets library=books;
run;
quit;
%put
**** This macro statement in LISTLIBRARY follows the PROC step code.;
%mend listlibrary;
data _null_;
put “This SAS language statement precedes the macro program call.”;
call execute('%listlibrary'),
put “This SAS language statement follows the macro program call.”;
run;
The SAS log for this program shows that the %PUT statements execute during execution of the DATA step as do the SAS language PUT statements. The text written by the %PUT statements appears in the SAS log during execution of the DATA step. The PROC DATASETS output, however, does not appear until after the DATA step concludes.
During execution of the DATA step, the macro processor directs immediate execution of the two %PUT statements while it places the PROC step on the input stack for execution after the DATA step concludes.
140 %macro listlibrary;
141 %put **** This macro statement in LISTLIBRARY precedes the PROC step;
142 proc datasets library=books;
143 run;
144 quit;
145 %put **** This macro statement in LISTLIBRARY follows the PROC step;
146 %mend listlibrary;
147
148 data _null_;
149 put “This SAS language statement precedes the macro program call.”;
150 call execute('%listlibrary'),
151 put “This SAS language statement follows the macro program call.”;
152 run;
This SAS language statement precedes the macro program call.
**** This macro statement in LISTLIBRARY precedes the PROC step
**** This macro statement in LISTLIBRARY follows the PROC step
This SAS language statement follows the macro program call.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: CALL EXECUTE generated line.
1 + proc datasets library=books;
Directory
Libref BOOKS
Engine V9
Physical Name f:ooks
File Name f:ooks
Member File
# Name Type Size Last Modified
1 YTDSALES DATA 771072 02Feb15:16:21:13
1 + run;
1 + quit;
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
Example 9.10: Using CALL EXECUTE to Conditionally Call a Macro Program
Example 9.10 uses CALL EXECUTE to conditionally execute the macro program REP16K. First, PROC MEANS computes the sales for each section in BOOKS.YTDSALES and stores the results in an output data set. A DATA step processes the output data set and examines the total sales per section. When the sales exceed $16,000, the CALL EXECUTE statement in the DATA step calls macro program REP16K.
The argument to CALL EXECUTE is the macro program name, REP16K. This macro program has one parameter, SECTION. The argument to CALL EXECUTE is a text expression that resolves to the call to REP16K with the parameter specified as the current value of SECTION. The CATS function concatenates the parts of the macro program call.
The SAS language statements in the macro program execute when the DATA step finishes. In this example, the CALL EXECUTE routine executes as many times as there are observations that satisfy the IF statement condition. Each time CALL EXECUTE executes, it calls the macro program REP16K for the section value of the current observation. Therefore, when the DATA step finishes, there can be several PROC REPORT steps on the input stack ready to process.
In this example, total sales exceed $16,000 in two sections, “Certification and Training” and “Software”. Thus, two PROC REPORT steps execute after the DATA step.
%macro rep16k(section);
proc report data=books.ytdsales center nowd;
where section=“§ion”;
title “Sales > $16,000 Summary for §ion”;
column publisher n saleprice;
define publisher / group;
define n / “Number of Books Sold” ;
define saleprice / sum format=dollar10.2 “Sale Price” ;
rbreak after / summarize;
run;
%mend rep16k;
options mprint;
proc means data=books.ytdsales nway noprint;
class section;
var saleprice;
output out=sectsale sum=totlsale;
run;
data _null_;
set sectsale;
if totlsale > 160000 then
call execute(cats('%rep60k(',section,')'));
run;
The SAS log for this program shows that two PROC REPORT steps execute after completion of the DATA step. The option MPRINT is in effect and shows the code for the two PROC REPORT steps. Note that the processing of CALL EXECUTE also lists the code for the PROC REPORT steps.
222 %macro rep16k(section);
223 proc report data=books.ytdsales nowd;
224 where section=“§ion”;
225 title “Sales > $16,000 Summary for §ion”;
226 column publisher n saleprice;
227 define publisher / group;
228 define n / “Number of Books Sold” ;
229 define saleprice / sum format=dollar10.2 “Sale Price” ;
230 rbreak after / summarize;
231 compute after;
232 publisher=“**Total”;
233 endcomp;
234 run;
235 %mend rep16k;
236
237 options mprint;
238
239 proc means data=books.ytdsales nway noprint;
240 class section;
241 var saleprice;
242 output out=sectsale sum=totlsale;
243 run;
NOTE: There were 3346 observations read from the data set
BOOKS.YTDSALES.
NOTE: The data set WORK.SECTSALE has 6 observations and 4 variables.
NOTE: PROCEDURE MEANS used (Total process time):
real time 0.04 seconds
cpu time 0.00 seconds
244
245 data _null_;
246 set sectsale;
247
248 if totlsale > 16000 then
249 call execute(cats('%rep16k(',section,')'));
250 run;
MPRINT(REP16K): proc report data=books.ytdsales nowd;
MPRINT(REP16K): where section=“Certification and Training”;
MPRINT(REP16K): title “Sales >$16,000 Summary for Certification and Training”;
MPRINT(REP16K): column publisher n saleprice;
MPRINT(REP16K): define publisher / group;
MPRINT(REP16K): define n / “Number of Books Sold” ;
MPRINT(REP16K): define saleprice / sum format=dollar10.2 “Sale Price” ;
MPRINT(REP16K): rbreak after / summarize;
MPRINT(REP16K): compute after;
MPRINT(REP16K): publisher=“**Total”;
MPRINT(REP16K): endcomp;
MPRINT(REP16K): run;
MPRINT(REP16K): proc report data=books.ytdsales nowd;
MPRINT(REP16K): where section=“Software”;
MPRINT(REP16K): title “Sales >$16,000 Summary for Software”;
MPRINT(REP16K): column publisher n saleprice;
MPRINT(REP16K): define publisher / group;
MPRINT(REP16K): define n / “Number of Books Sold” ;
MPRINT(REP16K): define saleprice / sum format=dollar10.2 “Sale Price” ;
MPRINT(REP16K): rbreak after / summarize;
MPRINT(REP16K): compute after;
MPRINT(REP16K): publisher=“**Total”;
MPRINT(REP16K): endcomp;
MPRINT(REP16K): run;
NOTE: There were 6 observations read from the data set WORK.SECTSALE.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
NOTE: CALL EXECUTE generated line.
1 + proc report data=books.ytdsales nowd; where section=“Certification and Training”;
title “Sales > $16,000 Summary for Certification and Training”; column publisher n
saleprice; define publisher / group; define n / “Number of Books
2 + Sold” ; define saleprice / sum format=dollar10.2 “Sale Price” ; rbreak after /
summarize; compute after; publisher=“**Total”; endcomp; run;
NOTE: There were 524 observations read from the data set
BOOKS.YTDSALES.
WHERE section='Certification and Training';
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
3 + proc report data=books.ytdsales nowd; where section=“Software”; title “Sales >
$16,000 Summary for Software”; column publisher n saleprice; define publisher /
group; define n / “Number of Books Sold” ; define saleprice / sum
4 + format=dollar10.2 “Sale Price” ; rbreak after / summarize; compute after;
publisher=“**Total”; endcomp; run;
NOTE: There were 857 observations read from the data set
BOOKS.YTDSALES.
WHERE section='Software';
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds
Example 9.11: Using CALL EXECUTE to Call a Specific Macro Program
Example 9.11 shows how you can conditionally call different macro programs during the execution of a DATA step. The PROC steps constructed by the macro program execute after the DATA step ends.
Example 9.11 defines two macro programs: LOWREPORT and HIGHREPORT. Each macro program generates a different PROC REPORT step.
As in Example 9.10, PROC MEANS computes total sales by section and saves the results in an output data set. A DATA step examines the results and, depending on the value of the total sales, it determines whether one of the two macro programs should be executed. If sales exceed $20,000, then CALL EXECUTE specifies a call to macro program HIGHREPORT. If sales are less than $12,000, then CALL EXECUTE specifies a call to macro program LOWREPORT. Neither macro program is called if sales are between $12,000 and $20,000.
The argument to each of the two CALL EXECUTE references is a text expression that resolves either to a call to HIGHREPORT or to a call to LOWREPORT. Both macro programs have the same parameter, SECTION. The current observation’s value of SECTION is specified as part of the text expression that resolves to the macro program call. The CATS function concatenates the parts of the macro program call.
In this example, section “Software” exceeds total sales of $20,000. The DATA step calls macro program HIGHREPORT once.
Two sections, “Networking” and “Programming,” have total sales less than $12,000. The DATA step calls macro program LOWREPORT twice, once for each of these sections.
%macro highreport(section);
proc report data=books.ytdsales nowd;
where section=“§ion”;
title “Sales > $20,000 Report for Section §ion”;
column publisher n saleprice;
define publisher / group;
define n / “Number of Books Sold” ;
define saleprice / sum format=dollar10.2 “Sale Price” ;
rbreak after / summarize;
compute after;
publisher=“**Total”;
endcomp;
run;
%mend highreport;
%macro lowreport(section);
proc report data=books.ytdsales nowd;
where section=“§ion”;
title “Sales < $12,000 Report for Section §ion”;
column datesold n saleprice;
define datesold / group format=monname15. “Month Sold”
style(column)=[just=left];
define n / “Number of Books Sold”;
define saleprice / sum format=dollar10.2 “Sales Total”;
rbreak after / summarize;
run;
%mend lowreport;
proc means data=books.ytdsales nway noprint;
class section;
var saleprice;
output out=sectsale sum=totlsect;
run;
data _null_;
set sectsale;
if totlsect < 12000 then
call execute(cats('%lowreport(',section,')'));
else if totlsect > 20000 then
call execute(cats('%highreport(',section,')'));
run;
The SAS log for this program shows the three calls to the two macro programs. Compared to Example 9.10, MPRINT is not in effect when Example 9.11 executes. The results of the CALL EXECUTE call, however, are displayed.
951 %macro highreport(section);
952 proc report data=books.ytdsales nowd;
953 where section=“§ion”;
954 title “Sales > $20,000 Report for Section §ion”;
955 column publisher n saleprice;
956 define publisher / group;
957 define n / “Number of Books Sold” ;
958 define saleprice / sum format=dollar10.2 “Sale Price” ;
959 rbreak after / summarize;
960 compute after;
961 publisher=“**Total”;
962 endcomp;
963 run;
964 %mend highreport;
965
966 %macro lowreport(section);
967 proc report data=books.ytdsales nowd;
968 where section=“§ion”;
969 title “Sales < $12,000 Report for Section §ion”;
970 column datesold n saleprice;
971 define datesold / group format=monname15. “Month Sold”
972 style(column)=[just=left];
973 define n / “Number of Books Sold”;
974 define saleprice / sum format=dollar10.2 “Sales Total”;
975 rbreak after / summarize;
976 run;
977 %mend lowreport;
978
979 proc means data=books.ytdsales nway noprint;
980 class section;
981 var saleprice;
982 output out=sectsale sum=totlsect;
983 run;
NOTE: There were 3346 observations read from the data set
BOOKS.YTDSALES.
NOTE: The data set WORK.SECTSALE has 6 observations and 4 variables.
NOTE: PROCEDURE MEANS used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds
984
985 data _null_;
986 set sectsale;
987 if totlsect < 12000 then
988 call execute(cats('%lowreport(',section,')'));
989
990 else if totlsect > 20000 then
991 call execute(cats('%highreport(',section,')'));
992 run;
NOTE: There were 6 observations read from the data set WORK.SECTSALE.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
NOTE: CALL EXECUTE generated line.
1 + proc report data=books.ytdsales nowd; where section=“Networking”; title “Sales
< $12,000 Report for Section Networking”; column datesold n saleprice; define
datesold / group format=monname15. “Month Sold” style
2 +(column)=[just=left]; define n / “Number of Books Sold”; define saleprice / sum
format=dollar10.2 “Sales Total”; rbreak after / summarize; run;
NOTE: There were 479 observations read from the data set
BOOKS.YTDSALES.
WHERE section='Networking';
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
3 + proc report data=books.ytdsales nowd; where section=“Programming”; title
“Sales < $12,000 Report for Section Programming”; column datesold n saleprice;
define datesold / group format=monname15. “Month Sold”
4 + style(column)=[just=left]; define n / “Number of Books Sold”; define saleprice
/ sum format=dollar10.2 “Sales Total”; rbreak after / summarize; run;
NOTE: There were 337 observations read from the data set
BOOKS.YTDSALES.
WHERE section='Programming';
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds
5 + proc report data=books.ytdsales nowd; where section=“Software”; title “Sales >
$20,000 Report for Section Software”; column publisher n saleprice; define
publisher / group; define n / “Number of Books Sold” ; define
6 + saleprice / sum format=dollar10.2 “Sale Price”; rbreak after / summarize;
compute after; publisher=“**Total”; endcomp; run;
NOTE: There were 857 observations read from the data set
BOOKS.YTDSALES.
WHERE section='Software';
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds
The RESOLVE SAS language function can resolve a macro variable reference or macro program call during execution of a DATA step. The result returned by RESOLVE is a character value whose length equals that of the data set character variable to which the result is being assigned. It takes as its argument a text expression, a data set character variable, or a character expression that produces a text expression that can be resolved by the macro processor.
The RESOLVE function acts during execution of a DATA step. Therefore, a RESOLVE function could be coded so that it executes with each pass of a DATA step, and at each execution it could return a different value.
The RESOLVE function is similar to the SYMGET function in that both functions can resolve macro variable references during execution of a DATA step. However, the SYMGET function is more limited in its functionality and in the arguments it can accept. The RESOLVE function can accept a variety of different types of arguments, including macro variables and macro program calls, while SYMGET’s sole function is to resolve a macro variable reference.
The SYMGET function can resolve macro variables only if they exist before execution of the DATA step in which the function is included. An exception to this is if a statement containing CALL SYMPUT or CALL SYMPUTX that defines the macro variable executes before the SYMGET function. As stated above, the RESOLVE function acts during execution, and a macro variable defined in a DATA step with CALL SYMPUT or CALL SYMPUTX can be retrieved in the same DATA step with the RESOLVE function.
The syntax of the RESOLVE function is
resolve('argument')
The three types of arguments that RESOLVE accepts are:
• a text expression that is enclosed in single quotation marks. This text expression can be a macro variable reference, an open code macro language statement, or a macro program call. If you enclose the text expression in double quotation marks, the macro processor attempts to resolve it before the DATA step is compiled, while the SAS program is being constructed. Enclosing the argument in single quotation marks delays resolution until the DATA step executes.
dsvar=resolve('&macvar'),
• the name of a data set character variable. The value of this variable is a text expression representing a macro variable reference, an open code macro language statement, or a macro program call.
%let label1=All the Books Sold;
data temp;
length textlabel $ 40;
macexp='&label1';
textlabel=resolve(macexp);
run;
• a character expression that can be resolved to a text expression. The text expression represents a macro variable reference, an open code macro language statement, or a macro program call.
%let quartersale1=Holiday Clearance;
%let quartersale2=2 for the Price of 1;
%let quartersale3=Back to School;
%let quartersale4=New Releases;
data temp;
set books.ytdsales;
length quartersalename $ 30;
quarter=qtr(datesold);
quartersalename=resolve(
cats('&quartersale',put(quarter,1.)) );
run;
By default, the length of the text value returned by RESOLVE is 200 bytes. If you want the character variable that holds the result to have a different length, you must explicitly define the length for the variable. This can be done with the LENGTH statement or with the ATTRIB statement.
Example 9.12: Obtaining Macro Variable Values with RESOLVE by Resolving Character Expressions
The code in Example 9.12 was presented above in the discussion on the types of arguments that RESOLVE can accept. The goal of Example 9.12 is to create a character variable that contains the name of the sale in the quarter in which an item was sold. Sale names are stored in macro variables. The RESOLVE function in the DATA step looks up the correct sale name based on the quarter the item was sold. The argument to the RESOLVE function is a character expression that resolves to a macro variable name.
Example 9.12 defines four macro variables, QUARTERSALE1, QUARTERSALE2, QUARTERSALE3, and QUARTERSALE4, which contain the name of each quarter’s sale. The DATA step processes data set BOOKS.YTDSALES and determines the quarter in which each item sold. A macro variable name is constructed by concatenating the text part of the macro variable name, QUARTERSALE, to the quarter number. This expression is the argument to RESOLVE. RESOLVE returns the value of the specific macro variable and assigns this value to data set character variable QUARTERSALENAME.
Note that the text &QUARTERSALE is enclosed in single quotation marks. The single quotation marks prevent the macro processor from attempting to resolve a macro variable with that name during compilation of the DATA step.
Output 9.5 shows the results of the PROC FREQ crosstabulation of QUARTER and QUARTERSALENAME.
%let quartersale1=Holiday Clearance;
%let quartersale2=2 for the Price of 1;
%let quartersale3=Back to School;
%let quartersale4=New Releases;
data temp;
set books.ytdsales;
length quartersalename $ 30;
quarter=qtr(datesold);
quartersalename=resolve(
cats('&quartersale',put(quarter,1.)) );
run;
proc freq data=temp;
title 'Quarter by Quarter Sale Name';
tables quarter*quartersalename / list nocum nopct;
run;
Output 9.5 presents the PROC FREQ results produced by Example 9.12.
Output 9.5 Output from Example 9.12
Example 9.13: Using RESOLVE to Call a Macro Program within a DATA Step That Assigns Text to a Data Set Variable
This example shows how you can call a macro program from within a DATA step with the RESOLVE function. The macro program executes with each pass of the DATA step and it returns text to the DATA step.
As in Example 9.12, Example 9.13 looks up a sale name based on the quarter. In this example, PROC MEANS computes total sales by quarter for variable SALEPRICE and saves the results in an output data set. A DATA step processes the output data set created by PROC MEANS. The RESOLVE function executes the same macro program with each pass of the DATA step. The value of quarter is passed as a parameter to the macro program.
The definition for macro program GETSALENAME precedes the PROC MEANS step. When called by the RESOLVE function, macro program GETSALENAME simply looks up a text value based on the value of parameter QUARTER and returns this text to the DATA step. The assignment statement in the DATA step assigns this text value to data set variable QUARTERSALENAME.
As in Example 9.12, the argument to the RESOLVE function in Example 9.13 is constructed during execution of the DATA step, and the argument is enclosed in single quotation marks, which prevent the macro processor from attempting to resolve the call to the macro program during compilation of the DATA step.
%macro getsalename(quarter);
%if &quarter=1 %then %do;
Holiday Clearance
%end;
%else %if &quarter=2 %then %do;
2 for the Price of 1
%end;
%else %if &quarter=3 %then %do;
Back to School
%end;
%else %if &quarter=4 %then %do;
New Releases
%end;
%mend getsalename;
proc means data=books.ytdsales noprint nway;
class datesold;
var saleprice;
output out=quarterly sum=;
format datesold qtr.;
run;
data quarterly;
set quarterly(keep=datesold saleprice);
length quartersalename $ 30;
quartersalename=resolve( cats('%getsalename(',put(datesold,qtr.),')') );
run;
proc print data=quarterly label;
title 'Quarter Sales with Quarter Sale Name';
label datesold='Quarter'
saleprice='Total Sales'
quartersalename='Sale Name';
run;
Output 9.6 presents the PROC PRINT report produced by Example 9.13.
Output 9.6 Output from Example 9.13
Elements of PROC SQL can interface with the macro facility. During execution of a PROC SQL step, you can create and update macro variables. Additionally, with each execution of PROC SQL, the procedure creates and maintains macro variables that hold information about the processing of the PROC SQL step. This section describes only the macro facility interface features of PROC SQL. For complete information on PROC SQL, refer to PROC SQL documentation.
The INTO clause on the SELECT statement can create and update macro variables. Calculations that are done with the SELECT statement, as well as entire data columns, can be saved in the macro variables that you name with the INTO clause.
The INTO clause is analogous to the CALL SYMPUT and CALL SYMPUTX routines in the DATA step. Like these routines, the INTO clause creates and updates macro variables during execution of a step. In the case of the INTO clause, the step is a PROC SQL statement.
The INTO clause provides a link to the macro variable symbol table during execution of PROC SQL. Values that are assigned to the macro variables are considered to be text.
In general, SAS adds the macro variables that you create with PROC SQL to the most local macro symbol table available when PROC SQL executes. If PROC SQL is not submitted from within a macro program, the macro processor stores the macro variables in the global macro symbol table.
The basic syntax of the INTO clause on the PROC SQL SELECT statement follows:
SELECT col1,col2,...
INTO :macro-variable-specification
<, . . .macro-variable-specification>
FROM table-expression
WHERE where-expression
other clauses;
Note the punctuation on the INTO clause: the macro variable names are preceded with colons (:), not ampersands (&). Macro variables are named explicitly on the INTO clause. Numbered lists of macro variables can also be specified on the INTO clause. The macro variable specification can be written as follows.
• If you want to store the first value returned into a single macro variable, use this syntax. Leading and trailing blanks are preserved unless the TRIMMED option is specified.
:macro-variable <TRIMMED>
• If you want to store a series of values that are returned in a single macro variable, use this syntax. Unless the NOTRIM option is included, SAS removes leading and trailing blanks from the values saved in the macro variable. The SEPARATED BY option is required, and it specifies the character(s) that indicate row separation in the value of macro-variable.
:macro-variable <SEPARATED BY 'character(s)'<NOTRIM>>
• If you want to store the values that the SELECT statement returns in a series of macro variables, use this syntax. The value N does not have to be exactly equal to the number of values returned. If N is greater than the number of values returned, SAS creates the number of macro variables equal to the number returned. If N is less than the number of values returned, SAS creates only N macro variables. Unless the NOTRIM option is included, SAS removes leading and trailing blanks from the values saved in the macro variables.
:macro-variable-1 - macro-variable-n <NOTRIM>
• Another way of specifying a series of macro variables follows. The upper limit of the series is not specified. In this situation, SAS creates the number of macro variables equal to the number of values returned. Unless the NOTRIM option is included, SAS removes leading and trailing blanks from the values saved in the macro variables.
:macro-variable-1 - <NOTRIM>
The INTO clause cannot be used during creation of a table or view. It can be used only on outer queries of the SELECT statement.
Example 9.14: Using the INTO Clause in PROC SQL to Save Summarizations in Macro Variables
Example 9.14 presents a simple application of the INTO clause. The PROC SQL SELECT statement computes the total sales and the total number of books sold for a specific publisher identified by macro variable FINDPUBLISHER. It stores the computations in two macro variables, TOTSALES and NSOLD. A %PUT statement following the step writes the values of these two global macro variables to the SAS log.
%let findpublisher=Technology Smith;
proc sql noprint;
select sum(saleprice) format=dollar10.2,
count(saleprice)
into :totsales, :nsold
from books.ytdsales
where publisher=“&findpublisher”;
quit;
%put &findpublisher Total Sales=&totsales;
%put &findpublisher Total Number Sold=&nsold;
The SAS log for the preceding program follows.
27 %let findpublisher=Technology Smith;
28 proc sql noprint;
29 select sum(saleprice) format=dollar10.2,
30 count(saleprice)
31 into :totsales, :nsold
32 from books.ytdsales
33 where publisher=“&findpublisher”;
34 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
35 %put &findpublisher Total Sales=&totsales;
36 %put &findpublisher Total Number Sold=&nsold;
Technology Smith Total Sales= $5,503.41
Technology Smith Total Number Sold= 238
Notice the leading blanks before the resolved macro variable values on the results of the two %PUT statements. If you add the TRIMMED option to each macro variable reference on the INTO clause, SAS removes the leading blanks.
into :totsales trimmed, :nsold trimmed
When the INTO clause includes the two TRIMMED options, the results of the %PUT statements are as follows.
Technology Smith Total Sales=$5,503.41
Technology Smith Total Number Sold=238
Example 9.15: Demonstrating the Default Action of the INTO Clause in Saving the First Row of a Table
The default action of the PROC SQL INTO clause stores the first row of a table in the macro variables named on the INTO clause. This example demonstrates that action.
Example 9.15 sorts the BOOKS.YTDSALES data set by DATESOLD and saves the sorted observations in data set DATESORTED. The PROC SQL step creates three macro variables FIRSTDATE, FIRSTTITLE, and FIRSTPRICE, and it sets their values to the values of data set variables DATESOLD, BOOKTITLE, and SALEPRICE for the first observation in DATESORTED. Three %PUT statements following the step write the values of these three global macro variables to the SAS log. A PROC PRINT of the first five observations of DATESORTED shows that the values assigned to the macro variables were from the first observation in DATESORTED.
proc sort data=books.ytdsales out=datesorted;
by datesold;
run;
proc sql noprint;
select datesold,booktitle,saleprice
into :firstdate,:firsttitle,:firstprice
from datesorted;
quit;
%put One of the first books sold was on &firstdate;
%put The title of this book is &firsttitle;
%put The sale price was &firstprice;
proc print data=datesorted(obs=5);
title
'First Five Observations of Sorted by Date BOOKS.YTDSALES';
run;
The SAS log displays the values of the three macro variables.
6611 proc sql noprint;
6612 select datesold,booktitle,saleprice
6613 into :firstdate,:firsttitle,:firstprice trimmed
6614 from datesorted;
6615 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
6616 %put One of the first books sold was on &firstdate;
One of the first books sold was on 01/01/2014
6617 %put The title of this book is &firsttitle;
The title of this book is Web Development Title 9
6618 %put The sale price was &firstprice;
The sale price was $23.68
6619 proc print data=datesorted(obs=5);
6620 title 'First Five Observations of Sorted by Date BOOKS.YTDSALES';
6621 run;
NOTE: There were 5 observations read from the data set WORK.DATESORTED.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
Output 9.7 shows the PROC PRINT report that verifies the values of the macro variables created by the PROC SQL step.
Output 9.7 Output from Example 9.15
Example 9.16: Using the INTO Clause in PROC SQL to Create a Macro Variable for Each Row in a Table
Numbered lists on the INTO clause can store rows of a table in macro variables. The PROC SQL step in Example 9.16 totals the sales for each of six sections in the bookstore, producing an extract of six rows. The SELECT statement and the INTO clause save the six section names and six formatted total sales values in twelve macro variables.
In this example, it is known that BOOKS.YTDSALES has six section values. Example 9.19 shows a way to write the INTO clause if you do not know the number of values the SELECT clause will return.
proc sql noprint;
select section, sum(saleprice) format=dollar10.2
into :section1 - :section6,
:sale1 - :sale6 from books.ytdsales
group by section;
quit;
%put *** 1: §ion1 &sale1;
%put *** 2: §ion2 &sale2;
%put *** 3: §ion3 &sale3;
%put *** 4: §ion4 &sale4;
%put *** 5: §ion5 &sale5;
%put *** 6: §ion6 &sale6;
The SAS log showing the execution of the %PUT statements follows:
95 %put *** 1: §ion1 &sale1;
*** 1: Certification and Training $16,227.32
96 %put *** 2: §ion2 &sale2;
*** 2: Networking $11,353.04
97 %put *** 3: §ion3 &sale3;
*** 3: Operating Systems $15,963.34
98 %put *** 4: §ion4 &sale4;
*** 4: Programming $10,160.95
99 %put *** 5: §ion5 &sale5;
*** 5: Software $29,374.98
100 %put *** 6: §ion6 &sale6;
*** 6: Web Development $12,065.77
Example 9.17: Storing All Unique Values of a Table Column in One Macro Variable with PROC SQL
A feature of the INTO clause allows you to store all values of a column in one macro variable. These values are stored side by side. To do this, add the SEPARATED BY option to the INTO clause to define a character that delimits the string of values.
The PROC SQL SELECT statement in Example 9.17 stores all unique section names in the macro variable ALLSECT.
proc sql noprint;
select unique(section)
into :allsect separated by '/'
from books.ytdsales
order by section;
quit;
%put The value of macro variable ALLSECT is &allsect;
The SAS log showing the execution of the %PUT statement follows:
6681 %put The value of macro variable ALLSECT is &allsect;
The value of macro variable ALLSECT is Certification and Training/Networking/Operating Systems/Programming/Software/Web Development
Example 9.18: Storing All Values of a PROC SQL Dictionary Table Column in One Macro Variable
Example 9.18 is similar to Example 9.17 in that it saves all values of a column in one macro variable, but this example does not apply the UNIQUE function. Example 9.18 makes use of the DICTIONARY tables feature of PROC SQL. It saves in one macro variable DATASETNAMES the names of all the SAS data sets in a library specified by the value of macro variable LISTLIB. A blank separates the data set names assigned to DATASETNAMES. Assume there are three SAS data sets in library BOOKS: YTDSALES, SALES2013, and SALES2012.
proc sql noprint;
select memname
into :datasetnames separated by ' '
from dictionary.tables
where libname=“&listlib”;
quit;
%put The datasets in library &listlib is(are) &datasetnames;
The SAS log showing the execution of the %PUT statement follows:
6720 %put The datasets in library BOOKS is(are) &datasetnames;
The datasets in library BOOKS is(are)
SALES2012 SALES2013 YTDSALES
PROC SQL creates and updates four macro variables after it executes each statement. You can use these macro variables in your programs to control execution of your SAS programs. These macro variables are stored in the global macro symbol table. Table 9.2 lists the four PROC SQL macro variables.
Table 9.2 Macro variables created by PROC SQL
|
Macro Variable |
Description |
|
SQLEXITCODE |
Contains the highest return code that occurred from some types of SQL insert failures. This return code is written to the SYSERR macro variable when PROC SQL terminates. |
|
SQLOBS |
Set to the number of rows produced with a SELECT statement |
|
SQLRC |
Set to the return code from an SQL statement |
|
SQLOOPS |
Set to the number of iterations of the inner loop of PROC SQL |
The pass-through facility of PROC SQL also creates two macro variables, SQLXMSG and SQLXRC. These macro variables contain information about error conditions that might have occurred in the processing of pass-through facility SQL statements. For complete information on these macro variables, refer to SAS/ACCESS documentation. Table 9.3 describes the two macro variables.
Table 9.3 PROC SQL macro variables used with the pass-through facility
|
Macro Variable |
Description |
|
SQLXMSG |
Set to descriptive information and DBMS-specific return code generated by a pass-through facility SQL statement |
|
SQLXRC |
Set to the return code generated by a pass-through facility SQL statement |
Example 9.19: Using the PROC SQL SQLOBS Automatic Macro Variable
Macro program LISTSQLPUB in Example 9.19 uses the SQLOBS macro variable to list the values of a series of macro variables that were created by a SELECT statement and the INTO clause. The goal of the program is to save the names of the publishers in BOOKS.YTDSALES in a series of macro variables.
The INTO clause contains only the reference to the first macro variable in the PUB series of macro variables. This reference is followed by a dash (-). This structure tells SAS to create a macro variable for each value that the SELECT clause returns. The number of publishers in BOOKS.YTDSALES is 12. The SELECT statement returns twelve values and saves the values in macro variables PUB1-PUB12.
After the SELECT statement executes, PROC SQL updates the SQLOBS macro variable. The iterative %DO loop uses this value as its upper index value. The %PUT statement in the iterative %DO loop lists each publisher’s name.
Depending on the complexity of your programming, you might want to save the value of SQLOBS in another macro variable after that PROC SQL step ends. This would prevent loss of the SQLOBS value you need in case you submit other SELECT statements before you execute code that references that original SQLOBS value.
options mprint;
%macro listsqlpub;
proc sql noprint;
select unique(publisher)
into :pub1 -
from books.ytdsales;
quit;
%put Total number of publishers: &sqlobs..;
%do i=1 %to &sqlobs;
%put Publisher &i: &&pub&i;
%end;
%mend listsqlpub;
%listsqlpub
The SAS log after LISTSQLPUB executes follows:
MPRINT(LISTSQLPUB): proc sql;
MPRINT(LISTSQLPUB): select unique(publisher) into :pub1 -
from books.ytdsales;
MPRINT(LISTSQLPUB): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds
Total number of publishers: 12.
Publisher 1: AMZ Publishers
Publisher 2: Bookstore Brand Titles
WARNING: Apparent symbolic reference LEE not resolved.
Publisher 3: Doe&Lee Ltd.
Publisher 4: Eversons Books
Publisher 5: IT Training Texts
Publisher 6: Mainst Media
Publisher 7: Nifty New Books
Publisher 8: Northern Associates Titles
Publisher 9: Popular Names Publishers
Publisher 10: Professional House Titles
Publisher 11: Technology Smith
Publisher 12: Wide-World Titles
If you specify an upper limit on the series of macro variables on the INTO clause that is less than the number of values that the SELECT clause returns, SAS still executes the step. In this situation, SAS creates only the number of macro variables that are specified, which is 2, and assigns the first values it returns to those macro variables.
MPRINT(LISTSQLPUB): proc sql noprint;
MPRINT(LISTSQLPUB): select unique(publisher) into :pub1 - :pub2 from books.ytdsales;
MPRINT(LISTSQLPUB): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
Total number of publishers: 2.
Publisher 1: AMZ Publishers
Publisher 2: Bookstore Brand Titles
If you specify an upper limit on the series of macro variables INTO clause that is greater than the number of values that the SELECT clause returns, SAS creates only the number of macro variables equal to the number of values that the SELECT clause returns. This SAS log shows that the upper limit is set to 100.
MPRINT(LISTSQLPUB): options symbolgen;
MPRINT(LISTSQLPUB): proc sql noprint;
MPRINT(LISTSQLPUB): select unique(publisher) into :pub1 - :pub100 from books.ytdsales;
MPRINT(LISTSQLPUB): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds
MPRINT(LISTSQLPUB): options nosymbolgen;
Total number of publishers: 12.
Publisher 1: AMZ Publishers
Publisher 2: Bookstore Brand Titles
WARNING: Apparent symbolic reference LEE not resolved.
Publisher 3: Doe&Lee Ltd.
Publisher 4: Eversons Books
Publisher 5: IT Training Texts
Publisher 6: Mainst Media
Publisher 7: Nifty New Books
Publisher 8: Northern Associates Titles
Publisher 9: Popular Names Publishers
Publisher 10: Professional House Titles
Publisher 11: Technology Smith
Publisher 12: Wide-World Titles
A %PUT statement verifies that there is no PUB13 macro variable.
186 %put &pub13;
WARNING: Apparent symbolic reference PUB13 not resolved.
&pub13
Using PROC SQL, you can obtain information about your SAS session by accessing dictionary tables. These read-only SAS data views contain such information as option settings, librefs, member names and attributes in a library, and column names and attributes in a table or data set. Example 9.18 used a dictionary table to capture the names of all the data sets in a specific library and saved that information in one macro variable.
One dictionary table, OPTIONS, provides information about the current settings of SAS system options including macro facility related options. Another dictionary table, MACROS, provides information about macro variables including their scope and values. With these dictionary tables, you can access information about your current SAS session and programmatically use that to control execution of your SAS programs.
Example 9.20: Accessing Macro Option Settings with PROC SQL and Dictionary Tables
The OPTIONS dictionary table contains current settings and descriptions for SAS system options. A column in the table, GROUP, assigns a category to the setting. One group is MACRO. To display the current settings of the options in the MACRO group, submit the following code.
proc sql;
select * from dictionary.options
where group='MACRO';
quit;
The PROC SQL step that follows saves the setting of the macro option MINDELIMITER in a macro variable MYSETTING.
options mindelimiter='#';
proc sql noprint;
select setting
into :mysetting
from dictionary.options
where optname='MINDELIMITER';
quit;
%put My current MINDELIMITER setting is &mysetting;
The SAS log after submitting the preceding program follows.
62 %put My current MINDELIMITER setting is &mysetting;
My current MINDELIMITER setting is #
The PROC SQL step illustrates a simple example in working with the MACRO group of the dictionary tables. However, it might be much easier to simply submit the following %LET statement to define macro variable MYSETTING.
%let mysetting=%sysfunc(getoption(mindelimiter));
Example 9.21: Accessing Macro Variable Characteristics with PROC SQL and Dictionary Tables
The dictionary table MACROS contains information about macro variables. Included in this table are the macro variables that you create as well as automatic variables created by SAS. To display the list of macro variables currently defined in your session, submit the following PROC SQL step.
proc sql;
select * from dictionary.macros;
quit;
As seen earlier in this book, submitting the following statement would display much of the same information.
%put _all_;