Activities and Practices (with Solutions) for SAS® Enterprise Guide® 1: Querying and Reporting

Practice Level 1: Creating a Project and Running the Characterize Data Wizard

In this practice, you create a project and run the Characterize Data Wizard.

Note: If you do not have the Lesson1 project, you can create a new project using one of the following methods:

• Select File > New > Project from the menu.
• Click the Create a new item icon and select Project.
• Click Create a new project in the Get Started section of the Start Page tab.
• If no project is currently open, click Create a new project in the Project pane.
  
  

1. In the Lesson1 project, add the employee_master table to the Practices process flow. Note: If you created a Practices process flow in the previous demonstration, be sure to click the Practices tab in the work area before you add the SAS table.
   • Select File > Open to open the table.
   • Select My Computer on the left side of the Open window and click Browse.
   • Navigate to the data folder in the course file location.
   • Select the employee_master table and click Open. The table appears in the data grid on a new tab in the work area.
   
   
   
2. Use the Characterize Data Wizard to investigate the distribution of each of the columns in the employee_master table.
   
   • Click the Tasks icon in the navigation area and expand the Describe category. Note: The Tasks pane has three views: SAS Tasks, Recent, and Favorites. Verify that all tasks are listed. In addition, tasks can be sorted by category or name. Verify that tasks are sorted by Category.
   • Double-click Characterize Data to open the task.
     
   • Do not create any result data sets.
   • Limit the number of unique categorical values to be reported per variable to 15.
   
   

   Solution:

   • Do not create any result data sets by clearing the SAS Data Sets check box in Step 2. Click Next.
   • Enter 15 in the Maximum number of unique categorical values to be reported per variable field.
   • Click Finish to run the task.
   
   
3. Run the task. How many missing values are there for the Country variable?
   
   

   Solution:

   one
   
4. Close the Characterize Data and employee_master tabs in the work area.
   
   
   
5. Save the project as Lesson1 in the course file location.
   • Select File > Save project as from the main menu.
   • If necessary, click the My Computer icon and click Browse.
   • Navigate to the course file location.
   • Enter Lesson1 for the file name and click Save.
   
   
   

Practice Level 2: Running the Data Set Attributes Wizard

In this practice, you run the Data Set Attributes Wizard. The Data Set Attributes Wizard enables you to create a report with information about a particular data set, including creation date, location, and number of observations, as well as the variable names, labels, types, and formats.

Note: If you did not complete the Level 1 practice, create a new project and add the employee_master table.

1. In the Lesson1 project, open the employee_master table in the data grid and open the Data Set Attributes Wizard.
   
   Hint: The Data Set Attributes Wizard in is the Data category.
   
   

   Solution:

   • Double-click employee_master in the Project pane or process flow to open the table in the data grid on a separate tab in the work area.
   • Click the Tasks icon in the navigation area and expand the Data category.
   • Double-click Data Set Attributes to open the task.
   • In Step 1, verify that the correct table is listed. Click Next.
   
   
2. In Step 2 of the Data Set Attributes Wizard, make sure that Default Report is the only output that is selected.
   
   

   Solution:

   Under Other outputs, clear the Enhanced Report check box.
   
3. Run the task. How many variables are in the data set?
   
   

   Solution:

   11
   
4. Close the Data Set Attributes and employee_master tabs in the work area.
   
   
   
5. Save the project as Lesson1 in the course file location.
   
   
   

Practice Challenge: Submitting a SAS Program with a Coding Error

In this practice, you submit a SAS program with a coding error. In addition to using the point-and-click tasks, you can create a new SAS program or include existing SAS programs in your project. Enterprise Guide provides a color-coded, syntax-checking SAS language editor for editing new or existing SAS programs in your project.

1. Add the program e101p03.sas from the practices folder to the Lesson1 project. Note: If you created a Practices process flow in the previous demonstration, be sure to click the Practices tab in the work area before you add the SAS program file.
   
   

   Solution:

   • Insert an existing SAS program by selecting File > Open or by clicking the Open a file icon on the main toolbar.
   • Click My Computer > Browse.
   • Navigate to the practices folder in the course file location.
   • Select the e101p03.sas program and click Open.
   
   
2. Run the program, review the log, and identify the error. Note: You can click the error description to go to the corresponding line in the log.
   
   

   Solution:

   • Click the Run icon on the toolbar associated with the SAS program.
   • Because the program has an error, the SAS log appears automatically in the work area.
   • Display only the error messages by clicking the Errors tab and deselecting the Warnings tab and Notes tab.
   • To view the corresponding code in the detailed log, click the error description.
   • The SAS keyword var was misspelled as val.
   
   
3. Correct the error on the Code tab, submit the corrected program, and review the results. Note: You can right-click the error description to go to the corresponding line in the program code.
   
   

   Solution:

   • Right-click the error description and select Go to program source to quickly navigate to the affected code in the program.
   • Change the spelling of the SAS keyword to var.
   • Click the Run icon to submit the modified program.
   
   
4. Close the e101p03.sas tab. Click Save when prompted to save the changes to the program.
   
   
   
5. Save the project as Lesson1 in the course file location. Select Yes when you are prompted to save the changes that you made to the e101p03.sas program.
   
   
   

Practice Level 1: Assigning a Project Library

In this practice, you create the orion library to access Orion customer and employee SAS tables.

1. In the Lesson2 project, add a new process flow and rename it to Practices.
   
   Note: If you do not have the Lesson2 project, create a new project.
   
   

   Solution:

   • Select File > New > Process flow.
   • Right-click Process Flow in the Project pane and select Rename.
   • Enter Practices.
   
   
2. Create the orion library and point to the location of the course data.
   
   

   Solution:

   • Use the Servers pane in the navigation area to assign a library. Expand Servers > your server name and select Libraries.
   • Click the Assign new library icon.
   • In Step 1, enter orion in the Name field and click Next.
   • In Step 2, verify that the engine is BASE.
   • Navigate to or enter the location of the course data. Click Next.
   
   
3. Include the ACCESS=READONLY option in Step 3 of the wizard. Then test and assign the library.
   
   

   Solution:

   • Enter access in the Name field and readonly in the Value field. Click Next.
   • Click Test Library and verify that OK is displayed.
   • Click Finish.
   
   
4. View the orion library in the Servers pane. If you do not see the library, select Libraries and click Refresh.
   
   
   
5. Save the Lesson2 project in the course file location.
   
   • Select File > Save all from the main menu.
   
   
   

Practice Level 2: Using the Servers Pane for File Operations

In this practice, you use the Servers pane to copy, paste, and delete SAS tables

1. If you haven't already, complete the Level 1: Assigning a Project Library practice.
   
   
   
2. You can use the Servers pane to create a copy of a SAS table.
   • To create a temporary copy, right-click the travel_expenses table in the orion library and select Copy.
   • Right-click the Work library and select Paste.
   
   
   
3. Double-click the travel_expenses table in the Work library to add it to the project. By default, the data appears in the data grid on a separate tab in the work area. Close the TRAVEL_EXPENSES tab.
   
   
   
4. Right-click travel_expenses in the Project pane or process flow and select Delete. Is the table deleted from the library?
   
   

   Solution:

   No, only the shortcut is removed from the project, but the table remains in the library.
   
5. Right-click travel_expenses in the Work library in the Servers pane and select Delete. Are you able to delete the table?
   
   

   Solution:

   Yes. If you have Write access to a library and the data sets within, you can move, copy, and delete files in the Servers pane. These actions cannot be undone, so be careful!
   
6. Right-click travel_expenses in the orion library in the Servers pane. Are you able to delete the table?
   
   

   Solution:

   No. Because you used the ACCESS=READONLY option when you defined the orion library, you cannot make changes to the contents.
   

Practice Challenge: Automatically Assigning a Library

You can set a SAS Program option to automatically submit SAS code when the server is connected. In this practice, you use this option to submit the LIBNAME statement that creates the orion library.

1. If you haven't already, complete the Level 1: Assigning a Project Library practice. Copy the LIBNAME statement from the Code tab. The copied statement resembles the following:
   
   

   LIBNAME ORION BASE "S:\workshop\data" access=readonly;

   
   
   
2. Open the Options window and select the SAS Programs category. Edit the Submit SAS code when server is connected option. Paste the LIBNAME statement in the opened window and save the change. Click OK to close the Options window.
   
   

   Solution:

   • Select Tools > Options from the main menu.
   • Click the SAS Programs category in the selection pane.
   • Click Edit for the Submit SAS code when server is connected option.
   • Paste the copied LIBNAME statement in the Edit window and click Save.
   • If necessary, select the Submit SAS code when server is connected option.
   • Click OK to close the Options window.
   
   
3. Test that the orion library is automatically assigned when the server is connected.
   • Close all open items.
   • In the Servers pane, right-click the name of the server and select Disconnect.
   • Restore the server connection by right-clicking the name of the server and selecting Connect.
   • Expand Libraries and verify that the orion library has been assigned.
   
   

   Solution:

   When a server is connected, a green check mark appears on the icon.
   

Practice Level 1: Importing Data from an Excel Worksheet

In this practice, you create a table that contains a copy of the data that is in an Excel workbook. The Excel workbook contains two worksheets. Only one worksheet is imported.

1. In the Lesson2 project, use the Import Data Wizard to create a SAS table named employee_addresses from the Addresses worksheet in the employee_info workbook. Save the table in the default SAS library, which can be Work, Sasuser, or egtask.
   
   Note: If you do not have the Lesson2 project, create a new project.
   
   

   Solution:

   • Select File > Import data and navigate to the course data.
   • Select employee_info.xlsx and click Open.
   • In Step 1, click Browse to change the name of the table.
   • Enter employee_addresses as the file name and click Save.
   • Click Next.
   
   
2. Indicate that the first row contains column names. Rename columns to comply with SAS naming conventions.
   
   

   Solution:

   • In Step 2, verify that Addresses is the selected worksheet.
   • Verify that the First row of range contains field names check box is selected.
   • Select the Rename columns to comply with SAS naming conventions check box.
   • Click Next.
   
   
3. Change the type of the Postal_Code column to String. Scan all values in the column to determine a proper length.
   
   

   Solution:

   • In Step 3, click in the Type column for the Postal_Code row.
   • Click the down arrow and select String.
   • Click OK to scan all values in the column to determine a proper length.
   
   
4. Modify each of the labels to replace underscores with spaces.
   
   

   Solution:

   In the Label column, double-click to change the labels to Employee ID, Employee Name, Street ID, Street Number, Street Name, and Postal Code.
   
5. Finish the Import Data Wizard and view the SAS table. How many rows are in the SAS table?
   
   

   Solution:

   Click Finish. The Import Data tab appears, displaying the output data. There are 424 rows in the table. To view the column labels, click the ellipsis (More options) at the end of the data grid toolbar and select Show labels.
   
6. Close the Import Data Wizard when finished examining the data. Rename the Import Data task to Import Employee Addresses.
   
   

   Solution:

   • Right-click Import Data (employee_info.xlsx[Addresses]) in the Project pane or process flow and select Rename.
   • Enter Import Employee Addresses.
   
   
7. Save the Lesson2 project.
   
   
   

Practice Level 2: Importing Data from a CSV File

In this practice, you create a table from a comma-delimited CSV file.

1. In the Lesson2 project, import the data in the payroll.csv file to create a SAS table named payroll.
   
   

   Solution:

   • Select File > Import data and navigate to the course data location.
   • Select payroll.csv and click Open.
   • In Step 1 of the Import Data Wizard, verify that the name of the new SAS table is payroll.
   • Click Next.
   
   
2. Indicate that the file is comma delimited and the first row contains column names.
   
   

   Solution:

   • In Step 2, verify that a comma is the delimiter.
   • Verify that the File contains field names on record number check box is selected and 1 is specified as the record number.
   • Click Next.
   
   
3. Do not include the Job_Title column.
   
   

   Solution:

   In Step 3 of the Import Data Wizard, clear the check box for Job_Title.
   
4. Change the type for Employee_ID to Number.
   
   

   Solution:

   In Step 3 of the Import Data Wizard, click in the Type column for the Employee_ID variable. Click the down arrow and select Number.
   
5. Change the type for Salary to Currency and use a currency format that displays no decimal places (for example, DOLLAR16.).
   
   

   Solution:

   • In Step 3 of the Import Data Wizard, click in the Type column for the Salary variable. Click the down arrow and select Currency.
   • Double-click in the Output Format column for the Salary variable and click the ellipsis icon.
   • In the Output Data Set Format window, change the number of decimal places to 0 (zero).
   • Click OK to accept the change and close the window.
   
   
6. Change the output format for Birth_Date to DDMMYY10. to display dates using a Day/Month/Year format (for example, 17/02/1969).
   
   

   Solution:

   • Double-click in the Output Format column for the Birth_Date variable and click the ellipsis icon.
   • If necessary, select the Date category in the Output Data Set Format window.
   • Select the DDMMYYw.d format and change the Overall width field to 10.
   • Click OK to accept the change and close the window.
   
   
7. Change the name for Termination to Term_Date and specify a label of Termination Date.
   
   

   Solution:

   • Double-click in the Name column for the Termination variable. Enter Term_Date.
   • Double-click in the Label column for the Termination variable. Enter Termination Date.
   
   
8. Finish the Import Data Wizard and view the SAS table. How many rows are in the SAS table?
   
   

   Solution:

   Click Finish. The Import Data tab appears, displaying the output data. There are 1,048 rows in the table. To view the column labels, click the ellipsis (More options) at the end of the data grid toolbar and select Show labels.
   
9. Close the Import Data Wizard when finished examining the data. Rename the Import Data task to Import Employee Payroll.
   
   

   Solution:

   • Right-click Import Data (payroll.csv) in the Project pane or process flow and select Rename.
   • Enter Import Employee Payroll.
   
   
10. Save the Lesson2 project.
    
    
    

Practice Challenge: Importing a Fixed-Width Text File

In a file with fixed columns, the fields are aligned so that each field starts in the same byte position. SAS imports fixed-column data at specific byte positions for each. In this practice, you create a table from a fixed-width text file.

1. In the Lesson2 project, import the data in the oldorders.txt file to create a SAS table named oldorders.
   
   

   Solution:

   • Select File > Import data and navigate to the course data location.
   • Select oldorders.txt and click Open.
   • In Step 1 of the Import Data Wizard, verify that the name of the new SAS table is oldorders.
   • Click Next.
   
   
2. Indicate that the file has fixed columns and the first row contains column names. Then indicate where each field starts. You do not need to place a column break before the first field.
   
   Hint: The ruler at the top of the field break box is measured in number of bytes. In the field break box, you must insert break lines to indicate where each column starts. An arrow marks the location of each break line. To specify a break line, click the tick mark in the ruler that is located above the box. You can use the mouse to move a break line to a different location. To remove a break line, double-click the arrow or drag the arrow out of the box.
   
   

   Solution:

   • In Step 2, select Fixed columns.
   • Select the File contains field names on record number check box and verify that the value is 1.
   • Click on the start of each field to define the column breaks. You do not need to place a column break before the first field.
   • Click Next.
   
   
3. In Step 3, modify the properties of the variables as follows:
   
   • Exclude the Employee_ID variable.
   • Format Discount with a percent sign. Use a width of 5 and no decimal places.
   • Change the type of Total_Retail_Price, CostPrice_Per_Unit, Shipping, and Profit to Currency. Accept the default output format.
   
   

   Solution:

   • Exclude the Employee_ID variable by clearing the Inc check box.
   • Format Discount with a percent sign and zero decimal places by double-clicking in the Output Format box and clicking the ellipsis icon. In the Numeric format category, scroll to select the PERCENTw.d format. Change the overall width to 5 and click OK.
   • Change the type of Total_Retail_Price, CostPrice_Per_Unit, Shipping, and Profit to Currency by changing the drop-down list in the Type column to Currency.
   
   
4. Finish the Import Data Wizard and view the SAS table. How many rows are in the SAS table?
   
   

   Solution:

   Click Finish. The Import Data tab appears, displaying the output data. There are 617 rows in the table.
   
5. Close the Import Data Wizard when finished examining the data. Rename the Import Data task to Import Old Orders.
   
   

   Solution:

   • Right-click Import Data (orders.txt) in the Project pane or process flow and select Rename.
   • Enter Import Old Orders.
   
   
6. Save the Lesson2 project.
   
   
   

Practice Level 1: Producing a One-Way Frequency Task Report

In this practice, you use the One-Way Frequencies task to produce a report of employees by Department

1. Establish the List Data and the One-Way Frequencies tasks as favorites.
   
   • In the Tasks pane, expand the Describe category.
   • Click the Star icon for the List Data task and the One-Way Frequencies task.
   
   
   
2. Remove the SAS procedure title from generated results.
   
   • Select Tools > Options.
   • Select Tasks - General from the selection pane.
   • Clear the Include SAS procedure titles in results check box.
   • Click OK to close the Options window.
   
   
   
3. In the Lesson3 project, add a new process flow and rename it Practices. Then add the employee_master table. Note: If you do not have the Lesson3 project, you can create a new project.
   
   

   Solution:

   • Select File > New > Process flow.
   • Right-click Process Flow in the Project pane and select Rename. Enter Practices.
   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
4. Use the One-Way Frequencies task to analyze the Department column.
   
   • Include a horizontal bar chart as part of the report.
   • Add the title Employee Count by Department and delete the footnote.
   • Modify the task properties to change the task label to Dept Freq Report.
   • Generate the report and examine the results.
   
   

   Solution:

   • From the data grid toolbar, select Tasks > One-Way Frequencies.
   • In the Data panel, drag and drop Department to the Analysis variables role.
   • Include a horizontal bar chart as part of the report.
     • Select Plots in the selection pane.
     • Select the Horizontal check box.
   • Add the title Employee Count by Department and delete the footnote.
     • Select Titles in the selection pane.
     • Select Analysis in the Section pane and clear the Use default text check box.
     • Delete the current text and enter Employee Count by Department.
     • Select Footnote in the Section pane and clear the Use default text check box.
     • Delete the default footnote text.
   • Modify the task properties to change the task label to Dept Freq Report.
     • Select Properties in the selection pane.
     • Click Edit.
     • Delete the text in the Label field and enter Dept Freq Report.
     • Click OK.
   • Generate the report and examine the results.
     • Click Run. The Dept Freq Report tab appears in the work area.
     • If necessary, click the Results tab to view the report.
   
   
5. Modify the One-Way Frequencies task to display only frequencies and percentages. Run the task. How many employees are in the Marketing Department?
   
   

   Solution:

   • Click the Modify Task icon from the task toolbar to reopen the One-Way Frequencies task.
   • Select Statistics in the selection pane and select the Frequencies and Percentages radio button in the Frequency table options pane.
   • Click Run.
   • There are 20 employees in the Marketing Department.
   
   
6. Close all tabs except for the process flow, and save the Lesson3 project.
   
   
   

Practice Level 2: Creating a Grouped Frequency Report

In this practice, you use the One-Way Frequencies task to generate a count and percentage report of employees by Job_Title. You group the results by Department.

1. If you haven't already, complete the Level 1: Producing a One-Way Frequency Task Report practice.
   
   
   
2. Use the One-Way Frequencies task to analyze Job_Title, grouped by Department.
   
   • Include only frequencies and percentages.
   • Order the output report by descending frequencies.
   • Add the title Employees by Job Title and delete the footnote.
   • Change the task label to Jobs by Dept Freq Report.
   • Run the task.
   
   Note: If you closed the employee_master tab, then select the table in the process flow and use the Tasks pane to launch the task.
   
   

   Solution:

   • From the data grid toolbar, select Tasks > One-Way Frequencies.
   • In the Data panel, assign Job_Title to the Analysis variables role and Department to the Group analysis by role.
   • Include only frequencies and percentages.
     • Select Statistics in the selection pane.
     • Select Frequencies and percentages in the Frequency table options pane.
   • Order the output report by descending frequencies.
     • Select Results in the selection pane.
     • Change the Order output data by option to Descending frequencies.
   • Add the title Employees by Job Title and delete the footnote.
     • Select Titles in the selection pane.
     • Select Analysis in the Section pane and clear the Use default text check box.
     • Delete the default text and enter Employees by Job Title as the title.
     • Select Footnote in the Section pane and clear the Use default text check box.
     • Delete the default footnote text.
   • Change the task label to Jobs by Dept Freq Report.
     • Select Properties in the selection pane.
     • Click Edit.
     • Delete the text in the Label field and enter Jobs by Dept Freq Report.
     • Click OK.
   • Run the task.
     • Click Run to generate the report. The Jobs by Dept Freq Report tab appears in the work area.
     • If necessary, click the Results tab to view the report.
   
   
3. In the Engineering Department, which job title has the most employees?
   
   

   Solution:

   Cabinet Maker II
   
4. Close all tabs except for the process flow, and save the Lesson3 project.
   
   
   

Practice Challenge: Using Group Values in the Title of a Grouped Frequency Report

In this practice, you use group values in the title of a grouped frequency report. You can use the #BYVAL option to insert the name of each distinct group in titles and footnotes.

Note: Documentation about the BYLINE option and the use of #BYVAL can be found by accessing online Help and searching for BYLINE System Option.

1. If you haven't already, complete the Level 2: Creating a Grouped Frequency Report practice and then continue with the next steps.
   
   
   
2. Modify the Jobs by Dept Freq Report task.
   
   • Change the title to Employees by Job Title in #byval(department) Department. This syntax places the name of each distinct group in the title.
   • Access the code in the One-Way Frequencies task by selecting Preview code. Select the Show custom code insertion points check box.
   • To remove the secondary BYLINE title, add the provided statement at the earliest point of insertion in the task code.

     options nobyline;

   • To reinstate the secondary BYLINE title for future tasks, add the provided statement at the end of the task code.

     options byline;

   
   

   Solution:

   On the Jobs by Dept Freq Report tab, click the Modify Task icon from the task toolbar.
   • Change the title to Employees by Job Title in #byval(department) Department. This syntax places the name of each distinct group in the title.
     • Select Titles in the selection pane.
     • Change the title to Employees by Job Title in #byval(department) Department.
   • Access the code in the One-Way Frequencies task by selecting Preview code. Select the Show custom code insertion points check box.
     • In the lower left part of the task window, click the Preview code button to open the Code Preview for Task window.
     • In the upper left part of this window, select the Show custom code insertion points check box.
   • To remove the secondary BYLINE title, add the provided statement at the earliest point of insertion in the task code.
     • Click the first line that indicates <insert custom code here>.
     • Enter the following SAS OPTIONS statement:

       options nobyline;

   • To reinstate the secondary BYLINE title for future tasks, add the provided statement at the end of the task code.
   • Click the last line that indicates <insert custom code here>.
   • Enter the following SAS OPTIONS statement:

     options byline;

   • Close the Code Preview for Task window.
   
   
3. Run the task and view the results.
   
   

   Solution:

   • Click Run to generate the report.
   • If necessary, click the Results tab to view the report.
   
   
4. Close all tabs except for the process flow, and save the Lesson3 project.
   
   
   

Practice Level 1: Producing a Listing Report

In this practice, you use the List Data task on the employee_master table to produce a report on employees by Department.

1. In the Lesson3 project, select the employee_master table in the Project pane or process flow and use the Tasks pane to launch the List Data task.
   
   
   
2. Make the following role assignments:
   
   • List variables: Employee_Name, Job_Title, Salary
   • Group analysis by: Department
   • Identifying label: Employee_ID
   
   

   Solution:

   • Hold down the Ctrl key and select Employee_Name, Job_Title, and Salary in the Variables to assign pane.
   • Drag the selected items to the List variables role.
   • Drag Department to the Group analysis by role.
   • Drag Employee_ID to the Identifying label role.
   
   
3. Add the title Orion Star Employees and delete the footnote.
   
   

   Solution:

   • Select Titles in the selection pane and clear the Use default text check box.
   • In the Text field, delete the default title and enter Orion Star Employees.
   • Select Footnote in the Section pane and clear the Use default text check box.
   • Delete the default footnote text.
   
   
4. Modify the task properties to change the task label to Employee List.
   
   

   Solution:

   • Select Properties in the selection pane.
   • Click Edit.
   • Delete the text in the Label field and enter Employee List.
   • Click OK.
   
   
5. Generate the report. What is Ramond Capps' job title in the Accounts Management Department?
   
   

   Solution:

   Click Run to generate the report. Ramond Capps' job title is Auditor II.
   
6. Close all tabs except for the process flow, and save the Lesson3 project.
   
   
   

Practice Level 2: Using Multiple Group Values in a Listing Report

In this practice, you use the List Data task to produce an employee report that lists employees by Department and Job_Title.

1. In the Lesson3 project, select the employee_master table in the Project pane or process flow and use the Tasks pane to launch the List Data task.
   
   
   
2. Make the following role assignments:
   
   • List variables: Employee_Name, Hire_Date, Salary
   • Group analysis by: Department, Job_Title
   • Identifying label: Employee_ID
   
   

   Solution:

   • Drag Employee_Name, Hire_Date, and Salary from the Variables to assign pane to the List variables role.
   • Drag Department and Job_Title to the Group analysis by role.
   • Drag Employee_ID to the Identifying label role.
   
   
3. Add the title Departmental Employee Report and delete the footnote.
   
   

   Solution:

   • Select Titles in the selection pane and clear the Use default text check box.
   • In the Text field, delete the default title and enter Departmental Employee Report.
   • Select Footnote in the Section pane and clear the Use default text check box.
   • Delete the default footnote text.
   
   
4. Modify the task properties to change the task label to Dept Employee List.
   
   

   Solution:

   • Select Properties in the selection pane.
   • Click Edit.
   • Delete the text in the Label field and enter Dept Employee List.
   • Click OK.
   
   
5. Generate the report.
   
   

   Solution:

   Click Run to generate the report. A separate tab appears, displaying the task results.
   
6. What is the highest salary for an Accountant III in the Accounts Department?
   
   

   Solution:

   $52,036
   
7. Close all tabs except for the process flow, and save the Lesson3 project.
   
   
   

Practice Challenge: Grouping Data by a Formatted Value

In this practice, you produce a report that lists employees by the year that they were terminated.

1. In the Lesson3 project, select the employee_master table in the Project pane or process flow and use the Tasks pane to launch the List Data task.
   
   
   
2. Apply a filter to the input data source. The report should display only employees who have been terminated (that is, where the Termination value is not missing).
   
   

   Solution:

   • Click Edit.
   • From the first drop-down list, select Termination.
   • From the second drop-down list, select Is not missing.
   • Click OK.
   
   
3. Make the following role assignments:
   
   • List variables: Employee_Name
   • Group analysis by: Terminations
   • Identifying label: Employee_ID
   
   

   Solution:

   • Drag Employee_Name from the Variables to assign pane to the List variables role.
   • Drag Termination to the Group analysis by role.
   • Drag Employee_ID to the Identifying label role.
   
   
4. Apply the YEAR4. format to the Termination column.
   
   

   Solution:

   • Right-click the Termination column name in the Task roles pane and select Properties.
   • In the Termination Properties window, click Change for the format.
   • In the Formats window, select the Date category.
   • Scroll down in the list of formats, select YEARw.d, and verify that the overall width is 4.
   • Click OK to close the Formats window.
   • Click OK to close the Termination Properties window.
   
   
5. Print the number of rows for each BY group.
   
   

   Solution:

   • Select Options in the selection pane.
   • Select the Print number of rows check box.
   
   
6. Add the title Terminated Employees and delete the footnote.
   
   

   Solution:

   • Select Titles in the selection pane and clear the Use default text check box.
   • In the Text field, delete the default title and enter Terminated Employees.
   • Select Footnote in the Section pane and clear the Use default text check box.
   • Delete the default footnote text.
   
   
7. Modify the task properties to change the task label to Terminated Employee List.
   
   

   Solution:

   • Select Properties in the selection pane.
   • Click Edit.
   • Delete the text in the Label field and enter Terminated Employee List.
   • Click OK.
   
   
8. Generate the report and examine the results.
   
   

   Solution:

   Click Run to generate the report. Review the results on the Terminated Employee List tab.
   
9. How many employees were terminated in 2014?
   
   

   Solution:

   61
   
10. Close all tabs except for the process flow, and save the Lesson3 project.
    
    
    

Practice Level 1: Filtering Data in the List Data Task

In this practice, you use the List Data task on the employee_addresses table to produce a report. You display only employees located in San Diego.

1. In the Lesson3 project, add the employee_addresses table to the Practices process flow.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_addresses > Open. The data appears on a new tab in the work area.
   
   
2. Create a report with the List Data task and show the Employee_Name, Street_Number, Street_Name, and Postal_Code columns. Identify each row with the Employee_ID column.
   
   

   Solution:

   • From the data grid toolbar, select Tasks > List Data.
   • In the Data panel, hold down the Ctrl key and select Employee_Name, Street_Number, Street_Name, and Postal_Code. Drag them to the List variables role.
   • Drag Employee_ID to the Identifying label role.
   
   
3. Modify the properties for the columns so that the report displays the following labels: ID, Name, Street Number, Street Name, and ZIP Code.
   
   

   Solution:

   • Right-click each of the variable names and select Properties.
   • In the Properties window, enter the appropriate text in the Label field.
   • Click OK.
   • Note: In the Task roles pane, right-click and select Show Labels to display the labels.
   
   
4. Filter the data so that only the employees from San Diego are included in the report.
   
   

   Solution:

   • Click Edit to open the Edit Data and Filter window.
   • Using the first drop-down box in the Task filter, select City.
   • In the second drop-down box, select Equal to as the operator.
   • In the third box, click the ellipsis (Value Selector). Select San Diego and click OK.
   • Click OK to close the Edit Data and Filter window.
   
   
5. Change the title to Employee List for San Diego and delete the footnote.
   
   

   Solution:

   • Select Titles in the selection pane.
   • Verify that Report Titles is selected in the Section pane. Clear the Use default text check box and delete the default text. Enter Employee List for San Diego.
   • Select Footnote in the Section pane and clear the Use default text check box.
   • Delete the default footnote text.
   
   
6. Modify the task properties to change the task label to San Diego List.
   
   

   Solution:

   • Select Properties in the selection pane.
   • Click Edit.
   • Delete the text in the Label field and enter San Diego List.
   • Click OK.
   
   
7. Generate the report. On what street does James Blackley live?
   
   

   Solution:

   Click Run to generate the reports. James Blackley lives on Dubose St.
   
8. Close all tabs except for the process flow, and save the Lesson3 project.
   
   
   

Practice Level 2: Filtering Data in the One-Way Frequencies Task

In this practice, you use the One-Way Frequencies task to produce a report of Australian employees by Job_Title.

1. In the Lesson3 project, use the One-Way Frequencies task to analyze Job_Title in the employee_master table.
   
   • Filter the data to select only employees from Australia (AU).
   • Include only frequencies.
   • Order the output report by descending frequencies.
   • Add the title Frequency of Australian Employees by Job Title and delete the footnote.
   • Change the task label to AU Job Freq Report.
   
   Note: If you closed the employee_master tab, select the table in the process flow and use the Tasks pan to launch the task.
   
   

   Solution:

   • From the data grid toolbar, select Tasks > One-Way Frequencies.
   • Drag Job_Title to the Analysis variables role.
   • Filter the data to select only employees from Australia (AU).
     • Click Edit to open the Edit Data and Filter window.
     • Using the first drop-down box in the Task filter, select Country.
     • In the second drop-down box, select Equal to as the operator.
     • In the third box, click the ellipsis (Value Selector). Select AU and click OK.
     • Click OK to close the Edit Data and Filter window.
   • Include only frequencies.
     • Select Statistics in the selection pane.
     • Select Frequencies only in the Frequency table options pane.
   • Order the output report by descending frequencies.
     • Select Results in the selection pane.
     • Change the Order output data by option to Descending frequencies.
   • Add the title Frequency of Australian Employees by Job Title and delete the footnote.
     • Select Titles in the selection pane.
     • Select Analysis in the Section pane and clear the Use default text check box.
     • Delete the default text and enter Frequency of Australian Employees by Job Title as the title.
     • Select Footnote in the Section pane and clear the Use default text check box.
     • Delete the default footnote text.
   • Change the task label to AU Job Freq Report.
     • Select Properties in the selection pane.
     • Click Edit.
     • Delete the text in the Label field and enter AU Job Freq Report.
     • Click OK.
   
   
2. Generate the report. Which job title has the most employees?
   
   

   Solution:

   Click Run to generate the reports. The job title with the most employees is Sales Rep I.
   
3. Close all tabs except for the process flow, and save the Lesson3 project.
   
   
   

Practice Challenge: Using a Complex Filter in a List Report

The ability to filter data in tasks using the point-and-click Edit Data and Filter window is limited to the operators provided in the drop-down lists. To create filters that include SAS functions, you can modify the code to add a custom WHERE statement in the program generated by the task.

1. In the Lesson3 project, use the List Data task and show the Employee_ID and Birth_Date columns from the employee_master table. Format Birth_Date and use the MMDDYY10. format.
   
   Note: If you closed the employee_master tab, you can select the table in the process flow and use the Tasks pane to launch the task.
   
   

   Solution:

   • From the data grid toolbar, select Tasks > List Data.
   • In the Data panel, hold down the Ctrl key and select Employee_ID and Birth_Date. Drag them to the List variables role.
   • Right-click the Birth_Date column and select Properties.
   • In the Birth_Date Properties window, click Change.
   • In the Format window, select MMDDYYw.d and change the overall width to 10.
   • Click OK > OK to return to the task window.
   
   
2. Modify the task code to insert a WHERE statement in the PROC PRINT step of the program. The WHERE statement should select only those employees with an October birthday. Add the code to the BeforeRUNStatement insertion point.
   
   Hint: Use the MONTH function. Documentation about this function can be found by accessing online Help and searching for MONTH Function.
   
   

   Solution:

   • Click the Preview code button to open the Code Preview for Task window.
   • Select the Show custom code insertion points check box.
   • Scroll down in the code and locate the BeforeRUNStatement insertion point. This location is below the FORMAT statement and above the RUN statement for PROC PRINT.
   • Click in the BeforeRUNStatement insertion point immediately below the FORMAT statement and above the RUN statement for PROC PRINT.
   • Enter the provided code:

     where month(Birth_Date) = 10;

   • Close the Code Preview for Task window.
   
   
3. Do not print the row numbers.
   
   

   Solution:

   • Select Options in the selection pane.
   • Clear the Print the row number check box.
   
   
4. Add the title to Employees with October Birthdays.
   
   

   Solution:

   • Select Titles in the selection pane.
   • Clear the Use default text check box and delete the default text.
   • Enter Employees with October Birthdays.
   
   
5. Modify the task properties to change the task label to Oct Birthday List.
   
   

   Solution:

   • Select Properties in the selection pane.
   • Click Edit.
   • Delete the text in the Label field and enter Oct Birthday List.
   • Click OK.
   
   
6. Generate the report. What is the birth date for Employee ID 120265?
   
   

   Solution:

   Click Run to generate the reports. The birth date for Employee ID 120265 is 10/04/1959.
   
7. Close all tabs except for the process flow, and save the Lesson3 project.
   
   
   

Practice Level 1: Using the Bar Chart Wizard

The employee_master table includes salary information for employees. In this practice, you use the Bar Chart Wizard to create a three-dimensional horizontal bar chart for Salary by Country.

1. In the Lesson3 project, select the employee_master table in the Project pane or process flow and use the Tasks pane to launch the Bar Chart Wizard.
   
   

   Solution:

   • In the Lesson3 project, highlight the employee_master table in the Project pane or process flow.
   • If necessary, in the Tasks pane, select SAS Tasks to view all task categories.
   • Expand Graph and double-click Bar Chart Wizard. Note: Be careful to start the Bar Chart Wizard, not the task.
   
   
2. In Step 1 of the Bar Chart Wizard, modify the data to exclude the US (United States).
   
   

   Solution:

   • Apply a filter to the employee_master table by clicking Edit.
   • Select Country from the first drop-down list and Not equal to from the second drop-down list.
   • Next to the third box, click the ellipsis (Value Selector) to retrieve specific values from the data.
   • Select US and click OK.
   • No other filter condition is required. Click OK.
   • Click Next.
   
   
3. In Step 2 of the Bar Chart Wizard, create a horizontal bar chart that shows the sum of Salary for each country. Arrange the bars in descending height.
   
   

   Solution:

   • Select the Horizontal bar chart check box.
   • To create a separate bar for each country, select Country in the Bars drop-down list.
   • To order the bars in descending height or length, click the Properties icon. In the Properties window, select Descending bar height and click OK.
   • To set the height of the bars, select Salary from the Bar length drop-down list.
   • Click Next.
   
   
4. In Step 3 of the Bar Chart Wizard, change the following attributes:
   
   • Create a three-dimensional chart.
   • Color each bar differently.
   • Print data labels at the top of each bar with the sum of Salary.
   • Label the bar length (horizontal) axis as Total Salary.
   • Turn on reference lines.
   
   

   Solution:

   • Select the 3D chart check box.
   • In the Color bars by drop-down list, select Bar category to color each bar differently.
   • Select the Data labels check box and select Sum.
   • Click Axis Labels, enter Total Salary as the Bar length label, and click OK.
   • Select the Use reference lines check box.
   • Click Next.
   
   
5. In Step 4 of the Bar Chart Wizard, use Total Salary for Each Country as the graph title and Excludes the United States as the footnote.
   
   

   Solution:

   • Enter Total Salary for Each Country in the Graph text box.
   • Enter Excludes the United States in the Footnote text box.
   
   
6. Run the task. What is the total of annual salaries in Germany (DE)?
   
   

   Solution:

   Click Finish. The results appear on a new tab in the work area. The total of annual salaries in Germany (DE) is $3,867,800.
   
7. Close all tabs except for the process flow, and save the Lesson3 project.
   
   
   

Practice Level 2: Using the Pie Chart Task

The employee_master table includes salary information for employees. In this practice, you use the Pie Chart task to create a pie chart for Salary by Department.

1. In the Lesson3 project, select the employee_master table in the Project pane or process flow and use the Tasks pane to launch the Bar Chart Wizard.
   
   

   Solution:

   • In the Lesson3 project, highlight the employee_master table in the Project pane or process flow.
   • If necessary, in the Tasks pane, select SAS Tasks to view all task categories.
   • Expand Graph and double-click Pie Chart.
   
   
2. Use the Pie Chart task to generate a simple pie chart where the slices represent the sum of Salary for each Department.
   
   • Exclude the Sales Department from the report.
   • Use a layout that shows the department name and salary total outside each slice, and the percentage of total salary on the inside of each slice.
   • Change the title to Total Salary by Department and the footnote to Figures exclude the Sales Department.
   • Change the task label to Dept Salaries Pie/No Sales.
   • Create the graph in PDF as well as in HTML format.
   
   

   Solution:

   • Verify that Simple Pie is selected as the type of chart to create.
   • Select Data in the selection pane.
   • Drag Department to the Column to chart role and Salary to the Sum of role.
   • Exclude the Sales Department from the report.
     • Apply a filter to the employee_master table by clicking Edit.
     • Select Department from the first drop-down list and Not equal to from the second drop-down list.
     • Next to the third box, click the ellipsis (Value Selector) to retrieve specific values from the data.
     • Select Sales and click OK.
     • No other filter condition is required. Click OK.
   • Use a layout that shows the department name and salary total outside each slice, and the percentage of total salary on the inside of each slice.
     • Select Layout in the selection pane.
     • Verify that the name and statistic value will be displayed outside of each slice.
     • Select Inside to have the percentage be displayed inside each slice.
   • Change the title to Total Salary by Department and the footnote to Figures exclude the Sales Department.
     • Select Titles in the selection pane.
     • For the graph title, clear the Use default text check box and enter Total Salary by Department.
     • Select Footnote in the Section pane, clear the Use default text check box, and enter Figures exclude the Sales Department.
   • Change the task label to Dept Salaries Pie/No Sales.
     • Select Properties in the selection pane and click Edit.
     • In the General panel, change the task label to Dept Salaries Pie/No Sales.
   • Create the graph in PDF as well as in HTML format.
     • Select Results in the selection pane of the Properties window.
     • Select Customize result formats, styles, and behavior. Verify that HTML is selected, and select the PDF check box to create those result formats.
     • Click OK.
   
   
3. Run the task. What percentage of total salaries does the Engineering Department account for?
   
   

   Solution:

   Click Run to generate the report. A separate tab appears, displaying the task results. The Engineering Department accounts for 5.51%.
   
4. If you are completing the Challenge Practice: Isolating a Slice in a Pie Chart, leave the Dept Salaries Pie/No Sales tab open. If not, close all tabs except for the process flow, and save the Lesson3 project.
   
   
   

Practice Challenge: Isolating a Slice in a Pie Chart

In this practice, you isolate a pie slice by offsetting (or "exploding") the slice.

1. If you haven't already, complete the Level 2: Using the Pie Chart Task practice to generate a pie chart that shows total salary by department.
   
   
   
2. Modify the Pie Chart task to slightly separate the Engineering slice from the remainder of the pie. This can be done by inserting the explode="Engineering" option at the end of the PIE statement. Add the code to the InGraphOptions insertion point.
   
   

   Solution:

   • On the Dept Salaries Pie/No Sales tab, click the Modify Task icon from the task toolbar.
   • Click Preview Code and select the Show custom code insertion points check box.
   • Scroll down in the code and locate the InGraphOptions insertion point. This location is after the NOHEADING option and before the semicolon that closes the PIE statement.
   • Enter the provided code. Note: Engineering is case sensitive.

     explode="Engineering"

   • Close the Code Preview for Task window.
   
   
3. Generate the report and examine the results.
   
   

   Solution:

   Click Run to generate the report. A separate tab appears, displaying the task results. The pie slice for Engineering should be isolated, or offset.
   
4. Close all tabs except for the process flow, and save the Lesson3 project.
   
   
   

Practice Level 1: Creating a Table Using the Query Builder

In this practice, you use the Query Builder to create a table with San Diego employees sorted by Postal_Code.

1. In the Lesson4 project, add a new process flow and rename it Practices. Then add the employee_addresses table. Note: If you do not have the Lesson4 project, you can create a new project.
   
   

   Solution:

   • Select File > New > Process flow.
   • Right-click Process Flow in the Project pane and select Rename. Enter Practices.
   • Select File > Open and navigate to the course data location.
   • Select employee_addresses > Open. The data appears on a new tab in the work area.
   
   
2. Use the Query Builder to create a query named San Diego Employees Query and a table named SanDiegoEmployees.
   
   • Include these columns: Employee_ID, Employee_Name, Street_Number, Street_Name, and Postal_Code.
   • Filter the data to include only employees from San Diego.
     Note: To select from a list of values, click the down arrow (Get Values) at the end of the Value box. If you enter data values, remember that character values are case sensitive (for example, "CA" is not the same as "Ca").
   • Order the results by ascending Postal_Code.
   
   

   Solution:

   • Click Query Builder on the data grid toolbar.
   • Enter San Diego Employees Query in the Query name field.
     • Click Change next to the Output name field.
     • Enter SanDiegoEmployees in the File name field and click Save.
   • Double-click the following columns to select them: Employee_ID, Employee_Name, Street_Number, Street_Name, and Postal_Code.
   • Filter the data to include only employees from San Diego.
     • Click the Filter Data tab.
     • Drag and drop City to the Filter Data tab to start the New Filter Wizard.
       • In Step 1, verify that Equal to is selected as the operator.
       • Click the down arrow (Get Values) at the end of the Value box.
       • On the Values tab, click Get Values and select San Diego.
       • Click Next.
       • In Step 2, verify the filter and click Finish.
   • Order the results by ascending Postal_Code.
     • Click the Sort Data tab.
     • Drag and drop Postal_Code onto the Sort Data tab and verify that Ascending is the selected sort direction.
   
   
3. Run the query. How many rows are in the new SanDiegoEmployees table?
   
   

   Solution:

   Click Run to execute the query. A new tab appears in the work area, displaying the results. There are 112 rows in the table.
   
4. Close all tabs except for the process flow, and save the Lesson4 project.
   
   
   

Practice Level 2: Using a Compound Filter in the Query Builder to Create a Table

In this practice, you use the Query Builder to create a table that includes all employees with the word Chief or Manager in their job titles.

1. In the Lesson4 project, add the employee_master table to the Practices process flow. Note: If you do not have the Lesson4 project, you can create a new project.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
2. Use the Query Builder to create a query named Offsite Meeting Query and a table named meeting_emps.
   
   • Include these columns: Employee_ID, Employee_Name, Department, and Job_Title.
   • Filter the data to keep rows where the Job_Title contains the word Chief or Manager.
     Note: Remember that character values are case sensitive.
   • Order the output table by ascending Department and then Employee_ID.
   
   

   Solution:

   • Click Query Builder on the data grid toolbar.
   • Enter Offsite Meeting Query in the Query name field.
     • Click Change next to the Output name field.
     • Enter meeting_emps in the File name field and click Save.
   • Double-click the following columns to select them: Employee_ID, Employee_Name, Department, and Job_Title.
   • Filter the data to keep rows where the Job_Title contains the word Chief or Manager.
     • Click the Filter Data tab.
     • Drag and drop Job_Title to the Filter Data tab to start the New Filter Wizard.
       • In Step 1, select Contains as the operator.
       • Enter Chief in the Value field. Click Next.
       • In Step 2, verify the filter and click Finish.
     • Drag and drop Job_Title a second time onto the Filter Data tab to start the New Filter Wizard.
     • In Step 1, select Contains as the operator.
     • Enter Manager in the Value field. Click Next.
     • In Step 2, verify the filter and click Finish.
     • Change the operator to OR.
   • Order the results by ascending Department and then Employee_ID.
     • Click the Sort Data tab.
     • Drag and drop Department onto the Sort Data tab and verify that Ascending is the selected sort direction.
     • Drag and drop Employee_ID onto the Sort Data tab and verify that Ascending is the selected sort direction.
   
   
3. Run the query. How many rows are in the new meeting_emps table?
   
   

   Solution:

   Click Run to execute the query. A new tab appears in the work area, displaying the results. There are 81 rows in the table.
   
4. Close all tabs except for the process flow, and save the Lesson4 project.
   
   
   

Practice Challenge: Using an Advanced Filter in the Query Builder

In this practice, you use the Query Builder to create a table with employees who retired in December, regardless of the retirement year.

1. If necessary, in the Lesson4 project, add the employee_master table to the Practices process flow.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
2. Use the Query Builder to create a query named December Retirees Query and a table named DecemberRetirees.
   
   • Include these columns: Employee_ID, Employee_Name, Department, Manager_ID, and Termination.
   • Label the Termination column as Termination Date and apply the DATE9. format.
   • Include only those employees who retired in December.
     Note: Use the MONTH function. Documentation about this function can be found by accessing online Help and searching for MONTH Function.
   • Order the output table by ascending Manager_ID and then Employee_ID.
   
   

   Solution:

   • Click Query Builder on the data grid toolbar.
   • Enter December Retirees Query in the Query name field.
     • Click Change next to the Output name field.
     • Enter DecemberRetirees in the File name field and click Save.
   • Double-click the following columns to select them: Employee_ID, Employee_Name, Department, Manager_ID, and Termination.
   • Label the Termination column as Termination Date and apply the DATE9. format.
     • On the Select Data tab, select Termination and click the Properties icon to open the Properties window for the column.
     • Enter Termination Date in the Label field.
     • To apply a format to this column, click Change.
     • In the Formats window, select Date from the Categories pane and DATEw.d from the Formats pane. Change the overall width to 9. Click OK.
     • Click OK.
   • Include only those employees who retired in December.
     • Click the Filter Data tab.
     • Click the New Filter icon and select Advanced Filter.
     • Click Next.
     • In Step 2, expand the Functions folder and find the MONTH function.
     • Double-click MONTH Function to add it to the expression.
     • Select Favorites > Selected Columns to quickly collapse the Functions folder.
     • Double-click Termination to add the column to the expression.
     • Move the cursor to the end of the expression and enter =12.
     • Click Validate to validate the syntax. The Validate window should indicate that the expression syntax is valid.
     • Close the window.
     • Click Next to verify the filter.
     • Click Finish.
   • Order the results by ascending Manager_ID and then Employee_ID.
     • Click the Sort Data tab.
     • Drag and drop Manager_ID onto the Sort Data tab and verify that Ascending is the selected sort direction.
     • Drag and drop Employee_ID onto the Sort Data tab and verify that Ascending is the selected sort direction.
   
   
3. Run the query. How many rows are in the new DecemberRetirees table?
   
   

   Solution:

   Click Run to execute the query. A new tab appears in the work area, displaying the results. There are 32 rows in the table.
   
4. Close all tabs except for the process flow, and save the Lesson4 project.
   
   
   

Practice Level 1: Adding a Computed Column to a New Table

In this practice, you use the Query Builder to create New_Salary, which is the current salary plus a 2% raise.

1. In the Lesson4 project, add the employee_master table to the Practices process flow.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
2. Use the Query Builder to create a query named New Salary Query and a table named new_salary.
   
   • Include these columns: Employee_ID, Employee_Name, Salary, and Hire_Date.
   • Modify the properties of the Salary column to change the column name to Old_Salary.
   • Create a new column named New_Salary that is the current salary plus a 2% raise. Apply the DOLLAR12.2 format to the column. Hint: Multiply Old_Salary by 1.02.
   • Arrange the columns in this order: Employee_ID, Employee_Name, Old_Salary, New_Salary, and Hire_Date.
   • Include only active employees who have a missing value for Termination.
   • Order the results by ascending Employee_ID.
   
   

   Solution:

   • Click Query Builder on the data grid toolbar.
   • Enter New Salary Query in the Query name field.
     • Click Change next to the Output name field.
     • Enter new_salary in the File name field and click Save.
   • Double-click the following columns to select them: Employee_ID, Employee_Name, Salary, and Hire_Date.
   • Modify the properties of the Salary column to change the column name to Old_Salary.
     • Select Salary and click the Properties icon to open the Properties window for the column.
     • Enter Old_Salary in the Column Name field.
     • Click OK.
   • Create a new column named New_Salary that is the current salary plus a 2% raise. Apply the DOLLAR12.2 format to the column.
     • To add the New_Salary column, begin by clicking the Add A New Computed Column icon on the Select Data tab, or you can select Computed Columns > New.
       • In Step 1, select Advanced expression and click Next.
       • In Step 2, expand Selected Columns.
       • Double-click Salary(Old_Salary) to add the column to the expression. Select or enter the multiplication operator (*). Enter 1.02:

             t1.Salary * 1.02                

     • Click Next.
     • In Step 3, enter New_Salary in the Column Name field.
     • To apply a format to this column, click Change. In the Formats window, select Currency from the Categories pane and DOLLARw.d from the Formats pane.
     • Change the overall width to 12 and the decimal places to 2.
     • Click OK.
     • Click Next.
     • In Step 4, review the summary of the new column's properties and click Finish.
   • Arrange the columns in this order: Employee_ID, Employee_Name, Old_Salary, New_Salary, and Hire_Date.
     • On the Select Data tab, select New_Salary and click the up arrow (Move Up) icon.
   • Include only active employees who have a missing value for Termination.
     • Click the Filter Data tab.
     • Drag and drop Termination to the Filter Data tab to start the New Filter Wizard.
   • In Step 1, change the operator to is missing.
   • Click Next.
   • In Step 2, verify the filter and click Finish.
   • Order the results by ascending Employee_ID.
     • Click the Sort Data tab.
     • Drag and drop Employee_ID onto the Sort Data tab and verify that Ascending is the selected sort direction.
   
   
3. Run the query. What is the New_Salary for John Hornsey (120106)?
   
   

   Solution:

   Click Run to execute the query. A new tab appears in the work area, displaying the results. John Hornsey's New_Salary value is $37,123.92.
   
4. Close all tabs except for the process flow, and save the Lesson4 project.
   
   
   

Practice Level 2: Using a Function to Create a Computed Column

In this practice, you use the Query Builder to create a table from the employee_donations table with a new column that indicates the total contributions from each employee.

1. In the Lesson4 project, add the employee_donations table to the Practices process flow.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_donations > Open. The data appears on a new tab in the work area.
   
   
2. Use the Query Builder to create a query named Donations Query and a table named total_donations.
   
   • Include these columns: Employee_ID, Recipients, and Paid_By.
   • Create a new column named Total_Donations that totals the values of the four quarterly donations. Apply the DOLLAR12.2 format to the column. Note: Use a function to calculate the total and thereby ignore any missing values that might be in the input columns.
   • Order the results by ascending Employee_ID.
   
   

   Solution:

   • Click Query Builder on the data grid toolbar.
   • Enter Donations Query in the Query name field.
     • Click Change next to the Output name field.
     • Enter total_donations in the File name field and click Save.
   • Double-click the following columns to select them: Employee_ID, Recipients, and Paid_By.
   • Create a new column named Total_Donations that totals the values of the four quarterly donations. Apply the DOLLAR12.2 format to the column.
     • To add the Total_Donations column, begin by clicking the Add A New Computed Column icon on the Select Data tab, or you can select Computed Columns > New.
       • In Step 1, select Advanced expression and click Next.
       • In Step 2, expand the Functions folder and find the SUM function.
       • Double-click SUM Function to add it to the expression.
       • Select Favorites > Tables to quickly collapse the Functions folder.
     • Expand Tables > t1 (employee_donations). Double-click Qtr1 to add the column to the expression. Select or enter a comma. Double-click Qtr2 to add the column to the expression. Select or enter a comma. Double-click Qtr3 to add the column to the expression. Select or enter a comma. Double-click Qtr4 to add the column to the expression. Click Next.
     • In Step 3, enter Total_Donations in the Column Name field.
     • To apply a format to this column, click Change. In the Formats window, select Currency from the Categories pane and DOLLARw.d from the Formats pane.
     • Change the overall width to 12 and the decimal places to 2.
     • Click OK.
     • Click Next.
     • In Step 4, review the summary of the new column's properties and click Finish.
   • Order the results by ascending Employee_ID.
     • Click the Sort Data tab.
     • Drag and drop Employee_ID onto the Sort Data tab and verify that Ascending is the selected sort direction.
   
   
3. Run the query. What are the total donations made by employee ID 120275?
   
   

   Solution:

   Click Run to execute the query. A new tab appears in the work area, displaying the results. The Total_Donations value for Employee_ID 120275 is $60.00.
   
4. Close all tabs except for the process flow, and save the Lesson4 project.
   
   
   

Practice Challenge: Using a Date Function for a Computed Column

In this practice, you use the Query Builder to create a table that includes active employees (those with no termination date) and how old the employee was when hired.

1. In the Lesson4 project, add the employee_master table to the Practices process flow.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
2. Use the Query Builder to create a query named Age Hired Query and a table named age_hired.
   
   • Include these columns: Employee_ID, Employee_Name, Birth_Date, and Hire_Date.
   • Create a new column named Hired_Age that calculates the number of years between the employee's birth date and the hire date. Format the new column to display without decimal places.
     
     Note: Use the YRDIF function. For the default calculation of someone's age, you need only two arguments to the function, a start date and an end date. If you want to read more about other ways to calculate date differences (such as using a standard 30-day month and 360-day year instead of actual days), documentation about this function can be found by accessing online Help and searching for YRDIF Function.
     
     
   • Include only employees without a termination date.
   • Order the results by ascending Employee_ID.
   
   

   Solution:

   • In the Lesson4 project, right-click the employee_master table in the process flow and select Query Builder.
   • Enter Age Hired Query in the Query name field.
     • Click Change next to the Output name field.
     • Enter age_hired in the File name field and click Save.
   • Double-click the following columns to select them: Employee_ID, Employee_Name, Birth_Date, and Hire_Date.
   • Create a new column named Hired_Age that calculates the number of years between the employees' birth date and the hire date. Format the new column to display without decimal places.
     • To add the Hired_Age column, begin by clicking the Add A New Computed Column icon on the Select Data tab, or you can select Computed Columns > New.
       • In Step 1, select Advanced expression and click Next.
       • In Step 2, expand the Functions folder and find the YRDIF function. Note: The syntax for the YRDIF function is to the right of the list of functions.
       • Double-click YRDIF Function to add it to the expression.
       • Select Favorites > Tables to quickly collapse the Functions folder.
     • Expand Tables > t1 (employee_master). Double-click Birth_Date to add the column as the first argument in the expression. Double-click Hire_Date to add the column as the second argument in the expression. Delete the comma and third argument because the default value of 'AGE' is correct.

       YRDIF(t1.Birth_Date, t1.Hire_Date)

     • Click Next.
     • In Step 3, enter Hired_Age in the Column Name field.
     • To apply a format to this column, click Change. In the Formats window, select Numeric from the Categories pane and w.d from the Formats pane.
     • Change the overall width to 3 and verify that the number of decimal places is 0.
     • Click OK.
     • Click Next.
     • In Step 4, review the summary of the new column's properties and click Finish.
   • Include only employees without a termination date.
     • Click the Filter Data tab.
     • Drag Termination to the Filter Data tab.
     • In Step 1, change the operator to Is missing.
     • Click Next to verify the filter and click Finish.
   • Order the results by ascending Employee_ID.
     • Click the Sort Data tab.
     • Drag and drop Employee_ID onto the Sort Data tab and verify that Ascending is the selected sort direction.
   
   
3. Run the query. How old what Sherie Sheedy when hired?
   
   

   Solution:

   Click Run to execute the query. A new tab appears in the work area, displaying the results. Sherie Sheedy was hired when she was 21.
   
4. Close all tabs except for the process flow, and save the Lesson4 project.
   
   
   

Practice Level 1: Using the Query Builder to Group and Summarize Data

In this practice, you use the Query Builder to create an output table with the average payroll by Department.

1. If necessary, in the Lesson4 project, add the employee_master table to the Practices process flow.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
2. Use the Query Builder to create a query named Average Salary per Department Query and an output table named AvgSal_Dept.
   
   • Include only the Department and Salary columns in the query.
   • Select the AVG (average) statistic for Salary.
   • Sort the table in increasing order of AVG_of_Salary.
   
   

   Solution:

   • Right-click the employee_master table in the process flow and select Query builder.
     • Enter Average Salary per Department Query in the Query name field.
     • Click Change next to the Output name field.
     • Enter AvgSal_Dept in the File name field and click Save.
   • Double-click the following columns to select them: Department and Salary.
   • Select the AVG (average) statistic for Salary.
     • Click in the Summary column for the Salary column.
     • Select the AVG statistic from the drop-down list.
   • Sort the table in increasing order of AVG_of_Salary.
     • Click the Sort Data tab.
     • Drag and drop AVG_of_Salary onto the tab area.
     • Verify that the sort direction is set to Ascending.
   
   
3. Run the query. Which department has the highest average salary?
   
   

   Solution:

   Click Run to execute the query. A new tab appears in the work area displaying the results. The department with the highest average Salary value is Executives.
   
4. Close all tabs except for the process flow, and save the Lesson4 project.
   
   
   

Practice Level 2: Using the Query Builder to Group, Summarize, and Filter Data

In this practice, you use the Query Builder to create an output table with an employee count, as well as the average and total payroll by Department.

1. If necessary, in the Lesson4 project, add the employee_master table to the Practices process flow.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
2. Use the Query Builder to create a query named Salary Summary by Dept Query and an output table named salary_summary.
   
   • Include Department, Employee_ID, and Salary (twice) on the Select Data tab.
   • Select the appropriate statistics to calculate the number of employees, the average salary, and the total salary for each department.
   • If necessary, modify the formats applied to the AVG_of_Salary and SUM_of_Salary columns to round values to the nearest dollar.
   • Filter the query to include only those departments with more than 10 employees.
   • Sort the data by decreasing department size, based on the number of employees.
   
   

   Solution:

   • Right-click the employee_master table in the process flow and select Query builder.
     • Enter Salary Summary by Dept Query in the Query name field.
     • Click Change next to the Output name field.
     • Enter salary_summary in the File name field and click Save.
   • Double-click the following columns to select them: Department, Employee_ID, and Salary. Double-click Salary a second time.
   • Select the appropriate statistics to calculate the number of employees, the average salary, and the total salary for each department.
     • Click in the Summary column for the Employee_ID column. Select the COUNT statistic from the drop-down list.
     • Click in the Summary column for the first Salary column. Select the AVG statistic from the drop-down list.
     • Click in the Summary column for the second Salary column. Select the SUM statistic from the drop-down list.
   • If necessary, modify the formats applied to the AVG_of_Salary and SUM_of_Salary columns to round values to the nearest dollar.
     • Verify that DOLLAR13. is specified as the format for both AVG_of_Salary and SUM_of_Salary.
   • Filter the query to include only those departments with more than 10 employees.
     • Click the Filter Data tab.
     • Drag COUNT_of_Employee_ID from the list of columns to the Filter the summarized data pane.
     • In the New Filter Wizard, select Greater than in the Operator field.
     • Enter 10 as the value.
     • Click Finish.
   • Sort the data by decreasing department size, based on the number of employees.
     • Click the Sort Data tab.
     • Drag and drop COUNT_of_Employee_ID onto the tab area.
     • Change the sort direction to Descending.
   
   
3. Run the query. Out of the departments that have more than 10 employees, which has the fewest employees?
   
   

   Solution:

   Click Run to execute the query. A new tab appears in the work area displaying the results. Out of the departments that have more than 10 employees, the Concession Management department has the fewest: 11.
   
4. Close all tabs except for the process flow, and save the Lesson4 project.
   
   
   

Challenge: Using the Query Builder to Produce a Top-Ten Report

When using the Query Builder, you can choose to generate your results in any one of three formats: data table, data view, or report. In this practice, you override the default setting of producing a data table to save the query results as a report.

1. If necessary, in the Lesson4 project, add the employee_master table to the Practices process flow.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
2. Use the Query Builder to create a query named Top 10 Paid Jobs Titles.
   
   • Calculate the average salary for each job title. Rename the column AverageSalary and apply a format to round to the nearest dollar.
   • Filter the query to exclude any employees with Chief as part of the job title.
   • Sort the data by decreasing sequence of average salary.
   
   

   Solution:

   • Right-click the employee_master table in the process flow and select Query builder.
     • Enter Top 10 Paid Job Titles in the Query name field.
   • Calculate the average salary for each job title. Rename the column AverageSalary and apply a format to round to the nearest dollar.
     • Double-click the following columns to select them: Job_Title and Salary. .
     • Click in the Summary column for the Salary column. Select the AVG statistic from the drop-down list.
     • Select AVG_of_Salary and click the Properties icon to open the Properties window for the column.
     • Enter AverageSalary in the Column Name field.
     • Verify that the format contains no decimal place. If necessary, click Change next to the Format field.
     • Change the decimal places to 0.
     • Click OK.
     • Click OK to close the Properties window.
   • Filter the query to exclude any employees with Chief as part of the job title.
     • Click the Filter Data tab.
     • Drag Job_Title to the Filter the raw data pane.
     • In the New Filter Wizard, select Does not contain in the Operator field.
     • Enter Chief as the value.
     • Click Finish.
   • Sort the data by decreasing sequence of average salary.
     • Click the Sort Data tab.
     • Drag and drop AverageSalary onto the tab area.
     • Change the sort direction to Descending.
   
   
3. Modify the query options to produce the following results:
   
   • Generate a report, rather than a data table.
   • Limit the number of output rows to 10.
   • Title the report Top 10 Average Salaries by Job Title.
     Hint: Look for the appropriate options by selecting Options > Options for This Query.
   
   

   Solution:

   • Generate a report, rather than a data table.
     • Select Options > Options for This Query.
     • In the Results format pane, select the Override the corresponding default settings in Tools->Options check box and select Report.
   • Limit the number of output rows to 10.
     • In the Results panel of the Query Options window, locate the Query limits pane.
     • Select the Limit number of rows to save in output check box and enter 10.
   • Title the report Top 10 Average Salaries by Job Title.
     • In the selection pane of the Query Options window, select Titles.
     • Select the Override the corresponding default settings in Tools->Options check box for the title text.
     • Enter Top 10 Average Salaries by Job Title.
     • Click OK.
   
   
4. Run the query. Which job has the lowest average salary of the top 10 jobs?
   
   

   Solution:

   Click Run to execute the query. A new tab appears in the work area displaying the results. Marketing Manager has the lowest average salary out of the top 10, at $81,242.
   
5. Close all tabs except for the process flow, and save the Lesson4 project.
   
   
   

Practice Level 1: Joining Data from Two Tables

In this practice, you use the Query Builder to create a table whose data results from a join of two tables.

1. If necessary, in the Lesson4 project, add the employee_master and employee_addresses tables to the Practices process flow.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   • Select File > Open and navigate to the course data location.
   • Select employee_addresses > Open. The data appears on a new tab in the work area.
   
   
2. Use the Query Builder to join employee_master and employee_addresses to create a table named payroll_location.
   
   • Label the query Payroll Location Join Query.
   • Open the Tables and Joins window to verify that the join between the two tables is on the Employee_ID column.
   • Include the following columns in the order listed: Employee_ID (from the employee_master table), Employee_Name (from the employee_master table), Birth_Date, Street_Number, Street_Name, City, State, Country, and Salary.
   
   

   Solution:

   • In the process flow, hold down the Ctrl key and select employee_master and employee_addresses.
   • Right-click one of the two tables and select Query builder.
     • Enter Payroll Location Join Query in the Query name field.
     • Click Change next to the Output name field.
     • Enter payroll_location in the File name field and click Save.
   • Open the Tables and Joins window to verify that the join between the two tables is on the Employee_ID column.
     • Click Join Tables to open the Tables and Joins window.
     • Notice that an inner join on Employee_ID is being performed.
     • Click Close.
   • Double-click the following columns to select them: Employee_ID (from the employee_master table), Employee_Name (from the employee_master table), Birth_Date, Street_Number, Street_Name, City, State, Country, and Salary.
   
   
3. Apply the DOLLAR12.2 format to the Salary column and the DATE9. format to the Birth_Date column.
   
   

   Solution:

   • Select Salary and click the Properties icon to open the Properties window for the column.
   • Click Change.
   • In the Formats window, select Currency from the Categories pane and DOLLARw.d from the Formats pane.
   • Change the overall width to 12 and decimal places to 2.
   • Click OK > OK.
   • Select Birth_Date and click the Properties icon to open the Properties window for the column.
     • Click Change.
     • In the Formats window, select Date from the Categories pane and Datew.d from the Formats pane.
     • Change the overall width to 9.
     • Click OK > OK.
   
   
4. Create a new column named Bonus that represents 5% of the Salary column. Apply the DOLLAR12.2 format.
   
   

   Solution:

   • Click the Add A New Computed Column icon on the Select Data tab.
     • In Step 1, select Advanced expression and click Next.
     • In Step 2, type or click to create the following expression:

         Salary * .05

     • Note: If you select Salary rather than enter it in the expression, it's included as either t1.Salary or t2.Salary.
     • Click Next.
     • In Step 3, enter Bonus in the Column Name field.
     • Click Change next to the Format field.
     • Select Currency from the Categories pane and DOLLARw.d from the Formats pane.
     • Change the overall width to 12 and the decimal places to 2.
     • Click OK.
     • Click Next > Finish.
   
   
5. Include only active employees or those that do not have a value for Termination. Sort the data by ascending Employee_ID.
   
   

   Solution:

   • Click the Filter Data tab.
     • Drag Termination to the Filter Data tab. In Step 1, change the operator to Is missing.
     • Click Finish.
   • Sort the data by ascending Employee_ID.
     • Click the Sort Data tab.
     • Drag and drop Employee_ID onto the Sort Data tab and verify that Ascending is the selected sort direction.
   
   
6. Run the query. What street does John Hornsey live on?
   
   

   Solution:

   Click Run to execute the query. A new tab appears in the work area displaying the results. John Hornsey lives on Sherwood Road.
   
7. Close all tabs except for the process flow, and save the Lesson4 project.
   
   
   

Practice Level 2: Joining Data from Three Tables

In this practice, you use the Query Builder to create a table whose data results from a join of three tables.

1. If you haven't already, complete the Level 1: Joining Data from Two Tables practice.
   
   
   
2. Add the country_lookup SAS table to the Practices process flow in the Lesson4 project.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select country_lookup > Open. The data appears on a new tab in the work area.
     Note: Be care to select the country_lookup SAS table and NOT the Excel workbook.
   
   
3. Modify the Payroll Location Join Query task. Manually join country_lookup and employee_addresses by the common column.
   
   Note: The common column might be named differently in the two tables.
   
   

   Solution:

   • Right-click Payroll Location Join Query in the process flow and select Modify.
   • In the Query Builder, click Add Tables.
     • Select the country_lookup table and select Open.
     • A message appears and indicates that Enterprise Guide cannot automatically join the tables by common columns. Click OK.
     • In the Tables and Joins window, select Country in the employee_addresses table.
     • Select Country a second time and drag the cursor to connect with Country_Key in the country_lookup table.
     • The Join Properties window appears automatically. Verify that the join condition is t2.Country = t3.Country_Key.
       Note: The table alias name employee_addresses table (t2) might differ depending on the order in which the table was initially added to the query.
     • Click OK > Close.
   
   
4. Remove the Country column on the Select Data tab and replace it with the column representing the full country name found in the country_lookup table.
   
   

   Solution:

   • Select Country on the Select Data tab and click the Delete icon.
   • Double-click Country_Name from the country_lookup table to add it to the query.
     
   • Select Country_Name on the Select Data tab and click the up arrow (Move Up) icon to move it after the State column.
   
   
5. Run the query. In what city and country does John Hornsey live?
   
   

   Solution:

   Click Run to execute the query. A new tab appears in the work area displaying the results. John Hornsey lives in Sydney, Australia.
   
6. Close all tabs except for the process flow, and save the Lesson4 project.
   
   
   

Practice Challenge: Joining Data Using a Non-Equijoin Condition

In this practice, you use the Query Builder to join data from two sources where the join condition involves a non-equijoin condition.

Two columns in the bonus_schedule table, Employed_After and Employed_Before, define the time range for a given bonus percentage. To find the correct bonus percentage for each employee:

• Join the Hire_Date column from the employee_master table with the Employed_After column using the greater-than-or-equal-to operator.
• Set a second join with the Hire_Date and Employed_Before columns with the less-than-or-equal-to operator.

1. If necessary, in the Lesson4 project, add the employee_master and bonus_schedule tables to the Practices process flow.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   • Select File > Open and navigate to the course data location.
   • Select bonus_schedule > Open. The data appears on a new tab in the work area.
   
   
2. Because there are no matching columns between these two tables, a manual join is required. Join the tables so that Hire_Date is on or after the Employed_After column and is on or before the Employed_Before column.
   
   

   Solution:

   • In the process flow, hold down the Ctrl key and select the employee_master and bonus_schedule tables.
     • Right-click one of the tables and select Query builder. A message appears and indicates that Enterprise Guide cannot automatically join the tables by common columns.
     • Click OK.
     • In the Tables and Joins window, select Hire_Date in the employee_master table.
     • Select Hire_Date a second time, and drag the cursor to connect with Employed_After in the bonus_schedule table.
     • The Join Properties window appears automatically. Change the join condition to t1.Hire_Date >= t2.Employed_After.
     • Click OK.
     • Select Hire_Date again in the employee_master table.
     • Select Hire_Date a second time and drag the cursor to connect with Employed_Before in the bonus_schedule table.
     • The Join Properties window appears automatically. Change the join condition to t1.Hire_Date <= t2.Employed_Before.
     • Click OK.
     • Click Close.
   
   
3. Name the query Employees Bonuses Query and the table bonuses. Include the Employee_ID, Employee_Name, Hire_Date, Salary, and Bonus_Percent columns.
   
   

   Solution:

   • Enter Employees Bonuses Query in the Query name field.
   • Click Change and enter bonuses in the File name field.
   • Click Save.
   • On the Select Data tab, double-click the following columns to select them: Employee_ID, Employee_Name, Hire_Date, Salary, and Bonus_Percent.
   
   
4. Create a new column named Bonus_Amount that multiplies Salary by the percent value in the Bonus_Percent column. Display Bonus_Amount with a dollar sign, comma, and two decimal places.
   
   

   Solution:

   • Click the Add A New Computed Column icon on the Select Data tab
     • In Step 1, select Advanced expression > Next.
     • In Step 2, type or click to create the following expression:

           t1.Salary * t2.Bonus_Percent

   • Click Next.
   • In Step 3, enter Bonus_Amount in the Column Name field.
   • Click Change next to the Format field.
   • Select Currency from the Categories pane and DOLLARw.d from the Formats pane.
   • Change the overall width to 12 and the decimal places to 2.
   • Click OK.
   • Click Next > Finish.
   
   
5. Run the query. What are the Bonus_Percent and Bonus_Amount values for Riu Horsey?
   
   

   Solution:

   Click Run to execute the query. Riu's Bonus_Percent is 2.20% and his Bonus_Amount is $738.93.
   
6. Close all of the tabs except for the process flow and save the Lesson4 project.
   
   
   

Practice Level 1: Using the Summary Statistics Wizard

In this practice, you use the Summary Statistics Wizard to create a report that analyzes the salaries of all employees by Department. In addition, you export the output data to an Excel file as a step in the project.

1. In the Lesson5 project, create a new process flow named Practices and add the employee_master table.
   
   

   Solution:

   • If necessary, select File > New > Process flow. Note: If you do not have the Lesson5 project, select File > New > Project to create it.
   • Right-click the process flow in the Project pane and select Rename.
   • Enter Practices as the new name.
   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
2. Use the Summary Statistics Wizard to create a report on the Salary column by Department.
   
   • Include the mean, minimum, maximum, and median salary for each group and round values to the nearest dollar.
     Hint: To find the median statistic, click the Percentile tab in the Edit Statistics window.
   • Create a SAS data set named salary_stats that includes the calculated statistics.
   • Enter Summary Statistics for Salary by Department as the title and delete the footnote.
   
   

   Solution:

   • Highlight the employee_master table in the Project pane or process flow.
   • In the Tasks pane, expand Describe, and double-click Summary Statistics Wizard.
     • In Step 1, verify the data and click Next.
     • In Step 2, drag Salary to the Summary statistics of (Analysis variable) role.
     • Drag Department into the For each value of (Classification variable) role.
     • Click Next.
   • Include the mean, minimum, maximum, and median salary for each group and round values to the nearest dollar.
     • In Step 3, designate the statistics to include in the report.
     • Click Edit.
       • On the Basic tab, clear the check boxes for Standard deviation and Number of observations.
       • Change the number of decimal places to 0 (zero).
       • Click the Percentile tab and select Median.
       • Click OK.
   • Create a SAS data set named salary_stats that includes the calculated statistics.
     • Continuing in Step 3, select the Save statistics to data set check box.
     • Click Browse.
     • Enter salary_stats in the File name field and click Save.
     • Click Next.
   • Enter Summary Statistics for Salary by Department as the title and delete the footnote.
     • In Step 4, delete the default analysis title and enter Summary Statistics for Salary by Department.
     • Delete the default footnote text.
   
   
3. Run the task. What is the minimum Salary value for the Engineering department?
   
   

   Solution:

   Click Finish. The results appear on a new tab in the work area. The minimum Salary for the Engineering department is 33306.
   
4. Rename the task Salary by Dept Summary.
   
   

   Solution:

   • Right-click Summary Statistics in the Project pane and select Rename.
   • Enter Salary by Dept Summary.
   
   
5. As a step in the project, export the output data to an Excel file named SalaryStats.xlsx. Store the output file in the output folder in the course file location.
   
   

   Solution:

   • Select Share > Output Data > Export as a step in project from the task toolbar.
     • In Step 1 of the Export Wizard, verify that Summary Statistics for <library>.employee_master is highlighted.
     • Click Next.
     • In Step 2, select Microsoft Excel Workbooks (*.xlsx) as the output file type.
     • Click Next.
     • In Step 3, select the Use labels for column names check box.
     • Click Next.
     • In Step 4, change the name of the output file.
       • Click Browse and navigate to the output folder in the course file location.
       • Enter SalaryStats in the File name field.
       • Select Save.
   • Click Next to review the export settings.
   • Click Finish. A new tab for the task appears, indicating that the SalaryStats.xlsx file was successfully exported.
   
   
6. Close all tabs except for the process flow, and save the Lesson5 project. Notice that the Export File task was added to the process flow.
   
   
   

Practice Level 2: Using the Summary Statistics Task

In this practice, you use the Summary Statistics task to create a report that summarizes salaries by Department and Country. In addition, you limit the tables that appear in the generated results.

1. If necessary, in the Lesson5 project, create a new process flow named Practices and add the employee_master table.
   
   

   Solution:

   • If necessary, select File > New > Process flow.
   • Right-click the process flow in the Project pane and select Rename.
   • Enter Practices as the new name.
   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
2. Use the Summary Statistics task to summarize Salary by Department, and then by Country.
   
   • Include the mean, minimum, maximum, and median salary for each group and round values to two decimal places.
     Hint: To find the median statistic, click the Percentile tab in the Edit Statistics window.
   • Enter Summary Statistics for Salary by Department and Country as the title and delete the footnote.
   • Rename the task Salary by Dept, Country Summary.
   
   

   Solution:

   • Highlight the employee_master table in the Project pane or process flow.
   • In the Tasks pane, expand Describe, and double-click Summary Statistics.
   • Drag Salary to the Analysis variables role.
   • Drag Department to the Classification variables role. Then drag Country.
   • Include the mean, minimum, maximum, and median salary for each group and round values to two decimal places.
     • Select Statistics - Basic in the selection pane.
     • Clear the Standard deviation and Number of observations check boxes.
     • Select 2 for the maximum decimal positions.
     • Select Statistics - Percentiles in the selection pane.
     • Select the Median check box.
   • Use Summary Statistics for Salary by Department and Country as the analysis title and delete the footnote.
     • Select Titles in the selection pane.
     • Select Analysis in the Section pane and clear the Use default text check box.
     • Delete the current text and enter Summary Statistics for Salary by Department and Country.
     • Select Footnote in the Section pane and clear the Use default text check box.
     • Delete the default footnote text.
   • Rename the task Salary by Dept, Country Summary.
     • Select Properties in the selection pane.
     • Click Edit.
     • Delete the text in the Label field and enter Salary by Dept, Country Summary.
     • Click OK.
   
   
3. Run the task. What is the median Salary value for the Administration department in France (FR)?
   
   

   Solution:

   Click Run to run the task. A new tab appears in the work area displaying the results. The median Salary for the Administration department in France is 35545.50.
   
4. Modify the task to generate multiple reports for all combinations of the two classification variables. Use the Results panel and select All ways for the Combinations of classification variables option.
   
   

   Solution:

   • Click the Modify task icon from the task toolbar to reopen the Summary Statistics task.
   • Select Results in the selection pane.
   • Select All ways for the Combinations of classification variables option.
     
   
   
5. Run the task. Note: Four tables are included in the report, one for each combination of the classification variables.
   
   What is the median salary for all departments in all countries?
   
   

   Solution:

   Click Run to run the task. The results are updated on the Results tab. The median salary for all departments in all countries is 36336.38.
   
6. Modify the task again to include only the second and third tables from the previous results.
   
   

   Solution:

   • Click the Modify task icon from the task toolbar to reopen the Summary Statistics task.
   • Select Results in the selection pane.
   • Select Specify ways for the Combinations of classification variables option.
   • Enter 1 in the Specify ways box.
     
   
   
7. Run the task. What is the mean salary for all departments in Australia (AU)?
   
   

   Solution:

   Click Run to run the task. The results are updated on the Results tab. The mean salary for all departments in Australia (AU) is 40204.93.
   
8. What is the median salary for the Administration department in all countries?
   
   

   Solution:

   The median salary for the Administration department in all countries is 36396.00.
   
9. Close all tabs except for the process flow, and save the Lesson5 project.
   
   
   

Practice Challenge: Modifying the Summary Statistics Task Code to Include an Additional Statistic

The default placement of the overall summary (_TYPE_=0) is the first row of the output data set. The DESCENDTYPES option orders row in the output data set by descending _TYPE_ value. This places the overall summary value in the last row of the output data set.

In this practice, you insert custom code into the SAS program generated by the Summary Statistics task to reorder the rows in the output data set, placing the overall summary row last.

1. If necessary, in the Lesson5 project, create a new process flow named Practices and add the employee_master table.
   
   

   Solution:

   • If necessary, select File > New > Process flow.
   • Right-click the process flow in the Project pane and select Rename.
   • Enter Practices as the new name.
   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
2. Use the Summary Statistics task to create an output data set that contains the mean salary by Country. Use the following task settings:
   
   • Summarize Salary by Country.
   • Include only the mean statistic, and round values to two decimal places.
   • Create an output data set named mean_country_salaries.
   • From the drop-down list for the Combinations of classifications variable option, select Specify ways. Do not modify the default value for the Specify ways field.
   • Suppress all displayed output.
   • Rename the task as Salary by Country Summary.
   
   

   Solution:

   • Highlight the employee_master table in the Project pane or process flow.
   • In the Tasks pane, expand Describe, and double-click Summary Statistics.
   • Summarize Salary by Country.
     • Drag Salary to the Analysis variables role.
     • Drag Country to the Classification variables role.
   • Include only the mean statistic, and round values to two decimal places.
     • Select Statistics - Basic in the selection pane.
     • Clear the Standard deviation, Minimum, Maximum, and Number of observations check boxes.
     • Select 2 for the maximum decimal positions.
   • Create an output data set named mean_country_salaries.
     • Select Results in the selection pane.
     • Select the Save statistics to data set check box and click Browse.
     • Enter mean_country_salaries in the File name field and click Save.
   • In the Results panel, use the drop-down list for the Combinations of classifications variable option to select Specify ways.
   • In the Results panel, select the Suppress all displayed output check box.
   • Rename the task Salary by Country Summary.
     • Select Properties in the selection pane.
     • Click Edit.
     • Delete the text in the Label field and enter Salary by Country Summary.
     • Click OK.
   
   
3. Run the task. Notice that the overall summary row is first in the output data set.
   
   

   Solution:

   Click Run to run the task. A new tab appears in the work area displaying the results. Notice that the overall total (_TYPE_=0) appears as the first row.
   
4. Modify the task's underlying code to add the DESCENDTYPES option to the PROC MEANS statement. Add the option directly after the listed statistics in the InProcMeans insertion point.
   
   

   Solution:

   • Click the Modify task icon from the task toolbar to reopen the Summary Statistics task.
   • Click Preview code to customize the task code.
   • Click the Show custom code insertion points check box and scroll down until you locate the InProcMeans insertion point at the end of the PROC MEANS statement and before the semicolon.
   • Enter the DESCENDTYPES option:
     

       /* Start of custom user code (InProcMeans) */
       DESCENDTYPES 
       /* End of custom user code (InProcMeans) */

   • Close the Code Preview for Task window.
   
   
5. Run the task and verify that the overall summary row is last in the output data set.
   
   

   Solution:

   Click Run to run the task. The results are updated on the Output Data tab. Notice that the overall total appears as the last row.
   
6. Close all tabs except for the process flow, and save the Lesson5 project.
   
   
   

Practice Level 1: Using the Summary Tables Wizard

In this practice, you use the Summary Tables Wizard to create a report that displays the minimum and maximum salary for each department and job title.

1. In the Lesson5 project, select the employee_master table in the Project pane or process flow and use the Tasks pane to launch the Summary Tables Wizard.
   
   

   Solution:

   • Highlight the employee_master table in the Project pane or process flow.
   • In the Tasks pane, expand Describe and double-click Summary Tables Wizard.
   
   
2. Filter the data so that only employees with Warehouse in their job title are included.
   
   

   Solution:

   • In Step 1 of the wizard, click Edit.
   • Select Job_Title from the first drop-down list.
   • Select Contains from the second drop-down list.
   • Enter Warehouse as the value in the third box. Warning: values entered in the third box are case sensitive.
   • Click OK.
   • Click Next to select the analysis variables.
   
   
3. Add Salary to the Analysis variables box two times. Select Minimum for the first statistic and Maximum for the second.
   
   

   Solution:

   • In Step 2, click Add and select Salary.
     • In the Analysis variables box, select the cell in the Statistic column.
     • From the drop-down list, select Minimum.
   • Click Add and select Salary a second time.
     • In the Analysis variables box, select the cell in the Statistic column.
     • From the drop-down list, select Maximum.
   
   
4. Hide the analysis variable labels and apply the DOLLARw.d format to the table with 10 for the overall width and 0 (zero) for the decimal places.
   
   

   Solution:

   • From the Analysis variables labels drop-down list, select hidden.
   • Click Browse to select the table format.
     • Select Currency in the Categories pane and DOLLARw.d in the Formats pane.
     • Change the overall width to 10 and the leave the decimal places at 0 (zero).
     • Click OK.
   • Click Next to move to select the classification variables.
   
   
5. Add Department and then Job_Title in the Rows box. Hide the row headings.
   
   Hint: Click More Options to customize the table structure.
   
   

   Solution:

   • To add a classification variable to the rows, click Add for that box.
     • From the pop-up menu, select Department.
       Note: The variables that you assign as classification variables are character or discrete numeric variables that are used to divide the input data into categories. The statistics are calculated on all selected analysis variables for each unique combination of classification variables.
   • To add the second classification variable to the rows, click Add for that box.
     • From the pop-up menu, select Job_Title.
   • Click More Options to hide the row headings.
     • In the Table Structure dialog box, select the Hide the row headers check box.
     • Click OK.
   • Click Next to specify the totals.
   
   
6. Eliminate the row totals.
   
   

   Solution:

   • From the Rows drop-down list, select None.
   • Click Next to optionally select an output data set.
   
   
7. Do not create an output data set.
   
   

   Solution:

   Select Next to specify the titles and footnotes.
   
8. Provide a title of Salary Ranges for Warehouse Jobs and remove the footnote.
   
   

   Solution:

   Enter Salary Ranges for Warehouse Jobs as the title and delete the footnote text.
   
9. Run the task. Which departments have warehouse assistants?
   
   

   Solution:

   Click Finish to run the task. The results appear on a new tab in the work area. The departments with warehouse assistants are Administration and Stock & Shipping.
   
10. Change the task label to Warehouse Salary Ranges.
    
    

    Solution:

    • Right-click Summary Tables in the Project pane and select Rename.
    • Enter Warehouse Salary Ranges.
    
    
11. Close all tabs except for the process flow, and save the Lesson5 project.
    
    
    

Practice Level 2: Using the Summary Tables Task

In this practice, you use the Summary Tables task to create a report that shows warehouse employee average salaries by Country. Enhance the report by adding customized titles that highlight overall totals.

1. In the Lesson5 project, select the employee_master table in the Project pane or process flow and use the Tasks pane to launch the Summary Tables task.
   
   

   Solution:

   • Highlight the employee_master table in the Project pane or process flow.
   • In the Tasks pane, expand Describe and double-click Summary Tables.
   
   
2. Filter the data so that only employees with Warehouse in their job title are included.
   
   Hint: When creating the filter, be aware that values are case sensitive.
   
   

   Solution:

   • Click Edit.
   • Select Job_Title from the first drop-down list.
   • Select Contains from the second drop-down list.
   • Enter Warehouse as the value in the third box.
   • Click OK.
   
   
3. Establish the task roles by adding the Salary variable as the analysis variable and Job_Title and Country as the classification variables.
   
   

   Solution:

   • Drag Salary from the Variables to assign list and drop it onto the Analysis variables role in the Task roles pane.
   • Drag Job_Title and Country from the Variables to assign list and drop it onto the Classification variables role in the Task roles pane.
   
   
4. Create the summary table layout by dragging and dropping items to the preview area as indicated below:
   
   • Select Summary Tables in the selection pane to define the table layout.
   • Select Country and drag it to the box in the Preview area that represents the rows of the table.
   • Select Total (ALL) and drag it to below Country.
   • Select Job_Title and drag it above the N statistic.
   • Select Salary and drag it below Job_Title.
   • Drag the Mean statistic to the columns, dropping it on top of the N statistic under Salary.
   • Select Total (ALL) and drag it to the right of Job_Title. Next, drag Salary to the columns, placing it below Total. Finally, select the Mean statistic and then drop it on top of the N statistic in the Total column.
   
   
   
5. Enter Warehouse Employee Statistics by Country as the title and delete the footnote.
   
   

   Solution:

   • Select Titles in the selection pane.
   • Select Table Titles in the Section pane and clear the Use default text check box.
   • Delete the current text and enter Warehouse Employee Statistics by Country.
   • Select Footnote in the Section pane and clear the Use default text check box.
   • Delete the default footnote text.
   
   
6. Change the task label to Warehouse Employee Stats.
   
   

   Solution:

   • Select Properties in the selection pane.
   • Click Edit.
   • Delete the text in the Label field and enter Warehouse Employee Stats.
   • Click OK.
   
   
7. Run the task and review the results.
   
   

   Solution:

   Click Run to run the task. A new tab appears in the work area, displaying the results.
   
8. Modify the report to meet the appearance requirements by clicking the Modify Task icon from the task toolbar to reopen the Summary Tables task.
   
   • Remove the headings for Job_Title and Salary. Hint: Removing the existing label text removes the row or column heading.
   • Change the Mean statistic heading to Average Salaries.
   • Apply the DOLLARw.d format to the data values with 12 for the overall width and 0 (zero) for the decimal places.
   • For the total row at the bottom of the report, change the title to All Countries and set the text background color to light turquoise. Also, change the background color of the data values to light turquoise.
   • For the total column on the right side of the report, make the following updates:
     • Change the title to All Job Titles and set the text background color to light turquoise.
     • Remove the heading for Salary.
     • Change the Mean statistic heading to Average Salaries and set the text background color to light turquoise.
     • Apply the DOLLARw.d format to the data values with 12 for the overall width and 0 (zero) for the decimal places. Use a background color of light turquoise.
   
   

   Solution:

   • Remove the headings for Job_Title and Salary.
     • Select Summary Tables in the selection pane.
     • Right-click Job_Title and select Heading Properties.
       • In the Heading Properties for Job_Title window, delete the existing label and click OK.
     • Right-click Salary and select Heading Properties.
       • In the Heading Properties for Salary window, delete the existing label and click OK.
   • Change the Mean statistic heading to Average Salaries. Apply the DOLLARw.d format to the data values with 12 for the overall width and 0 (zero) for the decimal places.
     • Right-click the Mean statistic and select Heading Properties.
       • In the Heading Properties for Mean window, enter Average Salaries as the label and click OK.
     • Right-click the Mean statistic and select Data Value Properties.
       • In the Data Value Properties for Mean window, click the Format tab.
       • Select Currency in the Categories pane and DOLLARw.d in the Formats pane.
       • Change the overall width to 12 and leave the decimal places at 0 (zero).
       • Click OK.
   • For the total row at the bottom of the report, change the title to All Countries and set the text background color to light turquoise. Also, change the background color of the data values to light turquoise.
     • Right-click Total below Country and select Heading Properties.
       • In the Heading Properties for ALL window, click the General tab and enter All Countries as the label.
       • Click the Font tab and select Light Turquoise for the background color.
       • Click OK.
     • Right-click Total below Country and select Data Value Properties.
       • In the Data Value Properties for ALL window, click the Font tab and select Light Turquoise for the background color.
       • Click OK.
   • For the total column on the right side of the report, make the listed updates.
     • Right-click Total beside Job_Title and select Heading Properties.
       • In the Heading Properties for ALL window, click the General tab and enter All Job Titles as the label.
       • Click the Font tab and select Light Turquoise for the background color.
       • Click OK.
     • Right-click Salary below the Total column and select Heading Properties.
       • In the Heading Properties for Salary window, click the General tab and delete the existing label.
       • Click OK.
     • Right-click the Mean statistic below the Total – Salary column and select Heading Properties.
       • In the Heading Properties for Mean window, enter Average Salaries as the label.
       • Click the Font tab and select Light Turquoise for the background color.
       • Click OK.
     • Right-click the Mean statistic below the Total – Salary column and select Data Value Properties.
       • In the Data Value Properties for Mean window, click the Format tab.
       • Select Currency in the Categories pane and DOLLARw.d in the Formats pane.
       • Change the overall width to 12 and the leave the decimal places at 0 (zero).
       • Click the Font tab and select Light Turquoise for the background color.
       • Click OK.
   
   
9. Run the task. In Australia (AU), what is the average salary across all warehouse job titles?
   
   

   Solution:

   Click Run to run the task. The results are updated. In Australia (AU), the average salary across all warehouse job titles is $36,225.
   
10. If you are moving onto the Challenge Practice: Creating and Applying a Custom Percentage Format, leave the Warehouse Employee Stats tab open. If not, close all tabs except for the process flow, and save the Lesson5 project.
    
    
    

Practice Challenge: Creating and Applying a Custom Percentage Format

In this practice, you display a percent sign after the percentage values in the table created by the Summary Tables task.

Note: If you use the PERCENTw.d format, it multiplies data values by 100, and then adds a percent sign (%) to the end of the formatted value. The Summary Tables task already multiplied the values by 100 to compute the percent. Therefore, if you use the PERCENTw.d format to format the values, they are incorrect. Because you do not want the value multiplied by 100, you need to create a picture format. Use PROC FORMAT to add the percent sign to the value.

1. If you haven't already, complete the Level 2: Using the Summary Tables Task practice.
   
   
   
2. Open and run the e105p06.sas program from the practices folder. Review the log and verify that the format was output. Close the program.
   
   Note: This program creates a user-defined format named MYPCT that rounds values to the nearest tenth and adds a percent sign.
   
   

   Solution:

   • Select File > Open.
   • Navigate to the practices folder in the course file location.
   • Select e105p06.sas and click Open.
   • Submit the program.
   • Review the log and verify that the format was output.
   • Close the e105p06.sas tab.
   
   
3. Modify the Warehouse Employee Stats task that was created in the Level 2 practice.
   
   • Add the ColPctSum statistic to the right of the Mean statistic for both the Job_Title and Total columns.
   • Change all ColPctSum statistic headings to Percent of Salaries.
   • Apply the MYPCT format by modifying the data value properties for all percentage columns in the report.
     Hint: Look for the User Defined category in the Format window.
   
   

   Solution:

   • If the Warehouse Employee Stats tab is still open, click the Modify Task icon from the task toolbar. Otherwise, right-click the Warehouse Employee Stats task in the process flow or Project pane and select Modify.
   • Add the ColPctSum statistic to the right of the Mean statistic for both the Job_Title and Total columns.
     • Select Summary Tables in the selection pane.
     • Drag the ColPctSum statistic to the columns, dropping it to the right of the Mean statistic under Job Title - Salary.
     • Drag the ColPctSum statistic to the columns, dropping it to the right of the Mean statistic under Total - Salary.
   • Change all ColPctSum statistic headings to Percent of Salaries.
     • For each ColPctSum statistic, right-click ColPctSum and select Heading Properties.
       • In the Heading Properties for ColPctSum window, click the General tab, enter Percent of Salaries as the label, and click OK.
   • Apply the MYPCT format by modifying the data value properties for all percentage columns in the report.
     • For each ColPctSum statistic, right-click ColPctSum and select Data Value Properties.
       • In the Data Value Properties for ColPctSum window, click the Format tab.
       • Select User Defined in the Categories pane and MYPCT. in the Formats pane.
       • Click OK.
   
   
4. Run the task and review the results.
   
   Note: If necessary, double-click HTML - Warehouse Employee Stats in the Practices process flow to view the output.
   
   

   Solution:

   Click Run to run the task. A new tab appears in the work area, displaying the results.
   
5. Close all tabs except for the process flow and save the Lesson5 project.
   
   
   

Practice Level 1: Using a Single Variable Prompt

In this practice, you create a prompt so that the user can select a variable from the employee_master table for the charting variable.

1. In the Lesson6 project, create a new process flow named Practices and add the employee_master table.
   
   

   Solution:

   • If necessary, select File > New > Process flow.
   • Right-click the process flow in the Project pane and select Rename.
   • Enter Practices as the new name.
   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
2. Use the Prompt Manager to create a new prompt.
   
   • Enter SelectVar as the name and Which column do you want to analyze? as the displayed text.
   • Require a value for this prompt.
   
   

   Solution:

   • Click the Prompt Manager pane in the navigation area.
   • Click the Add icon.
   • On the General tab, enter SelectVar in the Name field and Which column do you want to analyze? in the Displayed text field.
   • Select the Requires a non-blank value check box.
   
   
3. On the Prompt Type and Values tab, perform the following steps:
   
   • Change the prompt type to Variable.
   • Type or use the Load Values button to populate the Value list box with Country, Department, and Gender.
   • Assign Country as the default value.
   
   

   Solution:

   • Click the Prompt Type and Values tab and change the prompt type to Variable.
   • Type or use the Load Values button to populate the Value list box with Country, Department, and Gender.
     • To populate the prompt list with variables from the employee_master table, click Load Values.
     • In the Open File window, select Project > employee_master > Open.
     • Hold down the Ctrl key and select Employee_Name and Job_Title.
     • Click the Delete icon to remove the selected items for the list of values.
   • Assign Country as the default value.
     • Select the Default value check box and double-click Country in the Value list box.
   • Click OK to create the prompt.
   
   
4. Use the Bar Chart Wizard to create a horizontal bar chart based on employee_master.
   • Use the SelectVar prompt to specify the bars.
   • Select Salary as the variable to control the bar length.
   • Use the Statistics icon to set the statistic type as Average.
   • Change the following display attributes:
   • Color the bars separately.
   • Display the data labels and show the average salary.
   • Change the horizontal axis label to Average Annual Salary.
   • Assign Average Annual Salary by &SelectVar as the title and delete the footnote.
     Note: In the code, macro variables are referenced by preceding the prompt name with & (an ampersand).
   
   

   Solution:

   • Highlight the employee_master table in the Project pane or process flow.
   • In the Tasks pane, expand Graph and double-click Bar Chart Wizard.
   • Verify the data and click Next to advance to Step 2.
   • Select the Horizontal bar chart check box.
   • Select the SelectVar prompt in the Bars drop-down list.
   • Select Salary in the Bar length drop-down list and click the Statistics icon to change the statistic.
     • Select Average and click OK.
   • Click Next.
   • Color the bars separately.
     • In Step 3, use the Color bars by drop-down list to select Bar category.
   • Display the data labels and show the average salary.
     • In Step 3, select the Data labels check box and select Average from the drop-down list.
   • Change the horizontal axis label to Average Annual Salary.
     • In Step 3, click Axis Labels, enter Average Annual Salary as the Bar length label, and click OK.
     • Click Next.
   • Assign Average Annual Salary by &SelectVar as the title and delete the footnote.
     • In Step 4, change the title to Average Annual Salary by &SelectVar and remove the default footnote text.
       
   
   
5. Run the task. Select the appropriate prompt value to answer the following questions:
   
   • Which country has the highest average salary?
   • What is the average annual salary for employees in the Accounts department?
   
   

   Solution:

   Click Finish. When prompted, verify that Country is selected and click Run.
   
   Note: A warning symbol might appear on the Bar Chart tab and task icon. This symbol designates a warning in the log, indicating that the INSIDE=, OUTSIDE=, and SUBOUTSIDE options are ignored for HBAR charts.
   
   The country with the highest average salary is the US at $52,421.
   
   Click Run from the task toolbar. When prompted, select Department and click Run.
   
   The average annual salary for employees in the Accounts department is $52,241.
   
6. Close all tabs except for the process flow, and save the Lesson6 project.
   
   
   

Practice Level 2: Using Multiple Variable Prompts

In this practice, you create two prompts so that the user can select the charting and analysis variables from the orion_profit table when creating a donut chart.

1. In the Lesson6 project, add the orion_profit table to the Practices process flow.
   
   

   Solution:

   • If necessary, select File > New > Process flow. Note: If you do not have the Lesson6 project, select File > New > Project to create it.
   • Right-click the process flow in the Project pane and select Rename.
   • Enter Practices as the new name.
   • Select File > Open and navigate to the course data location.
   • Select orion_profit > Open. The data appears on a new table in the work area.
   
   
2. Use the Prompt Manager to create a new prompt by clicking the Prompt Manager pane in the navigation area and clicking the Add icon.
   
   • On the General tab, specify ChartVar as the name and Which column do you want to chart? as the displayed text. Require a value for this prompt.
   • On the Prompt Type and Values tab, change the prompt type to Variable.
     • Populate the Value list box with Product_Line, Product_Category, Customer_Group, and Customer_Type.
     • Assign Product_Line as the default value.
   • Warning: If you have both the Practices process flow and the Demos process flow, be sure to select the orion_profit table from the Practices process flow.
   
   

   Solution:

   • Use the Prompt Manager to create a new prompt by clicking the Prompt Manager pane in the navigation area and clicking the Add icon.
   • On the General tab, specify ChartVar as the name and Which column do you want to chart? as the displayed text. Require a value for this prompt.
     • On the General tab, enter ChartVar in the Name field and Which column do you want to chart? in the Displayed text field.
     • Select the Requires a non-blank value check box.
   • Click the Prompt Type and Values tab and change the prompt type to Variable.
     • Populate the Value list box with Product_Line, Product_Category, Customer_Group, and Customer_Type.
       • To populate the prompt list with character variables, click Load Values.
     • In the Open File window, select Project > orion_profit > Open.
     • Hold down the Ctrl key and select Product_Group, Product_Name, Supplier_Name, Customer_Name, Customer_Country, Customer_Gender, and Customer_Age_Group.
     • Click the Delete icon to remove the selected items for the list of values.
     • Assign Product_Line as the default value
       • Select the Default value check box and double-click Product_Line in the Value list box.
     • Click OK to create the prompt.
   
   
3. Use the Prompt Manager to create a new prompt.
   
   • On the General tab, specify AnalysisVar as the name and Which column do you want to analyze? as the displayed text. Require a value for this prompt.
   • On the Prompt Type and Values tab, change the prompt type to Variable.
     • Populate the Value list box with Quantity, Total_Retail_Price, and Profit.
     • Assign Profit as the default value.
       Hint: These three variables are all numeric variables. Change the selected Variable type value to Numeric before loading values.
   
   

   Solution:

   • Use the Prompt Manager to create a new prompt by clicking the Prompt Manager pane in the navigation area and clicking the Add icon.
   • On the General tab, specify AnalysisVar as the name and Which column do you want to analyze? as the displayed text. Require a value for this prompt.
   • On the General tab, enter AnalysisVar in the Name field and Which column do you want to analyze? in the Displayed text field.
   • Select the Requires a non-blank value check box.
   • Click the Prompt Type and Values tab and change the prompt type to Variable.
     • Populate the Value list box with Quantity, Total_Retail_Price, and Profit.
       • Select Numeric in the Variable type box.
       • Click Load Values
       • In the Open File window, select Project > orion_profit > Open.
       • Hold down the Shift key and select Order_ID and Delivery_Date.
         Note: The Shift key enables you to select a group of items that are contiguous (that is, next to each other) by selecting the first item, and then holding down the Shift key and selecting the last item.
       • Click the Delete icon to remove the selected items for the list of values.
     • Assign Profit as the default value.
       • Select the Default value check box and double-click Profit in the Value list box.
     • Click OK to create the prompt.
   
   
4. Use the Donut Chart task to create a simple donut chart based on orion_profit.
   • Assign the ChartVar prompt to the Column to chart role and the AnalysisVar prompt to the Sum of role.
   • Change the following display attributes:
     • Display the name and percentage outside the slice.
     • Assign Total&AnalysisVar by &ChartVar as the title and delete the footnote.
     • Change the task label to Dynamic Donut Chart.
   
   

   Solution:

   • Highlight the orion_profit table in the Project pane or process flow.
   • In the Tasks pane , expand Graph and double-click Donut Chart.
   • Verify that Simple Donut is selected as the type of chart to create.
   • Click Data in the selection pane.
     • Drag ChartVar to the Column to chart role and AnalysisVar to the Sum of role.
   • Display the name and percentage outside the slice.
     • Select Appearance - Layout in the selection pane.
     • Verify that the name is displayed outside of each slice.
     • Select Outside to have the percentage displayed outside each slice.
     • Select None to remove the statistic values from the chart.
   • Assign Total &AnalysisVar by &ChartVar as the title and delete the footnote.
     • Select Titles in the selection pane.
     • For the graph title, clear the Use default text check box and enter Total &AnalysisVar by &ChartVar.
     • Select Footnote in the Section pane, clear the Use default text check box, and delete the existing text.
   • Change the task label to Dynamic Donut Chart.
     • Select Properties in the selection pane and click Edit.
     • In the General pane, change the task label to Dynamic Donut Chart.
     • Click OK.
   
   
5. Run the task. Select the appropriate prompt values to answer the following questions:
   
   • What percentage of the total profit is the profit for the Outdoors product line?
   • What customer group accounts for the largest quantity sold?
   
   

   Solution:

   Click Run to run the task. When prompted, verify that Product_Line is selected as the column to chart and Profit is selected as the column to analyze. Click Run.
   
   The percentage of the total profit for the Outdoors product line is 22.33%.
   
   Click Run from the task toolbar. When prompted, select Customer_Group as the column to chart and Quantity as the column to analyze. Click Run.
   
   The customer group that accounts for the largest quantity sold is Orion Club Gold members at 50.03%.
   
6. If you are moving onto the Challenge: Using a Color Prompt in a Task practice, leave the Dynamic Donut Chart tab open. If not, close all tabs except for the process flow. Then save the Lesson6 project.
   
   
   

Practice Challenge: Using a Color Prompt in a Task

In this practice, you use a color prompt to dynamically change the chart background color.

1. If you haven't already, complete the Level 2: Using Multiple Variable Prompts practice to generate a donut chart.
   
   
   
2. Use the Prompt Manager to create a new prompt.
   
   • On the General tab, enter ColorBack in the Name field and Select the chart background color: in the Displayed text field.
   • On the Prompt Type and Values tab, change the prompt type to Color. Assign White as the default value.
   
   

   Solution:

   • Use the Prompt Manager to create a new prompt by clicking the Prompt Manager pane in the navigation area and clicking the Add icon.
     • On the General tab, enter ColorBack in the Name field and Select the chart background color: in the Displayed text field.
     • On the Prompt Type and Values tab, change the prompt type to Color.
     • Assign White as the default value.
       Note: Even though the default value appears to be set to white, the default value for the color prompt is set to Automatic. Be sure to select White as the value.
     • Click OK to create the prompt.
   
   
3. Modify the Donut Chart task to dynamically change the background color.
   
   • Identify the prompt to the task. Modify the task properties and add the ColorBack prompt to the list of prompts associated with the task.
   • Insert the GOPTIONS statement that sets the CBACK= option equal to the ColorBack prompt value at the beginning of the code and resets the graphic options at the end of the code.
     Note: The GOPTIONS statement sets default values for many graphic attributes. Using the CBACK= option in this statement sets the background color for a chart.
     • Add the following code to the Framework_BeforeTaskCode insertion point:

       goptions cback=&ColorBack;

     • Add the following code to the Framework_AfterTaskCode insertion point:

       goptions reset=all;

   
   

   Solution:

   • Modify the Donut Chart task to dynamically change the background color by clicking the Modify Task icon from the task toolbar.
     • Identify the prompt to the task. Modify the task properties and add the ColorBack prompt to the list of prompts associated with the task.
       • Select Properties in the selection pane and click Edit.
       • Select Prompts in the selection pane and click Add.
       • Select ColorBack and click OK.
       • Verify the project prompts used are ChartVar, AnalysisVar, and ColorBack.
       • Click OK to close the properties window.
   • Insert a GOPTIONS statement that sets the CBACK= option equal to the ColorBack prompt value at the beginning of the code and resets the graphic options at the end of the code. Use the provided code.
     • Click Preview code to customize the task code.
     • Click the Show custom code insertion points check box and locate the Framework_BeforeTaskCode insertion point at the top of the code.
     • Enter the provided code in the Framework_BeforeTaskCode insertion point:
       

       goptions cback=&ColorBack;

     • Scroll to the bottom of the code and enter the provided code in the Framework_AfterTaskCode insertion point:
       

       goptions reset=all;

     • Close the Code Preview for Task window.
   
   
4. Run the task. Use Product_Line as the column to chart, Profit as the analysis variable, and Gray - 25% as the background color.
   
   

   Solution:

   • Click Run.
   • Select Product_Line as the column to chart and Profit as the column to analyze.
   • Click the down arrow to select the background color and select Gray - 25%.
   • Click Run. The results are updated on the Dynamic Donut Chart tab.
   
   
5. Close all tabs except for the process flow and save the Lesson6 project.
   
   
   

Practice Level 1: Using a Text Prompt in a Query

From the Query Builder, you use the Prompt Manager to create a text prompt that enables the user to select a department. You use the prompt to filter the employee_master table.

1. If necessary, add the employee_master table to the Practices process flow in the Lesson6 project.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   
   
2. Open the Query Builder. Name the query Select Department Query and the output table select_dept. Include all columns in the query.
   
   

   Solution:

   • Open the Query Builder by right-clicking employee_master in the Project pane or process flow and selecting Query builder.
     • Enter Select Department Query in the Query name field.
     • Click Change next to the Output name field.
     • Enter select_dept in the File name field and click Save.
     • Select t1 (employee_master) in the selection pane and drag and drop it onto the Select Data tab. All columns are added to the query.
   
   
3. Use the Prompt Manager to create a new prompt.
   • On the General tab, enter SelectDept as the name and Select a Department: as the displayed text. Require a value for this prompt.
   • On the Prompt Type and Values tab, perform the following steps:
     • Verify that Text is the prompt type.
     • Set the method for populating the prompt to User selects values from a static list.
     • Click Get Values to populate the prompt list with the distinct values of Department from the employee_master table.
     • Set the default value as Sales.
   • Create the prompt and return to the Query Builder.
   
   

   Solution:

   • Use the Prompt Manager to create a new prompt by clicking Prompt Manager > Add.
     • On the General tab, enter SelectDept as the name and Select a Department: as the displayed text.
     • Select the Requires a non-blank value check box.
   • On the Prompt Type and Values tab, perform the following steps:
     • Verify that Text is the prompt type.
   • Select User selects values from a static list as the value in the Method for populating prompt field.
   • Click Get Values to populate the prompt list with the distinct values of Department from the employee_master table.
     • To define the data source, click Browse.
     • Select Project > employee_master > Open.
     • Change the column value in the Unformatted Values pane to Department.
     • Click Get values to retrieve all unique departments. Click the double-right arrow Add all icon.
     • Click OK to close the Get Values window.
   • Locate Sales in the list of values and click the Default radio button.
   • Create the prompt and return to the Query Builder.
     • Click OK to create the prompt.
     • Click Close to return to the Query Builder.
   
   
4. On the Filter Data tab, create a filter on the Department column so that the value is equal to the prompt, SelectDept. If necessary, select the Generate filter for a prompt value check box when creating the filter.
   
   

   Solution:

   • Click the Filter Data tab.
   • Drag and drop Department onto the tab area.
   • In the New Filter Wizard, verify that the operator is Equal to.
     • Select the Generate filter for a prompt value check box.
     • Click the drop-down list Get Values next to the Value field.
     • Click the Prompts tab and select &SelectDept.
     • Click Finish.
   
   
5. Run the query. Select the appropriate prompt values.
   
   Hint: Click Run from the task toolbar and select different prompt values.
   
   
   • How many employees are in the Sales department?
   • How many employees are in the Accounts Management department?
   
   

   Solution:

   Click Run to run the task. When prompted, verify that Sales is selected and click Run. The number of employees in the Sales department is 677.
   
   Click Run from the query toolbar. When prompted, select Accounts Management and click Run. The number of employees in the Accounts Management department is 9.
   
6. Close all tabs except for the process flow and save the Lesson6 project.
   
   
   

Practice Level 2: Using a Date Range Prompt in a Query

In this practice, you create a query that prompts the user to subset the employee_master table for a specified range of hire dates.

1. In the Lesson6 project, create a query based on the employee_master table.
   • Name the query Employee Hire Date Query and name the output table emp_hire_date.
   • Add all columns to the query.
   
   

   Solution:

   • Select File > Open and navigate to the course data location.
   • Select employee_master > Open. The data appears on a new tab in the work area.
   • Open the Query Builder by right-clicking employee_master in the Project pane or process flow and selecting Query builder.
   • Enter Employee Hire Date Query in the Query name field.
   • Click Change next to the Output name field. Enter emp_hire_date in the File name field and click Save.
   • Select t1 (employee_master) in the selection pane and drag and drop it onto the Select Data tab. All columns are added to the query.
   
   
2. Create a prompt that enables a selection for a range of dates.
   • Name the prompt DateRange and use Select a beginning and end date for the employee hire date range as the displayed text.
   • Change the prompt type to Date range and the date type to Day.
   • Set the minimum value allowed to January 01, 1985 and the maximum value allowed to February 01, 2018. Use those values as the default custom range.
   
   

   Solution:

   • Click Prompt Manager > Add.
     • Enter DateRange as the name and Select a beginning and end date for the employee hire date range as the displayed text.
     • Select the Requires a non-blank value check box.
     • Click the Prompt Type and Values tab.
     • Select Date range as the prompt type and verify that Day is selected as the date type.
     • Enter January 01, 1985 in the Minimum value allowed field and February 01, 2018 in the Maximum value allowed field.
     • For the default range, enter January 01, 1985 in the From field and February 01, 2018 in the To field.
     • Click OK to create the prompt.
   • Click Close to return to the Query Builder.
   
   
3. Create a filter on the Hire_Date column.
   • Set the operator to In a range.
   • Use the prompt that you created to select the date range at execution.
   
   

   Solution:

   • Click the Filter Data tab.
   • Drag and drop Hire_Date onto the tab area.
   • In the New Filter Wizard, select In a range as the operator.
     • Click the drop-down list Get Values next to the Value field and select &DateRange.
     • Click Finish.
   
   
4. Sort the results by ascending Hire_Date and then ascending Employee_ID.
   
   

   Solution:

   • Click the Sort Data tab.
   • Drag and drop Hire_Date onto the tab area.
   • Drag and drop Employee_ID onto the tab area.
   
   
5. Run the query and set the range of dates from January 1, 2010 to December 31, 2010. How many employees were hired between January 1, 2010, and December 31, 2010?
   
   

   Solution:

   • Click Run to run the query.
   • When prompted enter January 1, 2010 in the From field and December 31, 2010 in the To field.
   • Click Run.
     
     
   The number of employees hired between January 1, 2010, and December 31, 2010 is 23.
   
6. Close all tabs except for the process flow and save the Lesson6 project.
   
   
   

Practice Challenge: Using a Prompt in a SAS Program

In this practice, you create a prompt that can be used as a macro variable in a SAS program.

1. Open and run the e106p07.egp project from the practices folder. In the Project pane, double-click e106p07.sas to view the program. Notice that the program includes a WHERE statement that filters the data to include only employees in Australia. Verify that the program returns 108 rows.
   
   

   Solution:

   • Select File > Open. In the Open window, navigate to the practices folder in the course file location.
   • Select e106p07.egp and click Open.
   • Click Run to run the Autoexec process flow.
   • In the Project pane, double-click e106p07.sas to view the program.
   • On the Code tab, review the program code. Notice the WHERE statement:

     where upcase(country) = "AU";

   • On the Output Data tab, verify 108 rows were returned.
   
   
2. Create a text prompt named SelectCountry that enables the selection of a country. Use the following specifications when creating the prompt:
   
   

   Name	SelectCountry
   Displayed text	Select country:
   Options	Requires a non-blank value
   Prompt type	Text
   Method for populating prompt	User selects values from a static list
   Number of values	Single value
   List of values	Unformatted Value	Formatted Value
   	AU	Australia
   	US	United States
   	Note: Unformatted values are case sensitive.
   Default values	AU
   Additional option	Append formatted values with unformatted values

   
   

   Solution:

   • Click the Prompt Manager pane in the navigation area. Click the Add icon.
     • On the General tab, enter SelectCountry in the Name field and Select country: in the Displayed text field.
     • Select the Requires a non-blank value check box.
     • Click the Prompt Type and Values tab and verify that the prompt type is set to Text.
     • Select User selects values from a static list in the Method for populating prompt field.
     • Verify that Single value is selected in the Number of values field.
     • Click Add to add the first value.
       • Enter AU in the Unformatted Value field and Australia in the Formatted (Displayed) Value field.
       • Click the Default radio button.
         
     • Click Add to add the second value.
       • Enter US in the Unformatted Value field and United States in the Formatted (Displayed) Value field.
     • Select the Append formatted values with unformatted values check box.
     • Click OK to create the prompt.
   
   
3. Modify the WHERE statement in the SAS program to reference the SelectCountry prompt (macro variable) rather than the static value of AU.
   
   

   Solution:

   Change the WHERE statement to the following:

   where upcase(country) = "&SelectCountry";

   
   
4. Modify the properties of the program by clicking the View and set properties icon on the e106p07.sas toolbar. Add the SelectCountry prompt to the list of prompts associated with the program.
   
   

   Solution:

   • Click the View and set properties icon on the toolbar.
   • Select Prompts in the selection pane.
   • Click Add.
   • Select SelectCountry and click OK.
   • Verify that the prompt is used by the program and click OK.
   
   
5. Run the program and select the appropriate prompt values.
   • How many rows are returned from the query when Australia is selected?
   • How many rows are returned from the query when United States is selected?
   
   

   Solution:

   Click Run. When prompted, select Australia [AU]. There are 108 rows returned from the query.
   
   Click Run. When prompted, select United States [US]. There are 316 rows returned from the query.
   
6. Close all tabs except for the process flow, and save the Lesson6 project. It is not necessary to save the e106p07.egp project changes.
   
   
   

Practice Level 1: Creating a Report Using Multiple Results

In this practice, you create a SAS report that combines the output from three different tasks into a single PDF document.

1. Open and run the e107p01.egp project. Note: The project is found in the practices folder in the course file location.
   
   

   Solution:

   • Select File > Open.
   • In the Open window, click My Computer > Browse.
   • Navigate to the practices folder in the course file location.
   • Select the e107p01.egp file and click Open.
   • Click Run to run the Autoexec process flow.
   
   
2. Create a combined report. Use the One-Way Frequencies, Summary Tables, and Bar Chart SAS report results. Arrange the items as indicated below.
   
   

   One-Way Frequencies	Bar Chart
   Summary Tables

   
   

   Solution:

   • To begin creating a customized report, select File > New > Report.
     • Drag One-Way Frequencies to the row 1, column 1 position.
     • Drag Bar Chart to the row 1, column 2 position.
     • Drag Summary Tables to the row 2, column 2 position.
     • Expand the Summary Tables results to span two squares.
     • Click OK to close the New Report window. The customized report appears on a new tab in the work area.
   
   
3. Remove the titles from the Bar Chart and Summary Table results. Use Employee Statistics as the report header. Apply a 20 pt., bold, center-aligned style to the text. Add a line between the header and the report body.
   
   

   Solution:

   • Click Design and select Header and Footer.
     • On the Titles & Footnotes tab, clear the Title check box for the Bar Chart and Summary Tables items.
     • Click the Header tab.
     • Set the font characteristics to 20 pt., bold, and center aligned.
     • Enter Employee Statistics in the text box.
     • Select the Below header check box for the line position.
     • Click OK to close the Header & Footer window.
   
   
4. Change the page orientation to Landscape. Note: If necessary, change the paper size to Letter.
   
   

   Solution:

   • Click Design and select Page Setup.
     • If necessary, change the paper size to Letter.
     • Under Orientation, select Landscape.
     • Click OK to close the Page Setup window.
   
   
5. Click Page to preview the report. Return to the Normal view when you are finished viewing the report. Note: If the graph and frequency report do not appear side by side, return to the Normal view to reduce the size of the graph.
   
   
   
6. Change the name of the report by clicking the Properties icon on the report toolbar. Change the label to Employee Report and click OK.
   
   
   
7. Add a step in the project to export the combined report as a PDF document. Store the output file in the output folder located in the course file location. Do not overwrite existing results. Hint: To view the PDF file, click the Autoexec tab in the work area. Double-click the Employee Report.pdf object in the process flow. Note: Each time that the Export task is run, a new file is created with a date and time. They are appended to the file name.
   
   

   Solution:

   • Click Share > Export as a step in project.
     • In Step 1, verify that Employee Report is highlighted and click Next.
     • In Step 2, select Portable Document Format (*.pdf) and click Next.
     • In Step 3, click Browse and navigate to the output folder in the course file location.
       • Verify that the name of the file is Employee Report.pdf and click Save.
       • Clear the Overwrite existing output check box.
       • Click Next.
     • In Step 4, review the summary and click Finish to complete the export. A new tab appears in the work area, displaying the log.
   • To view the PDF file, click the Autoexec tab in the work area.
   • Double-click the Employee Report.pdf object in the process flow.
   
   
8. Close the project. It is not necessary to save the changes.
   
   
   

Practice Level 1: Working with Multiple Process Flows

In this practice, you organize an existing project by creating a new process flow and moving graphing tasks to the new process flow.

1. Open the e107p02.egp project and run the Autoexec flow. Note: The project is found in the practices folder in the course file location.
   
   

   Solution:

   • Select File > Open.
   • In the Open window, click My Computer > Browse.
   • Navigate to the practices folder in the course file location.
   • Select the e107p02.egp file and click Open.
   • Click Run to run the Autoexec process flow.
   
   
2. Add a new process flow named Graphic Reports to the project.
   
   

   Solution:

   • Create a second process flow by selecting File > New > Process flow.
   • Right-click the newly added process flow in the Project pane and select Rename.
   • Enter Graphic Reports as the name.
   
   
3. Add all bar chart and pie chart tasks to the Graphic Reports process flow. Hint: Examine the Project pane to verify that the items were moved.
   
   

   Solution:

   • Click the Autoexec tab in the work area.
   • Select the Salary by Department, Salary by Country, and Salary by City objects.
   • Right-click a selected item and select Move to > Graphic Reports.
   • Examine the Project pane or the Graphic Reports process flow and verify that the items were moved.
   
   
4. Run the Graphic Reports process flow to update the results.
   
   

   Solution:

   • If necessary, click the Graphic Reports tab in the work area.
   • Select Run > Run process flow.
   • Verify that results were created for each of the chart tasks.
   
   
5. Close the project. It is not necessary to save the changes.
   
   
   

Practice Challenge: Controlling Project Flow and Exporting Combined Code and the Log from a Project

In this practice, you organize the contents of an existing project to control the execution order of tasks. Then, export the project code and log.

1. Open the e107p03.egp project from the practices folder and run the Autoexec flow.
   
   

   Solution:

   • Select File > Open.
   • In the Open window, click My Computer > Browse.
   • Navigate to the practices folder in the course file location.
   • Select the e107p03.egp file and click Open.
   • Click Run to run the Autoexec process flow.
   
   
2. Manually link the Create Format task with the Employee_Master_code icon so that the Create Format task executes first.
   
   

   Solution:

   • Right-click the Create Format object and select Link to.
   • In the Link window, select Employee_Master_code and click OK.
   
   
3. In the Project pane, click the Open the project log icon and turn on logging for the project.
   
   

   Solution:

   In the Project pane, click the Open the project log icon. A new tab, Project Log, appears in the work area. Click the Turn On button.
   
4. Use the Project pane to run the entire project. Then view the project log.
   
   

   Solution:

   • Click the Run the project icon in the Project pane, or right-click the project name, e107p03, and select Run.
   • Use the Project Log tab in the work area to review the project log.
   
   
5. In the Project pane, click the ellipsis (More options) and select Share > Export all code in project to create a SAS program named EmployeeReports.sas. Include the code from all items in the project. Save the file in the output folder in the course files location.
   
   

   Solution:

   • In the Project pane, click the ellipsis (More options) and select Share > Export all code in project.
   • Click Browse, navigate to the output folder in the course files location, enter EmployeeReports.sas in the File name field, and click Save.
   • Verify that the options in the Export All Code window include all items as well as additional code that is generated by Enterprise Guide.
   • Click Export.
   
   
6. Using the Project Log tab in the work area, click Share and select Export. Save the file in the output folder in the course files location. Name the file ProjectLog1.txt.
   
   Note: When saving the file, change the Files of type field to Text Files (*.txt).
   
   

   Solution:

   • If necessary, click the Project Log tab in the work area.
   • Click Share and select Export.
   • In the Save window, navigate to the output folder. Select Text Files (*.txt) in the Files of type field.
   • Enter ProjectLog1.txt in the File name field. Click Save.
   
   
7. Create a new process flow named Review Files. Open and review the EmployeeReports.sas exported code. Open and review the ProjectLog1.txt exported project log.
   
   Hint: With the Review Files process flow active, select File > Open to open and review each of the saved files.
   
   

   Solution:

   • Create a new process flow by selecting File > New > Process flow.
     • Right-click the newly added process flow in the Project pane and select Rename.
     • Enter Review Files as the name.
   • Open and review the EmployeeReports.sas exported code.
     • Select File > Open.
     • In the Open window, click My Computer > Browse.
     • Navigate to the output folder.
     • Select the EmployeeReports.sas file and click Open.
     • Review the generated code and close the EmployeeReports.sas tab.
   • Open and review the ProjectLog1.txt exported project log.
     • Select File > Open.
     • In the Open window, click My Computer > Browse.
     • Navigate to the output folder.
     • Select the ProjectLog1.txt file and click Open.
     • Review the project log and close the ProjectLog1.txt tab.
   
   
8. Close the project. It is not necessary to save the changes.