Adding a Data Source

To add a table to a data query, use the SAS Folders tree to locate the table () and then drag and drop the table onto the workspace.

After you have created a data query and saved it, it can be used as an input data source to another data query. To add a subquery, use the SAS Folders tree to locate the data query (), and then drag and drop the data query onto the workspace.

The subquery is represented in the workspace by the columns that are selected for output in the subquery.

In most cases, you can use table names and column names that contain special characters, including blank characters. When you use a column in your data query, the data builder applies the n-literal syntax, such as 'table-name'n.'column-name'n, so that SAS can use the column.

Rules for SAS names apply. For more information, see “Names in the SAS Language” in SAS Language Reference: Concepts.

The data builder does not apply the n-literal syntax to code that you enter manually on the Where tab or Having tab or in column expressions. For example, if your table has a column that is named quantity ordered, then you must add the n-literal syntax (similar to the following example):

AVG(table.'quantity ordered'n)

When you plan to join data sources (tables or subqueries), the order in which you add the data sources to the workspace matters. The first data source that is added to the workspace is automatically assigned as the left table for any joins that you add to the data query.

If you are creating a data query that uses a fact table and dimension tables, then the simplest approach is to drag and drop the fact table onto the workspace first. You can perform left, right, or full joins with the dimension tables faster because you must specify only the join type. However, if you drag and drop a dimension table first, then you can easily use the button on the Joins tab to switch the left and right tables in the join.

If you are not using a fact table or dimension tables, then the sequence for adding tables to the workspace might not be very important. Just remember that the first table dropped onto the workspace is assigned as the left table, and you can switch the left table and right table on the Joins tab. The data builder takes advantage of vendor-specific features in SQL processing whenever possible. If the source tables are from a third-party vendor database, then the SAS/ACCESS Interface engine can optimize the performance of the data query by passing the SQL statements through to the database.