Home » Coding & Data, Enterprise Guide

SAS Enterprise Guide – Tips & Tricks for Editing a Data Table Directly

Submitted by on November 11, 2011 – 8:00 am One Comment

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:

  1. Opened the dataset and clicked the area above the row numbers and next to the column names. (Ctrl+G also works.)
  2. 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.
  3. 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. 😉 ]

sas bi enterprise guide delete rows

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.

  1. 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. 
  2. Right-click the row number to see a pop-up menu of options, such as Insert, Append, and Delete.
  3. Select Delete rows to remove the row.

Note: Delete cannot be “Undone”!  Use your new found magic with caution young wizards!

SAS Enterprise Guide Quick Tip 2

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 Enterprise Guide Quick Tip 3

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.

SAS Enterprise Guide Quick Tip 4

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. 

Never miss a post!

Get the latest BI Notes post in your Inbox when a new post is released! Click here for free subscription. You email address is kept private and you can unsubscribe anytime. Go ahead ... join us!

Attending SESUG 2015?

If you are planning to attend SESUG 2015 in beautiful Savannah, Georgia on Sept 29 then join me for my talk about data visualization "How to be the MacGyver of Data Visualization"! I'll show you some cool ways to visualize data with SAS Visual Analytics as well as some traps to avoid.

The following two tabs change content below.

Tricia Aanderud

SAS Consultant at Zencos
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has over 10 years of SAS programming experience. Contact her for assistance with your next project.

Tags: ,

One Comment »