Using the Workspace to Manipulate Data in a Data Set

Moving and Labeling Columns with the VIEWTABLE Window

With the VIEWTABLE window, you can rearrange columns and temporarily change column headings. This example uses the WORK.MYWORK data set that was previously created in the WORK library (see ' Viewing the Contents of a Data Set with the VIEWTABLE Window ' on page 310):

1. Click the heading for the Country Column.

   
   Display 19.9: VIEWTABLE Window Displaying MYWORK Data Set With Country Column Selected

2. Drag and drop Country onto Actual Sales . The Country column moves to the right of the Actual Sales column.

   
   Display 19.10: VIEWTABLE Window Showing Country Column Has Moved

3. Right-click the heading for Region and select Column Attributes from the menu.

   
   Display 19.11: VIEWTABLE Window Displaying Column Attributes Menu Option

4. In the Label box, type Sales Region and then click Apply .

   
   Display 19.12: Column Attributes Dialog Box

5. Click Close when you are finished.

Sorting Values of a Column with the VIEWTABLE Window

You can sort a data set in ascending or descending order, based on the values of a column. You can sort data permanently or create a sorted copy of a data set. This example creates a sorted copy.

This example continues to use the data set WORK.MYWORK that was created previously in ' Viewing the Contents of a Data Set with the VIEWTABLE Window' on page 310.

To sort the values of a column in WORK.MYWORK with the VIEWTABLE window:

1. Make sure that the WORK.MYWORK data set is still available.

   • If it is available, open it from SAS Explorer or VIEWTABLE.

   • If it is not available, recreate it from 'Viewing the Contents of a Data Set with the VIEWTABLE Window' on page 310.

2. Right-click the heading for Product .

3. Select Sort .

   
   Display 19.13: VIEWTABLE Window Displaying Sort Menu Option

4. Select Descending from the window.

5. When a warning message asks you if you want to create a new table, click Yes to create a sorted copy of the data set.

6. In the Sort dialog box, Table Name field, type WORK.Mysorted as the name for the sorted data set.

   
   Display 19.14: Sort Dialog Box

7. Click OK . Rows in the new data set are sorted in descending order by values of Product .

Creating a WHERE Expression with the VIEWTABLE Window

You can create a WHERE expression with the VIEWTABLE window. SAS generates the WHERE expression code automatically, and you can modify or edit the code.

A WHERE expression helps you subset a data set. A WHERE expression in SAS is modeled after the WHERE clause in SQL.

This example uses the PRDSALE data set that was created at the beginning of this topic, and shows you how to create a WHERE expression.

To create a WHERE Expression using the SAS workspace:

1. Double-click the WORK.PRDSALE data set created in the previous example. This opens the PRDSALE data set in the VIEWTABLE window.

2. Right-click any table cell (not a heading) and select Where . The WHERE EXPRESSION dialog box opens.

   
   Display 19.15: VIEWTABLE Window Displaying Where Menu Option

3. In the Available Columns list, click REGION .

   
   Display 19.16: Where Expression Dialog Box

4. Select EQ (equal to) from the pop-up list.

   
   Display 19.17: Where Expression Dialog Box Displaying EQ Menu Option

5. Click < Lookup Distinct Values > . This opens a window containing values. You can select values from the list.

   
   Display 19.18: Where Expression Dialog Box Displaying LOOKUP Option

6. In the Lookup Distinct Values window, select WEST .

   
   Display 19.19: Lookup Distinct Values Dialog Box

7. Notice that the complete Where expression appears in the Where box near the bottom of the display.

   
   Figure 19.1: Where Expression Dialog Box Displaying the Complete Where Expression

8. Click OK. The VIEWTABLE window now displays only the rows where the value of Region is WEST .

Editing Values in a Cell with the VIEWTABLE Window

You can edit a cell in a data set by opening the data set in the VIEWTABLE window and switching to edit mode. These are general instructions for editing cells in a SAS data set.

1. Open the Explorer window by selecting

   View – Explorer

   from the menu, or alternatively, by entering the explorer command in the command line.

2. Select a file that contains the cell that you want to edit.

3. Double-click the selected file to open it in the VIEWTABLE window.

4. Select

   Edit – Edit Mode

   from the VIEWTABLE menu.

   
   Display 19.20: Where Expression Dialog Box Displaying Edit Mode Menu Option

5. Click the cell that you want to edit.

6. Highlight the existing value and type a new value.

7. Press ENTER.

8. Select

   File – Close

   When prompted about saving changes to the data set, click Yes , if you want to save the change.

Clearing Subsetted Data from the VIEWTABLE Window

If you have subset rows in the VIEWTABLE window, as in the previous example, you can clear subsets and then redisplay all data in the data set.

1. Right-click anywhere in the data set except a column heading.

2. Select Where Clear . The VIEWTABLE window removes any existing subset(s) and displays all rows in the data set.

3. Select

   File – Close