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

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

Use of Macro to generate data values instead of code

From sasCommunity
Revision as of 14:41, 21 October 2007 by Donh (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

This article is still a work in progress.

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.


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 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. . . . . 

The Code

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

  • A macro called getDataAttribute that, for a specifed 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

%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 */
 %else &notfound;

 %let dsid = close(&dsid);

%mend getDataAttribute;
data sample_data;
 length data $41;
 input data;
 Created = '%getDataAttribute(data=' || trim(data) || ',attribute=crdte)';

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

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

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