Usage Notes for the View Data Window

These notes apply to the features that are included in the View Data window that you access when you right-click a table or external file and click Open in the pop-up menu.

You cannot view the contents of a cube in SAS Data Integration Studio. You can use Microsoft Excel or SAS Enterprise Guide to view the data in a cube. For details, see the documentation for SAS OLAP Server.

The View Data window cannot display data in a table or view unless the library for that table or view is defined in a current metadata repository.

For example, you cannot view the data in a SAS table if you change the SAS library to on the Physical Storage tab in the properties window for the table. To successfully view SAS table data in a work library, you must first reassign that library to an existing metadata library.

As another example, if the Temp option is checked for an SAS SPD Server library, you cannot use the View Data feature to display tables in the library.

The properties window for a SAS SPD Server library includes an Options tab. On the Options tab, there is an Advanced Options button. If you click the Advanced Options button and then select the Server Connection Information tab, you can specify YES or NO in the Temp field. The Temp field specifies whether a temporary LIBNAME domain is created for the library.

If you specify YES in the Temp field, any data objects, catalogs, or utility files created in the library are deleted after the job is executed. Accordingly, you cannot use the view data feature in SAS Data Integration Studio to view tables in the library.

Close the View Data window on a SAS table before running a job that updates data in that table. Otherwise, the job fails, with the following error message in the SAS log:

A lock is not available for table, lock held by another process.

If this error message appears, close the View Data window and run the job again. SAS releases the lock on the table, and the job can complete successfully. This problem affects only SAS tables.

Macro variable parameters can be used in the SASTableName field of tables. The View Data function generates code to resolve these macro variable parameters by using the default values of the parameters. You cannot view these tables unless the View Data function can recognize the values entered in their SASTableName fields. Therefore, you must use a specific syntax that joins the table name to the parameter value. For example, a table named PARAMTABLE that contains a parameter named &STATE would be entered as PARAMTABLE_&STATE in the Properties window for the table. Enter the new table name into the SASTableName field on the Advanced tab.

The data displayed in the View Data window for these parameterized tables is based on the value entered in the Default value field when the parameter is created.

You might receive the Oracle error, ORA-1555, or a message similar to the SAS/ACCESS error noted below when using the View Data window to display information in an SAP R/3 table:

FREECPIC1 SQL error 1555 occurred when accessing table QALS

This error occurs because the snapshot is too old or the rollback segment is too small. When a query is submitted to Oracle, it takes a snapshot of the currently committed transactions and creates a consistent set of results in a rollback segment. If the data of a table is updated frequently and the query is a long-running query, there might not be enough information in the rollback segment to reconstruct the older data. That is why the error occurs.

To avoid this error, create more or larger rollback segments, schedule long-running queries when there are fewer concurrent transactions made, or obtain a shared lock on the table that you are querying.

The View Data window cannot display data with user-defined formats unless the format library is available to the SAS Application Server that is used to display the data.

When permanent formats have been applied to data in data sets, the permanent formats are displayed even when the View Data window has been set to show unformatted data. This is true whether the setting is made on the View Data tab in the global Options window in SAS Data Integration Studio or on the View Data window toolbar. The settings that toggle between the display of formatted and unformatted data apply only to formats that are defined in the metadata.

The View Data window supports multiple column heading label options. The labels are displayed on the View Data toolbar in this order:

When you select more than one of these options, the column labels are displayed in a fixed sequence. The labels are sequenced as follows:

Therefore, the following column label combinations are possible:

If none of the column heading label options are selected, the physical column name is displayed. Also, if you select the Show Metadata Name option or the Show Description option for a column that is missing a metadata name or a description, the physical column name is displayed by default.

When primary keys and foreign keys are created that establish referential relationships between tables, the ability to edit these tables within the View Data window can be disabled under certain circumstances. You can open a table that has referential relationships to other tables for editing if no other tables within the relationship are open for editing. However, you cannot edit any of the other tables within the referential relationship. To clear this editing conflict between the tables, you must close all tables and then you can edit the first table that you reopen.

When you register metadata for a table in the New Table wizard, you can set its options by performing the following steps:

Any table options added before the table is created are ignored, even if they are applied in the metadata. In addition, if these table options are incompatible with the DATA step that View Data uses to build the physical table, the query might fail. In that case, the table is not created, and the query ends with an error. After you have successfully created the table, you can set any options that you need.

The View Data window constructs a SELECT query statement from the metadata for the selected table, view, external file, or transformation. For example, if the metadata for Table 1 specifies three columns that are named Col1, Col2, and Col3, the view data function generates the following query for that table:

SELECT Col1, Col2, Col3 FROM Table1

If the metadata for a SAS or DBMS data store does not match the data in the data store, an error dialog box is displayed. The dialog box gives you the option of ignoring the column metadata that has been registered for the data store and using any column definitions in the data store to format the columns for display.

The View Data window cannot display data for a fixed-width external file unless the SAS informats in the metadata are appropriate for the columns in the data.

Some database management systems do not commit changes as soon as they are requested. Accordingly, if a SAS Data Integration Studio job updates a table in a DBMS and you try to verify the update by using the View Data feature, the changes might not show up immediately.

If you want SAS changes to a DBMS table to show up immediately, select YES in the Whether to COMMIT immediately after a transaction field in the metadata for the DBMS library that is used to access the DBMS table.

To select this option for a DBMS library, display the property window for the library, select Options, and then click the Advanced Options button. Click the Input/Output tab. In the Whether to COMMIT immediately after a transaction field, select YES, and then click OK to save your changes.

The View Data window cannot display data from a fixed-width external file unless the SAS informats in the metadata are appropriate for the columns in the data.

In order to use the View Data window to view physical tables attached to a SAS WORK library, the SAS WORK library must be registered, and the library must be assigned to a SAS Application Server.

To display a Teradata table in the View Data window, you must connect to the database as a user with SELECT permissions. Otherwise, you get an error message saying that there are no columns in the table.