Demo Steps for SAS Enterprise Guide 1: Querying and Reporting

SAS Enterprise Guide 1: Querying and Reporting
Lesson 02, Section 1 Demo: Creating a Project and Exploring the Workspace

Let's take a tour through the Enterprise Guide workspace and run through the typical workflow.

1. Start SAS Enterprise Guide. When you first start SAS Enterprise Guide, the welcome window opens. In this window, you can open an existing project or start a new project. This window also has a link to the Getting Started tutorial. If you decide to disable this window, you start with a new project each time you open SAS Enterprise Guide.
   
   
2. Click New Project. When SAS Enterprise Guide is open, you can use the File menu to create a new project or open an existing project. Just keep in mind you can have only one project open at a time. The default window layout for this version of SAS Enterprise Guide includes the Project Tree, the Process Flow, and the Server List. This area on the right side of SAS Enterprise Guide is called the workspace.
   
   To start with, the Process Flow window is open in the workspace. Later, data and task results will be viewed in this same area. As you work in SAS Enterprise Guide, a hierarchical view of your work is displayed in the Project Tree and a flow diagram is displayed in the Process Flow window.
   
   The Server list is currently open in the Resources pane. The Server List displays the SAS servers that are configured to work with your installation of SAS Enterprise Guide. In the Resources pane, you can click these different icons to switch between the Task List, Folders List, Server List, Prompt Manager, and Data Exploration History. We'll talk more about these resources later in the course.
   
   
3. By default, the Project Tree and Resources pane are docked on the left side of the main window. Click the down-arrow to dock the window on the left side or to auto-hide it. Auto-hide hides the window, but displays the window name on the border of the Enterprise Guide window. Place the cursor over the window name to make the window visible.
   
   
4. Let's add an existing SAS data set by selecting File > Open > Data. You can also click the Open tool on the toolbar and select Data.
   
   
5. To navigate to data stored on your personal computer, select My Computer. Navigate to the location where you saved your data for the course and double-click customers to add the SAS data set to the project. A shortcut to the customers data set is added to the project tree and the process flow. By default, the data appears in the data grid in the workspace.
   
   
6. After a data source is added to the project, you can use it for analysis and reporting. With the customers data set open in the data grid, select Describe > Characterize Data. The Characterize Data task is in a wizard format that enables you to go through each of the three steps and modify any options. Step 1 enables you to verify the data source that you want to analyze. Select Next.
   
   
7. If you want more information about a particular task, you can open the Enterprise Guide Help by clicking the Help button in the task window. This opens help for the task you are using.
   
   
8. In Step 2 of the task, you can customize the report options, including producing a summary report, graph, or output data set. To change the name of the data set that includes frequency counts for any character columns, select Browse in the Frequency Data pane. Type CustomerCounts in the File name field and select Save > Next.
   
   
9. In Step 3, limit the number of unique categorical values to be reported per variable by typing 15.
   
   
10. Select Finish to run the task and view the results. To observe the status of the task as it runs, select Details in the lower left corner of Enterprise Guide. The Task Status window appears, and indicates the processing task, status, and server. The output includes frequency counts for character columns, summary statistics for numeric columns, and basic graphs to characterize each.
    
    
11. To view the contents of the current project, you can click Process Flow on the toolbar, double-click Process Flow in the Project Tree, or press the F4 key. The Characterize Data task was added in the project tree and process flow, and linked to the customers data set.
    
    
12. Return to the task results by double-clicking the Characterize Data icon in either the project tree or process flow. To view the task code, click the Code tab.
    
    
13. The Log window and summary display messages from SAS for each task that you execute. To view the log, click the Log tab.
    
    
14. This task produces two data sets. The first data set that was created is displayed automatically. Click the Output Data (2) tab to view the data set that contains frequency counts. The other data set containing summary statistics can be accessed either from the drop-down list on the Output Data tab or from the process flow.
    
    
15. The output data can be exported easily to a wide variety of other software formats, including Microsoft Excel. To open the data in Excel automatically, select Send To > Microsoft Excel. Close Excel and do not save the changes.
    
    
16. To organize your project, you can create multiple process flows. In this project we'll create a process flow for each lesson. In the Project Tree, right-click Process Flow and select Rename. Type Lesson 1. Select File > New > Process Flow. You can also use the New icon on the toolbar to create new projects, data, and other files.
    
    
17. Finally, let's save this project. From the File menu, select Save Project As. You have the option of saving the project to either your local machine or to a server. Let's name this project EG171_Demos. We'll use this project for demonstrations throughout this course. When the project is saved, you'll see the project name in the window title bar. Projects can be saved on a Windows machine or on a remote server where SAS is running.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 03, Section 2 Demo: Assigning a SAS Library and Accessing Data

In this demonstration we'll assign a library and add a data set to the project from that library.

1. From the Tools menu, select Assign Project Library.
   Note: Your SAS administrator is typically responsible for setting up libraries for you to access on the SAS server. However, if you want to define a library to SAS within your project or to DBMS tables to which you have Read access, you can use the Assign Project Library task.
   
   
2. In step 1, you name the library. As you can see, library names can only have eight characters. Let's name the library ORION.  The Servers drop-down menu has a list of servers that are configured to communicate with SAS Enterprise Guide. You select the server where the data you want to access is located.
   
   
3. In step 2, you supply the connection information for the type of data you want to access. First you select an engine type, and then you select an engine to access a specific type of data. For example, if you are creating a library to access SAS data sets, you can use the default settings: the File System engine type and the Base engine. If you are creating a library to read Oracle data, you select Database System for the engine type and then you select the engine to read Oracle data. Then you supply the required connection information, such as the database name, schema, and log in information. We're accessing SAS data sets, so we supply a path or browse to the location of the SAS data sets on the server. Keep in mind that the path to the data is relative to the location of SAS, not to the location of Enterprise Guide. This means that if SAS is located on your local computer, you can specify a path to a folder on your local computer. If SAS is located on a remote server, the path must to be to a directory on the remote server where SAS is located.
   
   
4. In step 3, you specify options. We're going to limit access to Read-only, so we'll type access in the Name field and readonly in the Value field. This ensures that even if you have Write access to the data at the operating system or database level, SAS will prevent changes to the data sources in the library.
   
   
5. In the last step, you verify the settings and click Test Library. You should see OK. Finally, click Finish to define the library in your project.
   
   
6. Now, let's look for the library we created and open a data source from the library. Like before, let's click the Open tool and select Data. This time on the left side of the window, click Servers. Double-click the active server, and then double-click Libraries. Now double-click the Orion library we just assigned. Open orders and to add it to the project.
   
   
7. Let's rename the process flow we are working in as Lesson 2.
   
   
8. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 03, Section 3 Demo: Importing Data from a Microsoft Excel Spreadsheet

Let's learn how to use the Import Data wizard to import a Microsoft Excel spreadsheet into a project.

1. Open the EG171_Demos project if it is not already open.
   
   
2. We'll start by clicking Open > Data. The Open Data window automatically opens to the last location where you added data. We want to open Products.xlsx.
   When you open an Excel file, the Import Data Wizard automatically opens. The Import Data Wizard consists of four steps. The options in some steps vary by the type of file you are importing, so we are going to see options that are particular to Microsoft Excel files.
   
   
3. In Step 1 we confirm the data source and designate the output SAS data set name and storage location. Here, the input data is the Products Excel file. The output SAS data set is named Products and it will be stored in the Work library. The default library for output data might be different in your environment. Click Next.
   
   
4. In the second step, you choose the data that you want SAS to read and specify some options for reading the data. When you are importing a Microsoft Excel data source, you can specify whether to read an entire spreadsheet, a specific range of cells, or a named range. Here, the worksheet ProductList is selected and that's the one we want. You also specify whether the first row of data contains column names and whether the columns should be renamed to comply with the variable name rules that you learned earlier in this lesson. Select Rename columns to comply with SAS naming conventions and leave First row of range contains field names selected. Note: The Rename columns to comply with SAS naming conventions option truncates names to 32 characters and replaces spaces and special symbols with an underscore. Click Next.
   
   
5. The third step in the wizard is to specify column attributes for the columns that you want to include in the SAS data set. Change the name of the first row to Product_ID and the label to Product ID. In this step, you can also select the columns that you want to import. By default all columns are selected, but you can clear the Include check box beside any column that you do not want in the output data set. You know that columns in a SAS data set must have a name, type, and length. Notice that in the Type column, some columns have the type Number and some have the type String. These correspond to Numeric and Character types. As you can see, Enterprise Guide makes some decisions about the attributes of the data that you are importing, but you can edit these attributes by clicking the attribute you want to change.
   
   Change the type for Supplier_ID to String. You are prompted to choose a method for determining the length of the character variable. Verify that Scan all values in the column is selected and click OK > Next. The length of a character column corresponds to the maximum number of characters that can be stored in a single data value. Click Next.
   
   
6. In Step 4 of the Import Data wizard, you can set some advanced options. You can learn more about these options in the Enterprise Guide Help. We don't need to select any of these, so we'll click Finish to create the SAS data set.
   
   
7. The new SAS data set opens in the data grid. Now the Products data set can be used in analyses and reports. If the underlying Excel data changes, you can click the Refresh icon to run the import again and update the output data. You can also click Modify Task to change the options that you specified in the wizard, and then run the import data task again.
   
   
8. The task and the new SAS data set are accessible by double-clicking the Import Data icon in the project. You can rename the Import Data task to emphasize the name of the SAS data set that is created in the import process. Right-click the Import Data icon in the project tree or process flow and select Rename. Type Import Products.
   
   
9. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 03, Section 3 Demo: Importing Data from a Text File

This time, we'll import data from a text file to create a SAS data set. Orion Star wants to create this data set with a few customizations. They want to drop Employee_ID, format the values for Discount with a percent format, and format the Profit values as currency. We can do all this when we generate the data set.

1. Select Open > Data and add the orders text file to the Demo project.
   
   
2. The text file opens in the workspace. Let's take a look at the file. Although it looks like data in columns with column headings, to your computer this is one long text string. You'll tell SAS how to structure the data in the Import Data Wizard.
   
   
3. Let's import the data. Right-click the orders.txt icon and select Import Data.
   
   
4. In step 1 of the Import Data Wizard, we see that the output data set will be named orders and it will be stored in the Work library. Click Next.
   
   
5. This text file has data in fixed columns, so we select Fixed columns. Select the option to specify that field names are in the first row. Now we need to tell SAS how to find the data for each column. Click at the beginning of each column to mark the column locations. If you accidentally set a column location in the wrong location, you can drag it to a new location or just double-click the ruler to remove it. You do not need to place a column break before the first field. Click Next.
   
   
6. In this step we can modify the properties of the columns. As you can see, the Import Data Wizard has already made some good guesses about the type of data in the columns, but we can make any changes we want to make here.
   
   
7. Clear the check box next to Employee_ID so that it won't be included in the data set.
   
   
8. For the Discount column, single-click twice in the Output Format box. Click the ellipsis button. In the Formats box, select PERCENTw.d format and change the overall width to 5.
   
   
9. We can format Profit as a currency value by changing the drop-down list in the Type column to Currency. That's all we need to do in this step. Click Next.
   
   
10. In step 4 we don't need to make any changes, so we're ready to import the data.
    
    
11. The orders data set opens in the data grid. Let's have a look at the Discount and Profit columns and make sure they're formatted correctly.
    
    
12. Let's save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 04, Section 2 Demo: Generating a One-Way Frequency Report

Orion management wants a report that summarizes the number of products offered for each category and the percentage each category contributes to the overall total. We'll use the One-Way Frequencies task to generate the report.

1. In the EG171_Demos project, create a new process flow and name it Lesson 3.
   
   
2. Add the Products SAS data set from the location of the course data.
   
   
3. This time let's use the menus at the top of the data grid to open the task. From the Describe menu, select One-Way Frequencies.
   
   
4. We want to analyze the number of products in each product category, so drag Product_Category from the Variables to assign pane and drop it on the Analysis variables role in the Task roles pane. That's all we need to do on the Data page.
   
   
5. Select Statistics. The Statistics page enables you to select the statistics that you want in the report. Suppose we want the report to show frequencies and percentages. Let's select that option.
   
   
6. Select Plots. Let's take a look at Plots. The Plots page enables you to generate horizontal or vertical bar charts.
   
   
7. Select Results. Select the Create data set with frequencies and percentages check box. Click Browse and name the data set ProductCounts. You can also scroll down in the Results page and specify how you want to order the output data. For example, you could sort your output data by descending frequencies.
   
   
8. Click Titles and add a custom title to the report. First, clear the Use default text check box. Delete the default title text and type Number of Products per Category.
   
   
9. Finally, let's select Properties and give the task a more descriptive name in the project. Click Edit. Let's name the task Products per Category and click OK.
   
   
10. Click Run to generate the report and examine the results.
    
    
11. Here's our report. Looking at the report, which product category has the most products? Clothes has the greatest number of products. The 108 products in the Clothes category make up 22.45% of all products offered by Orion Star. Let's look at the output data. Because we chose to create a data set, the same information that is in the report is stored in the data. Let's go back to our report for Orion Star. Notice that the report title includes The FREQ Procedure. Some procedures include the name of the SAS procedure in the title by default. You can change this setting in the Options window.
    
    
12. Select Tools > Options. In the Tasks General category, clear the Include SAS procedure titles in results check box. Notice that there is also an option in this category to change or delete the default footnote for all tasks. Let's close the Options window.
    
    
13. Now let's rerun the task by clicking Refresh. The new report does not include the SAS procedure name in the title.
    
    
14. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 04, Section 3 Demo: Modifying Result Formats for a Task

In this demonstration we'll modify the properties of a task to produce HTML, PDF, RTF, or text output.

1. In the Lesson 3 process flow, double-click the Products per Category task to reopen it.
   
   
2. We want to make changes to the options we set in the task, so let's click Modify Task. Select Properties, and then click Edit to open the Properties for the task.
   
   
3. In the Properties window, select Results, and then select Customize result formats, styles, and behavior. Let's select every result format so that we can compare them.
   
   
4. For HTML results, click the Style menu and select a different style.
   
   
5. Click OK.
   
   
6. Now let's click Run to generate the report in all output formats. Click Yes to replace the results from last time. Notice that each result format opens in its own tab.
   
   
7. The SAS Report, HTML, and text results can all be viewed directly on the Results tabs. Clicking the PDF or RTF tab opens the results outside of Enterprise Guide.
   
   
8. Let's save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 04, Section 4 Demo: Using the List Data Task to Create a Report

Let's learn how to use the List Data task to create a report. In this demonstration, we'll use the List Data task to print a list of Orion Star customers that includes ID, name, and age group. We'll generate a separate report for each country.

1. Open the EG171_Demos project if it isn't already open. In the Lesson 3 process flow, add the customers data set from the course data. With the data open in the data grid, select Describe > List Data.
   
   
2. Let's assign Customer_ID, Customer_Name, and Customer_Age_Group to the List variables role. Remember that the columns will be printed in the report in the order they are listed in this role.
   You can reorder the columns by dragging and dropping or by using the arrow keys. We want to create a separate report for each country, so let's assign Customer_Country to the Group analysis by role. Notice that the data will be sorted in ascending order for the Customer_Country variable.
   
   
3. Select Titles. Let's also specify a custom title for our report. We can delete the default title and type Orion Star Customer List.
   
   
4. Click Run. This looks like a good start -- we have a report for each country and we have the columns we wanted. However, it could be better -- let's modify the column labels and remove the row number column. Do you remember how to make changes to a task you've run?
   
   
5. Click Modify Task to reopen the task. Right-click Customer_Name in the task roles list and select Properties. In the Properties window, change Customer Name to Name, and then click OK. Let's also change Customer Age Group to Age Group.
   
   
6. The Identifying label role can be used to replace the Row number column with a column from the data. Drag Customer_ID from the List variables role to the Identifying label role. Note: To remove the row number without assigning a column to the Identifying label role, select Options in the selection pane and clear the Print the row number check box.
   
   
7. By default, the task icon in the project is labeled List Data. Give the task a more descriptive label so that it is easily identified in the project. Select Properties > Edit and type Customer List by Country in the Label field. Click OK.
   
   
8. Click Run and click Yes to replace the last results. This looks like the report that Orion Star management wanted.
   
   
9. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 04, Section 5 Demo: Filtering Data in a Task

Let's learn how to filter data in a task. We want to create a list of customers who were born in 1970 or later and who are high-activity Orion Club members. We'll use the customers data set.

1. Open the EG171_Demos project if it is not already open.
   
   
2. In the Lesson 3 process flow, open the customers data. We have a Customer_BirthDate column and a Customer_Type column that we can use to filter the data. Notice that there are two values that meet the condition of high-activity Orion Club members.
   
   
3. Select Describe > List Data.
   
   
4. On the Data page, click Edit to create a filter. From the first menu, select the variable Customer_Type. The value must be one of two possible values, so one way to write the expression is to select In a list from the second menu. Next we add the values for the expression. You could type them here, but it's easier and more accurate to select them from the unique values of the variable, so click Add Values. Hold down the Ctrl key and select the two values that indicate high activity and click OK. Then click OK to close the values window.
   
   
5. The rows have to meet two conditions, so we add the AND operator to the expression so that both filter conditions must be true for a row to be included in the report. Select Customer_BirthDate in the first box and Greater than or equal to in the second box. Now we select values. Remember that we want 1970 or later. Notice that the values are SAS dates, which are stored as the number of days since 01JAN1960. The formatted values also display in this window. This time the values in the data aren't going to help us unless someone happens to be born on Jan 1, 1970, so let's close this window and use a SAS date constant here.
   Type '01JAN1970'd. You must use this form to reference a SAS date constant in an expression. SAS converts the date enclosed in quotation marks to the SAS date equivalent before filtering the data. That's it for our expression that filters the data. Only rows that meet these two criteria will be used as input to the List data task.
   
   
6. Assign Customer_Name and Customer_Type to the List variables role and Customer_ID to the Identifying label role.
   
   
7. Select Titles and add a custom title. We'll use a two-line title with Orion Star Customers List on the first line and High Activity Customers Born in 1970 or Later on the second line.
   
   
8. Rename the task to indicate the filter that was applied. Select Properties > Edit. Type High Act/1970+ in the Label field. Click OK.
   
   
9. Click Run. Our report shows that only eight customers were born in 1970 or later and are Orion Club members with high activity.
   
   
10. Let's add a note to go with this task. Highlight the task and click the New icon, and then select Note. Let's use this note to explain the type of information we used to filter the data. Type This report includes customers in high activity groups born in 1970 or later. The note is automatically saved when you close it.
    
    
11. Rename the note by clicking the note label. Let's name it Filter Detail.
    
    
12. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 04, Section 6 Demo: Creating a Bar Chart Using the Bar Chart Wizard

To better understand the demographics of the Orion Star customers, management wants to see a comparison of average customer age by country. In this demonstration, we'll use the Bar Chart Wizard and task to create the chart.

1. Open the EG171_Demos project if it isn't already open.
   
   
2. In the Lesson 3 process flow, open or activate the customers data.
   
   
3. To open the Bar Chart Wizard, select Tasks > Graph > Bar Chart Wizard.
   
   
4. The first step in the wizard is to verify the data. Notice that you can also apply a filter in this step if you want. customers is the input data, so click Next.
   
   
5. In step 2, you assign variables to roles. You must specify a variable for the bars and a variable or statistic that will determine the height of the bars. Let's select a Horizontal bar chart. Remember that we want to create a separate bar for each country, so we'll select Customer_Country in the Bars drop-down list. You can use the Properties button to change the way the bars are sorted. Let's select Descending bar height and click OK. The length of the bars should be based on average age, so we select Customer_Age from the Bar length drop-down list. You can use the Statistics button to select Average or Sum. Let's change the statistic to Average. As you select options for the graph, watch the preview area to get an idea what your graph will look like. Click Next.
   
   
6. In step 3, you can customize the appearance. First, let's select 3D chart. Notice that the preview changes with the options we select. Next, let's change the Color bars by drop-down list to Bar category so that the bars have different colors. Select the Data labels check box and select Average from the drop-down list. Select the Use reference lines check box. Click Next.
   
   
7. In the last step, you can specify titles and footnotes. Change the title to Average Customer Age by Country.
   
   
8. Now we're ready to generate the bar chart. Click Finish. You can easily see that Australia has the highest average customer age and South Africa has the lowest average customer age.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 04, Section 6 Demo: Modifying the Bar Chart in Advanced View

After you've used a wizard to generate a report, you can open the task in Advanced View to access more options. Let's make some changes to the bar chart we created by using the wizard. We can apply formats to variables, change the shape of the bars, and make changes to the axes and reference lines. To do this, we'll open the task in Advanced View.

1. To open the task in Advanced View, right-click the task icon in the Project Tree and select Open > Open in Advanced View from the menu.
   
   
2. The task window opens and Horizontal bar chart is selected on the Graph page.
   
   
3. Select Data. The variables that you selected in the wizard are assigned to task roles. To format Customer_Age so that the data labels are rounded to one decimal place, right-click Customer_Age and select Properties. Select Change to apply a format. In the Formats window, select Numeric from the Categories pane and w.d from the Formats pane. Change the overall width to 4 and the decimal places to 1. Click OK > OK to return to the task window.
   
   
4. Now, let's change the bars to cylinders. Select Layout and select Cylinder from the shape menu.
   
   
5. Let's also make some changes to the horizontal axis. First, let's add our own label. On the Axis page, type Average Customer Age and change the font size to 12.
   
   
6. Select Reference Lines in the selection pane. Click the Specify values for lines check box, type 20, and then select Add. Repeat for 40 and 60. Change the style to Dashed and the color to light gray.
   
   
7. Select Vertical Axis in the selection pane. Type Country in the Label box.
   
   
8. Select Properties. Name the task Avg Age/Country. The default graph format is ActiveX. To be able to interact with the graph after the task runs, it must be in HTML format. In the Results pane, select Customize result formats, styles, and behavior and select HTML. Click OK.
   
   
9. Click Run and replace the results.
   
   
10. On the HTML results tab, let's take a look at some of the things we can do to further explore and modify the ActiveX graph. Right-click the graph and select Graph Properties. On the Graph tab, change the style to Curve and a new color scheme is applied.
    
    
11. Click the Bar tab and select Font. Increase the data label font size to 10. Click OK > OK to return to the graph and apply the changes.
    
    
12. Another change that can be made is to switch the chart type. Right-click the graph and select Chart Type > Vertical Bar.
    
    
13. A toolbar is also available to resize, rotate, and subset the graph. Right-click the graph and select Graph Toolbar. Click and use the mouse to drag the cursor across the top three bars. The chart is redrawn to include only the outlined portion. Click to reset the graph.
    
    
14. To save the graph, you can save the image as a JPEG file (saves any interactive changes that you made as a static image) or you can export the graph as an HTML file. (Interactivity is preserved but changes that you made interactively are lost.) To export the HTML result, select Export > Export HTML - Avg Age/Country. Navigate to the desired location and select Save.
    
    
15. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 05, Section 2 Demo: Using the Filter and Sort Task

Orion Star wants to analyze Internet sales that occurred since the beginning of 2013. To prepare the data for input to the various analytic tasks, the company must generate a new subset from the orders table. This query can be created with either the Filter and Sort task or the Query Builder. In this demonstration, we'll use the Filter and Sort task.

1. First we'll create a new process flow and name it Lesson 4. Then we'll add the orders SAS data set.
   
   
2. You'll notice that in the top of the data grid there is the Filter and Sort button. You can also open it by right-clicking the orders table in either the project tree or process flow and selecting Filter and Sort, or it's also available from the Tasks menu under the Data submenu.
   
   
3. In the Filter and Sort task, the Variables tab enables you to select columns from the input data set that you want in your output data set. On the Variables tab, click the double arrow to add all columns. All Internet orders have the Employee_ID column equal to 99999999, so it can be removed from the result. Highlight Employee_ID in the Selected pane and click the minus arrow.
   
   
4. Now we're ready to define a filter, so let's click the Filter tab. Here we can build simple conditions using variables names, operators, and data values. This should look familiar because it's similar to the window we used to create a filter in a task. You can filter by any variable in the input data, even if it is not selected in the Variables tab. There will be two conditions in this filter. Let's start by selecting Internet orders. These orders have Order_Type equal to 3, so we select Order_Type, Equal to from the second drop-down menu and I can either type the value here or reference the data values. By selecting the ellipsis button I see the values of the Order_Type column. Select 3 and click OK to finish the condition.
   
   
5. Next we need to add another condition. We select AND because both conditions must be true for the row to be included in the output data. We need orders that were placed in 2013 or later, so we select Order_Date, and then Greater than or equal to. This time instead of selecting a value from the Order_Date column, we want the values compared to the SAS date constant '01JAN2013'd. SAS date constants are written in a particular way as you can see here. Now the Filter expression is complete.
   
   
6. On the Filter tab, notice that you can also open the Advanced Filter Builder window to create more complex rules for extracting rows. This window enables you to build your expressions using constant values, columns, mathematical operators, and functions. You can use the Advanced Filter Builder to add these elements, or you can type them into the Expression box at the top of the Advanced Expression Editor.
   
   
7. Next, let's sort the data. On the Sort tab, you can sort by multiple variables and specify whether you want to sort the variable in ascending or descending order. The default is ascending order for each variable. If you sort by more than one variable, the data is sorted for the first variable listed, and then the second, and so on. We'll select Order_Date as the sort variable and change the sort direction to Descending. Our data will have the most recent orders listed first in the output data.
   
   On the Results tab, type Internet Orders 2013+ in the Task name field. Click Change and type internetorders2013 in the File name field. Select Save.
   
   
8. Click OK and verify the results.
   
   
9. Let's take a look at the Process Flow window. You can use this output data set an input for other tasks.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 05, Section 2 Demo: Using the Query Builder

Let's learn how to select columns and filter rows by using the Query Builder.

1. In the Lesson 4 process flow, double-click the orders data and click Query Builder at the top of the data grid. Note: You can also right-click orders in the project and select Query Builder.
   
   
2. The Query Builder has tabs for selecting columns, filtering rows, and sorting data. You can use the input boxes at the top to name the task and specify a name a location for the output data set.
   
   
3. Let's name the query Internet Orders 2013+ Query and let's also name the output data. To do that, click Change and type internet2013query in the File name box. Here, the output data will be created in the Work library because that's the default library for this SAS Enterprise Guide configuration. Yours might be different. Click Save.
   
   
4. Now we need to select the columns for the output data. The Select Data tab is similar to the Variables tab in the Filter and Sort task. You drag and drop columns from the data input list to the Select Data tab to include them in the output data set. Notice that columns are listed under the table icon. You might be wondering why this data is labeled t1 (orders). t1 stands for table1 and it's an alias that the SQL code uses for the orders data behind the scenes. You can see the name of the table in parentheses beside the alias.
   
   Select all the columns in the data by selecting the table icon and dragging it to the Select Data tab. To take Employee_ID out of the list, we can either drag it out of the tab or click the Delete button. You can use the arrow buttons to arrange the columns in the order that you want them. Let's look at the list of columns. t1 precedes each column name. That tells you that the column comes from table1. This is not that important when you only have one table in the query, but when you have multiple tables in the query, it helps you keep track of which table each column comes from.
   
   
5. Remember that one of the things you can do in the Query Builder is change the column properties. Let's do that. We want to change how the values in the Order_Date column are displayed, so let's select the column and click the Properties button to open the Properties window. Do you remember how to change the way values of this variable are displayed? You do that by applying a format.
   Click Change to get started. We want to apply a format from the Date category. Let's select the MMDDYYw.d format. You can see here what the values will look like when the format is applied with a width of 8. Let's change the width to 10. The preview changes to show you how the date will be formatted with a width of 10. Let's apply this format. We can scroll over in the Select Data tab to see the format we applied.
   
   
6. Now we're ready to create the filter. On the Filter Data tab, there are a couple of different ways to get started. You can click the New Filter button and then select Basic filter, or you can start by dragging the column you want to filter to the tab. Let's drag Order_Type to the Filter Data tab. This automatically opens the Basic Filter Wizard.
   
   
7. In step 1 we want to make sure that the comparison operator is equal to. In the Value box, we type 3. Let's see what happens if we click the drop-down arrow here. Using this window, you can get values from the variable to use for the comparison, or select another variable to compare, or select a prompt you've defined to compare. Order_Type = 3 is what we want, so let's click Finish to add the condition to the Filter Data tab.
   
   
8. Now we can build our next condition. This time, drag Order_Date to the Filter Data tab. For the operator, select greater than or equal to. Then, we type the SAS date constant "01JAN2013"d as we did before. Click Finish to add the condition. By default, the AND operator connects the two conditions, but you can change it.
   
   
9. Let's sort the data. On the Sort Data tab, we drag in the Order_Date column. Then we can change the sort direction to Descending.
   
   
10. The Query Builder also has a preview button. Here, you can preview the SQL code that the Query Builder is generating, and you can also preview the results. Notice that you can save a query to run later, or you can run the query immediately. Let's click Run. This data looks the same, except that you can see the different format we applied to the Order_Date column. You've learned how to use the Query Builder to filter and sort data.
    
    
11. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 05, Section 3 Demo: Creating a Column with an Expression

Let's learn how to create a new column by using an expression. Orion Star would like to analyze shipment methods by determining how many days passed between each order date and delivery date. The company also wants to calculate the total amount invoiced to the customer, which is the sum of the total retail price and shipping charges. In this demonstration, we'll use the Query Builder to create a table named shipping that has the new computed columns.

1. We already have the orders table in the Lesson 4 process flow, so we'll just right-click the data icon and select Query Builder.
   
   
2. Let's name this query Shipping Detail Query and let's name the output data set shipping. You can type the data set name directly in the Output name box, but you must be careful to type it after the dot that follows the library name or the query will not run.
   
   
3. For our output data set, we want to select Order_Date, Delivery_Date, Order_ID, Product_ID, Total_Retail_Price, Shipping, and Profit. Now we can drag these to the Select Data tab. Let's move Order_ID and Product_ID to the top.
   
   
4. You can add a computed column by opening the Computed Columns window and clicking New, or you can click the Add a New Computed Column button in the Select Data tab.
   
   
5. We'll use an expression to create the computed column, so we select Advanced expression, and then click Next.
   
   
6. Now we build the expression. To get the number of days between order and delivery, we need to subtract Order_Date from Delivery_Date. You can select columns in the data table through the link labeled t1(orders), or you can reference the columns that have been added to the Select Data tab. In this case, the columns are in the query, so we'll expand Selected Columns. Now double-click Delivery_Date, click the subtraction operator, and then double-click Order_Date. That completes the expression, so let's go to the next step.
   
   
7. Here, you can specify a column name, label, and length. The column name will be used as the column heading in reports. If you don't specify anything here, the column has a default name and the other attributes are assigned based on default settings or on the arguments. Type Days_to_Deliver in the Column name box and Days to Deliver in the Label box. Click Next.
   
   
8. In Step 4, review the properties and then click Finish. After you create a computed column, it appears in the left pane of the Query Builder under the Computed Columns category. Notice that computed columns are shown with a special icon. The new column is automatically added to the Select Data tab.
   
   
9. Now let's create a column for the total amount invoiced to the customer. The total amount is the sum of the retail price and shipping. Let's open the New Computed Column Wizard again. Notice that after you have one computed column, you now have the option of using that computed column to create another one. Select Advanced Expression and click Next.
   
   
10. This time, we're going to use a function in our expression, so open the Functions folder, scroll down to find the SUM function, and double-click it to add it to expression. Now let's insert the columns we are going to use as the function arguments. We can either type the columns in or retrieve them from the list. An easy way to get back to the list of columns is to click the Favorites button and select Tables. Then expand Selected Columns and double-click Total_Retail_Price, click or type a comma, and then double-click Shipping.
    
    
11. In Step 3, type Invoice_Amt in the Column Name field. Type Invoice Amount in the Label field. To display the data values as currency, click Change next to the Format field. In the Format window, select Currency in the Categories pane and DOLLARw.d in the Formats pane. Change the overall width to 8 and the decimal places to 2. Click OK > Next.
    
    
12. Verify the summary of the properties and click Finish. Both new columns are added to the selection pane and the Select Data tab.
    
    
13. To emphasize the orders with the longest delivery time, sort the output data by Days to Deliver in descending order. Click the Sort Data tab, and drag and drop the Days_to_Deliver column onto the tab area. Change the sort direction to Descending.
    
    
14. Let's run the query and view the output. Scrolling over to the right, we see the calculated columns. This is the data Orion Star requested.
    
    
15. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 05, Section 4 Demo: Summarizing and Filtering by Groups

Let's learn how to group, summarize, and filter grouped data. Orion Star would like to offer a sales promotion that highlights the most frequently purchased products. The company wants a list of all products with a total profit exceeding $500. In this demonstration, we'll use the Query Builder to group, summarize, and filter the orders data set.

1. In the Lesson 4 process flow, we'll start with the orders data set and open the Query Builder.
   
   
2. Name the query Top Products Query and name the output data topproducts.
   
   
3. Double-click Product_ID and Profit to add both columns to the Select Data tab.
   
   
4. On the Select Data tab, click in the Summary column for the Profit column. Select the SUM statistic from the drop-down list. Notice that the Automatically select groups check box is selected. Note: You can double-click SUM_of_Profit to modify the properties, including column name, label, and format.
   
   
5. Click the Filter Data tab. Drag SUM_of_Profit to the Filter the summarized data pane.
   
   
6. In the New Filter Wizard, verify that the column name is SUM_of_Profit. Select Greater than in the Operator field. Type 500 in the Value field and click Finish.
   
   
7. Click the Sort Data tab. Drag and drop SUM_of_Profit onto the tab area and change the sort direction to Descending.
   
   
8. Run the query. As you can see in the output data we have 19 rows, one per product ID with all the values in Sum_Of_Profit exceeding $500. We started with an input data set that included 617 rows. So, our summary and list of product IDs to share with Orion Star management is complete.
   
   
9. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 05, Section 5 Demo: Joining Tables

Let's learn how to join multiple tables to create a new output data set. In the previous demonstration, we created a table that included all products with total profit exceeding $500. Analysts need more detail about these top products, including the product category, the product name, supplier, and country name. The information is in several different tables. In this demonstration, we'll use the Query Builder to join the topproducts SAS table with the products SAS table and the country_lookup Excel spreadsheet.

1. In the Lesson 4 process flow, add the products SAS data set. The Products table has the Product_ID column. It also has the Supplier_Country column that contains two-letter abbreviations for the supplier country.
   
   
2. Next, add the Country_Lookup Excel spreadsheet. We'll have to import the data before we use it. The name of the data will be country_lookup. In step 2, select the check box for First row of range contains field names. That's all we need to do, so let's click Finish. Notice that the Country_Key column contains the two-letter country abbreviations for the countries.
   
   
3. In the Project Tree, double-click the Lesson 4 process flow. Hold the Ctrl key and select the data icons for Topproducts, products, and Data imported from country_lookup, and then right-click one of the tables and select Query Builder.
   
   
4. A message appears and indicates that Enterprise Guide was unable to find matching columns on which to join in all three tables. Click OK.
   
   
5. The Tables and Joins window automatically appears. To join the products and country_lookup tables, first select Supplier_Country in the products table. Select Supplier_Country a second time and drag it to connect it to Country_Key in the country_lookup table. Note: You can also right-click Supplier_Country and select Join with > t3 >Country_Key.
   
   
6. The Join Properties window automatically appears. Verify that the join type is Matching rows only given a condition (Inner Join) and that the condition is t2.Supplier_Country = t3.Country_Key. Click OK > Close to return to the Select Data tab.
   
   
7. Let's name the query Top Products Info Query and the output table topproductsinfo.
   
   
8. Now let's select the columns for our output data. We want Product_ID, SUM_of_Profit, Product_Category, Product_Name, Supplier_Name, and Country_Name.
   
   
9. Run the query and view the output.
   
   
10. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 06, Section 1 Demo: Summarizing Data

Let's learn how to use the Summary Statistics Wizard to create the report and output data for Orion Star management. We're going to summarize profit, store the results in an HTML file, and export the table to Microsoft Excel.

1. In the EG171_Demos project, create a new process flow named Lesson 5. Add the orion_profit data from the location of the course data.
   
   
2. From the Describe menu, select Summary Statistics Wizard. The wizard is using the orion_profit data and we don't need to create a filter, so click Next.
   
   
3. We want summary statistics for Profit, so select Profit and drag it to the Summary statistics of pane. We want to see profit summarized for each Product_Line and Product_Category, so let's select both variables and drag them to the For each value of pane. We aren't using a Group variable, so let's go on to the next step.
   
   
4. Let's click Edit and change the statistics that are generated. On the Basic tab, clear Standard deviation, Minimum, Maximum, and Number of observations. Select Sum. Change the number of decimal places to 2. Click the Percentile tab and select Median and then click OK.
   
   
5. The Show statistics option creates a report and the check box is selected by default. You can also select Histogram and Box and Whisker to create plots to help you visually understand the distribution of the data, as well as identify outliers or inconsistent values. Box-and-whisker plots are especially useful in comparing two or more sets of data.
   
   
6. We want to generate both a report and output data, so select Save statistics to data set. Click Browse and name the output data set profit_summary. That's it for Step 3, so click Next.
   
   
7. In Step 4, delete the default analysis title and type Summary of Profit by Product Line/Category.
   
   
8. Click Finish.
   
   
9. Notice the multiple tabs as part of the task results, including both the Results and Output Data tabs.
   
   
10. To create a more descriptive name for the task, right-click Summary Statistics in the project tree and select Rename. Type Profit by Product Summary.
    
    
11. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 06, Section 1 Demo: Exporting Items as a Step in the Project

Let's learn how to export results as a step in the project. We've generated the summary report and now we need to export the data to Excel. We want the exported file to refresh whenever the project runs, so in this demonstration we'll export the file as a step in the project.

1. To automate the export of the output data to Excel, click the Output Data tab. Select Export > Export Summary Statistics for <libname>.ORION_PROFIT As A Step In Project.
   
   
2. In Step 1 of the Export Wizard, verify that Summary Statistics for <library>.ORION_PROFIT is highlighted. Click Next.
   
   
3. In Step 2, select Microsoft Excel Workbooks (*.xlsx) as the output file type. Click Next.
   
   
4. In Step 3, select the Use labels for column names check box. Click Next.
   
   
5. In Step 4, change the name of the output file. Click Browse and navigate to the location where you stored the course data.Type Profit_Summary in the File name field. Select Save. Note: If you clear the Overwrite existing output check box and a file with the same name already exists, then a new filename is created by appending the current date and time to the existing filename.
   
   
6. Click Next to review the export settings and click Finish. The Profit_Summary.xlsx file is exported and an Export File task is added to the project. Note: If the project is rerun, both the Summary Statistics and Export File tasks run to update all results.
   
   
7. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 06, Section 2 Demo: Creating a Tabular Report

Orion Star marketing wants a tabular summary report that displays the sum of profit for each product category and customer age group with subtotals and totals for product lines. In this demonstration we'll use the Summary Tables Wizard to create this report.

1. In the Lesson 5 process flow, double-click the orion_profit to open it in the data grid.
   
   
2. Select Describe > Summary Tables Wizard from the menus above the data grid.
   
   
3. In Step 1, verify that the active data source is the orion_profit table. Click Next.
   
   
4. In Step 2, analysis variables and statistics are assigned. To calculate the sum of profit, select Add > Profit.
   
   
5. To apply a format so that the values in the table appear as currency values, click Browse. Select Currency from the Categories pane and DOLLARw.d from the Formats pane. Change the overall width to 8 and leave the number of decimal places as 0. Click OK > Next.
   
   
6. In Step 3, classification variables are assigned to define the rows and columns of the table. Select Add > Customer_Age_Group in the Columns pane, and select Add > Product_Category in the Rows pane. Click Next.
   
   
7. In Step 4, verify that Grand total only is selected for both rows and columns. Click Next.
   
   
8. Click Next twice to proceed through the final two steps. Click Finish to view the intermediate results.We can make some additional changes to this report in the wizard. Let's delete these column headings and the Product Category heading. We also need to group product category by Product Line in the report and add totals for each product line.
   
   
9. On the Results tab, select Modify Task to reopen the wizard.
   
   
10. To delete the Profit and Sum headings, click Next to advance to Step 2. Change the value in the Analysis variable labels and Statistics labels fields to hidden.
    
    
11. Click Next to advance to Step 3. To group Product_Category by Product_Line, select Add > Product_Line in the Rows box. Select Product_Line and click to move it to the top of the list.
    
    
12. To delete the labels for Product_Category, Product_Line, and the extra blank space that is included in the first row of the table, select More Options. Click the Hide the row headers check box. Click OK > Next.
13. To add subtotals for each value of Product_Line, change the value in the Rows field to Totals at each level.
    
    
14. Click Finish. When you are prompted to replace the results, click Yes.
    
    
15. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 06, Section 2 Demo: Enhancing a Tabular Report

Let's learn how to make some enhancements to the summary table by opening the task in advanced view.

1. In the Lesson 5 process flow, right-click the Summary Tables Wizard icon in the project tree or process flow. Select Open > Open in Advanced View.
   
   
2. Select Summary Tables in the selection pane.
   
   
3. To change the column heading for Customer_Age_Group, right-click Customer_Age_Group in the Preview area and select Heading Properties.
   
   
4. In the Heading Properties window, type Profit by Customer Age Group in the Label field. Click OK. Note: If you delete a label entirely, the box is also removed from the final report.
   
   
5. Change the column heading for the Product_Line subtotals by right-clicking the Total label and selecting Heading Properties.
   
   
6. Type Product Line Subtotal in the Label field. Click OK.
   
   
7. To highlight the subtotals with a different color background and font, right-click Total (the subtotals for Product_Line) and select Data Value Properties.
   
   
8. In the Data Value Properties window, click the Font tab. Select the Bold Italic font style and light yellow for the background color. Click OK.
   
   
9. To specify a label for missing values, right-click anywhere on the table and select Table Properties.
   
   
10. In the Table Properties window, click the General tab. Delete the default label in the Label for missing values field and type **. Click OK.
    
    
11. Click Run to generate the final report. Click Yes when you are prompted to replace the results.
    
    
12. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 07, Section 2 Demo: Creating a Variable Prompt

Let's learn how to create a Variable prompt. The CEO at Orion Star wants to view a pie chart that illustrates the distribution of profit based on different categories that she selects in a meeting. In this demonstration we'll create a variable prompt that will enable her to select a category.

1. In the EG171_Demos project, create a new process flow and name it Lesson 6.
   
   
2. Add the orion_profit data set.
   
   
3. Click the Prompt Manager button in the Resources pane. Select Add. Note: If you do not see the Prompt Manager, select View > Prompt Manager.
   
   
4. On the General tab, type ProfitCategories in the Name field. Type Select a variable to segment the pie chart: in the Displayed text field. Select the Requires a non-blank value check box.
   
   
5. On the Prompt Type and Values tab, change the prompt type to Variable so that the prompt is accessible in tasks. To populate the prompt list with variables from the orion_profit data set, select Load Values. Note: By default, the selected Variable type value is Character. If you select Load Values, all character columns from the selected table are added to the list. You can use the Variable type pane to select other column types.
   
   
6. In the Open file window, select Project > orion_profit > Open. All character variables from the orion_profit data set are entered into the list. Because Customer_Name, Supplier_Name, and Product_Name are not appropriate variables to use in the Bar Chart task, remove them from the list by highlighting the variables and clicking the Delete button.
   
   
7. To assign a default value, select the Default value check box and double-click Product_Category. Click OK to close the Add New Prompt window.
   
   
8. View the prompt manager. The prompt we created is listed here. In the next demonstration we'll use it in a task.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 07, Section 2 Demo: Using a Variable Prompt in a Task

We've created the ProfitCategories prompt, so now it's time to actually use it in a task. In this demonstration we'll use the prompt we created to create a Pie Chart task that the CEO can use in the board meeting.

1. In the Lesson 6 process flow, double-click the orion_profit data.
   
   
2. From the Graph menu, select the Pie Chart Wizard. Step 1 looks good, so let's go to Step 2.
   
   
3. We want to use the prompt we created in the Slice role, so click the drop-down menu and select the ProfitCategories prompt at the bottom of the list. For Slice size, select Profit.
   
   
4. In Step 3, let's select the option to create a 3D chart. Verify that the Slice name and Data value check boxes are selected. To add the percentage on the inside of each slice, select the Percentage check box. Change the value to Inside. Click Next.
   
   
5. Next, we want the title to show the name of the variable that the user selects in the prompt. So, let's type Profit by &ProfitCategories. Remember, you have to reference the macro variable name in a title. The name of the macro variable is the prompt name preceded by an ampersand. Be aware that the macro variable name is not case sensitive. I'm going to delete the footnote.
   
   
6. Now click Finish. In the prompt window, select Customer_Country and then click Run. As we would expect, the pie chart has a slice for each country.
   
   
7. Let's refresh this task and see what happens. The prompt window opens again. This time let's select Product_Category and then click Run. As you can see, creating a prompt and adding it to a task can make a task much more versatile.
   
   
8. Notice in the prompt manager that the prompt is used in the pie chart task.
   
   
9. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 07, Section 3 Demo: Creating and Using a Prompt in a Query

Let's learn how to create a text prompt that we can use to filter data in the Query Builder. When you filter data, you specify a value in the condition. The value is usually a text string, a number, a date, or a time. Text, Numeric, and Date type prompts can be used instead of a hard-coded data value in the Query Builder to filter data. In this demonstration, we'll create a text prompt and use it in a query to extract orders for a specific supplier. We'll generate a report of the orders and include the supplier name in the title.

1. In the Lesson 6 process flow, right-click orion_profit and select Query Builder.
   
   
2. Type Choose a Supplier Query in the Query name field. Click Change and type SupplierOrders in the File name field. Select Save.
   
   
3. On the Select Data tab, add Product_ID, Product_Name, Order_Date, Quantity, and Profit.
   
   
4. To build a prompt that enables the selection of a single supplier, select Prompt Manager > Add.
   
   
5. On the General tab, type ChooseSupplier in the Name field and Select a Supplier: in the Displayed text field. Select the Requires a non-blank value and Use prompt value throughout project check boxes. Note: Selecting the Use prompt value throughout project check box enables you to use the prompt value in future tasks.
   
   
6. On the Prompt Type and Values tab, verify that the prompt type is Text. Change the method for populating the prompt to User selects values from a static list. To populate the list with supplier names from the orion_profit data set, select Get Values.
   
   
7. To define the data source, click Browse > Project > orion_profit > Open.
   
   
8. Change the column value in the Unformatted Values pane to Supplier_Name.
   
   
9. Select Get values and click the double arrow to fill the prompt list with all Supplier_Name values.
   
   
10. Click OK > OK > Close to return to the Query Builder.
    
    
11. To use the prompt in a filter, click the Filter Data tab. Drag and drop Supplier_Name onto the tab area. In the New Filter Wizard, verify that the operator is Equal to. Click next to the Value field and select Prompts > &ChooseSupplier. Click Finish.
    
    
12. To create a report that includes the name of the selected supplier in the title, select Options. Select the Override the corresponding default settings in Tools > Options check box and select Report.
    
    
13. Select Titles in the selection pane and select the Override the corresponding default settings in Tools > Options check box. Type Orders for &ChooseSupplier. Click OK.
    
    
14. Click Run. At the prompt, select Carolina Sports > Run. Verify that 11 rows are returned.
    
    
15. Select Refresh to run the query again and select a different supplier.
    
    
16. Save the project.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 08, Section 1 Demo: Combining Results in a Single Report

This demonstration uses a saved project, so no steps are available to follow in your software. You will have an opportunity to perform similare steps in the upcoming practice.

SAS Enterprise Guide 1: Querying and Reporting
Lesson 08, Section 2 Demo: Updating Results in a Process Flow

This demonstration uses a saved project, so no steps are available to follow in your software. You will have an opportunity to perform similare steps in the upcoming practice.