search: Search support.sas.com KNOWLEDGE BASE     System Requirements     Install Center     Product Documentation     Papers     Samples & SAS Notes     Focus Areas SUPPORT     License Assistance     Administrator Services     Downloads & Hot Fixes LEARNING CENTER     Bookstore     Training     Certification     SAS Learning Edition     Higher Education Resources     SAS OnDemand For Academics COMMUNITY     User Groups     Events     E-Newsletters

• Print |
• E-mail  |
• Bookmark

Overview

The SAS^® Add-In for Microsoft Office currently does not have the capability to write back to the original table after the data has been modified in the spreadsheet. However, you can use the Copy to SAS Server feature in order to write the modified data back to the original table.

How to Copy the Modified Data Using Copy to SAS Server

If you open data in an Excel spreadsheet by using the SAS Add-In for Microsoft Office and then make changes to that data, you can use SAS→Active Data→Copy to SAS Server to save those changes. However, if you do not select the spreadsheet properly, then you might save the original data to the table instead of the modified data in the spreadsheet.

When you select SAS→Active Data→Copy to SAS Server from the menu, you must follow these steps in order to copy the modified data in the spreadsheet.

1. Make sure that you have row numbers displaying in your spreadsheet.



If you are not showing row numbers, you can turn them on in the SAS Add-In Options dialog box. Select SAS→Options and change the value of Insert row numbers in the first column to Always. After making this change, you must refresh your results in order to display the row numbers. You can refresh your results by selecting a cell in your data and then selecting SAS→Refresh.



2. Modify your data and then select the variable names and values in the spreadsheet that you want to copy. When you select the columns and rows, do not select the row numbers.
3. Make sure that the Active Data field in the SAS Analysis toolbar displays Active Selection.



4. Select SAS→Active Data→Copy to SAS Server to copy the modified data. Make certain the Copy to SAS Server dialog box contains Workbook, Worksheet, and Range information only. The dialog box should look similar to the following:



Note: If your dialog box contains information for the SAS Server, Library, and Data Set as well as the ones listed above, then you are not copying the modified data, but rather the original data that is referenced in the Data Set field. Here is an example of what the wrong dialog box might look like:



5. Specify the destination in the Copy to SAS Server dialog box. Next to the Save as field, click Browse, and then navigate to your original table. It will prompt you to overwrite the existing data. Select Yes to write the modified data back to the original table.

Note: To write back to your original table, you must have write permissions to the library where you are going to save the data. For more information on permissions and administering libraries, see the Technical Document What SAS^® Administrators Should Know about Libraries, Metadata, and SAS^® Enterprise Guide 4.1 (http://support.sas.com/techsup/technote/ts788.pdf). The information on administering libraries in SAS Enterprise Guide applies to the SAS Add-In for Microsoft Office as well.

Additional Documentation

For a list of available documentation for SAS Add-In for Microsoft Office, see http://support.sas.com/documentation/onlinedoc/addin/index.html

---

The SAS Add-in for Microsoft Office currently does not have the capability to write back to the original table after modifying data in the spreadsheet. However, you can use the Copy to SAS Server to write the modified data back to the original table.

Type:	Sample
Topic:	Common Programming Tasks ==> Reading and Writing SAS Data

Date Modified:	2008-05-08 09:56:37
Date Created:	2008-05-06 14:26:20

Operating System and Release Information

Product Family	Product	Host	Product Release		SAS Release
			Starting	Ending	Starting	Ending
SAS System	SAS Add-in for Microsoft Office	Microsoft® Windows® for x64	2.1
		Microsoft Windows 95/98	2.1
		Microsoft Windows 2000 Advanced Server	2.1
		Microsoft Windows 2000 Datacenter Server	2.1
		Microsoft Windows 2000 Server	2.1
		Microsoft Windows 2000 Professional	2.1
		Microsoft Windows NT Workstation	2.1
		Microsoft Windows Server 2003 Datacenter Edition	2.1
		Microsoft Windows Server 2003 Enterprise Edition	2.1
		Microsoft Windows Server 2003 Standard Edition	2.1
		Microsoft Windows XP Professional	2.1
		Windows Millennium Edition (Me)	2.1
		Windows Vista	2.1