Chapter 10: Data-driven Programming
Accessing and Displaying the Number of Rows in Tables
Accessing and Displaying a Cross-reference List of a Variable Defined in All Tables
Capturing a List of Variables from the COLUMNS Dictionary Table
Producing Multiple Excel Files
Data-driven programming, or data-oriented programming (DOP), is a specific programming paradigm where the data or data structures, and not the program logic, control the flow of a program. Often, data-driven programming approaches are applied in organizations with structured and unstructured data for filtering, aggregating, transforming, and calling other programs. This chapter explores several data-driven programming techniques that are available to SAS and PROC SQL users.
The SAS System collects and populates valuable information (“metadata”) about SAS libraries, data sets (tables), catalogs, indexes, macros, system options, titles, views, and other read-only tables called dictionary tables. Dictionary tables serve a special purpose by providing system-related information about the current SAS session’s SAS libraries, databases, and content. When a query is requested against a dictionary table, SAS automatically launches a discovery process at runtime to collect information pertinent to that table. Metadata content can be very useful for developing data-driven techniques. Other data-driven programming techniques include dynamically constructing a user-defined format directly from data, and using the SQL procedure and the macro language to perform an automated iterative (looping) process.
Programming languages are often classified by their basic features into one of many programming paradigms. Three popular programming paradigms in use today by programming professionals are:
1. Procedural programming – represented by blocks of code being organized logically by function, such as data input, data processing or manipulation, results, and output;
2. Object-oriented programming – represented by a combination of functionality (behaviors) and data (attributes) hidden inside an object which can then be arranged into classes;
3. Data-driven programming – represented by data controlling the flow of execution in a program.
In this chapter, we will be focusing on data-driven programming. Data-driven programming involves a program where the decisions and processes (the flow of execution) are controlled (or dictated) by the data (or data structures). The advantages of a data-driven programming environment are many including the ability to make our code a bit shorter and as a result possibly easier to read, enhanced flexibility due to the program’s ability to adapt to changing data and parameters, the assignment of default actions to tasks, and a reduction in maintenance and support activities due to the elimination of “hardcoded” logic and values.
SAS users have traditionally used the metadata results from PROC CONTENTS and PROC DATASETS to better understand the contents of SAS libraries and its various member types (i.e., DATA, INDEX, and VIEW); to construct user, system, and operations documentation; the creation of KEEP (or SELECT) lists of variables (or columns); automating one or more data sets variable attributes and characteristics; and other processing.
● PROC CONTENTS – Produces a directory of the SAS library and the details associated with each member type stored in a SAS library.
● PROC DATASETS – In Michael A. Raithel’s (2016) paper, titled “PROC DATASETS; The Swiss Army Knife of SAS® Procedures,” he shows why PROC DATASETS is one of the most versatile data management procedures. Like PROC CONTENTS, the PROC DATASETS CONTENTS statement produces a directory of the SAS library and the details associated with each member type (e.g., DATA, VIEW, INDEX) stored in a SAS library.
To understand the nature of the data structures and the associated metadata content of a SAS library (or database) and its member types, a PROC CONTENTS (or PROC DATASETS) can be specified. PROC CONTENTS displays summary information about the structure and member types of our SAS library (or database) environment. In the next example, a PROC CONTENTS is executed with the DIRECTORY option to display a list of the SAS files in the SAS library and the NODS option to suppress the contents of individual files when the _ALL_ option is specified in the DATA= option.
PROC CONTENTS Code
PROC CONTENTS DATA=mydata._all_ directory nods;
RUN;
Results
In the next example, a PROC CONTENTS is executed with the MEMTYPE=DATA option to list the names of the SAS data sets that reside in the user-assigned MYDATA SAS library.
PROC CONTENTS Code
proc contents data=mydata._all_
memtype=data
directory
nods;
run;
Results
In the next PROC CONTENTS example, the VARNUM option is specified to display the specific SAS library metadata content associated with data sets, columns (or variables), and column attributes, to better understand the SAS data environment.
PROC CONTENTS Code
proc contents data=mydata._all_
memtype=data
varnum;
run;
Results
. . . . . . .
In the next example, a PROC CONTENTS is specified to collect the CUSTOMERS SAS data set’s metadata and save the results to a user-assigned SAS data set using the OUT= option. The resulting SAS data set contains one observation for each variable found in the original data set. The SAS data set contains information about the original SAS data set and specific information about each variable. A PROC SQL SELECT query is then specified to view the data set metadata content.
PROC CONTENTS and PROC SQL Code
PROC CONTENTS DATA=MYDATA.Customers
OUT=WORK.Customers_Contents_Structure
NOPRINT;
RUN;
PROC SQL;
SELECT *
FROM WORK.Customers_Contents_Structure;
QUIT;
Results
Finally, the next example specifies the ODS OUTPUT destination to collect and save the results of a SAS data set’s metadata with the ATTRIBUTES= parameter. The resulting temporary SAS data set contains the attribute information such as data set name, the date the data set was created, the date the data set was last modified, the number of observations, the number of variables, and much more from each data set contained in the ‘MYDATA’ SAS library. A simple PROC SQL SELECT query is then specified to view the SAS data set metadata attribute content.
PROC CONTENTS and PROC SQL Code
ODS OUTPUT ATTRIBUTES=work.Data_Set_Attributes;
PROC CONTENTS DATA=MYDATA._ALL_
MEMTYPE=DATA;
RUN;
PROC SQL;
SELECT *
FROM WORK.Data_Set_Attributes;
QUIT;
Results
. . . . . . .
PROC SQL users can quickly and conveniently obtain useful information about their SAS session with a number of read-only SAS system tables called DICTIONARY tables. At any time during a SAS session, DICTIONARY tables can be accessed using the libref DICTIONARY in the FROM clause of a PROC SQL SELECT statement to capture information related to currently defined librefs, table names, column names, column attributes, formats, and more. The first notable characteristic about the libref DICTIONARY as it relates to DICTIONARY tables is that it can only be specified in the FROM clause of PROC SQL – and is not allowed as a libref in any other SAS procedure or in the DATA step. The second notable characteristic of the libref DICTIONARY is that it seems to hold special “super powers” where the maximum length of a libref anywhere else in the SAS System is limited (or constrained) to eight characters.
SAS 9.1 software supported 22 DICTIONARY tables and SASHELP views, SAS 9.2 supported 29 DICTIONARY tables and SASHELP views, SAS 9.3 supported 30 DICTIONARY tables and SASHELP views, and SAS 9.4 supports 32 DICTIONARY tables and SASHELP views. It should be noted that the content contained in the various DICTIONARY tables can also be found and accessed from the SASHELP views using any of your favorite procedures or in the DATA step.
The DICTIONARY table, TABLES, can be accessed to capture and display each table name and the number of observations in the user-assigned MYDATA libref. The following PROC SQL code provides a handy way to quickly determine the number of rows in one or all tables in a libref without having to execute multiple PROC CONTENTS statements by using the stored information in the DICTIONARY table, TABLES.
PROC SQL Code
PROC SQL;
SELECT LIBNAME, MEMNAME, NOBS
FROM DICTIONARY.TABLES
WHERE LIBNAME = "MYDATA"
AND UPCASE(MEMTYPE) = "DATA";
QUIT;
Results
To retrieve and display a list of the table (data set) names containing a selected variable, you could execute one or more PROC CONTENTS statements against the selected tables. Although this would produce the desired results, it often involves more effort and additional time mulling through piles of results to find the desired information. Or, using a more efficient approach, you could access the DICTIONARY table, COLUMNS, to produce a cross-reference listing of all the table names that contain the variable CUSTNUM in the user-assigned MYDATA libref, as shown below.
PROC SQL Code
PROC SQL;
SELECT LIBNAME, MEMNAME, NAME, TYPE, LENGTH
FROM DICTIONARY.COLUMNS
WHERE LIBNAME = "MYDATA"
AND UPCASE(NAME) = "CUSTNUM"
AND UPCASE(MEMTYPE) = "DATA";
QUIT;
Results
In lieu of specifying a PROC CONTENTS statement, the DICTIONARY table, COLUMNS, can be accessed to capture and display a table’s column names in any user-assigned libref. The following PROC SQL code provides a handy way to quickly capture and store the names of any columns contained in the CUSTOMERS table to a macro variable. To better visualize the type of data that is captured and stored in the user-defined macro variables, a %PUT statement is specified to display the contents of both macro variables on the SAS log.
PROC SQL Code
PROC SQL NOPRINT;
SELECT NAME,
COUNT(NAME)
INTO :MVARIABLES SEPARATED BY ', '
,:MVARIABLESNUM
FROM DICTIONARY.COLUMNS
WHERE LIBNAME = "MYDATA"
AND UPCASE(MEMNAME) = "CUSTOMERS";
QUIT;
%PUT &MVARIABLES &MVARIABLESNUM;
SAS Log Results
%PUT &MVARIABLES &MVARIABLESNUM;
Custnum, Custname, Custity 3
The previous example can be expanded so that only the character-defined variables are saved in the macro variable. The next example illustrates how PROC SQL code captures the names of the character-defined columns contained in the CUSTOMERS table. The contents of the macro variable are then specified in a SELECT statement to produce a report. As in the previous example, a %PUT statement is specified to display the contents of the user-defined macro variable on the SAS log.
PROC SQL Code
PROC SQL NOPRINT;
SELECT NAME
INTO :MVARIABLES SEPARATED BY ', '
FROM DICTIONARY.COLUMNS
WHERE LIBNAME = "MYDATA"
AND UPCASE(MEMNAME) = "CUSTOMERS"
AND UPCASE(TYPE) = "CHAR";
%PUT &MVARIABLES;
RESET PRINT;
SELECT &MVARIABLES FROM MYDATA.CUSTOMERS;
QUIT;
SAS Log Results
%PUT &MVARIABLES;
Custname, Custcity
Results
SAS users have a powerful DATA step routine called CALL EXECUTE that can be used for data-driven processing. The CALL EXECUTE routine accepts a single argument where the value can be a character-string or, when needed, a character expression containing SAS code elements to be executed after they are resolved. The CALL EXECUTE routine permits SAS statements and macro code to be stacked together and then executed.
When the CALL EXECUTE routine contains SAS statement code without macro variables or macro references, the code is appended to the input stack for immediate execution after the DATA step ends. The argument can be specified with single or double quotation marks, dynamically generating SAS code for execution. To leverage data-driven processes with CALL EXECUTE, a control data set called, Product_Types, is created using the PROC SQL CREATE TABLE and INSERT INTO statements with four distinct product types (i.e., “Laptop”, “Phone”, “Software”, and “Workstation”) represented as observations. The DATA step then reads the contents of the control data set populating the unique value for the Prodtype variable in the individual CALL EXECUTE statements. Note: The CATS function is used to strip blanks and concatenate multiple strings together. The CATX function is used to insert blanks ‘ ‘ and special characters between parameters.
In the next example, the PROC SQL step creates a control data set that contains the unique values for each product type (i.e., “Laptop”, “Phone”, “Software”, and “Workstation”). Then, a DATA _NULL_ step is specified to read the contents of the control data set followed by a series of CALL EXECUTE statements to generate and execute SAS code. The result is the creation of four Excel spreadsheets – one for each of the product types specified in the control data set.
Code
PROC SQL;
CREATE TABLE work.Product_Types /* Control Data Set */
( Prodtype CHAR(15) LABEL='Product Type' );
INSERT INTO work.Product_Types /* Populate Product Type Rows */
SET Prodtype='Laptop'
SET Prodtype='Phone'
SET Prodtype='Software'
SET Prodtype='Workstation';
QUIT;
DATA _NULL_;
SET work.Product_Types;
CALL EXECUTE(CATS('ods Excel
file="/folders/myfolders/', Prodtype,' - Products_Rpt.xlsx"
style=styles.barrettsblue
options(embedded_titles="yes");'));
CALL EXECUTE(CATX(‘ ‘, 'title ', Prodtype, ' Products;'));
CALL EXECUTE('proc sql ;');
CALL EXECUTE(CATS('select * from mydata.Products(where=
(Prodtype="',Prodtype,'"));'));
CALL EXECUTE('quit;');
CALL EXECUTE('ods Excel close;');
RUN;
The dynamically generated SAS code produced by the preceding CALL EXECUTE statements is displayed below.
Partial SAS Log Results
1 + ods Excel
2 + file="/folders/myfolders/Laptop-Products_Rpt.xlsx"
3 + style=styles.barrettsblue
4 + options(embedded_titles="yes");
5 + title Laptop Products;
6 + proc sql ;
7 + select * from mydata.Products(where=
8 + (Prodtype="Laptop"));
9 + quit;
10 + ods Excel close;
11 + ods Excel
12 + file="/folders/myfolders/Phone-Products_Rpt.xlsx"
13 + style=styles.barrettsblue
14 + options(embedded_titles="yes");
15 + title Phone Products;
16 + proc sql;
17 + select * from mydata.Products(where=
18 + (Prodtype="Phone"));
19 + quit;
20 + ods Excel close;
21 + ods Excel
22 + file="/folders/myfolders/Software-Products_Rpt.xlsx"
23 + style=styles.barrettsblue
24 + options(embedded_titles="yes");
25 + title Software Products;
26 + proc sql;
27 + select * from mydata.Products(where=
28 + (Prodtype="Software"));
29 + quit;
30 + ods Excel close;
31 + ods Excel
32 + file="/folders/myfolders/Workstation-Products_Rpt.xlsx"
33 + style=styles.barrettsblue
34 + options(embedded_titles="yes");
35 + title Workstation Products;
36 + proc sql ;
37 + select * from mydata.Products(where=
38 + (Prodtype="Workstation"));
39 + quit;
40 + ods Excel close;
Results
The FORMAT procedure is a powerful tool for building user-defined informats and formats. These “custom” user-defined informats and formats provide SAS with instructions on how to read data into SAS variables and write (or display) output. Custom-defined informats and formats are defined as temporary or permanent, and are stored as entries in SAS catalogs. The available operations performed by the FORMAT procedure include the ability to:
● Convert character values to numeric values
● Convert numeric values to character values
● Convert character values to other character values
To prevent hardcoding VALUE clauses, custom-defined formats can be dynamically created from a SAS data set. In a paper, Harry Droogendyk, argues that dynamically created custom-defined formats offer users a more efficient approach than processing sort, merge, and join operations by leveraging data-driven processes. Consequently, the FORMAT procedure is able to create informats and formats without specifying hardcoded INVALUE, PICTURE, or VALUE clauses by using a SAS control data set as input.
User-defined formats can be created from raw data or a SAS data set using PROC FORMAT’s CNTLIN= option. In order to create a user-defined format, we’ll need to construct a control data set with a few required variables. The control data set is specified with the CNTLIN= option of PROC FORMAT. To start the process, the control data set must have the following required variables:
● FMTNAME – specifies the name of a character variable whose value is the format or informat name.
● START – specifies the name of a character variable that contains the value to be converted.
● LABEL – specifies the name of a character variable that contains the converted value.
In the next example, a PROC SQL CREATE TABLE and INSERT INTO statements are specified to produce a control data set with the variables: FMTNAME, Start, and Label. The contents of the control data set are then displayed for verification purposes with a SELECT query. The control data set is then specified in the PROC FORMAT CNTLIN option. Finally, a SELECT query is processed using the FORMAT=$Prodtype to produce the desired expanded results. Note: Readers can obviously use other approaches such as, a DATA step, to construct the control data set.
Code
PROC SQL;
CREATE TABLE work.Product_Type_Control /* Control Data Set */
( FMTNAME CHAR(9) LABEL=’Format Name’,
Start CHAR(11) LABEL='Product Type',
Label CHAR(20) LABEL=’Format Label’ );
INSERT INTO work.Product_Type_Control /* Populate Product Type Rows */
SET FMTNAME = ‘$Prodtype’,
Start = 'Laptop',
Label = ‘Laptop Products’
SET FMTNAME = ‘$Prodtype’,
Start = 'Phone',
Label = ‘Phone Products’
SET FMTNAME = ‘$Prodtype’,
Start = 'Software',
Label = ‘Software Products’
SET FMTNAME = ‘$Prodtype’,
Start = 'Workstation',
Label = ‘Workstation Products’;
/* Display the control data set */
SELECT *
FROM work.Product_Type_Control;
QUIT;
/* Create the user-defined format */
PROC FORMAT LIBRARY=WORK CNTLIN=Product_Type_Control;
RUN;
/* Use the user-defined format to display the PRODTYPE value */
PROC SQL;
SELECT Prodnum,
Prodname,
Manunum,
Prodtype FORMAT=$Prodtype,
Prodcost
FROM MYDATA.PRODUCTS;
QUIT;
SAS Log Results
PROC SQL;
CREATE TABLE work.Product_Type_Control /* Control Data Set */
( FMTNAME CHAR(9) LABEL='Format Name',
Start CHAR(11) LABEL='Product Type',
Label CHAR(20) LABEL='Format Label' );
NOTE: Table WORK.PRODUCT_TYPE_CONTROL created, with 0 rows and 3 columns.
INSERT INTO work.Product_Type_Control /* Populate Product Type Rows */
SET FMTNAME = '$Prodtype',
Start = 'Laptop',
Label = 'Laptop Products'
SET FMTNAME = '$Prodtype',
Start = 'Phone',
Label = 'Phone Products'
SET FMTNAME = '$Prodtype',
Start = 'Software',
Label = 'Software Products'
SET FMTNAME = '$Prodtype',
Start = 'Workstation',
Label = 'Workstation Products';
NOTE: 4 rows were inserted into WORK.PRODUCT_TYPE_CONTROL.
SELECT *
FROM work.Product_Type_Control;
QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
PROC FORMAT LIBRARY=WORK CNTLIN=Product_Type_Control;
NOTE: Format $PRODTYPE has been output.
RUN;
NOTE: PROCEDURE FORMAT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: There were 4 observations read from the data set WORK.PRODUCT_TYPE_CONTROL.
PROC SQL;
SELECT Prodnum,
Prodname,
Manunum,
Prodtype FORMAT=$Prodtype.,
Prodcost
FROM MYDATA.PRODUCTS;
QUIT;
Results
The results from processing the dynamically generated control data set against the PRODUCTS table are displayed below.
The SQL procedure and the macro language are two versatile tools found in the Base SAS software. Combining the two together provides users with all the tools necessary to construct highly useful and effective data-driven programs. In this section, I will explain a data-driven approach to create multiple Excel files. Triggered by calling a macro to reduce coding requirements, the process uses the Macro language, PROC SQL, the ODS Excel destination, and PROC FREQ to send output (results) to Excel. The ODS Excel Destination became production in SAS 9.4 (M4). It serves as an interface between SAS and Excel. The ODS Excel features include:
● SAS Results and Output can be sent directly to Excel
● Offers a Flexible way to create Excel files
● Supports Reports, Tables, Statistics, and Graphs
● Formats Data into Excel Worksheet cells
● Permits Automation of Production-level Workbooks
The ODS Excel destination easily sends output and results to Excel. The ODS Excel syntax simplifies the process of sending output, reports, tables, statistics, and graphs to Excel files. The ODS Excel options are able to:
● Programmatically generate output and results
● Control font used and font sizes
● Add special features to row and column headers
● Adjust row and column sizes
● Format data values
● Align data to the left, center, or right
● Add hyperlinks for drill-down capability
In the next example, a data-driven approach using PROC SQL SELECT code embedded inside a user-defined macro routine is constructed to dynamically produce separate Excel spreadsheets containing the frequency results for each unique BY-group (e.g., Product Type). The SELECT query processes the Products table, creates a single-value macro variable with the number of unique (distinct) product types, and a value-list macro variable with a list of the unique product types separated with a tilde “~”. The user-defined macro uses the FREQ procedure, both macro variables along with their respective values, an iterative macro %DO statement, a %SCAN function, and WHERE= data set option to dynamically send the results to an Excel spreadsheet for each BY-group.
Macro and PROC SQL Code
%macro multExcelfiles;
proc sql noprint;
select count(distinct prodtype)
into :mprodtype_cnt /* number of unique product types */
from MYDATA.PRODUCTS
order by prodtype;
select distinct prodtype
into :mprodtype_lst separated by "~" /* list of product types */
from MYDATA.PRODUCTS
order by prodtype;
quit;
%do i=1 %to &mprodtype_cnt;
ods Excel file="/folders/myfolders/%SCAN(&mprodtype_lst,&i,~)-Rpt.xlsx"
style=styles.barrettsblue
options(embedded_titles="yes");
title "%SCAN(&mprodtype_lst,&i,~) Products";
proc freq data=MYDATA.PRODUCTS(where=
(prodtype="%SCAN(&mprodtype_lst,&i,~)"));
tables Prodname;
run;
ods Excel close;
%end;
%put &mprodtype_lst;
%mend multExcelfiles;
%multExcelfiles;
The dynamically generated SAS code produced by the iterative %DO statement is displayed below.
SAS Log Results
%macro multExcelfiles;
proc sql noprint;
select count(distinct prodtype)
into :mprodtype_cnt /* number of unique product types */
from MYDATA.PRODUCTS
order by prodtype;
select distinct prodtype
into :mprodtype_lst separated by "~" /* list of product types */
from MYDATA.PRODUCTS
order by prodtype;
quit;
%do i=1 %to &mprodtype_cnt;
ods Excel file="/folders/myfolders/%SCAN(&mprodtype_lst,&i,~)-Rpt.xlsx"
style=styles.barrettsblue
options(embedded_titles="yes");
title "%SCAN(&mprodtype_lst,&i,~) Products";
proc freq data=MYDATA.PRODUCTS(where=
(prodtype="%SCAN(&mprodtype_lst,&i,~)"));
tables Prodname;
run;
ods Excel close;
%end;
%put &mprodtype_lst;
%mend multExcelfiles;
%multExcelfiles;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
NOTE: There were 1 observations read from the data set MYDATA.PRODUCTS.
WHERE prodtype='Laptop';
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.08 seconds
cpu time 0.08 seconds
NOTE: Writing EXCEL file: /folders/myfolders/Laptop-Rpt.xlsx
NOTE: There were 3 observations read from the data set MYDATA.PRODUCTS.
WHERE prodtype='Phone';
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds
NOTE: Writing EXCEL file: /folders/myfolders/Phone-Rpt.xlsx
NOTE: There were 4 observations read from the data set MYDATA.PRODUCTS.
WHERE prodtype='Software';
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
NOTE: Writing EXCEL file: /folders/myfolders/Software-Rpt.xlsx
NOTE: There were 2 observations read from the data set MYDATA.PRODUCTS.
WHERE prodtype='Workstation';
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
NOTE: Writing EXCEL file: /folders/myfolders/Workstation-Rpt.xlsx
Laptop~Phone~Software~Workstation
Results
Four Excel spreadsheets are produced, as shown below.
Unlike procedural programming languages where a program’s flow of execution is described using a detailed step-by-step logical approach to solving a problem or like object-oriented programming where an object is told how to behave, data-driven programming involves writing code that has its decisions and processes (the flow of execution) controlled (or dictated) by the data (or data structures). Data-driven programming offers SAS users many advantages over rival programming paradigms including enhanced flexibility and easier maintenance due to a reduction, or elimination, of “hardcoded” values.
The first data-driven programming technique uses metadata DICTIONARY tables to provide valuable information about SAS libraries, data sets, columns and attributes, catalogs, indexes, macros, system options, and views. The second data-driven programming technique uses the CALL EXECUTE routine to process (or execute) code generated by a DATA step. The third data-driven programming technique creates a user-defined format from a control data set. The fourth data-driven programming technique uses the SQL procedure and the macro language to construct an automated iterative data-driven process.