 Hi there, it's me, Monica Wahee, giving you some demonstrations on the use of SAS on-demand for academics, otherwise known as SAS ODA. In this video, I'm going to show you more about automated processing of datasets for ETL into a SAS data warehouse using macros and macro variables. Okay, this video will probably not make much sense to you if you don't watch my previous video, where I demonstrate taking the original dataset named CHAP8 underscore 2 and processing it into 12 different monthly datasets. In that video, I read in a dataset that has multiple states in it. Florida is coded 12, for example, and has multiple months in it, coded 1 through 12. So in that video, I showed you how to split the big dataset into monthly datasets using macros and macro variables, and also the where clause. Now, in this video, I'm going to demonstrate another ETL thing we do with SAS and data warehousing, which is where we read in a file, process it, and then export the processed version, but we do that with a super complicated macro. That way we can shuffle in monthly files one at a time, transform them, and then spit them out. I'm going to show you an example of one of these super complicated macros. First we have a twist in this video. We are going to be importing our dataset from folder X, mapped to libname X, like we did in the last video, but this time we are going to output datasets to a different folder, which is named Y, and we map it in the code to libname Y. Okay, so up here is the simple version of a data step that we are trying to automate. See this code? Let's break it down. DataY.jan2018 underscore FL means we are asking SAS to output a dataset named jan2018 underscore FL into the folder Y, mapped to libname Y. Right. The next line is set X.jan2018, which says, read in the SAS file X.jan2018, that was output from running the code in the last video. Right. And the next line is an if clause, if underscore state equals 12. 12 is Florida. That's why we want to name the output dataset Y.jan2018 underscore FL, so it has a suffix. This is a very normal thing to want to automate. Of course, we want SAS to automatically do this for all 12 months. So that's what we do in this macro code down here. So this is enough code to kill a horse, right? But don't be scared of the code. Here we'll take it line by line. Let's start with the part of the code that tells us we are making a macro. See this code sandwiched between macro and mend is the entire macro, and we see that we are naming the macro keep underscore FL. And at the end, we are running the macro. So that's where we start with the code. Here's me turning off system options in the hopes SAS ODA doesn't complain at me. Okay, here we have the let command. That's a hint, right? That means we are setting a macro variable. The macro variable is being declared as M, and M is set to equal a list of what will turn into prefixes for the names of the data sets that will land in Y. See them? Jan, Feb, Mar, et cetera. Okay, so that's a macro variable. Okay, now here we have a do loop with the N command. We can see that this do loop is setting the value of J to the iteration number. And that number is going to be 1 to 12. Okay, we have three lets inside this loop. These macro variables are inside the loop. So they reevaluate each time we come around. The macro variables they are setting are month, out file, and in file. So you can imagine where this is going. We start with month, and we set this to a value that is, well, it's a substring. See the substring command? You can see the ampersand with M and with J. So that means we are calling up the current value of those variables. Since they were set at the beginning of each loop, the first one is going to have MbJan and Jb1. What this basically does is tells us to read in the three characters in M that pertain to the loop iteration. So if you are going through the loop the seventh time, where J equals seven, the substring code would make month be set to the three characters JUL, read in from the M variable. Once we get month to be the three characters we want, we can set the name of the out files and in files to have the month variable in the name. And also we can add the suffix underscore FL to the out file for Florida. We also can set the lib name so we are outputting to Y and inputting from X. Okay, here we finally get to the code. Recognize this code? This is the macro version of the raw code I showed you above. All right, let's first load this macro into SAS's brain by highlighting macro demand and running. Okay, great. Now let's go back to our code and highlight our macro call and run it. Okay, so here is Y. And here are our automated output data sets with all the right names and the FL suffix. The most important point I want to make is that if you want to run a SAS data warehouse, you are constantly deciding code, especially ETL code, and working on it. You start by crafting efficient data step code, then step by step, you transform it into a macro. I demonstrate this in my book, Mastering SAS Programming for Data Warehousing. Thank you for watching this video. Hopefully, if enough people read my book, we will have some gorgeous data warehouses out there. And I hope you have a gorgeous day.