Using Data Items Section

The Data Items Section, which is on buttom of the Query tab, displays all the result items that will be in the final query. It also specifies Sorting and Grouping, or Aggregation and other information for a column.

DataItems Section

Here are the Data Item tasks:

 

Adding a Data Item

You can add a new Data Item from either Table And Relations or Data Items section.

To add an aditional Data Item from the Data Items section:

This will bring up a Data Item Dialog. By default, the text of Expression box is <Unitialized>.  You can either type the name of the Data Item or select from column list in the Expression Builder. For more information on adding a Data Item from Data Item Dialog, see Expression Editor.

Deleting a Data Item

To delete a Data Item, either:

Editing a Data Item

This dialog allows you to view and edit information about a Data Item. You can

Here is the Data Item Dialog for a Customer Data Item in Samples SAS dataset:

DataItems Dialog

These are the editable properties for a Data Item:

  1. Name: You can type an alias for a Data Item in the Name field. Check the restrictions for the alias for each datasource. For example, V6 SAS only allows 8 characters for a field.

    DataItems Alias

  2. SAS Attributes: You can set Labels, Formats, and Informats for a SAS dataset field by typing directly in the fields below. For Formats and Informats, there is also a dialog available by pressing the ellipses(...) button next to each field.

    DataItems SAS Attributes

  3. Aggregation: You can set or change the grouping, or aggregate function for a Data Item. From the drop-down list, select the aggregate function to use on the item.
    DataItems Aggregation

  4. Expression: You can type an expression directly into the expression box. Press the dialog (...) button beside the box, to display the Expression Editor , which allows you to build a complex expression.

    DataItems Expression

  5. Order by: Indicate if the query will be sorted by this Data Item. You can specify the sort direction (ascending or descending), and the priority of the sort. The priority is used when multiple Data Items are sorted. Choose the direction and priority from the drop-down list. You can undo the setting by uncheck the Order By checkbox.

    DataItems Sort Order

Moving a Data Item

After you defining a list of Data Items, you can change the position of the Data Items in the result.

To move a selected Data Item, you can:

Setting Aggregate Fuctions

Aggregate functions provide statistical information about results. For example, you can use the aggregate function COUNT to count the number of records in a group of records, or use AVG to determine the average of values in a particular column. By default, the entire column of Aggregate Function and Grouping is blank if aggregation is not set.

To set Aggregations, click on Aggregate Function and Grouping column of selected Data Item.  Select a function from the drop-down list. The rest of the columns automatically change to Group By, you can specifically change the aggregates by selecting functions from the list, or deselect the aggregation by selecting the blank from the list.

NOTE: If you are going to use an Aggregate function, it is easier to specify the columns to group by beforespecifying the function on the target Data Item. Otherwise, you will get too many Data Items grouped.

You can also set Aggregations in Data Item Dialog.

Making a Data Item result or non-result

You can specify whether or not the Data Item is displayed in the query results. When you insert a Data Item into the query, the display checkbox  is checked. Uncheck it to keep the Data Item from displaying in the query results.

Setting Alias

You can set an Alias to a Data Item to any name that is not already used in the Query tables. Creating an alias doesn't change the name of the Data Item, it only specifies another name that can also be used to refer to the Data Item.

To set an Alias to a Data Item, you can either:

Setting Order Direction

This is to define a column from the result set to be used in ordering the result rows. The sort direction can be the default of ASC (Ascending) or can be specified as ASC or DESC (descending).

To set the Sort Order:

Setting Sort Priority

By default the Sort Priority is blank (no sort). If you have more than one Data Item to sort by, you may want to specify the priority with which to sort the columns.  When you sort by multiple Data Items, the Sort Priority is set to First, Second, Third, etc.  You can change the priorities by selecting the Sort Priority Column,and selecting the sort priority you want.

You can also set/change  Sorting Priority in Data Item Dialog.