Demo: Exporting results to Excel - SAS Tutorial

- [Instructor] Now let's export the report to Excel. The ODS Excel destination provides an enormous amount of flexibility. So there are many options available to control the output. We'll try just a few here. In the ODS statement you can specify a style for the output by using the STYLE equal option, and specifying one of the many different styles that are built into SAS. You can list additional options in the ODS statement by using the OPTIONS keyword and enclosing option-value pairs in parentheses. The SHEET_NAME option customizes the tab names in the workbook. In this example we'll use the ODS Excel destination to export reports to multiple worksheets in an Excel workbook. We're starting with a program that already creates some output for us. You'll notice there's proc means step, and I've also added a procedure called sgplot to create a graph for us. Let's run it to see what we start with. Here's our summary statistics. And then a nice histogram of the distribution of maximum wind. So that looks great in SAS but I would like to send these results to Excel. So in the program I'm going to use the output delivery system to send these results to the Excel destination. Before the title statement I'll add ods excel, and I'll use the file equal option to specify the file I want to write to. Again we'll use ampersand outpath to substitute the path for our output folder. And then the file will be wind.xlsx. That's the first part of our ODS sandwich. So at the end of the program I'll add the concluding ODS statement, ods excel and close. I'll run the program. And I'll take a quick look at the log. You'll see the note that the Excel file was created. And if I look in the output folder, there's wind.xlsx. I'll download the file and open it. So we have a separate worksheet for each of those steps. The means or summary statistics report as well as the graph. And notice the light blue background in the results that are generated by this default style. Remember, a style includes all of the colors and fonts associated with the display. What if I'd like to choose a different style? Or, what if I'd like to customize the labels associated with these worksheets? I can do that in my program. I'll close Excel, and back in the CODE tab I'll start by adding a little step to examine the available styles. There's a procedure called template. This procedure will allow us to view the different styles that are available. I'll use the list styles statement and run. If I run this step, it gives me a listing of all of the styles that are available in my environment. You'll have to experiment with these to see if there are any that you prefer for your output. For our demonstration, we'll try the SasDocPrinter style. Back in my program, on the ods excel statement I'll add the style equal option and type sasdocprinter. Now remember I also wanted to customize the worksheet names. I can do that continuing on the ods statement by using options. I type options and in parentheses I can take advantage of the sheet_name equal option. And I want the sheet name for the step that follows to be Wind Stats. That string will go in quotes. So that's my first ods excel statement. Now I would like a different label for the worksheet generated by the proc sgplot step. So before the title statement in proc sgplot I'm going to add another ods excel statement. And again I'll use options and take advantage of sheet_name equal, and this time the sheet name will be Wind Distribution. And I'll close that statement. So with those modifications I'll highlight everything in my ODS sandwich, from the first ods statement down to ods close, and run the program. Once again I'll download the wind.xlsx file. Notice the blue background is no longer there. And also notice our worksheet names, Wind Stats and Wind Distribution. We're just scratching the surface of the options that are available in ODS to customize your output. But this is a great start.