How to Automate Processing Monthly Data Warehouse Files with a Macro in SAS ODA – Demonstration
Channel: Monika Wahi
Date: 30/12/2021

*Note: I may be compensated, but you will not be charged, if you click on the links below.

💡 If you are a public health practitioner looking for a targeted program for enhancing your data science skills, please sign up to participate in my 30-minute market research interview so I can get your feedback: https://buff.ly/3UnLqmq

In this video, Monika Wahi shows you an example of combining macro variables, macros sublanguage, and data steps to automate data warehouse extract-transform-load (ETL) code in SAS.

GITHUB
Access code and dataset here: https://buff.ly/3xxhcEB 

💿 If you need to know how to import the dataset and map a LIBNAME watch this video: https://youtu.be/mHhT0Bs4Rmo
📀 Make sure you watch this video first to generate the input dataset X.JAN2018: https://youtu.be/CL38GDhZmZQ 

COURSE & BOOK
💿 Want to start using free, online SAS OnDemand for Academics – but don’t know how? Take Monika’s FREE online course, “Getting Started with SAS OnDemand for Academics (ODA): https://buff.ly/3CQVPxG 
📀 Want to run a SAS data warehouse? Learn how by reading Monika’s book, “Mastering SAS Programming for Data Warehousing”: https://buff.ly/31Hz1mg  

WANT TO SUPPORT MONIKA ON SOCIAL MEDIA?
❤️Subscribe to Monika's YouTube channel: https://buff.ly/2W6ijuc   
🧡Follow/connect with Monika on LinkedIn: https://www.linkedin.com/in/dethwench/ 
💛Follow Monika on Twitter: https://twitter.com/PHExaminer 
💚Try Monika’s courses on LinkedIn Learning: https://linkedin-learning.pxf.io/NKN0JO 
💙Try Monika's boutique research methods and data science courses here: https://buff.ly/3zM243P 

Timestamps for easy navigation:
00:28 First, watch this video where Monika takes the original annual dataset and uses a data step with WHERE criteria placed on a variable to output 12 monthly datasets: https://youtu.be/CL38GDhZmZQ
00:57 Explanation of what will be demonstrated in this video: Use a macro to read in each of the monthly datasets, one by one, process them, and export them (extract-transform-load, or ETL protocol).
01:24 Watch this video to learn about LIBNAME and importing SAS datasets into SAS: https://youtu.be/mHhT0Bs4Rmo 
01:43 Reviewing data step base code with hardcoding
02:23 Automating naming the output datasets
02:40 Typical use-case for this automation in a data warehouse
02:52 Reviewing macro code that automates the base code. 
* Want to learn the basics behind building SAS macros? Watch this video: https://youtu.be/jiOCYUsG6hc
* Want to better understand parameterizing a SAS macro? Watch this video: https://youtu.be/STx7RQD7BZI
* Want to learn about macro variables in SAS (which are different than SAS macros)? Watch this video: https://youtu.be/RKebRCcPkek
* Want to watch us make the base code that is automated in this video? Watch this video: https://youtu.be/Kwu_Ik8MpLM
03:50 Reviewing do loop coding, and now the values of the macro variables (and iteration variable) change through the processing steps
04:50 Reviewing the %SUBSTR substring code used to specify the month abbreviation
05:12 Using the substring in file names, and setting up data input/output names
05:35 Macro version of base code
05:47 Running macro code to load it in memory, then running the macro without setting any macro variables as parameters.
06:00 Reviewing output datasets input from the X folder and output in the Y folder
06:14 Explanation of how SAS macro code is built over time

How to Automate Processing Monthly Data Warehouse Files with a Macro in SAS ODA – Demonstration