SAS Enterprise Guide – Tips & Tricks for Editing a Data Table Directly
Some of you may not realize that you can edit the data tables directly in SAS Enterprise Guide. You can also delete, insert, and append rows to an existing table. Here’s some SAS Enterprise Guide tips for this simple magic. Get your scroll and favorite note-taking quill …
SAS Enterprise Guide Tips!
SAS EG Tip 1: Deleting a Duplicate Row
This morning I needed to remove a single row from a 2.8 million-row dataset. Since I had a suspicion the row was an exact duplicate of the row above it, manually opening the dataset with SAS Enterprise Guide and deleting the row would be easier. I did have full rights to the dataset and the library – so this trick can be influenced by that fact. I am recreating what I did here with the CANDY_SALES_SUMMMARY dataset, which is part of the SAS Enterprise Guide sample library that I noted when discussing sample data sources.
After using PROC PRINT to determine the observation number for OrderID 4801, which was 12461, I did this:
- Opened the dataset and clicked the area above the row numbers and next to the column names. (Ctrl+G also works.)
- In the Go To Cell, type the row number from the Obs column. You can also select the specific column you want to view from the drop-down.
- Ta dah! I scrolled through the row and it was an exact duplicate. [The example does not show a duplicate record, so you'll just have to take my word that was what happened. ]
To remove the row with code was going to create a lot of typing and a waiting. Using Update mode, you can directly add, edit, or delete the information in a single cell or row. In this case it was much quicker and I knew that I could regenerate the data if I needed to. Thus this was a low risk solution.
- To start Update mode, right-click a cell in the row. If you have ReadWrite permission, EG asks if you want to switch to Update mode. This is mainly a sanity check – Are you sure? This is because once done – it cannot be undone. Since this was a duplicate row, I was not worried.
- Right-click the row number to see a pop-up menu of options, such as Insert, Append, and Delete.
- Select Delete rows to remove the row.
Note: Delete cannot be “Undone”! Use your new found magic with caution young wizards!
SAS EG Tip 2: Editing a Cell
Once you have entered Update Mode (explained in Tip 1), you can make edits directly to the cell. For instance, if I wanted to change the region for this particular row, I just need to click in the cell and make the edits. This trick can be handy when you have a table that you use for formats, permissions, and so on.
SAS EG Tip 3: Inserting/Appending a Row
In Update Mode, right-click for the pop-up menu and select Insert Row. A window appears that prompts you for the location and number of rows you want to add. Then you can add the information you want in the row.
Note: If you want to add a row where there is only a few differences, just copy the row and paste it into your new row. Then make the needed edits.
Another Tip! Here’s a tip for working with the SAS Enterprise Guide Welcome Screen.
The Building Business Intelligence with SAS book, due to publish in Jan 2012, covers the SAS BI tools including tips for using SAS Enterprise Guide with OLAP Cubes, information maps, and stored processes. Sign up to have SAS Press send you an email when the title is available!