SAS Tutorial | Doing More with SAS Enterprise Guide: Tips and Advanced Techniques

Hi, everybody It’s Chris from SAS here, and welcome to “Doing More with SAS Enterprise Guide, Tips and Advanced Techniques.” I’m really excited to bring you this topic today I was on the original development team for SAS Enterprise Guide in SAS R&D, and I’m an avid user, to this day, of SAS Enterprise Guide And I know all the nooks, and crannies, and productivity tips that I get through the day with, and I’m excited to be able to share them with you So let’s get started about what we’re going to learn These are the topics that we’re going to cover during these sessions Of course, I’m going to give you a little bit of background about Enterprise Guide, maybe some behind-the-scenes knowledge that you didn’t have before even if you’ve used it for a while We’ll talk about the basic topics, like customizing your workspace and importing data But we’re going to go a little deeper than what you might get from an intro course or tutorial And I want to show you some tricks about the Data Viewer, how to get your content out to Excel– I’ll show you about five or six different ways to do that– how to take advantage of projects and process flows, and I’m going to spend time talking about some special tasks that are built into Enterprise Guide to help you do some cool things that maybe you didn’t know about We’ll talk about the Query Builder, of course And if you’re a SAS programmer, you’re going to want to stay tuned for those programming productivity tips that I’ve got, and we’ll talk about using source management, like Git, with SAS Enterprise Guide And , finally we’ll get into automation using scripts And I’ll make sure that at the end, you’re connected with all the Enterprise Guide resources you’ll need to be successful Ready? Let’s get started Whether you’re new to Enterprise Guide or a grizzled veteran, it’s great to take just a moment and talk about the basics about, what is SAS Enterprise Guide? It’s a client application that connects you to SAS and SAS resources So it isn’t SAS itself It isn’t the thing doing all the work to manage data and run analytics It’s a client app that connects to SAS, where all of that work gets done Enterprise Guide gives you access to the data in your organization, whether that’s SAS data sets, or flat files, or Excel, or databases It is your interface into a lot of data preparation, and that can be in the form of code, or it might be using the Query Builder, or other fit-for-purpose tasks within Enterprise Guide There are lots of tasks and wizards– hundreds– available in Enterprise Guide to make everyday tasks simpler But there’s always the option to break out the code when you want to do something more advanced, or maybe that’s just your preferred mode of working Enterprise Guide lets you organize your work using projects and process flows to help you stay more organized and repeat flows or sequences of things that you have to do again and again in an intuitive way So Enterprise Guide is kind of your gateway to SAS Now, Enterprise Guide is not the only client application out there There are other tools, like SAS Studio, which is a browser-based client to SAS, and the SAS Add-In for Microsoft Office, which is kind of like Enterprise Guide in that it installs on your desktop, but it runs only in Microsoft Office applications like Excel or Word, or PowerPoint But in concept, all these tools are operating in the same way, in that they’re a dedicated client app that many people may use, but they’re connecting to an installation of SAS that’s actually doing all the heavy lifting That SAS might be on your local desktop, or it more likely is centralized, maintained by your IT department or by somebody else in a central way And it’s collecting the work that you want to be done in the form of requests as you point and click in the client application, and it’s sending those requests over to SAS to fulfill, and then sending you back those results Those requests might be just, open this data set and show me the values, or it might be, run this query, or perform this linear regression, and then it brings you back the results for you to view and do further action with within Enterprise Guide

Now, many of us who have used SAS for a long time may have started using SAS installed on our desktops So a lot of SAS users still call that Base SAS or SAS for Windows That is a way of working that is becoming less and less common, and here’s why Organizations are wanting to more centralize their computing resources and their data resources Rather than having everything scattered amongst the organization on individuals’ workstations, they would rather have their SAS installation– which could be quite large– installed on a central machine or a series of machines And their data– it’s not always a great idea to have that scattered around the organization Organizations like to be able to centralize access to that, as well SAS Grid technology is another way that customers use SAS to scale up the way that their SAS jobs run, maybe because they have lots and lots of SAS users, or maybe they have SAS processing that happens that’s very intense and they can spread the work of that SAS work across many, many nodes to scale up and get things computed more quickly So SAS Grid technology works great with SAS Enterprise Guide Security is another big concern So a lot of organizations, like financial institutions, or insurance companies, or hospitals– they have access to sensitive data, data that it would be very bad if that data were to escape the confines of the organization So these companies, organizations– they centralize access to their data and require that their analysts access them using these client applications That way, they can mitigate and limit the potential that that data is distributed in ways that the company would not authorize Going along with that, the centralized scheme allows companies to have better control over who accesses the data, and to have visibility into when that data is accessed, and by who And so these auditing capabilities that are built into SAS, which are facilitated by using client applications, like Enterprise Guide, are really, really important and help to minimize and manage the risk that organizations who have to do important work with sensitive data– the risk that they would face And finally, let’s not discount the simplification of software updates SAS is a big application, and to install it and update it can be quite a chore, and it needs to be tightly controlled Mission-critical applications that rely on SAS often have to go through a lot of validation every time that the SAS installation is updated By keeping that in a central place, that minimizes the work that needs to be done We’re not having to update SAS on all of our analysts’ machines Instead, we can update it in just one place, where all of our analysts can access it Likewise, SAS Enterprise Guide, as a Windows client application, is easy to update So recent releases of Enterprise Guide have automatic updates, actually, that they can go out and check the SAS website for the availability of updates and then prompt the user to update that just with the click of a button Now is a good time to check, if you don’t already know, what version of Enterprise Guide you’re running The most recent release at the time of this recording is version 8.2, and version 8.2 and 8.1 look and feel very similar to each other The family of releases just before 8.1 was the 7.1 family of releases, leading all the way up to 7.15 There were several 7.1 releases in that time frame Or do you not know what release you’re running and haven’t really paid attention? It’s a good idea to check Most of what I’ll be demonstrating and talking about in this tutorial relates to Enterprise Guide 8.2, but almost everything that I’m talking about applies to 8.1,

as well And much of it also applies to the version 7.1 family of releases These are the more modern releases of Enterprise Guide And hopefully, you’re running at least one of those 8.2 would be ideal, of course But Enterprise Guide goes way back in history The first release hit the field in 1999, and it has been around since then with many major releases, some of them coinciding with major releases of SAS Now, one of the great things about Enterprise Guide is that it is backwards compatible with older SAS releases So even though you might be using 8.2, you could still talk to an older version of SAS 9.4, or SAS 9.3, or even SAS 9.2 But I kind of hope that you’re not running SAS 9.2 That release has been out and outdated for a long time But it gives you the flexibility to keep your Enterprise Guide version up-to-date and it doesn’t require you to update your version of SAS, which is often a more onerous process for organizations to undertake, and something that’s much more controlled If you’re like me, you’re going to spend a ton of time in this tool While the default layout and appearance of SAS Enterprise Guide is designed to help any user be productive, it is worth taking the time to explore the ways that you can customize the environment There are a ton of options available So you can change the layout change, the color scheme– so much about the tool that you can customize to help you feel productive Let’s take a look at some of the options you have available to you Now, the main layout of SAS Enterprise Guide looks something like this Along the top, you have access to the main menus, and the toolbar for quick actions Don’t let the small menu fool you There are a ton of features that are available when you start digging into this Over on the left, by default, we have the Navigation area This is going to show you things like your current project, if you’re using a project, and current files that you have available and open Down below, in this Servers area, part of the Navigation area, that gives you access to what’s going on with your SAS servers, your libraries, your data sets, and variables You will also have access to the Tasks menu, and if you’re using SAS metadata, items that are available there, like stored processes, and registered metadata data In this main area here, we have the Work area So this is where all of your content is going to appear So whatever you’re working on at the moment– that could be code, that could be data, it could be results that have come back from SAS This is where you’re going to spend most of your focus time in the tool, and you can arrange this pretty much how ever you want By default, the Start page in Enterprise Guide is designed to be a nice launching-off point for you to get going But you can even customize that, as well Let’s take a look So first things first– dark mode It’s all the rage All of the cool applications have dark mode SAS Enterprise Guide is no different In version 8.1, the developers added a dark mode to SAS Enterprise Guide Here, you see an animation just toggling back and forth between the two, dark mode and regular light mode But in a demo just after this, I will show you how to enable this for yourself But quick hint– there is just a nice quick keyboard shortcut that allows you to toggle between the two, dark mode and regular light mode That Control-F2 toggle will toggle between the two, but it won’t affect any custom editor settings you have So a lot of times, those of us who work in code or look at SAS logs, maybe we’ve customized the editor a bit for, say, custom fonts That’s one thing I always change So the dark default dark mode setting won’t affect that So there’s an extra step you need to do to customize And again, I’ll show you that in a demo just coming up Another thing to note is that not all of the windows within Enterprise Guide respect the dark mode, or what we call the Ignite theme

So a lot of the Task windows, and the Query window, some of those pop-up windows– they still use the normal Windows default color scheme So the dark mode is not going to carry through to every single window within Enterprise Guide, but it does cover many of them And also, you might find yourself wishing that you can customize the color schemes a little bit And at this moment, you can’t So you can only toggle between Ignite, dark mode, or Illuminate, the light mode, and the default color schemes that each of those themes give you You can’t customize those themes If you wanted to vary them at all Another aspect of Enterprise Guide that is going to be pretty exciting and worthwhile exploring is the wide variety of workspace arrangements you have Here, this animation is just flipping through a number of the different configurations that you could possibly have, how you can arrange your windows so that you can see the work that you’re doing and be more effective In earlier releases of Enterprise Guide prior to version 8.1, many users found themselves a little bit constricted because they could only see a couple of views of things at a time Say, one data set and one code view And if you wanted to see something else, you had to close one of those other views in order to open up something new But in 8.1 and 8.2 and later, you can have as many windows open as you want, and you can arrange those windows in all kinds of different ways You can have them arranged in geometric panes You can tear off tabs of content and float them in your window, and even over to multiple displays So you can really spread out as you work in Enterprise Guide these days Because there are so many options for the window layout within Enterprise Guide 8.1 and later, I’m going to just share some tips for how you can avoid getting yourself in trouble or get yourself back to a good spot, in case you get a little bit lost with so many windows going on My first step is to use the presets that are within the View menu There are some really popular default layouts that are available to you in those presets, and you can always choose one of those if you find yourself at sea with a layout that you’ve constructed for yourself You can revert back to one of those more comfortable preset layouts You can float windows across multiple screens And at the bottom of my presentation, you can see an example of where I’ve done that I have two displays usually in my workplace, and I often float windows between the two to give myself more room to spread my work out And you can also use F11 as a shortcut key to toggle back and forth between a full-screen view of whatever your active window is This gives you the opportunity to focus on a single piece of content– say, a piece of code that you’re working on– without being distracted by other panes within the Enterprise Guide tool And I mentioned that Start page at the beginning That Start page, amongst many of its features, also shows you the recent content you’ve had open But one of the really cool things that that Start page offers is also a list of pinned items You can easily pin an item that you’ve that you’ve selected or used recently within Enterprise Guide, and that pinned item will appear on basically a sticky list at the top of that Start page I use this quite often when I’m working on a project, sometimes over a matter of weeks It’s one of the more active things I’m working on, and I just pin it for the time being so that I have quick access to it whenever I come back into the tool Also, Enterprise Guide has a Favorites menu that you can customize So you can add your favorite tasks to a quick list of tasks You can add task templates to these favorites, as well, and I’ll show you how to do that later on in this tutorial Providing that list of Favorites and recently-accessed items provides a quick access to the things that you use most often within Enterprise Guide Because there are so many features, this Favorites list and this Recent list

gives you quick access to the things without having to hunt around too much In this demo, I’m going to show you some of those workspace customization tips that I promised OK First, let’s go ahead and do the dark mode thing So Control-F2 is the quick keyboard shortcut If I just press the Control-F2 combination, you could see it toggles back and forth I prefer dark mode and I’m going to show you a couple of things here in dark mode But for most of the tutorial, I’m going to keep it in the normal light mode because it just shows better on video But while I’m in dark mode, there’s a couple of other things I want to customize, as well I do spend a lot of time looking at code, and what’s very important to me are the programmer fonts The default fonts within Enterprise Guide are not the best, in my mind The default, I think, here is Courier New, but I prefer some other special programmer fonts So I’m going to go ahead and find those settings under Program, Editor Options And then under Editor Options, check the Appearance tab And under the Appearance tab, you’ll see you have the ability to customize just about every aspect of code and elements within your program to give them different color treatments I’m going to change my preferred font from Courier New to one that I like called Cascadia Code This is one that I actually downloaded from Microsoft It’s free Another alternative you might like is Consolas That’s another one that’s built into Windows Consolas is a good one, but I’m going to pick Cascadia Code You could see this reflected here in this preview window, and when I click OK, you can see it right now reflected in my Code window I’m not done yet I’m going to go back into these Editor Options because I want to affect not just code, but I want to look at what the logs look like, So I would like to also affect the log output, so I’m going to pick Cascadia Code for that And then also, listing– sometimes, I use a text listing output I’m going to pick Cascadia Code for that There, all applied Now, I’m done with that But sometimes, I go back and forth between different settings, and going back into these Editor Options again just to show you a trick you can do to get back to frequently-used settings I’m going to pick that Appearance tab again, and you’ll see there’s an item here called Scheme This allows you to group a set of preferences within your appearance settings together under a name, and then you can easily load up the collection of preferences again to get back to them So I’ve already done that with my DarkThemeCode theme But if I hadn’t, if this was everything I wanted to do, I could use the Save As button here and give this scheme a name, such as DarkThemeCode and save it, and then I have that for easy access later on I’ve already done it, so I’m just going to go ahead and pick DarkThemeCode here And then I need to do the same thing for Log I already have a DarkThemeLog And then I’ll do the same thing for Listing I have a DarkThemeListing, and then click OK And then I have my named schemes applied So that got everything just the way I want it for dark theme But I said I’m not going to stay in dark theme because I understand as you’re watching these videos, it can be a little difficult to see, through the videos, how things are going So I’m going to change it, for better contrast for the video, back to the light scheme So I’ll do Control-F2 again to go back You can see it didn’t apply it to my editor For that, I need to go back to these Editor Options And under Appearance, instead of DarkThemeCode,

I’m going to pick a scheme I saved previously called LightThemeCode And you see it has the same Cascadia Code applied So I just repeat that step for the Log file, and again for the Listing file And there we go Apply it, and there Now, I have it just the way I want While I’m in here, let me open up a project I have saved I’m just going to open that up here I had it pinned on my Start page, you might have noticed That gave me quick access to it I’m just going to bring up one of the process flows that it’s already saved in my project and run it While it’s running, you can see that the different statuses are highlighted for the different items As they complete there, they turn from yellow to green But what’s not happening is output isn’t opening automatically If you’ve used previous versions of Enterprise Guide, you might have experienced where as tasks or code finishes running, results start popping up coming out of those tasks, and that can be disruptive in your workflow So by default in version 8.1 and 8.2, those results are not popped up automatically Actually, I’m quite used to that now I like that as items complete, the results don’t pop up Then, I can go and just open up the results that I want For example, this program ran I’m going to go ahead and open up the data So I can see, OK, now I have my data view for this item Now that I have a few things open, let’s start playing with the layout, the workspace So I like this nice big view of my data If you remember a few minutes ago, I talked about how you can use F11 to toggle the full-screen mode, so I can see nothing but my data So I’ll just press F11, and you can see, OK, now the whole screen is taken up by just my Data view And I can spend time in here, focused as I would like to, on just the data And to get back out of that, I’ll just press F11 again, and that takes me back to my normal Project Layout view I can also do things like grab this tab and drag it around And you can see the interface highlights to indicate where I might be able to dock this tab if I’m interested in docking it First of all, I don’t need to dock it I can just float it So you can see here, I have floated this window I can resize it as I like and float it just in here And I could even drag it over to another monitor, if I wanted to It’s now off your view, but I have another monitor over here on the side that I could still see the data in I could also dock it And you could see in the center here, the little indicators that show where I might be able to dock this Do I want to dock it to the top of my view here, to the bottom, to the right, to the left, or right back here in the center again as its own tab? So this provides a really easy way for me to move my windows around and understand exactly what’s going to happen when I release my mouse button and allow the item to dock So I can look here and I could see, OK, this is a great example I’m now looking at my data here I have my code That’s not the code that ran this data Let’s open up the code that generated that data OK I have my code And I could see my code, I could see also over here my log, and I can see the data Not a lot of space to focus on here I can’t really see all of my code, and I can’t really see all of my log, and I can see just part of my data So again, I can drag this Code window around And notice, it all comes together as it’s all kind of packaged up in one docking window– my code and my log all together But I don’t need to keep it that way

I can take that log and I can move it around, and dock it outside, too So even though, by default, these items that are closely associated, like the code with the log and its output data and other results, are all bundled together in one super window, I can break that up, too, as I need to in order to spread out and have more room to work I’m just going to leave this docked there for right now And then I’m just going to dock this back to where it was In there Now, my content windows aren’t the only thing I can move around I can also move around these resource panes over on the side So I can pick one of these up and float it, as well, and dock it to another spot And you can see all the options I have in this little preview indicator, showing where those things would be docked So for example, if I want to keep my Project resources over to the side, I can easily just dock that over here on the right And likewise, maybe I want to move my Servers list over there, too I can do similar and just move it to be docked underneath that one So instead of the default view of having those resources over on the left, I’ve now moved them over onto the right That’s a preference that some people have Within here, I can also decide which things to show or not show So for example, I don’t need to have all these things available So if I don’t use Prompts, for example, I can just hide that pane, and then it goes away and doesn’t take up a spot in my list I can always get it back by finding it in the View menu You can see all the resources here that are available, and you can turn them on or off, as needed, as your preference dictates I can also move things around, like if I want to move this Servers item up here, I can put that over here up in this list of tabs, instead So I can have my Project and my Servers right here so that I’m not looking at them each at the same time, but they are together And maybe I want to go ahead and just close these panes, and then I have more space in here to see more items in my list as it expands So lots of different options you have in order to make room for yourself and the kind of work that you do And if you get yourself so out of whack that you can’t remember how you got here and where you can find windows you’ve closed or rearranged in such a way that you can’t get to them again, you can always go to the View menu and say Reset to Default Layout And after a verification prompt, you can restore it, and then you’re back to back to the way you were One of the first things we need to do when we start using Enterprise Guide is often import data into our project Importing data is easy in Enterprise Guide It’s really just a point-and-click operation But if you want to take it to the next step, there are some important things you should know about how the Import Data task works, and some techniques you can learn to help bring that to the next level so that you can be more productive and reuse those import steps in other scenarios Let’s take a look at what’s going on in import So the Import Data task is basically a point-and-click wizard It gives you really easy access to import flat files, like CSV, tab-delimited files, or Excel files, and a few other types, as well We’re going to talk about how this works, though, behind the scenes, and how to use some of the advanced options that are in the task to do even more and build repeatable steps So how does it work? Well, the Import Data task works on a local file So that means you pick a file that you can access from your local PC That may be on your local hard drive, or it could be on a network drive that you can access from your PC But regardless, it is a file you usually

select from your local system, whether it’s a CSV file, or an Excel file, or something similar The first thing that the Import Data task does is it scans that file to determine the field names and types So from Enterprise Guide, it looks through at least the first so many records of the file And by default, I think that’s about 4,000 records it’s going to look through at the most just to make its best guess at the field names and the types, whether that’s numeric, or character, or does it look like a date, or a currency value, that kind of thing And then that will set you up for the rest of the steps that the wizard does It will also try to clean that file or cleanse it, in case that you are bringing in a file– especially a text file– which maybe doesn’t have the most standard formatting And then it will present you with options for input So it will give you the list of those field names It will give you all of those guesses that it made, in terms the field names, and the types, and the lengths, the INFORMATs and FORMATs that should be applied And then you have the opportunity to modify and customize any of those before you actually commit to do the import Once you’ve made all of your selections and you press Finish, and the Import task runs, it actually takes this cleansed file that it prepared for you, based on all of the selections you made, and it copies that file from your local file system over to SAS So it has done this work for you It has scanned the file It has made a copy of the file, so it’s basically duplicated the data locally behind the scenes And then it moves that file over to SAS where it’s going to run some SAS code to actually do the import So the SAS code that is running is actually formulated to run on this cleansed version of the file, not necessarily the exact file that you selected for input It’s on the file that Enterprise Guide prepped for the input work to happen in the SAS session But is that what you want? Many of us do work with dirty data or files that are in the non-standard format, and so we can appreciate this cleansing work that Enterprise Guide is doing, and the scanning it does, and the guessing it does But what if your data’s already clean and already in the shape that we need? Do we need Enterprise Guide to do all that work for us? Well, good news, you can use some of the advanced options to bypass this step and not create this cleansed file that gets moved over to SAS And that will save some time And you can also limit the scan, so limit the number of records that Enterprise Guide will read in order to do it’s guessing, and that can sometimes save you some time You can then generalize this import step That’s another option you have, so that it can be run outside of Enterprise Guide So if all you’re doing is pointing and clicking through in a project, you just need your file in SAS, maybe you don’t need to do any of these advanced modifications on your options But if you want to reuse this step in other contexts, then it’s a great idea to go ahead and select these performance options, bypass the cleanse, generalize that import step It will create for you a DATA step, or if it’s an Excel file, it might even create a PROC IMPORT step for you that you can run in other contexts outside of SAS Enterprise Guide In this demo, I’m going to show you how to import a file in SAS Enterprise Guide, and then to take that import process and modify it to make it more flexible later on So I’m going to start by opening an existing file, and I’m just going to pick one from my local computer And I have a series here of CSV files that actually contain my online movie streaming history I’m going to pick the top one here, the biggest one And you’ll see that just opens up in Enterprise Guide, and I can get a preview– just a text view– of what that file looks like Looks like a pretty straightforward CSV file with a title and a date That is the title of a show and the date that it was streamed Now, in order to import this using the Import task,

I’m going to have to create a project in Enterprise Guide So if I start the Import task, it will create a project for me, or I can just click the Create Project button over here And I can go ahead, and just for good measure, go back to this text file view, right-click on the header, and say Add to Project So now that file is included in my project And then from there, I can right-click on the file name and select Import Data And this will bring up the Import Data task, which will do the first level of scanning the file for me and get me started with the whole process So for simple files, I could just click Finish right here, just trusting that the Import Data task is going to do everything it needs to do for me, clicking Finish, and it will create a data set with the fields that it detects So let’s see what that does Ah We can see it did the right thing It created a title that seems to be long enough to accommodate these fields, and a date An important thing to notice is it’s a real date field Even though the file input is clearly text, the Import task detected that, OK, this looks like a date format, so let’s import it as a proper date That way, we can do sorting and comparisons in math on that field later on But I’m not done I’m going to go back and modify this task to optimize it a little bit The first thing I’m going to do is I’m going to check out the Performance button here, and you’ll see that I have the opportunity to Bypass the data cleansing process, which will prevent Enterprise Guide from creating a cleansed data file for me Before I get into that, let’s take a look at the actual code that was generated by the task It’s pretty simple code It’s a DATA step that’s reading in this data file But look at the path The path is this big, long, convoluted path with a system-generated name that actually isn’t the file that we picked It’s a copy of the file, which looks probably a little bit different In fact, let’s go ahead and just open that up in another editor and just see what that looks like, because I’m now I’m curious So I’m just going to open it up here in Notepad++ And you could see it looks similar to the file that we opened before, but doesn’t have the headings, because the headings are encoded in the program that was generated And the separator character is not a comma, but it’s actually– I think it’s a delete character that is generated in this cleansed file, something that is unlikely to be encountered in the file itself So Enterprise Guide picks that character as a delimiter so as to not interfere with the real content of your file But all of that is really unnecessary for most clean, prepped data files, and so I’m going to bypass that process Going back into Modify Task, and then I’m going to go into Performance and select Bypass the cleansing process, and click OK, and Finish And now if we go back into the code, we’ll see, ah That is that actual path of the file that we selected The delimiter is actually the hex value for comma And everything seems to be all right now Furthermore, we can further modify the task If this was an Excel file, on the final path, we could say, Generalize import step to run outside Enterprise Guide And in this case, where everything is happening local, it may not make such a big difference But if it was more complicated than this, the code that it generates might be slightly different, more optimized for running outside in another environment As it is, I could just take this code and I could drop it into another SAS tool, like SAS Studio

or even just base SAS on Windows, and as long as the file is where it says it is, the Import task would work I can further modify this code if I want to, to optimize Sometimes, the Import Data task is very explicit in the code that it generates It’s going ahead and assigning a FORMAT and an INFORMAT to all the fields And while those things are important for the date field, because it tells SAS how to read the data and how to interpret it, for our text fields, it’s not as important I don’t need an INFORMAT to show me to read it as a text character of a certain length SAS will do that automatically And I might, if I want to generalize this for more use other places, change the title to be a different length To modify this task code to suit my purposes, I could just begin to type in here, and I’ll get this prompt that tells me the code is read-only Do I want to make a copy of it that can be modified? If I say yes– now, you might have noticed when I went to import that data that I actually had multiple CSV files in that folder I actually have five CSV files, and they’re all exactly the same format And so I’m going to change this code to actually import all of them at once This is a nice trick that the INFILE statement can do for us Instead of importing just one file, I can import a series of files by just using the standard filename wildcard notation So instead of _Dadasaurus, I’m going to just do _*, and that will match all of these files that are listed here in this directory While I’m in here, maybe I want to go ahead and modify the Title to be a little bit longer to accommodate some longer fields that might exist And I’m going to go ahead and change the name to Netflix _ALL And let’s go ahead and get rid of the FORMAT and INFORMAT, because as we said, I don’t need those But I’m going to change the FORMAT, actually, to be DATE9 because I prefer that But the INFORMAT needs to be the same because it is MMDDYYYY And I’m going to go ahead and remove the format descriptor on this INFORMAT descriptor, because I don’t want to limit the number of characters I’m reading on the INFORMAT So if I click Run here, you’ll see now I get also still a clean nice clean file, but it’s a lot longer It’s all the records here And with further modifications to this code, I can also encode the filename that’s going to appear, which file it came from, by using the filename option on the INFILE statement I’m not going to go into that right now I actually have another tutorial on the SAS Users YouTube channel that you can check out that goes into this in much more detail So I encourage you, if you’re interested in knowing how to read multiple text files using SAS, starting with the process in Enterprise Guide, you can go check out my more extensive tutorial elsewhere on the channel Obviously, when we’re working with SAS, we are working a ton with data Let’s spend some time looking at the kinds of cool things we can do within the Data Viewer within SAS Enterprise Guide Let me open up some data And just to make it a little bit easier to work with, I’m going to go ahead and make this full screen so I can focus on it So I’m just going to press F11 Now, let’s look at some of the features and functions

of the Data Viewer within the tool here Obviously, we can do our standard navigation So I can use the arrow keys to move around between records I can use the End key to get to the end of the record I can press the Home key to get back to the beginning– start of the record, the first column If I want to navigate all the way to the end of the data, I can press Control-End, and that gets me down to the last record in the last column Or if I want to get back to the top, Control-Home will do that, as well Bam We can also go to a specific record If we know exactly the record that we want to go to, we can press Control-G, for Go To And if I know the record number– let’s say, 3,000– I can type that in and navigate to the particular Column I want In this case, I’ll just leave it at title, but I could pick a different column Click OK, and then you could see that the selection navigates us right down to the 3,000th observation So I can also find in here So just as you think you should be able to, you can press Control-F, and you can find a piece of text or a value within the whole data set So if I want to find, say, Tiger King, and search the whole table– so Find– and you can see, there it is down here It was done in record 5,793 Pretty cool Once we’re in the view here, we can look at the different properties for the column So if I just hover over each column name, you can see it shows me the main attributes– the Name of the column; if it has a label, it would show there; and the length of the column in bytes in SAS terms I can also navigate way over to the scroll thumb And as I move the thumb around, you can see it’s showing me a preview of which record I’m navigating to and the total number of records available So that total number of records available is accurate when we’re working with a SAS data set or with any data source for which we have a known record count in our metadata If we’re looking at a SAS Data View or some databases, for example, we may not know the exact record count as we’re navigating, so that number will be indeterminate But in this case, this is a SAS Data Set, so we know exactly how many records because that’s encoded in the data set I can also look at the properties of a column just by right-clicking and selecting Properties And I can see the more detailed properties for each column– the Name, the Type, whether it’s the main group– Enterprise Guide assigns a group to a column type, so it could be a date type, or currency, for example, or a date time All of these are numerics in SAS They are treated specially because they are special types because they have certain formats assigned If the column isn’t wide enough for us to see the whole value, we can auto-size the column just by double-clicking on the border of it, and then Enterprise Guide Viewer will expand to accommodate the length of the largest value within that column So in many ways, this Data Viewer works like any other tabular viewer that you’re probably used to, so nothing really special there I want to spend some time talking about how you can filter your view of the data, and that is by using the Where tool So up here on the left, you can see there’s a button for Where And if I click to expand it, it will show me a field where I can type in my WHERE expression And you can see, actually, it shows me an example for this data source of what a WHERE clause might look like So this is a standard WHERE expression in SAS So if you are used to using the WHERE statement within your DATA step or within a PROC statement, the syntax that’s valid there is also valid here It’s the exact same thing So for example, if I want to do WHERE the title contains–

and I’m going to use the question mark as a shorthand for contains– office and press Enter, you can see now my view is filtered to just records– and there are plenty of them– that contain the word office But I can get even fancier I can combine with Boolean values, Boolean expressions So I can say “and” I want the date– this would be the date streamed– greater than, let’s say, 01jan2020 And notice I’m expressing this date value using the date literal syntax that we’re all familiar with in SAS, where I have a quoted value and then the d to indicate it’s a date value So I’ll press Enter here, and you can see now I’m filtered down to just the occurrences of streaming that happened in 2020 If I want to go and change that to, say, less than 2020, so before 2020, I’m going to press Enter there, and you can see, OK, now, I’ve just got the pre-2020 dates So pretty simple I can also do between So I’d say WHERE date BETWEEN, let’s say, 2018 and 2019 So just to remove the ambiguity here, I’m going to put some parentheses in there to help that, and press Enter And now you can see I’ve got only the ones that occur within 2018 And how many records? I can look over here on the thumb and see it’s 213 episodes of The Office were streamed in the year 2018 So big year for The Office We can also use SAS functions within these expressions So another way to express what I’ve done here with the BETWEEN operator is instead of using BETWEEN and has having to specify two dates, let’s change this to WHERE the date is just in the year 2018 So I’m going to say WHERE the year part of date– I’ll just say the YEAR function, which will return just the year– equals 2018 And I get the same results If I want to easily just change this to, let’s say, look at 2017, you can see, oh, there’s very many fewer And if I go to 2019, I could see, OK, let’s look over here in the thumb 140 records there And then let’s see what this year has been like in 2020 so far 165 So it’s shaping up to be a big year for The Office here So all of these expressions– it can be quite flexible to explore in here without having to do the work of, say, writing code or starting the Query Builder, which would create a whole other data set, which all of that takes time and storage So this can be a faster way to get a quick view of what’s in your data Let’s see You can also look at all the metadata properties for all the columns by clicking the Properties button up here, this little icon in the toolbar, to view the properties And you can see this is, in general, the properties for this data set And we have a Columns tab that shows us now all of the column information in one place And if I want to save this off– to, say, create a data dictionary or something like that– I can Copy to clipboard and paste that into an Excel sheet, if I want to, and there it is, ready for any kind of reporting I might want to do on this data And while we’re in here, let’s look at what it takes to copy the data out If I want to take some of this data and put it into another place, I can just

select the data I want, right-click, and I can, of course, copy the values But this feature, called Copy with headers, is actually pretty neat When I Copy with headers, what it puts on the clipboard is a tab-delimited series of values for these records, including the headers So if I create a new sheet and paste that in, you could see in Excel, I have basically a little mini spreadsheet that contains part of the data This is a pretty neat way to be able to share data out quickly without a big process One of the realities we have to deal with when we’re using SAS in a large organization is that eventually, the people we work with are going to want their output in Excel Now, to some of us, that feels like a tragedy because obviously, we much prefer working in SAS But in order to keep our colleagues and managers happy, we need to find a way to get the great output we’ve created with SAS into a format that our constituents can be familiar with There’s no shortage of ways that you can export your SAS content to Microsoft Excel I’m going to go through a few of those here Each method is different and serves different purposes, and it’s important to have all of these in your toolkit so you can decide the best method to use for whatever situation you’re in But first, what do you use today? When you’re working with Enterprise Guide, do you just use the Export menu in Enterprise Guide, or the Share menu to dump your data right into an Excel sheet? That is one thing you can do, but there are many more options Or do you do it the traditional way of PROC EXPORT from a SAS program? Certainly, that still works, too Or maybe you stand on principle and you just won’t create Excel files, and you’ll leave your colleagues to their own devices to figure out how to get the data If so, well, good for you But for most of us, we don’t have that option These are just a few of the methods we’ll talk about I really like to talk about Send to Excel because I think it’s an overlooked method that is really simple to use for ad-hoc purposes when you just need to quickly get some SAS output to Excel I’ll talk about ODS Excel That is something that is built into SAS You can create your report output pretty much the way you like it in SAS, using the SAS programming language There are some options built into SAS Enterprise Guide to make that even easier And then we’ll also cover the Export or Share methods that are built into the menus within Enterprise Guide that allow you to move your data directly into an Excel spreadsheet And we’ve already talked a little bit about the Copy with headers approach That is another simple way When you just have a few records that you want to jump into an Excel spreadsheet with or maybe paste into an email message, don’t forget that That’s a really simple method you can use without a lot of process All right Here we are back in SAS Enterprise Guide Let’s take a look at Send to Excel You’ll find it under the Share menu in Enterprise Guide 8.1 and later In earlier versions, you’ll find it under the Send To menu So we have a number of options here depending on what other tools you might have installed But I’m going to go ahead and pick Send to Microsoft Excel What this does is it actually launches an instance of Microsoft Excel, and then it actually sends the content from Enterprise Guide into that sheet It’s kind of like a glorified copy and paste, or yeah, it’s exactly like a glorified copy and paste Who am I kidding? So it’s bringing up Excel and actually sending the records to Excel And it ran quickly Now, while I’m in Excel, I can modify the sheet further if I would like to say, bold, the headings or do other kinds of things in Excel And then I can save this Excel file and share it with other people And that’s a pretty typical way that, on an ad-hoc basis,

you might share content to Excel But there’s a couple of downsides to it One is, first of all, if the data is very large, then it’s going to be very slow to actually send to Excel It’s going to take a long time And so it may not be the most efficient thing to do If it’s something you want to repeat– if you want to have a process in your Enterprise Guide project that does this every time you run the project, this isn’t the way to do it This is purely driven by the menu, so you have to point and click to make this happen But there are automated ways that you can send your content to Excel, so let’s talk about that next Let’s talk about ODS Excel Now, if you’re familiar with programming in SAS, you are undoubtedly familiar with ODS and the many different options in ODS that we have– they’re called Destinations– to create output in different file formats The default one in Enterprise Guide, and in many tools that we use, is HTML So we can create report output using HTML And you’ll see here, I’ve got an example of a report I created It’s using HTML And it’s got a graph and it’s got some tables All of this together might make a nice report in Excel, but it’s HTML right now So how do we turn this into an Excel report? In Enterprise Guide, it can be very easy Under Tools, Options, let’s explore the many different kinds of results that we can have So HTML, as I said, is the default But we also have Excel We have our RTF, basically for Word, PDF, and PowerPoint We have our traditional listing, which is just text-only And then we have the proprietary SAS Report, which is used among some other SAS tools Most of us are good with HTML in our day-to-day, but sometimes, we need Excel for other people, or we need PDF for static reports, that kind of thing So you can change any of these options and set them at the application level to set your preferences And then with each within each of those options, you have options to say how you want that HTML Which appearance style would you want? How about Excel? There’s metadata you can add into Excel and have all kinds of controls But remember, when you set these options here in the Options dialog, it affects everything you’re doing in Enterprise Guide It is an application-wide setting So it would be nice if we can just control this on a task-by-task basis And guess what? We can We can get to that in the properties for each task or program that we’re working with In this case, I’m working here with a program that generated this graph and table output So I’m going to go ahead and select View and Set Properties, this little icon in the toolbar And you’ll see a mini version under the Results tab here of my options And so to get Excel output, I’m just going to select to Customize results formats, and then uncheck HTML and check the Excel option, and click OK And then I’ll run this And then as it completes, you’ll see I no longer have this HTML report Instead, I have a placeholder for Excel Enterprise Guide won’t open the Excel file inside the app, but it will give me an easy way to launch Excel so that you can see these results And so we can see what this generated was an Excel file that has one sheet per piece of output Maybe that’s what you want This is the default output from ODS Excel But there are lots of options we can select to customize that Let’s take a look at what that looks like I’ll just close that Here, I’ve modified my code a little bit Recognizing that I’m going to be creating some Excel output, I use the ODS EXCEL statement and this ID option, which signifies I want to basically modify the Enterprise Guide-generated ODS EXCEL statement

to add or modify some options I’m going to change the SHEET_INTERVAL to none, which means that it’s not going to break out this content among different sheets I want it all on one sheet By default, the SHEET_INTERVAL is PROC or TABLE– I think it’s TABLE– and so every piece of output gets a different sheet And then I want to customize the name of the sheet, called Shows So having made that change, I’ll just run this And it comes back pretty quickly So I’ll just open up this Excel output again And we can see what is different You could see, instead of having multiple tabs, multiple sheets, I’ve just got the one, called Shows, and everything is included on the one sheet now as a single report Great That’s a great start Let’s take this to the next level What if I want to not just have my report output, but I actually want to share out my data, as well? So let’s say I want to create an Excel report that contained the report I just showed you plus the raw data from my input, so that we have that for reference within the same Excel workbook? It’s a little more coding we need to add to make this happen So what I’m going to do is undo this modification I made And I’m just going to hit Control-/ to comment that quickly And then I’m going to uncomment these other items that I’ve staged here Control-Shift-/ does that for me And just by way of explanation, really quickly, I’m going to, first of all, define where I want this Excel file to be So by default, when Enterprise Guide generates ODS statements for you, the file it generates is in a temporary file in the SAS session, and Enterprise Guide does you the favor of bringing in that file back into your session so that you can easily view it I’m going to be explicit about where I want this to be I’m just going to put it in the WORK folder, calling it report.xlsx And I’m going to use the ODS EXCEL statement myself with my own ID, specifying that FILE output, and then keeping those options I set before– the SHEET_INTERVAL =’none’ and the SHEET_NAME = ‘Shows’ Then at the end, I’m going to uncomment these items When I’m done creating that report, I’m going to close that ODS EXCEL destination, but then I want to append, in the same workbook, that detailed data that comes from my viewing data set So for that, I’m going to use PROC EXPORT here, specifying the DATA = viewing I’m going to use the same output destination, OUTFILE And in this case, I’m just going to be able to append to it So one thing to know about ODS EXCEL is when you use ODS EXCEL, it always is going to create a new xlsx file But PROC EXPORT can add to an existing Excel file So we wouldn’t be able to do this in the reverse order I can’t use a PROC EXPORT and then use ODS EXCEL to append onto a file that I created using PROC EXPORT But I can use ODS EXCEL and then use PROC EXPORT to append onto the thing it created So I’m going to specify DBMS = xlsx And this does require SAS Access to PC File Formats, which in practice, most people have But if you don’t, just keep that in mind that in order to use PROC EXPORT to generate native Excel files, it does require that module from SAS I’ll REPLACE the sheet if it already exists In this case, it won’t, but that’s just specifying that And then the SHEET_NAME– I’ll just call this ALL VIEWING Then the next thing I need to do– this is important– is I want to make sure that in my properties that Enterprise Guide is generating the code for me, I want to customize these results formats again, but I’m going to deselect everything So I’m going to clear out all of these options, which basically means, Enterprise Guide, I

don’t want you to create any default ODS output for me Click OK, and then I will run this Now we can see a much longer filename here It’s coming from my temp area in my SAS session But let’s open this up and see what we got So what we have is we have that report that we saw before, same as it was And now we have a second sheet here, ALL VIEWING, that is the raw data that fed into that report for reference So I’ve packaged up here, in a single Excel sheet, the nice output from my project so it’s ready for my constituents Finally, let’s look at how Export works So within Enterprise Guide, I can also share directly to Excel, an Excel file Again, under the Share menu, I can use Export So instead of using Send to, which would automate Excel and bring up an Excel session for me, if I’d rather just write directly to an Excel spreadsheet file, I can use Export So when I click on Export, first of all, I need to generate a name or select a name So let’s call it Viewing But you can see my files of type– I have a lot of different file types here that I can pick from But I’ll pick xlsx And then I can just save that, and that’s it That file was created Let’s see if I can find it on my drive Here it is, viewing.xlsx If I open this up, you can see here, it’s just a native Excel file Nothing really special about it When you export this way, it’s exporting just the data So there’s no formatting– that is, no appearance formatting– no ability to export a graph, or anything like that This is just the data rows And in this case, it’s Enterprise Guide writing directly to the Excel file So this style of export does not require SAS Access to PC files because its Enterprise Guide doing the work, not SAS There is no SAS code generated to make this export step happen If I want to repeat this step every time I run the project, then the thing to do is to select Export as a step So very similar to what I just selected before, I would just do Share, but instead of Export, I would select Export as a step in project And now I have a slightly different interface, a little bit wizard to step through, but I have pretty much the same options So I’m going to select this VIEWING, and then I want to export as an xlsx file I have an option to use labels for column names if I’d rather, and then click Next I’m going to put it on my local machine It opens that in a temp folder And I’m going to select to Overwrite existing output, so every time I run this, it will overwrite the version that’s there I have a summary here of what’s going to happen, and I click Finish And then it runs, and you see it exports Bringing up and viewing my temp folder here, I can see that, indeed, the file is there And it looks very similar to what I just showed you with the plain old Export task But the difference is that this is a task now in my flow, and you can see it actually has a little bit of a log showing me what happened, that the Export job completed And you can see that here, now, in my flow, there is this Export step that now exists, and it shows me the flow of things So once this data set is created and this whole flow runs,

this Export step will happen again and generate that file for me Let’s talk about Enterprise Guide projects One of the things that makes Enterprise Guide different from some other SAS tools is that you can organize and store your work within these project files, which can make it easier for you to maintain your work over time Let’s talk about some of the other advantages projects have for you But first, what you need to know about projects is that you don’t need to use them, at least within Enterprise Guide 8.1 and later It used to be that every action that you did within Enterprise Guide, first thing that would happen was it would create a project file for you And if all you wanted to do was write code or open some data, a project file may have felt like a little bit of overkill Well, now, if all you’re doing is coding or viewing data, you no longer need to have a project file going But you do need project files if you have any kind of interactions with tasks or queries So that is going to basically kick off the creation of a process flow within your project, and you’d need that project structure in order for those things to happen Project files change over time And the way it works with SAS Enterprise Guide is project files are always forward-compatible That is, you can have a project file from a previous release and open it in a future release However, once you’ve saved that project file in the most current release, you are not going to be able to take it back and open it up in an earlier release That’s a frustration that sometimes people run into, but it is just the way it works And the way to plan for that is just to make backup copies of your files every time you do a version migration, just in case Project files are a great way to organize your work, and they can be self-contained, in that all of your code, and your data, and notes can all be in this one project file But they are opaque to other processes That means, there’s nothing else that can really read Enterprise Guide projects, other than Enterprise Guide, or in some cases now, SAS Studio can open up project files and bring some of that work forward But there aren’t many other tools– well, no other tools that can really read a project file So a project file is not really shareable with people who are not using Enterprise Guide You think of projects like a recipe They contain the list of ingredients of everything you’ve done in order to accomplish the work that the project represents and the instructions for how to combine them But it doesn’t always contain those ingredients That is, you may have a reference to code or a reference to data, but the code and the data don’t need to be within the project file itself They can live in another place in a file system Let’s talk about some of the special things that you can do with projects, though You can, of course, as I said, organize your work and add documentation– never a bad idea– using the Notes feature And these can be like a sticky note within your project and process flow You can create relationships between items in your project and your process flow with links So you may notice, as you build up your project and your flow, that items are linked together and that this task creates this data and creates this output, and so those things are represented in links But you can create your own links that help to not just document but also enforce a sequence of events that happen in your project There’s a special process flow, called Autoexec, that you can add to your project When you have a project that contains a flow called Autoexec, much like the traditional SAS program that would kick off at the start of your traditional SAS session, the Autoexec flow is a flow that will kick off when you open your Enterprise Guide project So if you have a special task, or say a piece of code, or a LIBNAME assignment, or something that needs to run before anything else in your project, you can include this in an Autoexec flow, and basically in code, enforce that sequence of events happening

Projects can be much more portable among team members if you use relative paths for things that you reference within your project So files you might reference within your project include SAS program files, or flat files, or Excel files that you’re importing You can mark a project as having a relative file of references, and so the project, as you move it around, will retain those linkages If you move that file to another directory or you give it to a colleague who has maybe a different directory structure, everything will work relative to each other when you use this feature It’s possible to include conditional branches within your process flow You can set up rules for different parts of your project to run, depending on conditions as your flow executes And if you have a special sequence of tasks that need to happen in a special order, you can capture this in what’s called an Ordered List An Ordered List let you pick tasks from across your project, no matter what flow they’re in, and add them as a sequence of things to run And then any time you run that Ordered List, just those tasks will run in the order that you specify And you can share work across different projects by using copy and paste So you can copy a task, or one item, from a project to another project, or you can copy an entire sequence– a whole flow– across different projects, and everything will be maintained as you copy from one to the other I want to share with you a short demonstration of a special task called the Project Reviewer Now, it’s not built into SAS Enterprise Guide It’s a custom task And you can download it for free from my blog at Just search for Project Reviewer, and you’ll find the blog with a link to the download A couple of special instructions are included for how you would install this into your SAS Enterprise Guide Once it’s installed, you’ll find it in your list of Tasks I already have it installed, so I’m going to go ahead and find it in my Task list It’s called Project Reviewer, so I’m just going to type in project, and you could see the task it shows me here It works, of course, only if you have a project open So I already have a project open And it will show you the list of process flows you have, as well as what’s in those flows So in this second flow, I have seven items that execute, and they are programs, and queries, and other tasks They were all modified by me, so it’s just showing who last modified them, how long they took to run the last time they were executed, when they were created, when they were last modified, and whether they have any errors All of this is information that you can get from these items in your project You can right-click on any item that runs, and select Properties, and see all of this information available to you The Project Reviewer task just sums it all up for you in one view, and then gives you the total running time for the project In this case, it’s a pretty fast running project It only takes four seconds to run I can also create a report out of this, and encode this report into my process flow so that any time that the project runs, it will actually create a report about how it ran So I created a report and it ran pretty quickly here, and you can see a summary report of basically what I just saw in that window, but now it’s here in my project in report form And this task is now included in my project So anytime my project runs, this Project Reviewer task would also run for me It’s just a handy little tool to get a little bit more information out of your project and make things a little bit more visible for others to see what’s going on One of the special subtleties of SAS Enterprise Guide is how files get moved around Most of the time, we’re just pointing and clicking, and we’re selecting files from our local machine, generating files on a remote SAS session, and everything just works It just connects and shows up in our Enterprise Guide session, as if by magic

But what’s happening behind the scenes is Enterprise Guide is actually copying files or moving files back and forth between your local PC and a remote SAS session You can take control of how Enterprise Guide does this when needed And this is important, because sometimes, you need to explicitly copy files from one place to another And there are some special tasks within Enterprise Guide that allow you to do that One of them is called, fittingly enough, the Copy Files task And think of this as sort of like FTP within your Enterprise Guide session You can pick any file from your local machine, or series of files, and copy them to a location on the remote SAS session Likewise, you can copy files from a remote SAS session, a remote directory in your SAS environment, and copy them to your local PC Think of doing this when you have a local Excel file that you want to run PROC EXPORT on the remote SAS session, or you’ve generated an Excel file in your remote SAS session, and you want to download that to your local PC to attach it to an email or distribute it to some colleagues In addition, there are a couple of other special tasks specifically for data sets, and it’s the Download Data to PC and the Upload Data to the Server These tasks specialize in selecting data set files from your local PC and putting them into SAS libraries, or selecting SAS library members and downloading them to your local PC as a SAS data set file Let’s talk about the Upload and Download Data Sets tasks first They deal with SAS7BDAT files So we’re copying SAS7BDAT files– that is a SAS data set file– from your local PC to a remote SAS library The Download version of the task copies data from any SAS library to a local SAS7BDAT file And it’s important to note that this doesn’t need to be a SAS library It could be a database library that you’re pointing at It will still make a copy for you and download it to your local PC as a SAS7BDAT file If you have used SAS CONNECT and PROC UPLOAD and DOWNLOAD in the past, this works like those But it’s not using SAS CONNECT Instead, it’s using the connection that Enterprise Guide has with the SAS server to move these files back and forth The steps, therefore, are not represented in SAS code That is, even though there could be a little bit of code that is generated to fixing encodings, which I’ll talk about in a moment, most of the work that’s happening– that is, the file transfer itself– is not in a SAS program that you could copy and use it in some other environment As I just mentioned, there are some postprocessing steps that can happen during these tasks to fix encoding So if you have a local SAS7BDAT file that happens to be Unix-encoded, but you upload it to a Windows Server or vice-versa, well, these tasks will fix that encoding for you They will rewrite the data set into the native encoding of the SAS session where you’re sending it to The Copy Files task is a much broader workhorse It can move any file between your local PC and your remote SAS session You can move one file at a time, or you can move a whole batch of them using standard wildcard notation, such as the asterisk or a question mark You can also generate dynamic names and folders as instructions for which files to copy and to where using SAS macro variables If you’re moving text files back and forth, especially between the Windows and Unix environment, you have the option of fixing those line endings That’s because those environments use different conventions for the line endings And it also supports the task template mechanism that’s built into Enterprise Guide, which makes it easy to reuse among different projects So if you have a set of files or a type of copy operation that you have to repeat over and over again in just about every project that you do, make a task template for that,

and then it’s easy to just drop that into any project where you need it In this demo, I’m going to show you some examples of the Copy Files task in action Let’s imagine that I have a remote SAS session– let’s call it SAS App– and I have some local CSV files that I need to import using SAS App And then on that SAS App server, I need to read those files in, do some analysis, create a report– say, an Excel-formatted report– and then download them back to my local PC so that I can use it there or share it with colleagues, for example So in order to get this done, I’m going to need to upload those CSV files to SAS App, do all of the work in SAS to create the report, and then download the Excel file when finished So on my screen here, on the left, I have a list of my data files that I’m going to import It’s these CSV files that I’ve been using in other demos There’s five of them The first thing I need to do is I need to create a folder on my SAS App server to receive these files Maybe you already have one set up But in my case, I just want to make sure that, regardless of the file structure, I just need a temporary space for where these files are going to go So what I’ve done in my program here is I’ve identified, first of all at the local directory, where these are coming from as a macro variable And then, using a trick in the LIBNAME statement, I’ve identified a path for these to go into– just a subdirectory of my work folder And then I’ve created, using the LIBNAME statement, that subfolder that I can address using this macro variable called NetflixData This is important because in the Copy Tasks task, I’m going to be able to use these macro variables Let’s take a look at the Copy Files task right now So I’m going to find that in my Task menu And I can just search in Tasks for a copy, and you’ll see the Copy Files task comes up And I’ll just double-click to open that Let’s take a look at the fields that the Copy Files task lets us use First of all, I can pick which SAS server to use In this case, I want to use SASApp, because that’s the folder where I’m going to copy these files to I’m going to Upload from my local PC to the SAS session, because I need to copy the CSV files up there And then I would specify the files to copy, and I could specify the full path with a wildcard using, say, *.csv, and then specify a Destination folder on SASApp where I want them to go I’ve already done that, so let’s go ahead and open up the Copy Files task I have in my process flow here So just modifying this, I’ll just show you what I’ve already selected Upload, and then I’ve used that macro variable I defined This is the macro variable that defines the local path where my files are coming from– &localdata backslash *.csv– backslash because Windows like a backslash for the filenames And then the Destination folder is that folder I created, which I’ve assigned to the Netflix Data macro variable It’s very important that I click Resolve macro variables in source and destination paths so that the task knows to expect some macro variables here, and it will resolve those for me before it tries to do any of the copying Just in case, I selected to Overwrite existing files with the same name And because these are text files, I’m going to go ahead and Fix the line endings because if they’re Windows text files, they’ll have a different convention for the line ending than Unix does, and my SAS App happens to be a Unix server So when I run my program here– first, to define my macro variables So that ran And then I’m going to go ahead and run my Copy Files task And let’s open it, and you could see the log generate here

as it’s copying those files It resolved those macro variables, told me what files it found that matched my wildcard– *.csv– and then for each file it uploaded, it had an entry in the log Now, this is not a SAS program log This is a log from the task Remember, there’s no SAS code that’s happening here This is all just work that the task is doing using Enterprise Guide OK Let’s go back to the process flow My next step is I’m going to read the data in, and this is using the same imports style that I used before I’m just going to assign a filename to this path with the wildcard, *.csv, and then use INFILE to read them all in, in one fell swoop So let’s go ahead and run that on SAS App Great Now, back to my process flow I’m going to run another piece of SAS code that generates the Excel report And again, this SAS code may look familiar I’ve used it in other parts of this tutorial But in this case, I’m going to set dest to the folder on my SAS App server– the same folder I put the data in And I’ll call this report report.xlsx, and then generate the report using ODS EXCEL And then I’m going to tack on the detailed data from my report data as an extra sheet using PROC EXPORT in the same Excel file And then I’m going to use the Copy Files task to copy that down So let’s look at the settings for this Copy Files task So similar to the Upload, but in the other direction So in this case, I’m going to Download this file from my SAS session to my local PC The Source I’m copying from is that folder on my SAS App/report.xlsx That’s the file I intend to create using that piece of code I just showed you And then my local Destination folder is that &localdata macro variable that I defined earlier And just like I did before, I need to make sure I select Resolve SAS macro variables, and I’m going to Overwrite files with the same name, even though currently, there’s no conflict And I do not need to fix our line endings, in this case, because it’s an Excel file It’s binary Line endings is not an issue So let’s go ahead and run I’m going to go ahead and run this to the end, so I’m just going to right-click on my task here and say Run from selected item, which will run this task and then the one following And then as that Copy Files task completes, you’ll see the report.xlsx appears here in my local folder, and I can just double-click to open that in Excel and see what we got And there’s my report Here’s my Excel report from ODS EXCEL, and then all that detailed data that was created using PROC EXPORT following that So in a production capacity, I can just rerun this whole flow and it will copy the local files up to the server, import them, create the report, and then download that final report back to my local PC to complete the roundtrip of processing I want to show you one more thing related to the Copy Files task, and this is a trick you can actually use with just about any task Let’s say I’ve defined the settings in my task that I want to be able to reuse in other projects I can create a task template to do that The way I do that is I can right-click on the task within my project and select Create task template And my next step is to define a Name for it, so I’m just going to call this Copy Netflix Files to Server And I’ll just Create it Actually, if I have a lot of templates, I can organize these in folders and do all kinds of cool productivity things But for now, I’m just going to make a generic Copy Netflix Files to Server And now, if I search my Tasks–

let’s see Under SAS Tasks, I’ll go copy And you’ll see here, under my Tasks Templates, I have a new entry called Copy Netflix Files to Server If I open this up, you’ll see it comes prepopulated with all the settings that I had saved in the version I created in my project I can use this task in a new project to carry those settings over so that I can use them again and again This is a great way to be able to reuse your work You can also even share your template with teammates using the Task Template Management System that’s built into Enterprise Guide Cool way to reuse your work No Enterprise Guide tutorial would be complete without talking about the Query Builder Let’s face it Some of us spend a lot of time in the Query Builder, so it makes sense to make sure that we know all the things that it can do for us so that we can take advantage of its many, many features So here are a few things you should know about the Query Builder in SAS Enterprise Guide First of all, it creates PROC SQL code And in a nutshell, that’s really all it does But PROC SQL is big SQL, as a programming language is big, so there’s a lot going on here The PROC SQL that Enterprise Guide generates is mostly standard SQL, but it has some differences It offers point-and-click access to a bunch of data prep tasks, and here’s just a few of those Obviously, the basic filter with the WHERE clause, all kinds of joins, being able to recode variables, to sort data, to summarize and group those summaries, calculate new columns, connect to your databases, select and subselect– so subquery or nested queries– SELECT DISTINCT– that is, to dedupe data You can filter on join conditions You can filter on the summary– that’s the HAVING clause It can prompt for values You can use Enterprise Guide prompts to prompt an end user for values during your query And then you can add SASisms, like a label and a format to your output So this is a great time to think about what databases are you using in your workplace, because the Query Builder generating SQL is compatible with many databases that are out there So do you use databases, or are you just SAS data only? Or maybe you use a database at your enterprise, like Oracle or Teradata Or maybe you use a cloud-based database, like Amazon Redshift Or maybe you don’t really know because somebody else set all these LIBNAMEs up for you, and you just point Enterprise Guide at data and query away Let’s talk about a few of the things you can do Of course, you can do the simple filtering So once you throw your table into the Query Builder, it’s easy to just go over to the Filter tab and add one or more filters And you can combine these filters in multiple ways You can “and” them You can “or” them You can group them to create more complex logic, as well You can summarize So you can use the aggregation expressions, like SUM or COUNT, to just summarize data that’s in your data source And then Enterprise Guide will automatically group those summaries by the remaining fields within your data source that you’ve selected to include You can compute new columns So for example, you want to create a dummy variable that is a 1 or a 0 based on the value of one of your fields? Well, that’s easy to do You can just use the Expression Builder to compute a new column, and there’s some built-in expressions that you can use, or you can create a more complex expression using a full-on Expression Builder tool that lets you select from a whole bunch of different functions and operators to build more complex expressions Of course, you can join data

So you can add multiple tables The Query Builder supports up to, I think, 32 tables, which would be a crazy large join if you were to do it Most of us join maybe two to four tables at a time But I know some of you out there are joining lots and lots of tables When you join tables in Enterprise Guide, the Query Builder will automatically select a key field to do the join The join field will be based on fields that are the same name and type So it’s not the most sophisticated way to auto-select a join condition You might find yourself having to delete joins in the Query Builder and reform them using the fields that you need to use It’s a great idea, if you know your data, to be explicit about how things are joined Of course, you can have all the different types of joins– LEFT JOINs, RIGHT JOINs, INNER, and FULL, OUTER as well as a number of natural JOINs, as well And just like we saw with the Copy Files task, where I demonstrated using a task template to save the work we’ve done so that we can reuse it later, you can use Task Templates in the query settings So you can create a query and create a query template So a lot of us use some complex queries that can take a long time to build, and it’s a really nice feature to be able to reuse that query later in another project That’s what the query template allows you to do You can create a template out of a query you’ve built. Save it And then in another project, you can bring it up again and reconnect it with either the same data sources or different data sources that have the same attributes, and get a great leg up on reusing the query that you’ve built in the past In this tutorial, you’ve seen me do a fair amount of SAS programming I use Enterprise Guide for almost all of my SAS programming It’s one of my favorite environments for getting work done with SAS code Why? Because I think that Enterprise Guide offers just one of the best SAS programming experiences that there is out there Yes, there are great coding environments for general-purpose coding, but there aren’t any other environments, other than SAS Enterprise Guide and perhaps SAS Studio, which really understand the context of programming in the SAS environment The SAS language now is baked into the Program Editor within Enterprise Guide That means the language elements are there The keywords are colored appropriately There’s just so much of programming that’s easier when you have an environment like this Also, Enterprise Guide’s aware of the environment that you’re working in So it knows which libraries are available, which data sets, and even the variables within those data sets It can give you a list of FORMATs, and INFORMATs, and SAS functions So there’s so much available to you within this environment to just make the job of coding so much easier Next, I’ll share a few tips for how to get the most of your programming environment in Enterprise Guide Number one, turn on line numbers If they’re not on already, make sure that you go to your program, Editor Settings, and turn on line numbers They should be on already if you’re using 8.1 or later, but if you’re using an earlier version of Enterprise Guide, they might not be on by default Next, think about what you want to do about tabs It may not seem like a big deal, but tabs can make or break a team You really need to agree on whether you’re using tabs for indentation or spaces for indentation There are options within Enterprise Guide that you can set to control this behavior You can set the tab size You can insert spaces for tabs so you can still use the Tab key, but it’s actual space characters that are added into your code And you can even replace tabs when you open new files from other places If there are snippets of code that you use a lot, great idea to define those snippets as abbreviations within the editor Then you can type little shortcut pieces that will autocomplete to the full-on code that you need to use all the time I do this for LIBNAMEs every day

I have some SAS libraries that I access all of the time, and I have abbreviations set up that make it easy for me to just define those in any program This is a cool thing that’s new in Enterprise Guide 8.1 and later It’s that you can actually not just see the variables within your data set, but you can drag those right into in into the Program Editor by selecting them from your Server tree, your Library list, and drag them right in, and then add them right to your code Pretty cool We all have our favorite ways to format SAS code, in terms of our indentation, where we break lines, all that kind of stuff But Enterprise Guide can format code for you It may not be able to conform to all of your preferences, but it’s an easy way to take code that is otherwise messy and difficult to read Highlight it or just select the whole file, right-click, and say, Format Code Or use the Control-Shift-B keyboard shortcut in 8.1 or later, or Control-I in earlier versions of Enterprise Guide, that will format the code automatically Don’t forget that you can zoom in and out of your Program window Really handy when your program is really long You can zoom way out and get a great idea of your program flow and how the structure is This is a little-known trick, but it can come in handy sometimes You can use the Alt key, and the mouse key, and Shift to highlight columns of code or columns of text so that you can then select these blocks of text as a column and copy and paste them into someplace else Pretty neat thing to be able to do sometimes Of course, every editor has find and replace But don’t forget that Enterprise Guide, like other powerful editors, also allow for regular expressions to match on things that you’re looking for and also replace things that you’re looking for So using regular expressions and learning a little bit about how regular expressions can help with pattern matching to do more complex find and replace operations can really save you a lot of time when you’re making big changes Another hidden feature in Enterprise Guide is the ability to split your programming window You can right-click in the window and select Split, and split the view, and it will break your program into multiple views of the same code And then you can scroll those views independently So even though you’re looking at just one program file, you’re looking at different parts of it It can be really handy when you’ve got, say, a DATA step with some columns defined in one part of the program, and then you need to, later on in the code, reference that data set and its columns You can kind of pin the DATA step definition to the top and then work in your code down below in a different view And if the Program Editor isn’t good enough for you, or there’s some other thing you need to do that the Program Editor doesn’t support, you can always right-click and open the code out into your favorite default editor I sometimes use Notepad++ or VSCode to do other things So You can, from within Enterprise Guide, open up your code in one of those other editors Make changes there Enterprise Guide will detect when you’ve made changes in another editor and you’ve saved them, and it will update your view within Enterprise Guide to reflect the latest changes you’ve made And don’t forget the DATA Step Debugger built into Enterprise Guide since version 7.1.2, I think The DATA Step Debugger is a great way to find out what’s going on inside your DATA step It’s only good for DATA steps, but it’s an interactive debugging environment that allows you to really figure out what’s happening when you get stuck It’s also a great teaching tool and learning tool to know how the DATA step works Like any programming environment worth its salt,

SAS Enterprise Guide also integrates with Git Git is a version-control system that basically has taken the world by storm You may know it from working with GitHub, but there are other commercial systems, like Bitbucket and GitLab, that also integrate with this protocol Git is an open-source version-control system that is embedded into many, many tools, and Enterprise Guide is one of those And it’s just one of several SAS tools that have Git integration inside of it It’s a distributed version-control system It’s unique from other version-control systems in that every developer gets their own clone of the code repository It supports a variety of different workflows And as I mentioned, it is open-source Git aficionados often work exclusively on the command line with Git, but Enterprise Guide offers a nice user interface– a standard user interface flow for how you would clone repositories, when you make changes, commit those changes, push back to the original repository, view history, all of that in a way that you might expect, especially if you’ve worked with other tools Git has its own vocabulary, as well It has quite a lot to learn I’ve talked about it extensively in other forums, so while I could spend an hour talking about it here, we don’t really have that time But if you want to go deeper on this, I encourage you to search on for Git integration And you’ll find links to other webinars that we’ve done that describe the Git integration specifically with SAS Just a real quick overview– Enterprise Guide supports Git in a couple of ways One is without actually using any Git infrastructure at all that you need to set up It can support it just completely inside the project file So if you develop SAS programs and embed them in your project file, you can take advantage of Git-like functionality by having a program history You can commit your changes into this local, project-specific repository You can view history You can revert back to other versions So you can get quite a lot of the Git-like features from within that without any additional setup But if you do have Git in your workplace, and that may take the form of Enterprise GitHub, or GitLab, or Bitbucket, or something else, then you can take fuller advantage of the Git integration within Enterprise Guide and SAS tools that allow you to clone repositories of code to your local machine, work with them within Enterprise Guide, commit locally, push back those changes to the original repository, do all the branching and fetching that you would expect to be able to do in a full-fledged Git system So I encourage you, if you’re working on a team, especially, to learn more about this topic because using source management with SAS code really brings a level of discipline and rigor to your projects that is basically table stakes now for anybody who’s developing code By now, you’ve realized that SAS Enterprise Guide is primarily an interactive tool that you use point and click But that doesn’t mean you can’t automate things In fact, Enterprise Guide has a pretty rich automation model that you can use to actually script operations and make Enterprise Guide do things unattended In fact, this is exactly how Enterprise Guide works when you schedule a project When you use the interface to schedule a project or schedule a process flow, Enterprise Guide generates a script file for you and adds it to the Windows Scheduler to automate this activity, maybe when you’re not even at your desk or logged in So we can use that same mechanism to script Enterprise Guide, but writing our own scripts to do whatever we want within the tool It’s not just for scheduling Now, most often, VB Script is the language that’s used, but it doesn’t have to be that You can use PowerShell

You could use Python Any tool that can automate COM-compliant tools– these are Windows applications– can be used to automate Enterprise Guide Here’s a very simple script, just a very basic kind of Are You There? script for automating Enterprise Guide It declares a couple of variables, an Application object and a egVersion object And then it initiates the Application object with WScript.CreateObject() in VB Script And this just generates an instance of the Enterprise Guide 8.1, and then that goes out to the console– the name of the application and its version And the result? Unexciting, but effective You get to see that Enterprise Guide actually started up, and it can report back its version And this is a great sort of first step script to use just to make sure that everything is working in terms of your scripting mechanism And then you can move on to do more complicated things, and we’ll take a look at that in just a second Let’s take a look at some examples of automating SAS Enterprise Guide in scripts All my examples here are going to be using VB Script It’s pretty easy It’s built into the Windows operating system But as I’ve said before, you could use PowerShell or Python if those are the languages you’re familiar with Let’s take a look at the first one Basically, I’m going to show you a demo of the one I showed you just a minute ago in slides This is just creating a new Application object It’s a great script to run when you want to just make sure that all of your mechanisms are working So I’ve got here my script in Visual Studio Code– VSCode– and then I’ve got a terminal open underneath It’s just a normal command terminal for Windows And so I’m going to go ahead and just call cscript.exe And this is in my Windows directory under SysWOW64 because, confusingly enough, that’s where the 32-bit version of cscript is, and the version of SAS Enterprise Guide that I’m automating is a 32-bit version So this is important to remember The version of cscript or wscript that you’re using to automate has to match in bitness to the version of Enterprise Guide that you’re using So if you’re using 64-bit Enterprise Guide, that’s fine Just make sure you use the 64-bit version of the scripting runtime that you’re using to automate So I’m just going to automate this NewApp.vbs So I’m going to run that And you see it comes back, lickety-split, Enterprise Guide, Version 8.2 That is the version that I am running Great So let’s clear that Let’s move onto the next script It’s a little more complex I’m going to use this script to actually create a new project in Enterprise Guide, add a program, just by adding in a new code object, and then run that program So we do that by adding to the CodeCollection in the project Setting the server– in this case, I’m just going to use a local server First, I’m setting the profile The profile is the metadata profile that I’m connected to And there’s a special value here for the metadata profile called Null Provider, which means, don’t connect to a profile and I’m just using my local SAS for this work The program I’m running is this is just a simple PROC OPTIONS, and then after the program runs, I’m going to save it to my local current directory in this file called testProgram.log And then this little function here is just a helper to get the current directory for where my script is running So let’s run this one And when that comes back, you see over on the left, in my list of files here, a testProgram.log was created And let’s just throw that up here, and you can see the contents of it It looks like a SAS log, so perfect It did its job Let’s move on to the next example In this one, I’m going to just use the automation object to show the list of available profiles So we mentioned the special Null Provider value, which says,

don’t use a profile But you might have one or more profiles that you use in your enterprise to connect to different SAS environments So let’s see what I’ve got set up This automation step will show me just a list of available profiles and some of their details And that runs pretty quick And you can see here, here’s the metadata profiles I have And each one has a set of details, which in the video, I’m going to blur out because it contains some secrets so I don’t want to reveal some of those addresses OK Clear this And then finally, let’s move on to just running a project in batch So in this example, I’m going to, once again, launch Enterprise Guide automation, setting the active profile to one of my other SAS profiles that connects to a SAS App server And then I’m going to add a new program to my new project And then I’m going to run this program here Now, I’ve just coded the program here in text right inside my script In reality, you might have the program sitting on disk somewhere, and you could use VB Script to just read in the file and assign it to the sasProgram value here In this case, I am just creating a simple DATA step with a bit of a subset, and then running PROC MEANS on that, and then I’m going to save out the log output And then if this project creates output data sets, which I think it will, then I’m going to use this Enterprise Guide OutputDatasets object to save that data set as an Excel file So I’m basically automating the export to Excel within my script The program in SAS is creating a data set, but Enterprise Guide is going to save it as an Excel file And then I’m going to also save it the ODS output, which in this case is going to be a listing output So let’s run that And as you see, these results come back We have a log again Just throw that up in here and see That looks pretty good We have a listing file this time, so let’s throw this in here And it looks like it’s the output of our PROC MEANS and listing format, so perfect And we have an Excel file So let’s just double-click and open that in Excel And there is my data that was created up there on the server So all of that automated It can be run unattended Perfect And that’s it for this tutorial, but there is much more that you can learn Here are some resources you can use to learn more and ask questions, if needed Of course, please visit That is a great place to ask questions of your fellow SAS users And there is an active Enterprise Guide board there, lots and lots of users who are willing and eager to answer your questions Also, I’m there, too, so I am happy to jump in and answer questions I have published a lot of articles already about what I know about SAS Enterprise Guide at my blog at You can just search for SAS Enterprise Guide there, and you’ll find lots of articles to continue your learning We have webinars They can be found in the Ask the Experts section in the communities, as well Just look for Enterprise Guide in there This channel, of course–– there’s lots of Enterprise Guide videos some of which I’ve recorded So please check those out You can find me on Twitter @cjdinger, send me email, contact me on LinkedIn, or message me on communities Please, I want to be able to help you I want to hear from you Leave me comments and continue your learning, and happy Enterprise Guide-ing