Use of Macro to generate data values instead of code

From sasCommunity
Jump to: navigation, search

Problem Statement

The Macro Language is a text manipulation and generation facility. The results of macro almost always go to the SAS Supervisor for execution as SAS Code. Because that is what happens almost always has caused many users to conclude that the Macro language is just a SAS code generator.

It is however a generic text generation facility as demonstrated in the SAS Server Pages example where macro is used to generate HTML text.

This articles provides an alternative use of the Macro language - to generate data values for a variable in a data set.

Scenario

Consider an example where it is necessary to display the attributes of selected SAS data sets to a user via a web-based UI. Clearly one option is to use the SQL DICTIONARY facility. However there may be performance issues with the time needed to do the following query:

proc sql;
 create table created as
 select memname, crdate
 from dictionary.tables
 where trim(libname)||'.'||memname in (select data from. . . . . 
 ;
quit;

The Code

The following subsections present a macro based approach to generate/return data values:

  • A macro called getDataAttribute that, for a specified data set name, returns a text string which contains the value of the selected attribute.
  • Creation of a sample data set which contains:
    • a variable whose values are the data sets we are concerned with
    • a variable whose values are macro calls that execute the getDataAttribute macro
  • Defining of view of the data set created above which uses the RESOLVE function to execute the macro so the the text returned by the macro is the value of a data step character variables

The getDataAttribute Macro

This macro uses SCL functions (OPEN and ATTRN/ATTRC) via the %sysfunc macro to open a data set and get the value of the indicated attribute without generating any SAS code. This allows it to be called as a function to return a specfic value, in this case, the requested attribute.

It has the following parameters:

  • DATA which is the name of the data set whose attribute value is desired.
  • ATTRIBUTE which is the desired attribute. Note that the value must be a valid value for the ATTRN/ATTRC function.
  • TYPE indicates whether the attribute is Character or Numeric. If not specified, it defaults to numeric. Only the first character of the provided value is used.
  • NOTFOUND provides the value that should be returned to the input stack if the data set is not found/not available. The default is a quoted string for character attributes and a . (the representation for numeric missing) for character attributes. Different return values may be based on the calling program/environment and making this an argument provides that flexibility.
%macro getDataAttribute
      (data = ,      /* name of data set */
       attribute = , /* desired attribute */
       type=,        /* type of attribute - N or C */
       notfound =    /* value to return if not found */
       );

 %local dsid;

 %if %length(&type) = 0 %then %let type = N;
 %else %let type = %upcase(%substr(&type,1,1));

 %if &type = N %then %let notfound = .;
 %else %let notfound = ' ';

 %let dsid = %sysfunc(open(&data));
 %if &dsid ne 0 %then /* valid data set - return attribute value to input stack */
     %sysfunc(attr&type(&dsid,&attribute))
 ;
 %else &notfound;

 %let dsid = close(&dsid);

%mend getDataAttribute;

NOTE: This macro is provided as just a sample and it is not, in its present form, a fully robust implementation.

Sample Data Set

The following code creates a sample data set and the PROC PRINT step shows that the value of the variable Created is a call to the getDataAttribute macro for the indicated data set.

data sample_data;
 length data $41;
 input data;
 Created = '%getDataAttribute(data=' || trim(data) || ',attribute=crdte)';
datalines;
sashelp.class
sashelp.retail
work.sample_data
work.notfound
;

options nocenter;
proc print data = work.sample_data;
 title 'Sample Data - Value of Created is a macro call';
run;

Running the above code produced the following output:

Sample Data - Value of Created is a macro call                                      1

Obs          data                                  Created

 1     sashelp.class       %getDataAttribute(data=sashelp.class,attribute=crdte)
 2     sashelp.retail      %getDataAttribute(data=sashelp.retail,attribute=crdte)
 3     work.sample_data    %getDataAttribute(data=work.sample_data,attribute=crdte)
 4     work.notfound       %getDataAttribute(data=work.notfound,attribute=crdte)

Defining a VIEW that uses the RESOLVE function

The following SQL (a DATA Step view could have also been created) code creates a view of our data but uses the RESOLVE function on the variable Created to execute the macro and have the text returned by the macro call as the value of the variable.

Since the RESOLVE function returns a character string, the INPUT function is used to convert that character to its numeric representation (number of seconds since midnight, January 1, 1960.

The DATETIME format is used to display the value.

proc sql;
 create view work.sample as
 select data,
        input(resolve(Created),best16.) as Created format = datetime.
 from sample_data;
quit;

and the following PROC PRINT

proc print data = work.sample;
 title 'Sample Data - RESOLVE function executes macro call to return a value';
quit;

produces the following output demonstrating how the getDataAttribute macro's returned text becomes the value of the variable. Note how a missing value is returned for a data set that does not exist.

Sample Data - RESOLVE function executes macro call to return a value                2

     Obs          data                   Created

       1    sashelp.class       12MAY04:22:53:56
       2    sashelp.retail      12MAY04:22:54:56
       3    work.sample_data    21OCT07:16:45:03
       4    work.notfound                      .

Use Cases

Don Henderson has used this technique and similar macros to provide information about application Metadata tables where SQL Dictionary Tables was not an option.

An alternative use of the macro is to call it within, for example, a data step to generate the value on the right hand side of an assignment statement:

data _null_;
 Created = %getDataAttribute(data=sashelp.class,attribute=crdte);
 put Created = datetime.;
run;

For this usage of the macro, even though it is a data value being returned, it is reasonable to state that the macro is, in fact, generating code.

Summary

This article was intended to provide just one example of how macro can be used to do something other than generate SAS code. What macro is generating and how it is used is very dependent on the calling environment. While it is almost always the case that the text generated by a macro is passed to the SAS Supervisor for parsing and then execution, other uses are possible.

The macro included in this example can be used in a number of ways, not just limited to the example here. Likewise other macros could be written and invoked in a similar fashion.