Chapter 8: Using Macros to Automate ETL in SAS

Up to now, we have learned how to run PROCs and data steps in SAS, especially for extract, transform, and load (ETL) protocols, which make up a lot of the work of running a SAS data warehouse. In this chapter, we will learn about SAS macros and macro variables, and how they can help us with ETL.

First, we will learn how to build base code, then convert it to a macro. We will do this with both PROCs and data steps. Next, we will talk about different ways of storing and calling macros. Finally, we will look at an example of using macros to automate loading transformed data.

This chapter will teach you the following skills:

• How to make transformation macros by developing standalone data step code first, and then rebuilding it into a macro

• How to standardize ETL in a data warehouse using SAS macros, and the advantages and disadvantages of using macros for this

• How to use macros to load transformed datasets into a SAS...