40
9
Working with Columns
Adding Columns to a Query
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
62
Understanding How SAS Visual Data Builder
Works with Columns
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
62
Adding All of the Columns from a Table
. . . . . . . . . . . . . . . . . . . . . . . . . . . . .
63
Adding a Single Column from a Table
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
63
Adding a New Column Manually
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
64
Removing Columns
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
64
Specifying a Column Expression
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
65
Specifying Aggregations
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
66
Removing All Aggregations
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
68
Using Group By Variables
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
69
Using the Auto-Aggregate Functions
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
69
Using the Pivot By Feature
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
70
61
22
Adding Columns to a Query
Understanding How SAS Visual Data Builder
Works with Columns
When you add a data source (a table or subquery) to the workspace, the columns from
the data source are not automatically added as output columns to the query.
You need to add the columns to the query that you want to use as input. After the
columns are added, you can specify column expressions and aggregations and use the
sort and pivot by features.
Note: As an exception, the auto-aggregate feature requires that you set the default
aggregations for the table before they are added to the query.
When a data source is dropped onto the workspace, the column types are represented
by the following icons:
Table 9.1 1 Icons for Data Types
Icon
Description
This icon represents numeric data.
This icon represents character data.
Note: Date, time, and datetime data use this icon. After the column is
added, the Type and Format columns are updated with information
about the new column.
62
Chapter 9 9 / / Working with Columns
9
Adding All of the Columns from a Table
To add all of the columns from a table to a query, select the table in the workspace,
right-click, and select Add All Columns.
Display 9.1 1 Add All Columns
Adding a Single Column from a Table
To add one column from a table to a query, select the column name with your pointer.
Display 9.2 2 Add One Column
Adding Columns to a Query
63
21
TIP If you select the column name an additional time, the column is added to the
query again. This can be helpful if you want to use a column for both numeric and
character data. When you add a column more than once, a number is added to the
column name. If you change the column name, make sure that you do not have more
than one column with the same name.
Adding a New Column Manually
To add a new column manually:
1
Click the Column Editor tab.
2 Click
at the bottom of the table.
3
Specify values for the Column Name, Expression, and Type fields. The remaining
fields are optional.
Removing Columns
To remove a column from a query:
1
Click the Column Editor tab.
2 Select the column to remove, right-click, and select Remove Column.
64
Chapter 9 9 / / Working with Columns
23
Display 9.3 3 Removing a Column
TIP Hold down the Shift key or Ctrl key and click to select multiple columns.
Specifying a Column Expression
To specify a column expression:
1
On the Column Editor tab, select
table-name.column-name
from the Expression
column. If you added a new column manually, then you need to make sure that you
specified a table name and a column name.
2
Click
to open the expression builder. The table name and column name are
added automatically as the default SQL expression.
3
On the Fields tab, select columns from the source tables that you have added to the
query.
TIP You can begin entering the table name and column name in the SQL
expression area to use the autocomplete feature.
4
On the Functions tab, select the functions to apply to the source column.
Specifying a Column Expression
65
.NET Multipage TIFF SDK| Process Multipage TIFF Files SDK, developers are easily to access, extract, swap, reorder, insert, mark up and delete pages in any multi upload to SharePoint and save to PDF documents.
pdf change page order online; how to move pages in pdf converter professional
16
5 Enter arithmetic operators and expressions such as CASE statements directly in the
SQL expression area.
6
Click Apply to save the column expression.
Specifying Aggregations
To specify an aggregation for a column:
1
On the Column Editor tab, place your pointer in the Aggregations cell for the
column and click. Click the ellipsis button to select the aggregations to use.
2
In the Choose Aggregations dialog box, select the Aggregate functions radio
button.
Display 9.4 4 Choose Aggregations Dialog Box
3 Select the check boxes for the aggregate functions to use. Click Apply.
66
Chapter 9 9 / / Working with Columns
6
If you want to apply a common set of aggregate functions to a group of columns, hold
down the Ctrl key, select the columns on the Column Editor tab, right-click, and select
Choose Aggregations.
Display 9.5 5 Choose Aggregations Menu Item
Specifying Aggregations
67
11
The following display shows how adding aggregations results in additional output
columns. The aggregate function is appended to the column name.
Display 9.6 6 Output Columns Tab with Aggregations
Removing All Aggregations
To remove all the aggregate functions and group by settings:
1 On the Column Editor tab, select all the columns.
2
Right-click, and select Remove Aggregations.
TIP This menu option is available only when all of the columns are selected.
68
Chapter 9 9 / / Working with Columns
24
Using Group By Variables
When you add an aggregation to a column, the remaining columns are automatically
used as group by variables. The Aggregations column displays GROUP BY for these
variables.
You can use a column as a group by variable by following the steps in “Specifying
Aggregations” and selecting the Group by radio button.
Using the Auto-Aggregate Functions
The auto-aggregate feature is a productivity feature that enables you to specify a set of
aggregations to apply as default aggregations to numeric columns for a specific table. A
typical use is to automatically aggregate some of the columns in a fact table.
To use the auto-aggregate feature:
1 Select a table on the Design tab. On the Properties tab, the table properties are
displayed.
2
Select Enable for Auto-Aggregate.
3 Click the ellipsis button next to Functions to open the Choose Aggregations
window.
4
Select the check boxes for the aggregate functions that you want to apply, and then
click Apply.
Whenever you add a column to the query, the selected aggregate functions are
automatically applied.
Using the Auto-Aggregate Functions
69
15
Using the Pivot By Feature
The pivot by feature provides an easy and powerful way to summarize data for
analytics. You can specify a column to use as a categorical variable and the unique
values to use. When the query is run, the output table is summarized with the
aggregations that you apply.
To use the pivot by feature:
1
On the Column Editor tab, place your pointer in the Pivot By cell for the column to
use as the pivot column. Click the ellipsis button to select the pivot column and
values.
2
In the Pivot Values dialog box, select the pivot by column. You can enter search
criteria in Filter fields to filter the column names.
70
Chapter 9 9 / / Working with Columns
Documents you may be interested
Documents you may be interested
