Modifying Tables

---

When you have brought your data into the Analyst data table, you can change the organization and apply calculations to the data. You must be in Edit or Shared Edit mode to make modifications to the data table.

Viewing and Editing Data

To prevent changes to a table while you are viewing it, select Edit → Mode → Browse.

To make changes to the table, select Edit → Mode → Edit. While you are in Edit mode, no one else is able to make changes to the table.

To allow more than one person to make concurrent changes to the table, select Edit → Mode → Shared Edit. The record you are editing is locked while you are editing it, but other users can make changes to other records in the table.

When you are in Edit or Shared Edit mode, you can make changes to the data table by selecting a cell and typing in it.

Working with Columns

You can perform several operations on data table columns by selecting items from a pop-up menu. To display the pop-up menu for a column, select the column and click the right mouse button.

Figure 2.7: Column Pop-up Menu

These items are also available from the View, Edit, and Data menus.

Moving Columns

You can move columns by selecting one or more columns and selecting Move...from the pop-up menu to display the Move Columns dialog.

Figure 2.8: Move Columns Dialog

To move a column, select it in the Column order list, then click on the arrows to move it to the appropriate spot. Sort the columns by selecting Ascending and Descending under the Alphabetical order heading. Click on the Sort All button to sort the columns.

Select Save order with data to save this order with the data file. You must be in Edit mode to save the order with the data file.

Click OK when the columns are in the desired order.

Hiding Columns

To hide a column or columns from displaying in the data table, select the columns and select Hide...from the pop-up menu to display the Hide Columns dialog. Hidden columns are still used in an analysis unless you specify that they be excluded.

Figure 2.9: Hide Columns Dialog

To hide columns, select the desired columns and click on the Hide button.

To unhide columns, select the desired columns and click on the Remove button.

Select Exclude hidden columns from analysis to specify that the hidden columns be unavailable for Analyst tasks.

Holding Columns

To hold a column and all the columns to the left of it in place while you scroll through the columns in the data table, select a column, and select Hold...from the pop-up menu to display the Hold Columns dialog.

Figure 2.10: Hold Columns Dialog

Select a column from the column list and click OK to hold it.

Select a held column from the column list and click on the Release button to release it.

Inserting Columns

To insert one or more columns, select a column and select Insert from the pop-up menu. Then select the column type Character or Numeric. The new column is inserted to the left of the selected column. If you select more than one column, columns equal to the number you have selected are inserted to the left of the first column. If no column is selected, the new column is added to the end of the table.

You must be in Edit mode to insert columns.

Sorting Columns

Select a column and select Sort...from the pop-up menu to display the Sort dialog. Sort the rows in the data table by the selected column's values.

Figure 2.11: Sort Dialog

Select columns from the candidate list and click on the Sort by button to specify the column values to use in sorting.

Use the up and down arrows next to the Sort by list to specify the desired column sort order.

Select a variable in the Sort by list and click on the Ascend/Descend button to sort the rows in the data table in ascending or descending alphabetical order of column values. The rows are sorted in ascending order by default. You must be in Edit mode to sort columns.

Duplicating Columns

To duplicate one or more columns, select a column and select Duplicate from the pop-up menu. The duplicated column is inserted to the left of the selected column. If you select more than one column, each column is duplicated to the left of the first selected column.

You must be in Edit mode to duplicate columns.

Deleting Columns

To delete a column, select the column and select Delete...from the pop-up menu to display the Delete Items dialog.

Figure 2.12: Delete Items Dialog

Select the columns that you want to delete and click OK. To avoid deleting any columns, deselect all columns or click on the Cancel button.

You must be in Edit mode to delete columns.

Displaying Variable Labels

You can switch between displaying variable names as column headings in the data table and displaying labels as column headings in the data table by selecting a column and selecting Labels from the pop-up menu.

Column Properties

Select a column and select Properties...from the pop-up menu to display the Column Properties dialog.

Figure 2.13: Column Properties Dialog

The Column Properties dialog displays the name, label, type (numeric or character), length, format, and informat of the selected column. If the data table is in edit mode, you can change the name, label, format and informat for the variable that the column represents. Otherwise, you can only view the information.

Working with Rows

You can add, duplicate, and delete rows. To display the pop-up menu for a row, select the row and click the right mouse button.

Figure 2.14: Row Pop-up Menu

These items are also available from the Edit menu.

Adding a Row

To add a row to the end of the table, select a row and select Add from the pop-up menu.

You must be in Edit or Shared Edit mode to add a row.

Duplicating a Row

To duplicate a row, select the row, and select Duplicate from the pop-up menu.

You must be in Edit or Shared Edit mode to duplicate a row.

Deleting a Row

To delete a row, select the row, and select Delete from the pop-up menu.

You must be in Edit or Shared Edit mode to delete a row.

Typing in Data Values

You can change the data in a cell by selecting the cell and typing in the new value.

The Data Menu

From the Data menu, you can filter, sort, summarize, concatenate, merge, transpose, and apply calculations to your data.

Figure 2.15: Data Menu

The following topics describe a few important Data menu tasks. Two other important Data menu tasks, stacking columns and recoding values, are described and used in Chapter 16. Data menu tasks not described in this book include ranking and standardizing data, converting the values of a variable from numeric to character or character to numeric, producing a summary data set, transposing a data set, taking a random sample, and creating a new column that is a square, square root, reciprocal, or exponential of an existing column. Consult the Analyst online help for more information about these tasks.

Computing New Variables

You can specify an expression for creating a new column in the data table. Select Data → Transform → Compute...to display the Compute dialog.

Figure 2.16: Compute Dialog

Type the expression in the box under the new column name, or use a combination of typing and selecting variables, functions, and operators. A numeric column is created by default.

Click on an operator at the right of the expression box to add it to the expression. You can also type in an operator.

To add a variable to the expression, double-click on the variable name or select it and click on the arrow above the Variables list. You can also type in a variable name.

Functions are organized into categories. Select a category by clicking on the arrow next to the Category: field. Review information about a function by selecting it. This information appears in the box to the left of the function list. Add a function to the expression by double-clicking on it or selecting the function and clicking on the arrow above the Functions box. You can also type in any SAS function. The functions displayed are a subset of all SAS functions.

By default, the column name is CompN, where N is the lowest number that produces a unique name. Replace the default column name by typing in one of your choosing.

The Attributes button displays the Column Attributes dialog, in which you can specify the name, label, and other attributes for your computed column. If you want to create a column with character values, use this dialog to set the variable type to character. Numeric is the default variable type.

Click on the Verify button to make sure your expression is valid. Function parameters are not verified, and the variable type is not taken into account.

If you have already used the Compute dialog to add a column to the current data table, click on the Recall button to fill the expression box and the Column Attributes dialog with the most recent expression and attributes.

Recoding Ranges

In performing an analysis, you may want to work with a particular factor as a classification variable rather than as a continuous variable. Recoding ranges enables you to create a new variable with discrete levels based on the ranges of values of an existing variable.

Select Data → Transform → Recode Ranges...to designate the column whose ranges you want to use.

Figure 2.17: Recode Ranges Information Dialog

Click on the arrow next to Column to recode: to select a numeric column from the current data table.

Specify the name of the new column that will contain the new data values. The new column has a default name, which you can type over with a name of your choosing.

The new column type can be character or numeric. If you select Character, you can use a character string to correspond to each range.

You must specify the number of groups that the current range will be divided into.

To help you decide how many groups to form, the range of the existing column is displayed at the bottom of this dialog.

After you have selected a column to recode and the number of groups that you want the new variable to have, click OK to display a dialog in which you can specify the recoding to be performed.

Figure 2.18: Recode Ranges Dialog

Use this dialog to substitute new values for the original ranges of the column specified in the Recode Ranges Information dialog. The number of rows in the table corresponds to the number of groups.

The Lower Bound is the lower boundary of a range. The Upper Bound is the upper boundary of a range. The upper boundary is automatically transferred to the next range's lower boundary. Only the first N - 1 cells of the Upper Bound need to be filled in.

Type in a character or numeric value to correspond to the range. If you do not type in a value, a missing value (blank) is assigned to the range.

Under Operators, you can control what happens to column values that fall on a range boundary. The first option groups these values with smaller values; the second option groups these values with larger values.

If you select Recode missing values and the lowest lower bound is left blank, missing values are placed in the lowest new group. If you don't select Recode missing values, missing values remain missing.

The range of the existing column is displayed at the bottom of this dialog.

Computing Log Transformations

Select a column and select Data → Transform → Log(Y) to calculate the natural logarithm of the values in the selected column. A new column containing the logarithm of each value is created. Other transformations, such as exponentiating and taking a square root, are also available from the Transform item in the Data menu.

Generating Random Variates

To generate random variates, select Data → Random Variates, and then select the distribution to be used for generating the random variates.

Figure 2.19: Generate Random Variates from a Normal Distribution Dialog

You can leave the new column name as the default or specify a new column name in the New column name: field.

Enter a value for each parameter. Click OK to create a column with the specified distribution.

Combining Tables

You can concatenate the rows or merge the columns from two or more tables.

Concatenating Tables by Rows

To vertically join tables by concatenating their rows, select Data → Combine Tables → Concatenate By Rows...

Figure 2.20: Concatenate Tables by Rows Dialog

Click on the Open SAS Data button to open SAS data tables. Click on the Browse button to select a file from your operating system's directory.

To change the order of the tables that you are appending, select a table and click on the up or down arrow to move the table one level up or one level down in the list.

To remove a table from the list, select the table and click on the Remove button.

Select Append to append the tables that you have selected. If you have chosen to append the tables, you can change the order of tables in the list. When you append tables, the rows of the first table are followed by the rows of the succeeding tables.

Select Interleave to interleave the rows of the tables.

Common variables among the tables you have chosen to concatenate are listed in the Common variables list. Select a common variable and click on the Interleave By button to add it to the list of variables to interleave by. When you interleave table rows, the rows of the table are combined and ordered according to the common variables that you have selected.

Select a variable and click on the Remove button to remove it from the list of Interleave By variables.

Click on the Variables button to choose the variables that you want to keep in your concatenation. By default, when you concatenate by rows, the resulting table contains only the common variables.

Merging Tables by Columns

To join tables horizontally by merging their columns, select Data → Combine Tables → Merge By Columns...

Figure 2.21: Merge Tables by Columns Dialog

In the Merge Tables by Columns dialog, you can select data tables to merge and the variables you will keep in the merged table. You can merge up to six tables. Type the name of the table in the Table name field, click on the arrow to select a SAS data table, or click on the Browse button to select a file from a directory.

Click on the More button to merge more than two tables.

You can choose whether the new combined table displays only matching rows, rows that match those in Table 1, or all rows.

Common variables among the tables you have chosen to combine are listed in the Common variables list.

Select a common variable and click on the Merge By button to add it to the list of variables to combine the tables by.

Select a variable and click on the Remove button to remove it from the list of Merge By variables.

Click on the Variables button to choose the variables that you want to keep in your merged table. By default, when you merge by columns, the resulting table contains all the variables.

Splitting Columns

You can split selected columns to output a new column whenever the value of a variable changes. Select Data → Split Columns...to display the Split Columns dialog.

Figure 2.22: Split Columns Dialog

Select a column from the candidate list and click on the Split Column button to designate a column to split.

Select a variable from the candidate list and click on the Split By button to designate a variable to split the first column by.

You can use the default names or type in new names for the split column if the type of the Split By column is character. Numeric columns do not have default names.

Subsetting Data

You can view a subset of your data by selecting

Data → Filter → Subset Data...In the Subset dialog, you can apply a Where clause to your data.

Figure 2.23: Subset Dialog

All subsequent analyses are run on the subset of the data.

Select Data → Filter → None if you do not want to subset your data, or if you want to remove an existing subset. None is the default.

To save the subsetted data, select File → Save As...If you select File → Save, the entire data set, and not just the subset, is saved.

Example: Modifying a Data Table

In this example, you combine selected columns from two data sets and edit them in a new data table. This example assumes that you have no data set loaded in the Analyst data table. If you do, select File → New before starting the example.

Each data set contains the results of taste tests of breakfast cereal. Each cereal is rated by several judges, on a scale of 1 to 5. After you concatenate the two data sets, you split the rating column by sample number.

Open Data Sets for Editing

To select the data sets and bring them into a new Analyst data table, follow these steps:

1. Select Tools → Sample Data...

2. Select JRating1 and JRating2.

3. Click OK to create the sample data sets in your Sasuser directory.

4. Select Data → Combine Tables → Concatenate By Rows...

5. Click on the Open SAS Data button. Select Sasuser from the list of Libraries. Select Jrating1 from the list of members. Click OK.

6. In the Concatenate Tables by Rows dialog, click on the Open SAS Data button again. Select Sasuser from the list of Libraries. Select Jrating2 from the list of members. Click OK.

   
   Figure 2.24: Concatenate Tables by Rows Dialog

7. Select Interleave.

8. Select JUDGE and SAMPLE from the list of Common variables and click on the Interleave By button to use JUDGE and SAMPLE as the variables by which the rows of the data tables will be combined.

   
   Figure 2.25: Interleave by Common Variables

9. Click on the Variables button to select the columns to include in the new data table.

   
   Figure 2.26: Selected Columns for New Data Table

   Only those columns common to both data tables are kept by default, as shown in the Keep list. The column SWEETNESS is not kept as part of the resulting table. The number preceding the column name SWEETNESS represents the data table to which this variable belongs.

10. Click OK to return to the Concatenate Tables by Rows dialog. Click OK again to display the new combined data table in a results window.

    
    Figure 2.27: Combined Table

11. To modify the combined table, you need to open it in the Analyst data table. Close the results window. Select the Combined Table node in the project tree and click the right mouse button to display the pop-up menu. Select Open.

    
    Figure 2.28: Opening the Combined Table

12. By default, data tables are opened in Browse mode. Select Edit → Mode → Edit to change the mode from Browse to Edit.

Modify the Data

In the data table you can modify the data by splitting columns so that a new column is generated when the value of a variable changes. You can also subdivide data into ranges.

To subdivide the data into ranges and split the columns according to sample number, follow these steps:

1. Divide the taste test results into three categories: good, mediocre, and bad. Select Data → Transform → Recode Ranges...

2. Click on the arrow next to Column to recode: and select Rating. Type taste_test in the New column name: field. Change New column type: to Character. Type 3 in the Number of groups to be formed: field to designate three taste test ranges.

   
   Figure 2.29: Recode Ranges Information Dialog

   Click OK to specify the new ranges.

3. In the first row, type 0 in the Lower Bound column and 2 in the Upper Bound column. Type bad in the New Value column.

4. When you press the Enter key, the upper bound value of the previous row is automatically filled in as the lower bound of the current row. Type 3 in the Upper Bound column and mediocre in the New Value column.

5. Move your cursor to the third row. Type 5 in the Upper Bound column and good in the New Value column.

   
   Figure 2.30: Boundary Values

6. Click OK to save your new boundary values.

   In the new table, the new ranges are displayed in the taste_test column.

   
   Figure 2.31: Table with taste_test Column

7. Remove the Rating column by selecting the column and selecting Delete...from the pop-up menu. Click OK in the Delete Items dialog.

   
   Figure 2.32: Delete Rating Column

8. You are going to split the taste_test column by the Sample column so that a taste test for each sample is displayed by judge. Select Data → Split Columns...

9. In the Split Columns dialog, select taste_test from the list and click on the Split Column button. Select Sample from the list and click on the Split By button.

10. Select User-defined names for the column names. Type Sample_ in the Column name prefix: field.

    
    Figure 2.33: Taste_test Column Split by Sample

11. Click OK. The resulting table displays the results of the taste test by each participating judge.

Figure 2.34: Split Columns Table

---