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

In this practice you run the Characterize Data task and view the results.

1. In the Employees project, add the employee_organization data set.
   
   • Select File > Open > Data from the menu bar.
   • Select My Computer on the left side of the Open Data window.
   • Navigate to the location of the course data.
   • Select the employee_organization data set and select Open.
   
   
2. Use the Characterize Data task to investigate the distribution of each of the columns in the employee_organization data set. Limit the number of unique categorical values to be reported per variable to 15.
   
   • Select Describe > Characterize Data in the data grid to open the Task Wizard.
   • Select Next twice to advance to Step 3 of the Characterize Data Wizard.
   • Type 15 in the Limit the number of unique categorical values to be reported per variable field.
   
   
3. Run the task and view the results.
   Select Finish. View the report.
   
4. Save the project.
   Click the Save Project icon on the toolbar.
   

In this practice, you run the Data Set Attributes task.

1. In the Employees project, open the employee_organization data set in the data grid and open the Data Set Attributes task.
   
   
   • Double-click employee_organization in the project tree or process flow to open the table in the data grid. Note: If the data is not already in the project, go to File > Open > Data and navigate to the location of the course data. Double-click to add the data to the project and open it in the data grid.
   • Select Data > Data Set Attributes.
   
   
2. In Step 2 of the task, make sure that Default Report is the only other output that is selected.
   
   • Select Next to advance to Step 2 of the Data Set Attributes Wizard.
   • Under Other Outputs, clear the Enhanced Report check box.
   
   
3. Run the task and examine the results.
   Select Finish. View the report.
   
4. Save the project.
   Click the Save Project icon on the toolbar.
   

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. In this practice, you submit a SAS program that has a coding error and you view messages in the Log.

1. In the Employees project, open a new program window and copy and paste or type the following code into the Program tab. Note: You might also be able to open e101e03.sas from your course data folder.
   

   proc print data=sashelp.class;
      vat Name Sex Age Height Weight;
   run;
   

   • Depending on how you are accessing this course, you might be able to open e101e03.sas from your course data folder. If not, follow the next two steps.
   • Select File > New > Program from the menu bar.
   • Copy the code and paste or type the code it into the Program window.
   
   
2. Run the program, review the log summary, and identify the error. Hint: You can right-click on the error message in the log summary to go to the corresponding line in the program or log.
   
   • Select Run on the menu bar.
   • Because the program had an error, the SAS log appears automatically in the workspace. Review the log to identify the error. The SAS keyword var was misspelled as vat.
   
   
3. Correct the error in the Code window, resubmit the corrected program, and review the results.
   
   • Right-click on the error in the log summary and select Go To Program Source.
   • Change the spelling of the SAS keyword to var.
   • Select Run from the menu bar. Select Yes when you are prompted to replace the results.
   • View the report.
   
   
4. Save the project and select Yes when you are prompted to save the changes made to the e101e03 program.
   Click the Save Project icon.
   

In this practice you assign the Orion library to the location of the course data.

1. Use the Assign Project Library task to create the Orion library and point to the location of the course data. Include the access=readonly option in Step 3 of the task. Then, test and assign the library.
   
   • Select Tools > Assign Project Library.
   • In Step 1, type Orion in the Name field and select Next.
   • In Step 2, verify that the engine is BASE. Browse to or type the location of the course data. Select Next.
     
   • In Step 3, type access in the Name field. Then, type readonly in the Value field. Select Next.
   • In the final step, select Test Library and verify that OK is displayed. Select Finish.
   
   
2. View the Orion library in the Server List. If you do not see the library or the data sets, select your server and click Refresh.
   In the Server List, open your server, then Libraries, then Orion.
   
   
3. Save the Employees project.
   
   • Click Save Project.
   
   

This practice is a continuation of the Level 1 practice. In this practice, you use the server list to copy, paste, and delete data sets.

1. Assign the Orion library by using the instructions in the Level 1 practice solution.
   
   
2. The server list can be used to create a copy of a data set. To create a temporary copy, right-click the travel_expenses table in the Orion library and select Copy. Then 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.
   
   
4. Right-click travel_expenses in the project tree or process flow and select Delete. Is the table deleted from the Work library?
   No. 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 Server List pane and select Delete. Are you able to delete the table?
   Yes. If you have Write access to a library and the data sets within, you can move, copy, and delete files in the Server List pane. These actions cannot be undone, so be careful!
   
6. Right-click travel_expenses in the Orion library in the Server List pane and select Delete. Are you able to delete the table?
   No. Because you used the ACCESS=READONLY option when you defined the orion library, you cannot make changes to the contents.
   

When you view an existing data source in Enterprise Guide, you use the data grid. You can search the data grid for a specific value or variable name. Then you can hold, hide, or move columns while you browse the data.

1. In Lesson 2 process flow, add the employee_master SAS data set.
   
   • Select File > Open > Data.
   • Navigate to the location of the course data. Select employee_master and select Open.
   
   
2. Use the Find option in the Edit menu to search for Warehouse Manager in the Job_Title column.
   
   • Select Job_Title to highlight the column.
   • Select Edit > Find from the menu bar.
   • Type Warehouse Manager in the Find What field.
   • Select Find Next and examine the search results.
   • Select Close.
   
   
3. Move the Manager_ID variable so that it appears to the right of Employee_Name. Note: Moving the variable does not change the variable order in the data set; only the display order in the data grid is changed.
   
   • Highlight the variable Manager_ID by clicking the column heading.
   • Click the column heading again and drag it to the right of Employee_Name while you hold down the left mouse button.
   
   
4. Close the data grid.
   
   

In this practice, you add the Addresses spreadsheet from the Excel employee_info workbook, and create a new SAS data set.

1. In the Lesson 2 process flow, add the employee_info.xlsx workbook and create a new SAS data set named employee_addresses. You can save the data set in the default SAS library.
   
   • Select File > Open > Data.
   • If necessary, navigate to the location where the course data is stored and double-click employee_info.xlsx.
   • In step 1, click Browse and change the name of the output data set to employee_addresses. Click Save.
   • Click Next.
   
   
2. Read the data from the Addresses worksheet and indicate that the first row contains column names.
   
   • In step 2, select the Addresses worksheet.
   • Verify that the First row of range contains field names check box is selected.
   • Click Next.
   
   
3. Change the type of the Postal_Code column to String.
   
   • 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 column label to replace the underscore with a space.
   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 importing the data and view the data set. The data set should have 424 rows.
   
   • Click Finish.
   • There should be 424 rows in the data set.
   
   
6. Name the task Import Employee Addresses.
   
   • Right-click Import Data (employee_info.xlsx[Addresses]) in the Project Tree and select Rename.
   • Type Import Employee Addresses.
   
   
7. Close the data grid and save the Employees project.
   
   • Close the data grid.
   • Select File > Save Employees, or click the Save Project tool.
   
   

The employee_payroll.csv file is a comma-delimited text file. The file contains salary and demographic information for Orion Star employess. The first row in the text file contains appropriate names for the columns in the new SAS data set. Note: Enclosing quotation marks are automatically removed when the file is imported.

1. In the Lesson 2 process flow, import the comma-delimited file employee_payroll.csv as a new SAS data set named employee_payroll.
   
   • Select File > Import Data
   • Navigate to the location where the course data is stored and select employee_payroll.csv.
   • Click Open.
   • In step 1 of the Import Data Wizard, verify that the name of the new SAS data set is employee_payroll.
   • Click Next.
   • In step 2, verify that a comma is the delimiter and that field names will be read from the first record. Click Next.
   
   
2. Do not include the columns for Marital_Status or Dependents.
   In step 3 of the Import Data Wizard, clear the check boxes for the Marital_Status and Dependents columns.
   
   
3. Change the type for the Employee_ID column to Number.
   
   • Click in the Type column for the Employee_ID variable. Click the down arrow and select Number.
   
   
4. Change the type for the Salary column to Currency.
   Click in the Type column for the Salary column. Click the down arrow and select Currency.
   
5. Change the output format for Birth_Date to a Day-Month-Year format with a four-digit year (for example, 16-12-2008). Hint: Use the DDMMYYDw.d format and experiment with the width to display the year with four digits.
   • For Birth_Date, double-click in the Output Format column and click the ellipsis button.
   • In the Date category, select the DDMMYYDw.d format and change the Overall width to 10.
   • Click OK.
   
   
6. Change the output format for Salary to a currency format displaying no decimal places.
   
   • For Salary, double-click in the Output Format column and click the ellipsis button. Change the value of Decimal places to 0. Click OK.
   
   
7. Change the label of Employee_Term_Date to Employee Termination Date.
   In the Label column, double-click to change the label to Employee Termination Date.
   
   
8. Finish importing the data and view the new data set. The data set should have 424 rows.
   
   • Click Finish.
   • There should be 424 rows in the data set. Verify that the formats were applied to the Birth_Date and Salary columns.
   
   
9. Name the task Import Employee Payroll.
   
   • Right-click Import Data (employee_payroll.csv) in the Project Tree and select Rename.
   • Type Import Employee Payroll.
   
   
10. Close the data grid and save the Employees project.
    
    • Close the data grid.
    • Select File > Save Employees.
    
    

This practice is a continuation of the Level 2 practice. Modify the code generated by the Import Data task to subset data to include only current employees who do not have a termination date.

1. Open the Import Employee Payroll task that you saved in the Level 2 practice.
   Double-click the Import Employee Payroll task in the project.
   
2. On the Code tab, find the INFILE statement. Notice that the program references a temporary text file Enterprise Guide created as part of the import process. Modify the task to set an advanced option to generalize the import step to run outside Enterprise Guide. Rerun the task and examine the Code tab. How did the INFILE statement change?
   
   • Select the Code tab and scroll down to find the INFILE statement.
   • Select Modify Task. Click Next three times to advance to Step 4. Select the Generalize import step to run outside SAS Enterprise Guide check box.
   • Click Finish and select Yes when you are prompted to replace the results.
   • Select the Code tab and scroll down to the INFILE statement.
     
     The INFILE statement now references the original text file in the location of the course data.
   
   
3. Right-click the Import Employee Payroll icon in the Project Tree and select Add as Code Template.
   
   
4. Modify the SAS program so that the name of the data set is employee_payroll_Active.
   
   • In the DATA statement, change the name of the output data set to employee_payroll_Active.
   
   
5. Add a subsetting IF statement to the code to include only those employees with a missing value for Employee_Term_Date.
   Following the INPUT statement, insert the following statement:
   
   if Employee_Term_Date=.;
   
   Note: If you enter the keyword IF and place your mouse pointer over the text, a syntax tooltip opens. For additional help, you can click the link to open the documentation.
   
6. Modify the code so that the Employee_Term_Date variable is not included in the output data set.
   
   • Find the DROP statement in the DATA step.
   • Add Employee_Term_Date to this statement so that it appears as follows:
     
     drop Marital_Status Dependents Employee_Term_Date;
   
   
7. Run the code and view the data in the new data set. There should be 308 rows. Save the project.
   
   • Click Run. View the data.
   • Close the results.
   • Click the Save Project tool.
   
   

In this practice, you create a report of frequency count and percentages for the employee_organization data.

1. In the Lesson 3 process flow, use the employee_organization data set in the One-Way Frequencies task to generate a report that analyzes the Department column.
   
   • Select the Lesson 3 process flow and add the employee_organization data set from the location of the course data.
   • Select Tasks > Describe > One-Way Frequencies.
   • Drag and drop Department to the Analysis variables role.
   
   
2. Include a horizontal bar chart as part of the report.
   
   • In the selection pane, select Plots.
   • Select Horizontal.
   
   
3. Add the title Employee Counts by Department and delete the footnote.
   
   • In the selection pane, select Titles.
   • For Analysis, clear the Use default text check box.
   • Delete the current text and type Employee Counts by Department.
   • Select Footnote and clear the Use default text check box. Delete the default text.
   
   
4. Modify the task properties to rename the task Dept Freq Report.
   
   • In the selection pane, select Properties.
   • Click Edit.
   • Delete the text in the Label box and type Dept Freq Report.
   • Click OK.
   
   
5. Run the task and view the results. Which department has the most employees?
   
   • Click Run.
   • View the report. The Sales Department has the most employees: 201.
   
   
6. Modify the One-Way Frequencies task to display only frequencies and percentages. Rerun the report and replace the results. The new report should include only the statistics you requested.
   
   • On the Results tab, click Modify Task to reopen the One-Way Frequencies task.
   • In the selection pane, select Statistics.
   • Select Frequencies and Percentages.
   • Click Run.
   • Click Yes when you are prompted to replace the results.
   • The report shows only the statistics you requested.
   
   
7. Close the task results and save the Employees project.
   
   • Close the task results.
   • Select File > Save Project, or click the Save Project tool.
   
   

In this practice, you create a report with frequencies and percentages that analyzes job titles by department.

1. In the Lesson 3 process flow, use the employee_organization data with the One-Way Frequencies task to analyze Job_Title, grouped by Department. Include only frequencies and percentages.
   
   • In the Lesson 3 process flow, select the employee_organization data set.
   • Select Tasks > Describe > One-Way Frequencies.
   • Drag and drop Job_Title to the Analysis variables role and Department to the Group analysis by role.
   • In the selection pane, select Statistics, and then select Frequencies and Percentages.
   
   
2. Order the output report by descending frequencies. Hint: You might need to scroll down in the Results pane to find the Order output data by option.
   
   • In the selection pane, select Results.
   • Change the Order output data by drop-down list to Descending frequencies.
   
   
3. Add the title Employees by Job Title and delete the footnote.
   
   • In the selection pane, select Titles.
   • For Analysis, clear the Use default text check box.
   • Delete the default text and type Employees by Job Title.
   • Select Footnote, and clear the Use default text check box. Delete the default text.
   
   
4. Rename the task Jobs by Dept Freq Report.
   
   • In the selection pane, select Properties.
   • Click Edit.
   • Delete the text in the Label box and type Jobs by Dept Freq Report.
   • Click OK.
   
   
5. Run the task and view the report. In the IS department, how many employees have the job title Applications Developer IV?
   
   • Click Run.
   • In the IS department, there are three employees with the job title Applications Developer IV.
   
   
6. Close the task results and save the Employees project.
   
   • Close the task results.
   • Click the Save Project tool.
   
   

This practice is a continuation of the Level 2 practice.

1. Modify the Jobs by Dept Freq Report task to add the title Employees by Job Title in #byval(department) Department. This syntax places the name of each distinct group in the title.
   
   • On the Results tab of the One-Way Frequencies task created in the Level 2 practice, select Modify Task.
   • Select Titles in the selection pane.
   • Change title to Employees by Job Title in #byval(department) Department.
   
   
2. Select Preview Code, and then select Show custom insertion points.
   At the lower left of the task window, click the Preview Code button. Then click the Show custom insertion points option.
   
3. To remove the secondary BYLINE title, add the following statement at the first point of insertion in the task code:
   
   options nobyline;
   
   To reinstate the secondary BYLINE title for future tasks, add the following statment at the end of the task code:
   
   options byline;
   
   • Click the first line that says <insert custom code here>.
   • Type the following SAS OPTIONS statement:
     
     options nobyline;
     
     
   • Click the first line that says <insert custom code here>.
   • Type the following SAS OPTIONS statement:
     
     options byline;
     
     
   • Close the Code Preview for Task window by clicking the close button (x) in the upper right corner of the window.
   
   
4. Run the task and replace the results. View the modified report.
   Click Run, and then click Yes when you are prompted to replace the results.
   
5. Save the Employees project.
   
   • Close the task results.
   • Click the Save Project tool.
   
   

In this practice, you modify a task to produce HTML results.

1. In the Lesson 3 process flow, modify the One-Way Frequency task named Dept Freq Report to create HTML output with the EGDefault style. Then, change the properties of the HTML to select a different style.
   
   
   • In the Lesson 3 process flow, double-click the One-Way Frequency task named Dept Freq Report to reopen the task. Note: You can open any reporting task that you saved in your project.
   • Click Modify Task.
   • In the selection pane, select Properties. Click Edit.
   • In the Properties window, select Results.
   • Select Customize results formats, styles, and behaviors. Then, select HTML.
   • In the Style drop-down list, change the style to EGDefault. Click OK.
   • Click Run. Click Yes when you are prompted to replace the results.
   • Click the Results – HTML tab to view the HTML results with the EGDefault style applied.
   • In the Results – HTML tab, click Properties.
   • Click the Style drop-down menu and select another style. Click OK. You can apply any style to generated HTML results.
   • Close the task results and click the Save Project tool.
   
   

In this practice, you use the List Data task to create a list of employees grouped by city.

1. In the Employees project, add the employee_addresses data set to the Lesson 3 process flow.
   
   • Select File > Open > Data.
   • If necessary, navigate to the location where the course data is stored. Double-click employee_addresses.
   
   
2. Use the List Data task to create a report that includes the Employee_Name, Street_Number, Street_Name, City, and Postal_Code columns. Identify each row with the Employee_ID column.
   
   
   • Select Describe > List Data.
   • On the Data page, press Ctrl and selectEmployee_Name, Street_Number, Street_Name, City, and Postal_Code. Drag the variables 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: Employee ID, Name, Street Number, Street Name, City, and Postal Code.
   
   • In the Task roles pane, right-click Employee_ID and select Show Labels to display labels for all variables.
   • In the Task roles pane, right-click each of the variable names that you need to change and select Properties.
   • In the Properties window, type the appropriate text in the Label box, and then click OK.
   
   
4. Create separate report sections for each city.
   Assign City to the Group Analysis by role.
   
5. Rename the task Employees by City.
   
   • In the selection pane, select Properties. Click Edit.
   • In the Label box, type Employees by City, and then click OK.
   
   
6. Run the task and view the report. How many cities are listed?
   
   • Click Run.
   • View the report. There are employees in five cities.
   
   
7. Close the task results and save the project.
   
   • Close the task results.
   • Click the Save Project tool.
   
   

In this practice, you add a filter to input data so that you have a report with only employees from San Diego.

1. In the Lesson 3 process flow, modify the Employees by City task. Note: If you do not have this task in your project, perform the steps in the previous practice and then come back to this practice.
   
   • Double-click the Employees by City task to reopen it.
   • Click Modify Task.
   
   
2. Apply a filter so that only the employees from San Diego are included in the report.
   
   • Click Edit to open the Edit Data and Filter window.
   • Using the first drop-down list in the task filter, select City.
   • In the second drop-down list, select Equal to as the operator.
   • For the third box, click the ellipsis button. Double-click San Diego.
   • Click OK to close the Edit Data and Filter window.
   
   
3. Delete the City column from the report.
   In the Task roles pane, select City and drag it back to the Variables to assign pane. Do this for all task roles that City is assigned to.
   
   
4. Change the title to Employee List for San Diego and delete the footnote.
   
   • In the selection pane, select Titles.
   • Clear the Use default text check box and delete the default text.
   • Type Employee List for San Diego.
   • Select Footnote and clear the Use default text check box. Delete the default text.
   
   
5. Run the task and do not replace the results.
   
   • Click Run.
   • Click No when you are prompted to replace the results.
   
   
6. View the report.
   
   
7. Rename the task San Diego Employees.
   
   • In the Project Tree, right-click Employees by City 1 and select Rename.
   • Type San Diego Employees.
   • Click OK.
   
   
8. Close the task results and save the Employees project.
   
   • Close the task results.
   • If you notice that you have an extra shortcut to the employee_addresses data in your process flow, you can delete it by right-clicking the icon and selecting Delete.
   • Click the Save Project tool.
   
   

In this practice, you create a list report and apply a filter to select Australian employees.

1. In the Lesson 3 process flow, use the employee_addresses data set to run the List Data task and create a report that includes the Employee_ID, Employee_Name, Street_Number, Street_Name, and Postal_Code columns.
   
   • Select the employee_addresses data set in the Lesson 3 process flow.
   • Select Tasks > Describe > List Data.
   • In the Variables to assign list, select Employee_ID, Employee_Name, Street_Number, Street_Name, and Postal_Code, and drag the variables to the List variables role.
   
   
2. Modify the properties for the columns so that the report displays the following labels: Employee ID, Name, Street Number, Street Name, and Postal Code.
   
   • In the Task roles pane, right-click any variable and select Show Labels to display the labels.
   • Right-click each of the variable names that you need to change and select Properties.
   • In the Properties window, type the appropriate text in the Label box.
   
   
3. Filter the data so that only the employees from Australia are included in the report. Note: Include all variations of AU.
   
   • On the Data page, 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 In a list as the operator.
   • For the third box, click the Ellipsis button. In the box, type AU, press Enter, and then type au. Click OK. The third box should have this: (AU, au).
   • Click OK to close the Edit Data and Filter window.
   
   
4. Group and identify the report by City.
   
   • Assign City to the Group analysis by role.
   • Also assign City to the Identifying label role.
   
   
5. Add the title Employee List for Australia to the report.
   
   • In the selection pane, select Titles.
   • Clear the Use default text check box, delete the default text, and type Employee List for Australia.
   
   
6. Rename the task as AU List.
   
   • In the selection pane, select Properties.
   • Click Edit and type AU List in the Label box.
   • Click OK to close the Properties window.
   
   
7. Run the task and view the report.  Australian employees should be listed and grouped by city name.
   
   • Click Run.
   • View the report.
   
   
8. Close the task results and save the Employees project.
   
   • Close the task results.
   • If necessary, delete the extra data shortcut from the process flow or Project Tree.
   • Click the Save Project tool.
   
   

Filtering data in tasks by using the Edit Data and Filter window is limited to the operators provided in the drop-down menus. To create filters that include SAS functions, you can use the Query Builder or you can modify the code to add a custom WHERE statement in the SAS program generated by the task.

1. In the Lesson 3 process flow, add the employee_master SAS data set.
   
   • Select the Lesson 3 process flow.
   • Select File > Open > Data and navigate to the location of the course data.
   • Double-click employee_master.
   
   
2. Use the List Data task to create a report that includes the Employee_ID and Birth_Date columns. Format Birth_Date with the DATE9. format.
   
   • Select Describe > List Data.
   • In the Data pane, select Employee_ID and Birth_Date and assign them to the List variables role.
   • Right-click Birth_Date and select Properties.
   • In the Properties window, click Change.
   • In the Format window, change the overall width to 9.
   • Click OK, and then OK again to return to the task window.
   
   
3. Modify the task code to insert a WHERE statement in the PROC PRINT step of the SAS program. The WHERE statement should select only those employees with a birthday in October.
   Hint: Use the MONTH function. You can view documentation by selecting Help > SAS on the Web > SAS Product Documentation. In the Search box, type MONTH function.
   • In the List Data window, click the Preview Code button to open the Code Preview for Task window.
   • Select the Show custom code insertion points check box.
   • Click the insertion point immediately below the FORMAT statement and above the RUN statement for PROC PRINT. Type the following SAS statement:
     
     where month(Birth_Date) = 10;
     
     
   • Close the Code Preview for Task window.
   
   
4. Do not print the row numbers in the report.
   
   • In the selection pane, select Options.
   • Clear the Print the row number check box.
   
   
5. Add the title Employees with October Birthdays.
   
   • In the selection pane, select Titles.
   • Clear the Use default text check box and delete the default text.
   • Type Employees with October Birthdays.
   
   
6. Change the task label to Oct Birthday List. Run the task and view the report.
   
   • In the selection pane, select Properties.
   • Click Edit and type Oct Birthday List in the Label box.
   • Click OK.
   • Click Run and view the report.
   
   
7. Save the project.
   
   • Click the Save Project tool.
   
   

In this practice, you use the Bar Chart Wizard and task to create a chart that shows employee salary for each city.

1. In the Employees project, add the employee_detail SAS data set to the Lesson 3 process flow.
   
   • Select Open > Data.
   • If necessary, navigate to the location where the course data is stored. Double-click employee_detail.
   
   
2. Open the Bar Chart Wizard. In step 2 of the Bar Chart Wizard, create a vertical bar chart showing the sum of Salary for each City. Arrange the bars in descending height.
   
   • Above the data grid, select Graph > Bar Chart Wizard.
   • In step 1, confirm that the data set is employee_detail. Click Next. Note: If you are accessing SAS on a server, the name might be different because SAS makes a copy of the data set on the server where it resides.
   • In step 2, change the Bars drop-down menu to City.
   • Click the Properties button (next to the Bars menu) and select Descending bar height. Click OK.
   • Change the Bar height drop-down menu to Salary.
   • Click Next.
   
   
3. In step 3 of the Bar Chart Wizard, change the following attributes:
   
   
   • Create a 3-D chart.
   • Color each bar differently.
   • Label the top of each bar with the sum of Salary.
   • Label the bar height (vertical) axis as Total Salary.
   • Show reference lines.
   • In step 3 of the wizard, select the 3D chart check box.
   • Change the Color bars by drop-down list to Bar category.
   • Select the Data labels check box and select Sum in the drop-down menu.
   • Click Axis Labels and type Total Salary in the Bar height box and click OK to close the Axis labels window.
   • Select the Use reference lines check box.
   • Click Next.
   
   
4. In step 4 of the Bar Chart Wizard, title the graph Total Salary for Each City.
   In the Graph box, type Total Salary for Each City.
   
   
   
5. Run the task and view the graph. Which city had the highest total salary?
   
   • Click Finish.
   • Miami-Dade had the highest total salary.
   
   
6. Right-click the Bar Chart icon in the Project Tree or Process Flow and select Open > Open in Advanced View. Make the following modifications:
   
   
   • Modify the format applied to Salary so that you round the values to the nearest dollar.
   • Change the bar shape to prism.
   • Change the reference lines to dashed and light gray.
   • Right-click the Bar Chart icon, and select Open > Open in Advanced View to open the Bar Chart task window.
   • In the selection pane, select Data.
   • In the Task roles list, right-click Salary and select Properties.
   • Click Change. Verify that the format is DOLLARw.d and change Decimal places to 0. Click OK. Then, click OK to return to the task window.
   • In the selection pane, select Layout.
   • Click the Shape menu and select Prism.
   • In the selection pane, select Reference Lines.
   • Click the Style drop-down menu and select Dashed.
   • Click the Color menu and select the light gray color block.
   
   
7. Modify the properties of the Bar Chart task to create an HTML report along with the default SAS Report format.
   
   • In the selection pane, select Properties, and then click Edit.
   • Select Results.
   • Select Customize result formats, styles, and behavior.
   • Select the HTML check box and click OK.
   
   
8. Run the task and replace the results. View the updated graph.
   
   • Click Run.
   • Click Yes when you are prompted to replace the results.
   
   
9. Close the task results and save the project.
   
   • Close the task results.
   • Select Save Project.
   
   

In this practice, you use the Pie Chart Wizard and task to create a chart that shows employee salary for each department.

1. In the Employees project, add the employee_detail SAS data set to the Lesson 3 process flow if necessary.
   
   • Select Open > Data.
   • If necessary, navigate to the location where the course data is stored. Double-click employee_detail.
   
   
2. Use the Pie Chart Wizard to generate a graph where the slices represent the sum of Salary for each department.
   
   • Select the employee_detail data set and then select Tasks > Graph > Pie Chart Wizard.
   • In step 1, verify that the data set is employee_detail and click Next.
   • In step 2, select Department as the Slice value and Salary as the Slice size value.
   • Click the statistics button (next to the Slice size menu) and verify that the selected statistic is Sum. Click Next.
   
   
3. Show the department name and salary total outside each slice, and the percentage of total salary on the inside of each slice. Make the chart three-dimensional.
   
   • In step 3, select the 3D chart check box.
   • Select the Percentage check box and change the drop-down list value to Inside.
   • Click Next.
   
   
4. Change the title to Total Salary by Department.
   In step 4, type Total Salary by Department in the Graph box.
   
   
   
5. Run the task and view the pie chart. Which department has the highest total salary?
   
   • Click Finish.
   • View the graph. The Sales Department has the highest total salary.
   
   
6. Open the task in Advanced View to apply an appropriate format that rounds the display of Salary to the nearest dollar. Create the graph in HTML as well as in SAS Report format. Note: For now, ignore the warning symbol on the task icon.
   
   • In the Project Tree or Process Flow, right-click the Pie Chart task and select Open > Open in Advanced View.
   • In the selection pane, select Data.
   • Right-click Salary and select Properties. Click Change.
   • Verify that the format is DOLLARw.d and change Decimal places to 0. Click OK. Click OK to return to the task window.
   • In the selection pane, select Properties. Click Edit.
   • Select Results.
   • Select Customize result formats, styles, and behavior.
   • Select HTML and click OK.
   
   
7. Run the task and replace the results. View the updated pie chart. Note: You might see a warning symbol on the task icon, which indicates that there is a warning in the log. Examine the log to determine whether the warning affects the output.
   
   • Click Run. Click Yes when you are prompted to replace the results.
   • To view the log, click the Log tab. Scroll down to find the following message: WARNING: Text boundaries for the pie/donut slices overlap. This means that some of your labels are close together or overlapping, but it doesn't seriously affect the output.
   
   
8. Close the task results and save the Employees project.
   
   • Close the task results.
   • Click the Save Project tool.
   
   

This practice is a continuation of the Level 2 practice.

1. Modify the Pie Chart task to slightly separate the Sales slice from the remainder of the pie. This can be done by inserting the explode="Sales" option at the end of the PIE3D statement.
   
   • Open the Pie Chart task and click Modify Task.
   • Click Preview Code, and select the Show custom code insertion points check box.
   • Click to insert code before the semicolon that closes the PIE3D statement. This location is immediately after the NOHEADING option and before the FORMAT statement.
   • Enter the following option:
     
     explode="Sales"
     
     Note: Sales is case sensitive.
   • Close the Code Preview for Task window.
   
   
2. Run the task and replace the results. View the updated pie chart. Save the Employees project.
   
   • Click Run, then Yes when you are prompted to replace the results. View the pie chart.
   • Close the task results.
   • Click the Save Project tool.
   
   

In this practice, you use the Filter and Sort task to create a new table with San Diego employees sorted in postal code order.

1. Open the Employees project and create a new process flow named Lesson 4.
   
   • In the Employees project, click the New tool and select Process Flow.
   • Right-click Process Flow and select Rename. Type Lesson 4 and press Enter.
   
   
2. Add the employee_addresses data set to the Lesson 4 process flow.
   
   • Select Open > Data.
   • If necessary, navigate to the location where the course data is stored. Double-click employee_Addresses.
   
   
3. Using the Filter and Sort task, create a new table for the San Diego office manager. This table should include Employee_ID, Employee_Name, Street_Number, Street_Name, and Postal_Code.
   
   • Above the data grid, click Filter and Sort.
   • In the Variables tab, press CTRL and select Employee_ID, Employee_Name, Street_Number, Street_Name, and Postal_Code. Click the single arrow to move the variables to the Selected pane.
   
   
4. Create a filter to include only the employees from San Diego in the output table.
   
   • Click the Filter tab.
   • In the first drop-down menu, select City.
   • In the second drop-down menu, select Equal to.
   • For the third box, click the Ellipsis button, and then select San Diego. Click OK.
   
   
5. Order the output table in ascending postal code order
   
   • Click the Sort tab.
   • Click the Sort drop-down menu and select Postal_Code.
   
   
6. Name the task and output table SanDiegoEmployees.
   
   • Click the Results tab.
   • Type SanDiegoEmployees in the Task name field.
   • Click Change and type SanDiegoEmployees in the File name field.
   • Click Save.
   
   
7. Run the task and view the output data set. How many rows are in the new data set?
   
   • Click OK.
   • The new data set has 112 rows.
   
   
8. Close the task results and save the Employees project.
   
   • Close the task results.
   • Click the Save Project tool.
   
   

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

1. If you haven't already done so, open the Employees project and create a new process flow named Lesson 4. Add the employee_organization data set to the project.
   
   • In the Employees project, click the New tool and select Process Flow.
   • Right-click Process Flow and select Rename. Type Lesson 4 and press Enter.
   • Select Open > Data.
   • If necessary, navigate to the location where the course data is stored. Double-click employee_organization.
   
   
2. Use the Query Builder to create a query named Sales Employees Query and a table named Sales_Emps. Include all employees with the word Sales as part of their job title.
   
   • Above the data grid, click Query Builder, or right-click employee_organization and select Query Builder.
   • Type Sales Employees Query in the Query name box.
   • For the output data set, click Change and type Sales_Emps in the File name box. Click Save.
   • Click the Filter Data tab. Drag and drop Job_Title into the tab area.
   • In the New Filter window, select Contains from the Operator drop-down list.
   • Type Sales in the Value box and click Finish.
   
   
3. Include all columns and sort the resulting table by Department in ascending order
   
   • Click the Select Data tab.
   • Drag t1(employee_organization) onto the tab to add all columns in the data set.
   • Click the Sort Data tab. Drag and drop Department into the tab area. The default sort order is ascending.
   
   
4. Run the query and view the output data set.
   
   • Click Run.
   • Scroll through the output data. Verify that all values in the Job_Title column contain the word Sales. The new table has 189 rows.
   
   
5. Close the query results and save the Employees project.
   
   • Close the query results.
   • Click the Save Project tool.
   
   

Use the Data Explorer to create a subset of Australian employees with salaries less than $100,000.

1. In the Lesson 4 process flow, open a Data Exploration to explore the employee_master table in the orion library.
   Note: If the orion library is not available, use the Assign Project Library task to create the orion library that points to the location of the course data. Refer to the practice in Lesson 2.
   
   • Select File > Open > Data Exploration.
   • Double-click Servers > Libraries > ORION > employee_master.
   
   
2. Use the Data Exploration pane on the right to select only the following columns: Employee_ID, Employee_Name, Employee_Hire_Date, Salary, City, Department, and Job_Title.
   
   • In the Data Exploration pane, select Edit next to Selected Columns.
   • Hold down the Ctrl key and highlight Employee_ID, Employee_Name, Employee_Hire_Date, Salary, City, Department, and Job_Title. Select the arrow to move the columns to Selected columns.
   • Click Add to Pending Updates.
   
   
3. Filter the rows to include only employees from Australia who have salaries less than $100,000.
   
   • In the Data Exploration pane, select Edit next to Filter Conditions.
   • Change the first Select column value to Country.
   • Click the first filter, and select Click to load values. Select AU > Apply Selection.
   • Change the second Select column value to Salary.
   • Click the second filter and select the Conditional Filter tab.
   • Change is equal to to is less than and type 100000. Select Apply Selection.
   • Click Add to Pending Updates.
   
   
4. Sort the data by ascending employee name.
   
   • In the Data Exploration pane, select Edit next to Sort Order.
   • Select Employee_Name and select the arrow to move it to Selected columns.
   • Click Add to Pending Updates.
   
   
5. Apply all updates and view the results. There should be 71 rows and 7 columns.
   Select Apply Updates in the Data Exploration pane.
   
6. Select the Quick Stats icon in the Data Exploration pane and select Click to run all statistics. For each column, use the magnifying glass pointer to view the tables and graphs so you can answer the following questions:
   
   
   • What percentage work in the Sales Department?
   • What cities do they work in?
   • What is the average salary?
   • What is the oldest hire date?
   
   
   • What percentage work in the Sales Department? 64. 79%
   • What cities do they work in? Sydney, Melbourne
   • What is the average salary? $29,819.72
   • What is the oldest hire date? 01JAN1976
   
   
7. Add the Data Exploration to the process flow as a new data source.
   Select Add to Process Flow.
   
8. Save the Employees project.
   
   • Click the Save Project tool.
   
   

In this practice, you use the Query Builder to create a computed column, New_Salary, which is the current salary plus an additional 2% raise.
Practice: Create a Table with a Computed Column

1. In the Employees project, add the employee_payroll data set to the Lesson 4 process flow.
   
   • Select Open > Data.
   • If necessary, navigate to the location where the course data is stored. Double-click employee_payroll.
   
   
2. Use the Query Builder to create a query named New Salary Query. Name the output table New_Salary.
   
   • Above the data grid, click Query Builder, or right-click employee_payroll and select Query Builder.
   • Type New Salary Query in the Query name box.
   • Click Change to the right of the Output name box.
   • Type New_Salary in the File name box, and then click Save.
   
   
3. Include Employee_ID, Employee_Gender, Salary, Birth_Date, and Employee_Hire_Date. Modify the properties of the Salary column to change the column name to Old_Salary and add the Dollar12.2 format.
   
   • Press Ctrl and select Employee_ID, Employee_Gender, Salary, Birth_Date, and Employee_Hire_Date. Drag the columns and drop them on the Select Data tab.
   • Select Salary in the Select Data tab and click the Properties button. Note: Point your cursor to any button to see a tooltip with the name of the button.
   • In the Properties for Salary window, type Old_Salary in the Column Name box.
   • Click Change. Select Currency and DOLLARw.d. Change the overall width to 12 and decimal places to 2. Click OK.
   
   
4. Create a filter to include only active employees who have a missing value for Employee_Term_Date.
   
   • Click the Filter Data tab.
   • Drag and drop Employee_Term_Date onto the tab to open the New Filter Wizard.
   • In the Operator box, select Is Missing.
   • Click Finish.
   
   
5. Create a new column named New_Salary that is the current salary plus an additional 2% raise. Format the column to include dollar signs and two decimal places. Hint: Multiply Old_Salary by 1.02.
   
   • Click the Select Data tab.
   • Click Computed Columns, and then click New.
   • Select Advanced expression, and then click Next.
   • Expand Selected Columns to view the columns in the query. Double-click the Old_Salary column to add it to the expression. Note: The name of the variable in the expression will be t1.Salary, referring to the column name from the input table. The query result will reflect the column name change to Old_Salary.
   • Using the menu bar or the keyboard, type * (the multiplication operator), and type 1.02 for the operand.
   • Click Next.
   • In step 3 of the wizard, type New_Salary in the Column Name box.
   • Click Change next to the Format box.
   • In the Formats window, select the Currency category and the DOLLARw.d format. Set Overall width to 12 and Decimal places to 2.
   • Click OK, and then click Next.
   • Click Finish after reviewing the Summary step.
   
   
6. Run the query and view the output data set.
   
   • Click Run.
   • View the Old_Salary and New_Salary columns to see the raise added to the salary.
   
   
7. Close the query results and save the Employees project.
   
   • Close the query results.
   • Click the Save Project tool.
   
   

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. Add the employee_donations data set to the project.
   
   • Select Open > Data.
   • If necessary, navigate to the location where the course data is stored. Double-click employee_donations.
   
   
2. Use the Query Builder to create a query named Donations Query and a table named Total_Donations.
   
   • Right-click Employee_Dontations in the Project Tree and select Query Builder.
   • In the Query name box, type Total Donations Query.
   • Click Change. In the File name box, type Total_Donations and click Save.
   
   
3. Include the Employee_ID, Recipients, and Paid_By columns in the new table.
   Double-click the columns Employee_ID, Recipients, and Paid_By to add each one to the Select Data tab.
   
   
   
4. Create a new column named Total_Donations, which totals the values of the four quarterly donations. Format the new column to display dollar signs, commas, and two decimal places. Note: Use a function to calculate the total, and thereby ignore any missing values that might be in the input columns.
   
   • Click Computed Columns, and then click New.
   • Select Advanced expression, and then click Next.
   • In the Enter an expression text box, type the following: sum(Qtr1, Qtr2, Qtr3, Qtr4).
   • Click Next.
   • Type Total_Donations in the Column Name box.
   • Click Change. In the Formats window, select the Currency category and select the DOLLARw.d format. Set Overall width to 10 and Decimal places to 2. Click OK, and then click Next.
   • After verifying the summary, click Finish.
   • Click Close in the Computed Columns window.
   
   
5. Run the query and view the output data set. Verify that the new column was calculated correctly.
   
   • Click Run to submit the query.
   • You can click the Input Data tab to see the donations for each quarter and then click the Output Data tab to see the total donation for the year.
   
   
6. Close the query results and save the Employees project.
   
   • Close the query results.
   • Click the Save Project tool.
   
   

Use the Query Builder to create a table that includes active employees (those with no termination date) and the number of years that each was employed. No programming is required to complete this practice.

1. Use the employee_payroll table to create a query named Years Employed Query and a table named Years_Employed. Include Employee_ID and Employee_Hire_Date. Include only employees without a termination date.
   
   • If necessary, add the employee_payroll table to the project.
   • In the data grid, select Query Builder or right-click employee_payroll in the project and select Query Builder.
   • In the Query name field, type Years Employed Query.
   • Click Change. Type Years_Employed in the File name field and select Save.
   • On the Select Data tab, double-click the Employee_ID and Employee_Hire_Date columns to add each to the query.
   • Click the Filter Data tab.
   • Drag and drop Employee_Term_Date into the Filter the raw data text box. In the Edit Filter Wizard, select the Is missing operator. This part of the filter keeps only the active employees.
   • Click Finish.
   
   
2. Create a new column named Years_Employed that calculates the number of years that each employee worked at Orion Star, based on the hire date and today's date. Apply a format that displays the values rounded to one decimal place.
   
   Hint: The YRDIF function can be used to calculate the number of years between two dates. Also, the TODAY function can be used to provide the current date. To learn about the YRDIF function, you can select the function in the Advanced Expression Editor (in the Date and Time category). A new value for the basis (the third argument in the YRDIF function) was added in SAS 9.3. AGE calculates the accurate age based on the start and end dates provided in the YRDIF function. In earlier versions of SAS, you can use ACTUAL as the basis, but it can produce unexpected results if the start or end date occurs in a leap year.
   
   • Click the Select Data tab.
   • Select Computed Columns > New.
   • Select Advanced expression > Next.
   • Use the available functions list or enter the following expression:
     yrdif(Employee_Hire_Date, today( ), 'age')
   • Click Next. Type Years_Employed in the Column Name field.
   • Click Change to apply a format.
   • In the Format window, select the Numeric category and the w.d format.
   • Change the overall width to 4 and the decimal places to 1.
   • Click OK > Next.
   • Verify the summary and click Finish.
   
   
3. Run the query and view the output data set. Save the Employees project.
   
   • Click Run to submit the query. Your table should have 308 rows with 3 columns and values in the Years_Employed column.
   • Click Save Project.
   
   

In this practice, you group data by values of a column and you apply summary statistics to a column to create an output table with the average salary by city.

1. In the Employees project, add the employee_master table to the Lesson 4 process flow.
   
   • Select Open > Data.
   • If necessary, navigate to the location where the course data is stored. Double-click employee_master.
   
   
2. Use the Query Builder to create a query named Average Salary per City Query and an output table named AvgSal_City.
   
   • Above the data grid, click Query Builder.
   • Type Average Salary per City Query in the Query name box.
   • Click Change and type AvgSal_City in the File name box.
   • Click Save.
   
   
3. Include only the City and Salary columns in the query.
   Drag and drop the City and Salary columns into the Select Data tab.
   
   
   
4. Select the AVG (average) statistic for Salary. Note: After you choose a summary statistic, the Summary groups section opens at the lower part of the Select Data tab. The Automatically select groups check box is selected.
   
   • In the Select Data tab, click in the Summary column next to Salary and select AVG from the statistics list.
   • Verify that the name of the new column changes to AVG_of_Salary.
   
   
5. Sort the table in decreasing order of AVG_of_Salary.
   
   • Click the Sort Data tab.
   • Drag and drop AVG_of_Salary into the Sort Data tab.
   • Change the sort direction to Descending.
   
   
6. Run the query and view the output data. What city has the lowest average salary?
   
   • Click Run.
   • View the results. Melbourne has the lowest average salary at $31,394.62.
   
   
7. Close the query results and save the Employees project.
   
   • Close the query results.
   • Click the Save Project tool.
   
   

In this practice, you use the Query Builder to group data and apply summary statistics to two columns. Then you filter the grouped data.

1. Add the employee_master data set to the Lesson 4 process flow if necessary.
   Select Open > Data, navigate to the location where the course data is stored, and double-click the employee_master table.
   
   
   
2. Create a new query named Salary Summary by Dept Query and an output table named Salary_Summary.
   
   • Above the Data Grid, click Query Builder, or right-click employee_master in the project and select Query Builder.
   • Type Salary Summary by Dept Query in the Query name box.
   • Click Change. Type Salary_Summary in the File name box.
   • Click Save.
   
   
3. Include Department, Employee_ID, and Salary (twice) in the Select Data tab. Select the appropriate statistics to calculate the number of employees, the average salary, and the total salary for each department.
   
   • Double-click or drag and drop the Department, Employee_ID, and Salary columns into the Select Data tab. Make sure that you add Salary two times.
   • Next to Employee_ID, click in the Summary column and select COUNT.
   • Next to the first instance of Salary, click in the Summary column and select AVG.
   • Next to the second instance of Salary, click in the Summary column and select SUM.
   
   
4. Modify the formats applied to the AVG_of_Salary and SUM_of_Salary columns to round values to the nearest dollar.
   
   • Highlight the AVG_of_Salary column in the Select Data tab and click the Properties button.
   • Click Change next to the Format box.
   • In the Formats window, change the number of decimal places to 0. Click OK, and then click OK again.
   • Repeat these steps for the SUM_of_Salary column.
   
   
5. Filter the query to include only those departments with more than 10 employees.
   
   • Click the Filter Data tab.
   • Drag COUNT_of_Employee_ID to the Filter the summarized data pane.
   • In the New Filter window, select Greater than in the Operator box.
   • Type 10 in the Value box, and then click Finish.
   
   
6. Sort the data in decreasing sequence of department size, based on the number of employees.
   
   • Click the Sort Data tab.
   • Drag and drop COUNT_of_Employee_ID into the tab area.
   • Change the sort direction to Descending.
   
   
7. Run the query and view the output data. How many employees are in the Stock and Shipping Department and what is their average salary?
   
   • Click Run.
   • View the results. There are 18 employees in the Stock and Shipping Department, and the average salary is $35,046.
   
   
8. Close the query results and save the Employees project.
   
   • Close the query results.
   • Click the Save Project tool.
   
   

In this practice, you use the Query Builder to produce a report of the top ten jobs in terms of salary, excluding jobs at the chief level.

1. If necessary, add the employee_master data set to the Lesson 4 process flow.
   
   
2. Create a new query named Top 10 Paid Job Titles.
   
   • In the data grid, select Query Builder or right-click employee_master in the project and select Query Builder.
   • Type Top 10 Paid Job Titles in the Query name field.
   
   
3. Calculate the average salary for each job title. Rename the column AverageSalary and apply a format to round to the nearest dollar.
   
   • Double-click or drag and drop the Job_Title and Salary columns onto the Select Data tab.
   • Next to Salary on the Select Data tab, change the Summary field to AVG.
   • Select the AVG_of_Salary column on the Select Data tab and click to modify the properties.
   • Type AverageSalary in the Column Name field.
   • Click Change next to the Format field. Change the decimal places to 0. Click OK > OK.
   
   
4. Filter the data 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 window, select Does not contain in the Operator field.
   • Type Chief in the Value field and click Finish.
   
   
5. Sort the data in decreasing sequence of average salary.
   
   • Click the Sort Data tab.
   • Drag and drop AVG_of_Salary onto the tab area.
   • Change the sort direction to Descending.
   
   
6. 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.
   • Select Options.
   • In the Result format pane, select the Override the corresponding default settings in Tools->Options check box and select Report.
   • In the Query limits pane, select the Limit number of rows to save in output check box and type 10.
   • Select Titles in the selection pane and select the Override the corresponding default settings in Tools->Options check box.
   • Type Top 10 Average Salaries by Job Title and click OK.
   
   
7. Run the query and view the report. Save the project.
   
   • Click Run and view the results.
   • Click Save Project on the toolbar.
   
   

In this practice, you join two tables and then you create and format a computed column. Before creating the output data, you apply a filter.

1. In the Lesson 4 process flow, use the Query Builder to join employee_payroll and employee_addresses to create a table called employee_payroll_Location. Name the query Payroll Location Join Query.
   
   Note: If the employee_payroll and employee_addresses tables are not in the Lesson 4 process flow, add them first.
   
   • In the Lesson 4 process flow, press Ctrl and select employee_payroll and employee_addresses. With both tables selected, right-click one of the tables and select Query Builder.
   • Type Payroll Location Join Query in the Query name box.
   • Click Change. Type employee_payroll_Location in the File name box. Click Save.
   
   
2. Verify that the join between the two tables is on the Employee_ID column.
   
   • Click Join Tables. The two tables should automatically be connected by the Employee_ID column.
   • Click Close.
   
   
3. Include the following columns to the Select Data tab in order: Employee_ID, Employee_Name, Employee_Gender, Birth_Date, Salary, Street_Number, Street_Name, City, State, and Country.
   
   • Add the listed columns to the Select Data tab.
   • If necessary, reorder the columns so that they are in the order shown in the step.
   
   
4. Format the Salary column with a dollar sign, comma, and two decimal places. Format the Birth_Date column with the calendar form 01JAN2009.
   
   • Highlight Salary on the Select Data tab and click Properties.
   • Click Change next to the Format box.
   • 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, and then click OK again.
   • Highlight Birth_Date on the Select Data tab and click Properties.
   • Click Change next to the Format box.
   • Select Date from the Categories pane and DATEw. from the Formats pane.
   • Change the overall width to 9.
   • Click OK, and then click OK again.
   
   
5. Create a new column named Bonus that represents 1.5% of the Salary column. Format the values with a dollar sign, comma, and two decimal places.
   
   • On the Select Data tab, click the Computed Columns button to begin creating a new column.
   • In step 1, select Advanced expression, and then click Next.
   • In step 2, type or click to provide the following expression: Salary * .015. Note: If you select Salary rather than type it in the expression, it will be included as either t1.Salary or t2.Salary.
   • In step 3, type Bonus in the Column Name box.
   • Click Change next to the Format box.
   • Select Currency from the Categories pane and DOLLARw.d from the Formats pane.
   • Change the overall width to 10 and the decimal places to 2. Click OK.
   • Click Next, and then click Finish.
   
   
6. Include only active employees in the output table, or those that do not have a value for Employee_Term_Date.
   
   • Click the Filter Data tab. Drag and drop Employee_Term_Date into the tab area.
   • In the New Filter window, change Operator to Is missing and click Finish.
   
   
7. Run the query and view the output data.
   
   • Click Run.
   • Scroll right to view the Bonus column you created.
   
   
8. Close the query results and save the Employees project
   
   • Close the query results.
   • Click the Save Project tool.
   
   

In this practice, use the Query Builder to identify employees who did not make donations. The list is used to inform those employees about the donation program.

1. Add the employee_donations table to the Lesson 4 process flow if necessary.
   
   
2. Build a query to join the employee_addresses table with employee_donations. Name the query Employees Without Donations Query and the table no_donations.
   
   • In the Lesson 4 process flow, hold down the Ctrl key and select the employee_donations and employee_addresses tables. Right-click one of the tables and select Query Builder.
   • Type Employees Without Donations Query in the Query name field.
   • Click Change and type no_donations in the File name field. Select Save.
   • Select Add Tables. Select employee_addresses from the project and select Open.
   
   
3. Modify the join properties to include all rows from the employee_addresses table.
   
   • Select Join Tables to open the Tables and Joins window. Right-click the Venn diagram that connects the two tables and select Properties.
   • In the Join Properties window, verify that the table on the left is employee_addresses. Change the join type to All rows from the left table given a condition.
   • Click OK, and then click Close.
   
   
4. Include all columns from the employee_addresses table.
   
   • Drag the t1(employee_addresses) table onto the Select Data tab.
   
   
5. Add a filter to include only the missing Employee_ID values from the employee_donations table. This isolates those employees who do not appear in the employee_donations table.
   
   • Click the Filter Data tab.
   • Drag Employee_ID from the employee_donations table onto the tab.
   • In the New Filter window, change the operator to Is missing.
   • Click Finish.
   
   
6. Submit the query and view the output data. Save the Employees project.
   
   
   • Click Run.
   • There are 300 employees who did not give donations. Close the query results.
   • Click the Save Project tool.
   
   

Use the Query Builder to join the data from two sources where the join condition involves a non-equijoin condition. Note: No SAS programming experience is required to complete this practice.

Two columns in the bonus_schedule worksheet, 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 Employee_Hire_Date column with the Employed_After column with the greater-than-or-equal-to operator. Set a second join with the Employee_Hire_Date. This time, use the Employed_Before column with the less-than-or-equal-to operator.

1. Add the employee_payroll and bonus_schedule data sets to the Lesson 4 process flow if necessary.
   
   
2. Because there are no matching columns between these two tables, a manual join is required. Join the tables so that Employee_Hire_Date is on or after the Employed_After column and is on or before the Employed_Before column.
   
   • In the process flow, hold down the Ctrl key and select the employee_payroll 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 Employee_Hire_Date in the employee_payroll table. Select Employee_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 so that it is t1.employee_hire_date >= t2.Employed_After. Click OK.
   • Select Employee_Hire_Date again in the employee_payroll table. Select Employee_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 so that it is t1.employee_hire_date <= t2.Employed_Before.
   • Click OK, then Close.
   
   
3. Add Employee_ID, Employee_Hire_Date, Salary, and Bonus_Percent to the query.
   
   • Click the Select Data tab.
   • Double-click Employee_ID, Employee_Hire_Date, Salary, and Bonus_Percent to add each column to the query.
   
   
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.
   
   • On the Select Data tab, click New computed column to begin creating a new column.
   • In Step 1, select Advanced expression. Click Next.
   • In Step 2, type or click to create the following expression: t1.Salary * t2.Bonus_Percent
   • Click Next. In Step 3, type 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 10 and the decimal places to 2. Click OK.
   • Click Next, then click Finish.
   
   
5. Submit the query and view the output data. Save the project.
   
   • Click Run.
   • The output data has 424 rows.
   • Click Save Project.
   
   

In this practice, you generate a report that analyzes the salaries of all employees by Department.

1. Open the Employees project and create a new process flow named Lesson 5. Add the employee_master data set to the new process flow.
   
   • Click the New tool and select Process Flow. Click the Process Flow label and type Lesson 5, and then press Enter.
   • Click the Open tool and select Data.
   • If necessary, navigate to the location where the course data is stored. Double-click employee_master.
   
   
2. Use the Summary Statistics Wizard to generate a report on the Salary column by Department.
   
   • In the Data Grid, select Describe > Summary Statistics Wizard.
   • In step 1, verify that the data being processed is employee_master and click Next.
   • In step 2, drag the Salary column to the Summary statistics of pane and the Department column to the For each value of pane. Click Next.
   
   
3. Include the mean, minimum, maximum, and median salary for each group and round values to the nearest dollar.
   
   • In step 3, click Edit to open the Edit Statistics window. In the Basic tab, clear the Standard deviation and Number of observations check boxes. Use the drop-down box to select 0 for the number of decimal places.
   • Click the Percentile tab and select Median. Click OK to close the Edit Statistics window.
   
   
4. Create a SAS data set named salary_stats that includes the calculated statistics.
   
   • In step 3, select the Save statistics to data set check box.
   • Click Browse.
   • Type salary_stats in the File name box and click Save.
   • Click Next.
   
   
5. Type Summary Statistics for Salary by Department as the title and delete the footnote.
   
   • In step 4, add the title Summary Statistics for Salary by Department.
   • Delete the footnote.
   
   
6. Run the task and view the report. What is the mean salary for the Purchasing Department? Rename the task Salary by Dept Summary.
   
   • Click Finish. The mean salary for the Purchasing Department is $39,417.00.
   • In the Process Flow window, right-click the Summary Statistics Wizard icon and select Properties.
   • Change the label to Salary by Dept Summary.
   • Click OK to close the Properties window.
   
   
7. View the output SAS data set. As a step in the project, export the output data to an Excel file named SalaryStats. Select an option to use labels for column names.
   
   • Click the Output Data tab to view the output data. Notice that the data has some additional columns that were not in the report: _WAY_, _TYPE_, and _FREQ_. These columns were generated by the Summary Statistics analysis.
   • Select Export > Export Summary Statistics for <libname>.EMPLOYEE_MASTER As A Step In Project.
   • In the Export Wizard, click Next to advance to step 2.
   • In step 2, select Microsoft Excel Workbooks (*.xlsx). Click Next.
   • In step 3, select the Use labels for column names check box. Click Next.
   • In step 4, click Browse and navigate to a location where you have Write access.
   • Type SalaryStats in the File name box and click Save. Click Next.
   • In step 5, verify the selections and click Finish.
   • View the Lesson 5 process flow. The task creates an output data set and a SAS report, and the output data is exported to Excel.
   
   
8. Close the task results and save the Employees project.
   
   • Close the task results.
   • Click the Save Project tool.
   
   

In this practice, you generate a report to analyze the salaries of all employees by Department and State, and then you modify the report to include all combinations of Department and State.

1. If you have not already done so, open the Employees project and create a process flow named Lesson 5. Add the employee_master data set to the new process flow.
   
   • Create a new process flow named Lesson 5.
   • Select Open > Data.
   • If necessary, navigate to the location where the course data is stored. Double-click employee_master.
   
   
2. Use the Summary Statistics Wizard to summarize Salary by Department first, and then by State.
   
   • Select Tasks > Describe > Summary Statistics Wizard.
   • In step 1, verify that the data being processed is employee_master, and then click Next.
   • In step 2, drag the Salary column to the Summary statistics of pane and the Department and State columns to the For each value of pane. Click Next.
   
   
3. Include the mean, minimum, maximum, and median salary for each group, and round values to two decimal places.
   
   • In step 3, click Edit to open the Edit Statistics window. On the Basic tab, clear the Standard deviation and Number of Observations check boxes. Use the drop-down box to select 2 for the number of decimal places.
   • Click the Percentile tab and select Median. Click OK to close the Edit Statistics window.
   • Click Next.
   
   
4. Type Summary Statistics for Salary by Department and State as the title and delete the footnote.
   In step 4, add the title Summary Statistics for Salary by Department and State in the Analysis pane, and delete the footnote.
   
   
   
5. Run the task and view the report. Rename the task Salary by Dept, State Summary.
   
   • Click Finish and review the results.
   • In the Process Flow window, right-click the Summary Statistics Wizard icon and select Properties.
   • Change the label to Salary by Dept, State Summary. Click OK to close the Properties window.
   
   
6. Modify the task to generate multiple reports for all combinations of the two classification variables.
   
   • In the Project Tree, right-click the Summary Statistics Wizard icon and select Open > Open in Advanced View to open the Summary Statistics task window.
   • In the selection pane, select Results. Click the Combinations of classification variables drop-down menu and select All ways.
   
   
7. Run the report and click Yes when you are prompted to replace the results. View the updated report and verify that four tables are included in the report, that is, one for each combination of the classification variables.
   
   • Click Run.
   • Click Yes.
   
   
8. Modify the task again to include only the second and third tables from the previous results. Summarize Salary by Department first and Salary by State second. Run the task and replace the results. View the updated report.
   
   • Click Modify Task to reopen the task dialog box.
   • In the selection pane, select Results.
   • Click the Combination of classification variables drop-down menu and select Specify ways. In the Specify ways box, type 1.
   • Click Run. You should now have a report with only the tables for State and Department.
   
   
9. Close the task results and save the project.
   
   • Close the task results.
   • Click the Save Project tool.
   
   

Insert custom code into the SAS program generated by the Summary Statistics task to calculate the skewness statistic for employee salary by country.

1. In the Lesson 5 process flow, add the employee_master table if necessary.
   
   
2. Use the Summary Statistics Wizard to create a report to analyze salary by country. Include the default statistics and round the values to two decimal places. Include a histogram in the report. Add a title to the analysis: Salary by Country Summary. Delete the title and footnote for the histogram.
   
   • In the data grid, select Describe > Summary Statistics Wizard.
   • In Step 1, verify that the data being processed is employee_master and click Next.
   • In Step 2, drag the Salary column to the Summary statistics of pane and the Country column to the For each value of pane. Click Next.
   • In Step 3, click Edit to open the Edit Statistics window. On the Basic tab, use the drop-down box to select 2 for the number of decimal places. Click OK to close the Edit Statistics window. Select the Histogram check box. Click Next.
   • In Step 4, enter the title Salary by Country Summary in the Analysis pane. Delete the histogram title and the footnote.
   • Click Finish and view the report.
   
   
3. Run the task and view the results. Use the Properties window to rename the task Salary by Country Summary.
   
   • Click Finish.
   • In the In the Project Tree, right-click the Summary Statistics Wizard you just ran and select Properties.
   • Change the label to Salary by Country Summary. Click OK to close the Properties window.
   
   
4. Reopen the task in Advanced View and modify the underlying code in the Salary by Country Summary task to add the skewness statistics in the report. Add the SKEWNESS keyword directly after other listed statistics.
   
   Note: Skewness is a measure that indicates to what degree the distribution of data values has a longer tail to the left or to the right. For the salary distribution for both US and AU, there are a few extreme values at the high end and a concentration of values at the low end of the distribution, so the right tail of the distribution curve is longer (right-skewed). For such data, the skewness statistic is greater than zero. If the data is left-skewed, the skewness statistic is less than zero.
   
   • Right-click Salary by Country Summary in the project tree or process flow and select Open > Open in Advanced View.
   • Select Preview Code and select the Show custom code insertion points check box.
   • Scroll down to find the end of the PROC MEANS statement. Click to insert custom code between the N option and the semicolon. Type skewness.
   • Close the Code Preview window.
   
   
5. Run the task and replace the results. Verify that the additional skewness statistic was added to the report. Save the project.
   
   • Click Run, and then click Yes
   • Click Save Project.
   
   

In this practice, you create a summary table that lists minimum and maximum salary by country and department. Then you modify the heading and table properties of the report.

1. Open the employee_master table in the Lesson 5 process flow. Open the Summary Tables wizard.
   
   • In the Lesson 5 process flow, double-click the employee_master table to open the Data Grid.
   • Select Describe > Summary Tables Wizard.
   • In step 1 of the wizard, confirm that the employee_master table is being processed.
   • Click Next.
   
   
2. Add Salary to the Analysis variables list two times. Select Minimum for the first statistic and Maximum for the second.
   
   • In step 2, click Add and select Salary from the list. Repeat these steps so that Salary is listed twice. Notice at this point that the statistic for both lines is Sum.
   • Click in the Statistic column for the first Salary row and use the drop-down menu to select Minimum.
   • Click in the Statistic column for the second Salary row and use the drop-down menu to select Maximum.
   
   
3. Hide the analysis variable labels and apply the DOLLARw.d format to the table with 10 for the overall width and 0 for the decimal places.
   
   • Click the Analysis variable labels drop-down menu and select hidden.
   • Click Browse to open the Select Column Format window. Select the Currency category and select the DOLLARw.d format. Set the overall width to 10 and the decimal places to 0. Click OK to close the Select Column Format window.
   • Click Next.
   
   
4. Add Country to Columns and Department to Rows. Hide the row headers.
   
   • In step 3, click Add for the Columns section and select Country from the list.
   • Click Add in the Rows section and select Department from the list.
   • Click More Options and then select Hide the row headers.
   • Click OK to close the More Options window.
   • Click Next.
   
   
5. Eliminate the totals for both the rows and columns. Delete the default title and footnote.
   
   • In step 4, for both the column and row totals, use the drop-down menu to select None.
   • Click Next twice to move to step 6.
   • In step 6, delete the current title and footnote.
   
   
6. Run the task and view the report.
   
   • Click Finish.
   • View the report.
   
   
7. Modify the summary table in Advanced View. Change the heading properties for the Minimum and Maximum columns to display Low and High.
   
   • In the Process Flow window, right-click the Summary Tables wizard icon and select Open > Open in Advanced View. This opens the Summary Tables task window.
   • In the selection pane, select Summary Tables.
   • Right-click either of the Min statistics headings and select Heading Properties. In the Heading Properties for Min window, type Low in the Label box.
   • Click OK to close the window.
   • Right-click either of the Max statistics headings and select Heading Properties. In the Heading Properties for Max window, type High in the Label box.
   • Click OK to close the window.
   
   
8. Modify the heading properties for Country to display Salary Ranges by Country.
   
   • Right-click the Country header and select Heading Properties. In the Heading Properties for Country window, type Salary Ranges by Country in the Label box.
   • Click OK to close the window.
   
   
9. Modify the table properties to display missing values as a single hyphen.
   
   • Right-click in the table and select Table Properties.
   • In the Table properties window, delete the period and type a hyphen (-) in the Label for missing value box.
   
   
10. Name the task Salary Ranges by Country.
    
    • In the selection pane, click Properties.
    • Click Edit.
    • In the Label box, type Salary Ranges by Country, and then click OK.
    
    
11. Run the task and view the updated report.
    
    • Click Run, and click Yes in the message window.
    • View the updated report.
    
    
12. Close the task results and save the Employees project.
    
    • Close the task results.
    • Click the Save Project tool.
    
    

In this practice, you use the Summary Tables task to generate a report that calculates the frequency, percent of column frequency, and percent of column sum by department for each city in Australia.

1. Open the employee_master table in the Lesson 5 process flow. Open the Summary Tables Wizard.
   
   • In the Lesson 5 process flow, double-click the employee_master table to open the Data Grid.
   • Select Describe > Summary Tables Wizard.
   
   
2. Apply a filter to the input data so that only employees from Australia are included in the report.
   
   • In step 1 of the wizard, confirm that the employee_master table is being processed.
   • Click Edit.
   • In the first box of the Edit Data and Filter window, use the drop-down menu to select the Country column.
   • In the second box, use the drop-down menu to select Equal to, and in the third box, click the ellipsis button. Select AU, and then click OK twice.
   • Click Next.
   
   
3. Add the Salary variable as the analysis variable three times and select the first statistic as Frequency, the second statistic as % of Column Frequency, and the third statistic as % of Column Sum. Hide the analysis variable label.
   
   • In step 2, click Add and select Salary from the list. Repeat these steps twice so that Salary is listed three times.
   • Click in the Statistic column for the first Salary row and use the drop-down menu to select the Frequency statistic.
   • Click in the Statistic column for the second Salary row and use the drop-down menu to select the % of Column Frequency statistic.
   • Click in the Statistic column for the third Salary row and use the drop-down menu to select the % of Column Sum statistic.
   • Click the Analysis variable labels drop-down menu and select hidden.
   • Click Next.
   
   
4. Add City as the column classification variable and Department as the row classification variable. Hide the column and row headers.
   
   • In step 3, click Add for the Columns section and select City from the list.
   • Click Add in the Rows section and select Department from the list.
   • Click the More Options button. Select the Hide the column headers and the Hide the row headers check boxes.
   • Click OK to close the More Options window.
   
   
5. Include a title of Summary of Salaries for Australia and delete the footnote.
   
   • Click Next three times to move to step 6 of the wizard.
   • Delete the current text in the Table Titles box, and type Summary of Salaries for Australia by Department.
   • Delete the text in the Footnote box.
   
   
6. Run the task and view the report.
   
   • Click Finish.
   • View the report.
   
   
7. Modify the report by opening the task in Advanced View. Use the Summary Tables preview to delete ColPctN from the Total column. Hint: Delete Salary first and then delete ColPctN.
   
   • Right-click the Summary Tables Wizard icon, and select Open > Open in Advanced View.
   • In the selection pane, select Summary Tables.
   • In the Total column, right-click Salary above the ColPctN statistic and select Remove cells.
   • Right-click the ColPctN statistic and select Remove Cells.
   
   
8. Modify the headings and the change the background of data cells to match the final output shown here.
   
   • In the City column, right-click either N statistic and select Heading Properties. Type Employee Count in the Label box. Click OK.
   • In the City column, right-click either ColPctN statistic and select Heading Properties. Type Percent of Employees in the Label box. Click OK.
   • In the City column, right-click either ColPctSum statistic and select Heading Properties. Type Percent of Salaries in the Label box. Click OK.
   • In the Total column, right-click the N statistic and select Heading Properties. Type Total Employees in the Label box. Click OK.
   • In the Total column, right-click the ColPctSum statistic and select Heading Properties. Type Percent of Total Salaries in the Label box. Click OK.
   • Right-click the Total column header, and select Data Value Properties. Click the Font tab. Click the Background drop-down menu and select Light Cyan. Click OK.
   • Right-click the Total row header, and select Data Value Properties. Click the Font tab. Click the Background drop-down menu and select Light Cyan. Click OK.
   
   
9. Run the task and replace the results. View the updated report. Rename the task AU Salary Report.
   
   • Click Run, and then click Yes.
   • View the updated report.
   • In the Project Tree, right-click the Summary Tables task and select Rename.
   • Type AU Salary Report and then press Enter.
   
   
10. Close the task results and save the Employees project.
    
    • Close the task results.
    • Click the Save Project tool.
    
    

You might want to display a percent sign (%) after the percentage values in your table created by the Summary Tables task. 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. Complete the Level 2 practice.
   
   
2. Copy and paste or type the following code into a Program window.
   

   proc format;                           
      picture mypct low-high='009.9%';   
   run; 

   Run the code. This code creates a user-defined format named MYPCT that rounds values to the nearest tenth and adds a percent sign.
   
   • Depending on how you are accessing this course, you might be able to open e105e06.sas from your course data folder. If not, follow the next two steps.
   • Select File > Open > Program.
   • Copy and paste or type the code into the Program window.
   • On the Program tab, click Run.
   
   
3. Modify the AU Salary Report, which was created in the Level 2 exercise. 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.
   
   • Right-click the AU Salary Report task that was produced in the previous exercise and select Modify AU Salary Report.
   • Select Summary Tables.
   • Right-click the ColPctN column and select Data Value Properties.
   • Click the Format tab and the User Defined category. Highlight the MYPCT. format.
   • Click OK.
   • Repeat the steps to apply the format to the ColPctSum column. You must do the steps separately for the Total column.
   
   
4. Run the task and view the report. Save the project.
   
   • Click Run > Yes when you are prompted to replace the results.
   • View the updated report.
   • Click Save Project.
   
   

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. Open the Employees project and create a new process flow named Lesson 6. Add the employee_master table to the new process flow.
   
   • Click the New tool and select Process Flow.
   • Click the Process Flow label and type Lesson 6.
   • Click the Open tool and select Data.
   • If necessary, navigate to the location where the course data is stored. Double-click employee_master.
   
   
2. Create a new prompt named SelectVar and prompt the user with the following text: Which column do you want to analyze? Set an option to require a value for this prompt.
   
   • Select View > Prompt Manager to open the Prompt Manager window in the lower-left pane of the SAS Enterprise Guide window.
   • Click Add to open the Add New Prompt window.
   • Type SelectVar in the Name box and Which column do you want to analyze? in the Displayed text box.
   • Select the Requires a non-blank value check box.
   
   
3. Change the prompt type to Variable.
   
   • Click the Prompt Type and Values tab.
   • Click the Prompt type drop-down menu and select Variable.
   
   
4. Include the following variables as values in the prompt: Employee_Gender, City, Country, and Department. Assign City as the default value.
   
   • Click Load Values. In the Open File window, select Project, and then double-click employee_master in the Lesson 6 process flow.
   • All character variables in the employee_master table are added to the Variable List pane. Press Ctrl and select Employee_Name, Street_Name, State, Postal_Code, and Job_Title. Click the Delete button.
   • Select the check box for Default value in the Options pane and double-click City in the Value list.
   • Click OK.
   
   
5. Use the Bar Chart Wizard to create a 3-D horizontal bar chart based on employee_master. Use the SelectVar prompt you created to specify the bars. Select Salary as the variable to control the bar length. Set the statistic type to Average.
   
   • Select Tasks > Graph > Bar Chart Wizard.
   • In step 1, verify that employee_master is the data set, and then click Next.
   • In step 2, select the Horizontal bar chart check box.
   • Click the Bars drop-down menu and select SelectVar.
   • Click the Bar Length drop-down menu and select Salary.
   • In the Statistic window, select Average and click OK.
   • Click Next.
   
   
6. Change the following display attributes:
   
   • Color the bars separately.
   • Display data labels, and show the average profit.
   • Change the horizontal axis label to Average Annual Salary.
   • Specify a custom title that displays the value that is selected in the prompt.
   • In step 3, select the 3D chart check box.
   • Click the Color bars by drop-down list and select Bar category.
   • Select the Data labels check box and use the drop-down list to select Average as the displayed statistic.
   • Click Axis Labels, and in the Bar length box, type Average Annual Salary.
   • Click OK, and then click Next.
   • In step 4, delete the default title and type Average Annual Salary by &SelVar (or any title that includes the macro variable &SelVar).
   
   
7. Using the default variable City for the prompt value, run the wizard. View the graph. Run the task again and select a different value in the prompt.
   
   • Click Finish.
   • In the Specify Values for Project Prompts window, verify that City is selected.
   • Click Run and view the results.
   • Click Refresh.
   • In the Specify Values for Project Prompts window, select another value for the prompt.
   • Click Run and view the results.
   
   
8. Name the task Avg Annual Salary by ?.
   
   • Right-click the Bar Chart Wizard icon and select Properties.
   • In the Label box, type Avg Annual Salary by ?.
   • Click OK.
   
   
9. Close the task results and save the Employees project.
   
   • Close the task results.
   • Click the Save Project tool.
   
   

In this practice, you create a text prompt that enables the user to select a city value to filter the employee_master data.

1. In the Lesson 6 process flow, build a query based on the employee_master table. Name the query Select City Query and the output table City_Select. Include all columns in the query.
   
   • Right-click the employee_master table in the Lesson 6 process flow and select Query Builder.
   • Type City Select Query in the Query name box.
   • Click Change. In the File name box, type City_Select, and then click Save.
   • Right-click t1 (employee_master) and select Select All Columns.
   
   
2. Create a new prompt named SelectCity and prompt the user with the text Select a City:. Set an option to require a value for this prompt.
   
   • Click Prompt Manager, and then click Add.
   • Type SelectCity in the Name box.
   • Type Select a City: in the Displayed text box.
   • Select the Requires a non-blank value check box.
   
   
3. Verify that the prompt type is Text. For this prompt, the user will select a single value from a static list that you create.
   
   • Click the Prompt Type and Values tab.
   • Click the Method for populating prompt drop-down list and select User selects values from a static list.
   • Verify that the Number of values box is set to Single value.
   
   
4. For the prompt, use all the values of City from the employee_master data set. Set the default value as Miami-Dade.
   
   • Click Get Values, and then click Browse.
   • Select Project. Select employee_master in the Lesson 6 process flow. Click Open.
   • In the Unformatted Values area, use the drop-down menu to select City.
   • In the Available values pane, click Get Values and the five city values are displayed.
   • Move all five values to the Selected values pane by clicking the double arrow button.
   • Click OK to close the Get Values window.
   • In the List of values area, select the radio button in the Default column for Miami-Dade.
   • Click OK.
   • Click Close to close the Prompt Manager.
   
   
5. In the query, create a filter to select only rows that have a value of City equal to the value selected in prompt you just created.
   
   • Click the Filter Data tab.
   • Drag-and-drop City into the tab.
   • In step 1, verify that the operator is set to Equal to.
   • Click the Value drop-down arrow. Click the Prompts tab and select &SelectCity.
   • Click Next.
   • Review the summary and click Finish.
   
   
6. Run the query and use the default city, Miami-Dade. View the output data set. How many rows are in the output data?
   
   • Click Run.
   • Verify that Miami-Dade is the selected city, and then click Run.
   • The output table has 85 rows.
   
   
7. Rerun the query, and this time select Sydney in the prompt. View the output data set. How many rows are in the output data?
   
   • Click Refresh. The Specify Values for Project Prompt window opens.
   • In the prompt window, use the drop-down menu to select Sydney.
   • Click Run.
   • The output table has 47 rows.
   
   
8. Close the query results and save the Employees project.
   
   • Close the query results.
   • Click the Save Project tool.
   
   

In this practice you create a query that will prompt the user to subset the employee_master table for a selected range of hire dates.

1. In the Lesson 6 process flow, create a query based on the employee_master table. Name the query Employee Hire Date Query and name the output table Employee_Hire_Date. Add all columns to the query.
   
   • In the Lesson 6 process flow, right-click the employee_master data set and select Query Builder.
   • Type Employee Hire Date Query in the Query name box.
   • Click Change. Type Employee_Hire_Date in the File name box. Click Save.
   • With the Select Data tab active, right-click t1 (employee_master), and select Select All Columns.
   
   
2. Create a prompt that enables the user to select a range of dates. Name the prompt DateRange and prompt the user with the text: "Select a beginning and end date for the employee hire date range". The prompt type should be Date range and the date type should be Day.
   
   • Click Prompt Manager, and then click Add.
   • In the Name box, type DateRange, and in the Displayed Text box, type Select a beginning and end date for the employee hire date range.
   • Select the Requires a non-blank value check box.
   • Click the Prompt Type and Values tab.
   • Change the prompt type value to Date range.
   • Verify that the Date type value is Day.
   
   
3. In the query, create a filter on the Employee_Hire_Date column. Set the operator to In a range and use the prompt that you created to select the date range at execution.
   
   • Click the Filter Data tab.
   • Drag and-drop the Employee_Hire_Date column into the tab.
   • In step 1 of the New Filter window, click the Operator drop-down menu and select In a range.
   • Click the drop-down arrow for the Value box and select &DateRange.
   • Click Next.
   • In step 2, view the summary and click Finish to complete the filter.
   
   
4. Sort the results by ascending Employee_Hire_Date.
   
   • Click the Sort Data tab.
   • Drag Employee_Hire_Date onto the Sort Data tab.
   
   
5. Submit the query and specify the range of dates from January 1, 2010 to December 31, 2010 in the prompt. View the output data set.
   
   • In the Query Builder, click Run.
   • In the Specify Values for Project Prompt window, select January 1 for any year. Then delete the year and type 2010. Type or select December 31, 2010 in the To field.
   
   
6. Close the query results and save the Employees project.
   
   • Close the query results.
   • Click the Save Project tool.
   
   

Use the Prompt Manager to create a multiple value selection prompt for the City column.

1. In the Lesson 6 process flow, create a prompt named SelectCities that enables the selection of one or more cities from the City column in the employee_master data set.
   
   • Select Add in the Prompt Manager window.
   • On the General tab, type SelectCities in the Name field and type Select Cities to Include: in the Display Text field.
   • Select the Requires a non-blank value check box.
   • Click the Prompt Type and Values tab.
   • On the Prompt Type and Values tab, verify that the prompt type is Text. Change the Method of populating prompt value to User selects value from a static list.
   • Change the Number of values field to Multiple values.
   • Select Get Values then Browse. In the Open File window, select Project > employee_master > Open.
   • Use the drop-down list in the Unformatted values field to select City
   • Select Get Values. The five city values are populated in the Available values pane.
   • Click to move all cites to the Selected values pane.
   • Click OK and then click OK again.
   
   
2. Create a query based on the employee_master table. Name the query Multi-Cities Query and the output table Multi_Cities. Include all columns on the Select Data tab.
   
   • Right-click the employee_master table in the project tree or process flow and select Query Builder.
   • In the Query Builder, type Multi-Cities Query in the Name field.
   • Click Change and type Multi_Cities in the File name field. Select Save.
   • With the Select Data tab active, right-click t1 (employee_master) and select Select All Columns.
   
   
3. Build a filter that includes only those cities that are in the list of cities selected in the prompt.
   
   • Click the Filter Data tab.
   • Drag and drop City onto the tab area.
   • In the New Filter window, change the operator to In a list.
   • Select the Generate filter for a prompt value check box.
   • Select the drop-down arrow next to the Value field and select Prompts > &SelectCities.
   • Click Next > Finish.
   
   
4. Run the query and select Melbourne and Sydney for the cities. View the output data set.
   
   • Click Run.
   • In the prompt, hold down the Ctrl key and select Melbourne and Sydney.
   • Click to move the columns to the Selected pane.
   • Click Run and verify that the output table has 73 rows.
   • Click Save Project.
   
   

In this practice, you combine several reports and a banner image to create a report.

1. In the Employees project, create a combined using the Bar Chart and Salary Ranges by Country summary table. Place the reports beside each other. Note: If you do not have the specified reports, you can use any results that you have saved to do this practice.
   
   • Select File > New > Report, or click the New tool and select Report.
   • In the New Report window, drag and drop the Bar Chart report into the grid.
   • Drag and drop the Salary Ranges by Country summary table report in the grid to the right of the bar chart.
   • Click OK to create the report.
   
   
2. Add Orion-Banner.jpg as a banner image to the top of the report. Note: You can skip this step if you can't access the course data folder in this window.
   
   • Select Header & Footer.
   • Click Browse and navigate to the course data.
   • Select Orion-Banner.jpg > Open.
   • Click OK.
   
   
3. Select Page Setup to change the page orientation to Landscape.
   
   
4. Experiment with the size of the graph so that the table and graph fit together in the report.
   In Normal View, click the graph and resize it. Then switch to Page View to view the report. Resize the graph until you get the report layout that you like.
   
5. Export the report to a PDF document.
   
   • Click Export, and select Export Report.
   • In the Export window, navigate to the location where you stored the practice files for this course.
   • In the File name box, type EmployeeReport.
   • Click the Files of type drop-down menu and select Portable Document Format ( *.pdf).
   • Click Save.
   
   
6. Save the Employees project.
   
   

In this practice, you create a new process flow, copy a task to the new process flow, and run the process flow.

1. Add a new process flow named Graphic Reports to the Employees project.
   
   • Select File > New > Process Flow.
   • Right-click the Process Flow icon and select Rename.
   • Type Graphic Reports and press Enter.
   
   
2. Copy the Bar Chart task and results in the Lesson 3 process flow and paste it in the new Graphic Reports process flow.
   
   • Use the Process Flow drop-down menu on the toolbar to select the Lesson 3 process flow.
   • Right-click Bar Chart and select Copy.
   • Use the Process Flow drop-down menu to select the Graphic Reports process flow.
   • In the Graphic Reports process flow, right-click and select Paste.
   
   
3. Run the Graphic Reports process flow to update the results.
   Select Run > Run Graphic Reports to update all results in the Graphic Reports process flow.
   
   
   
4. Save the Employees project.
   
   

In this practice, you organize a process flow to control the sequence of execution. Then, you run the project and export the code and log.

1. Open the Employees project.
   
   
2. Click the Project Log above the process flow and make sure logging is turned on for the project.
   
   • Click the Project Log button. If project logging is off, click the Turn On button.
   • Return to the Lesson 5 process flow window by double-clicking Lesson 5 in the Project Tree.
   
   
3. Run the entire project and view the project log.
   
   • Select Run > Run Project.
   • In the prompt window, use the default settings. Click Run.
   
   
4. Select File > Export > Export All Code In Project to create a SAS program named EmployeeReports.sas. The program should include the code from all items in the project.
   
   • Select File > Export > Export All Code In Project.
   • Click Browse and select a location where you can save a file. In the File name box, type EmployeeReports.sas, and then click Save.
   • Notice the options in the Export All code window to include additional code generated by SAS Enterprise Guide.
   • Click Export.
   
   
5. Export the project log as a text file.
   
   • In the Lesson 5 process flow window, click the Project Log button.
   • In the Project Log window, select Export > Export Project Log.
   • In the Export window, select a location to save the file and type ProjectLog1 in the File name box. Save the file as a text (.txt) file. Click Save.
   
   
6. Save the Employees project.