1 / 110

Chapter 4: Creating Simple Queries

Chapter 4: Creating Simple Queries. Chapter 4: Creating Simple Queries. Objectives . State the function of the Filter and Sort task and the Query Builder. Compare the functionality available in each task. Filter and Sort Task and the Query Builder.

henry-levine
Télécharger la présentation

Chapter 4: Creating Simple Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4: Creating Simple Queries

  2. Chapter 4: Creating Simple Queries

  3. Objectives • State the function of the Filter and Sort task and the Query Builder. • Compare the functionality available in each task.

  4. Filter and Sort Task and the Query Builder The Filter and Sort task and the Query Builder can be used to create a new data source from one or more tables according to the criteria specified by the user.

  5. 4.01 Multiple Answer Poll • Double-click on any data source in your project. Select Filter and Sort and explore the available tabs. What functionality do you think is supported by this task? • Subsetting rows • Selecting columns • Calculating new columns • Controlling the sort order of the rows • Summarizing data • Create a SAS data set

  6. 4.01 Multiple Answer Poll – Correct Answers • Double-click on any data source in your project. Select Filter and Sort and explore the available tabs. What functionality do you think is supported by this task? • Subsetting rows • Selecting columns • Calculating new columns • Controlling the sort order of the rows • Summarizing data • Create a SAS data set

  7. Filter and Sort Task The Filter and Sort task enables you to create a new SAS table by selecting rows, columns, and a sort sequence.

  8. 4.02 Quiz Close the Filter and Sort task and return to the data grid. Select Query Builder. What options appear to be available that are not present in the Filter and Sort task?

  9. 4.02 Quiz – Correct Answer Close the Filter and Sort task and return to the data grid. Select Query Builder. What options appear to be available that are not present in the Filter and Sort task? Possible answers: Query name, Output name, Computed Columns, Prompt Manager, Tools, Options, Add Tables, Join Tables

  10. Query Builder The Query Builder enables you to create a new SAS table by selecting rows, columns, and a sort sequence. It also enables computing new columns, joining tables, grouping, summarizing, and modifying column attributes.

  11. Filter and Sort Task versus the Query Builder

  12. Chapter 4: Creating Simple Queries

  13. Objectives • Apply a filter in a query. • Exclude columns in a query. • Reorder rows in a query.

  14. Business Scenario Internet Orders(Order_Type =3) Orion Star wants to analyze Internet sales since 2008. To prepare the data for input to the various analytic tasks, the company must generate a new data source from the orders table, including only those Internet orders placed on or after 01JAN2008.

  15. Filter and Sort Task The Variables, Filter, and Sort tabs in the Filter and Sort task provide functionality to select rows and columns in a designated sort order.

  16. Filter and Sort: Filter Simple filters can be built using variable names, operators, and data values. Select Advanced Edit…to build more complex filters.

  17. Advanced Filter Builder The Advanced Filter Builder provides access to advanced operators and SAS functions to create more complex rules for extracting rows.

  18. Filter and Sort: Sort and Results You can sort by multiple variables, and designate either ascending or descending sequence. You can also name the task and output table.

  19. Query Builder • The Query Builder provides similar tabs for selecting columns, filtering rows, and sorting data. Additional functionality is available, including the following: • modifying columnproperties • grouping andsummarizing data • applying formats • selecting distinctrows • joining tables

  20. Using Query Results in Tasks Data sources generated from queries can serve as the input data for follow-up tasks.

  21. Selecting Columns and Filtering Rows

  22. Exercise This exercise reinforces the concepts discussed previously.

  23. Chapter 4: Creating Simple Queries

  24. Objectives • Define a new column of data in a query by building an expression.

  25. Business Scenario Delivery_Date - Order_Date SUM(Total_Retail_Price, Shipping) Orion Star wants to analyze shipment methods by determining how many days elapse between each order date and delivery date. The company also wants to calculate the total amount invoiced to the customer, which is the sum of total retail price and shipping charges.

  26. Computed Columns Select to begin creatinga new column. New summarized columns, recoded columns, or columns based on an expression can be added to a query in the Query Builder.

  27. New Computed Column Wizard A wizard guides you through the process of creating the new column and assigning attributes such as the column name, label, and format.

  28. Expression Editor The Expression Editor enables you to build expressions based on variables, operators, and functions.

  29. SAS Functions A SAS function is a routine that returns a value that is determined from specified arguments. General form of a SAS function: function-name(argument1,argument2, . . .) sum(Salary,Bonus) Example:

  30. Using SAS Functions • SAS functions can do the following: • perform arithmetic operations • compute sample statistics (for example, sum, mean, and standard deviation) • manipulate SAS dates • process character values • perform many other tasks Sample statistics functionsignore missing values.

  31. 4.03 Multiple Choice Poll Var1+Var2+Var3 • What is the result of the expression given the values of Var1, Var2, and Var3? • . (missing) • 3 • 9 • 12

  32. 4.03 Multiple Choice Poll – Correct Answer Var1+Var2+Var3 • What is the result of the expression given the values of Var1, Var2, and Var3? • . (missing) • 3 • 9 • 12

  33. 4.04 Multiple Choice Poll sum(Var1,Var2,Var3) • What is the result of the expression given the values of Var1, Var2, and Var3? • . (missing) • 3 • 9 • 12

  34. 4.04 Multiple Choice Poll – Correct Answer sum(Var1,Var2,Var3) • What is the result of the expression given the values of Var1, Var2, and Var3? • . (missing) • 3 • 9 • 12

  35. Computed Columns Computed columns appear in the left pane and can be used in a filter, for sorting, or as an input to another computed column.

  36. Creating a Column with an Expression Delivery_Date - Order_Date SUM(Total_Retail_Price, Shipping) This demonstration illustrates using the Computed Column wizard to define new columns based on advanced expressions.

  37. Exercise This exercise reinforces the concepts discussed previously.

  38. Chapter 4: Creating Simple Queries

  39. Objectives • Assign a grouping variable in a query. • Select the analysis variable and the summary statisticto compute. • Filter grouped data.

  40. Business Scenario Orion Star wants to offer a sales promotion that highlights the most lucrative products. The company would like a list of all products with a total profit that exceeds $500.

  41. Grouping Data The Query Builder canbe used to group and summarize data.

  42. Grouping Data Choose a statisticfor columns to be summarized. Columns without an assignedstatistic will automaticallydefine the groups. Data can be grouped and summarized using the Select Data tab.

  43. Grouping by Column Values The query result includes one row for every unique value of the group column(s) and a calculated statistic for the summarized column(s).

More Related