SaviCells
From sasCommunity
[edit] SaviCells Guide and Sample Code
SaviCells is a product designed to move data into and out of Excel from/to SAS datasets. SaviCells relies on an XML file for its commands and is called from SAS using the X command (or an equivalent).
Contents |
[edit] Code Download
[edit] Requirements
SaviCells relies on the SAS OleDb client for reading in SAS datasets. This client can be found, for free, on SAS’s tech support area under downloads. SaviCells also uses the .NET Framework 3.5.
[edit] Install Overview
The install directory of SaviCells, by default, would be c:\program files\savian\savicells. Within this directory are the following core files:
SaviCells.exe
This is the primary application and what is called from SAS
SCParms.xml
This is the configuration file used for processing instructions
Additionally, SaviCells has a number of other files. The others that would be useful for an advanced user are:
SCParms.xsd
An XSD file that wraps SCImport.xsd and SCExport.xsd
SCImport.xsd
Shows the possible XML tags used in the import section of the SCParms.xml file
SCExport.xsd
Shows the possible XML tags used in the export section of the SCParms.xml file
[edit] Quick Start
- Create a directory called c:\temp
- Copy the shoes.sas7bdat and the class.sasbdat from the SAS Help files and place them in the c:\temp directory
- Run the following command in SAS:
options noxwait noxsync; x '"C:\Program Files\Savian\SaviCells\savicells.exe " "C:\Program Files\Savian\SaviCells\SCParms.xml"' ;
SaviCells should have created 4 files in the c:\temp directory:
- Test2.xls Excel workbook (native format)
This should contain the shoes and class datasets now as worksheets with some test formatting. - SaviCells_shoes.txt
The shoes worksheet as a delimited file ready to be brought into SAS. - SaviCells_class.txt
The class worksheet as a delimited file ready to be brought into SAS. - SaviCells.log
A log file showing what was processed.
[edit] XML Configuration File
The sample xml file delivered with SaviCells (SCParms.xml) is very rich to demonstrate all of the features. What is possible in that XML files can be found in the SCExports.xsd and the SCImports.xsd which are in the SaviCells install directory. Before making changes to this XML file, make a backup or else create a new file and simply use that as the argument in the SAS x command.
The XML file is composed several sections.
[edit] Header
<?xml version="1.0" encoding="utf-8"?>
<Actions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="SCParms.xsd"
log="c:\temp\SaviCells.log">
The header indicates that this is an XML file and its choices can be found in the SCParms.xsd (located in the same directory as the SCParms.xml). Additionally, it indicates a location for where to write the log file.
[edit] Export
This indicates what and how to export data from SAS. Here is a breakdown of its major areas:
Workbook
<Workbook physicalLocation="c:\temp\test2.xls" use1904Dates="false">
Starts a workbook section and indicates where the workbook is to be written. If this workbook is to ultimately go to a Mac, Macs use a 1904 dating system. Normally, leave the use1904Dates as false.
Worksheet
The worksheet object which ranges from the <Worksheet> tag to the </Worksheet> closing tag has a lot of elements, most of which are self-explanatory. Please see some of the samples for easier examples. Within the sample XML file, the class dataset is much simpler and easier to follow for someone new to SaviCells:
<Worksheet name="Class">
<Data sasLibrary="c:\temp" sasDataset="Class" formatHandling="1" dataType="sas7bdat"/>
<Range start="B1" end="B3" name="testRange"></Range>
</Worksheet>
The above XML tells SaviCells that it should create a worksheet named Class, the data will come from a SAS library found at c:\temp, the dataset to look for is class.sas7bdat, data will be returned from SAS with formatting (please see the SCExport.xsd for choices), and that the data will be placed into a range starting at B1, going to B3, and the range will be named ‘testRange’.
[edit] Import
The import tags in SCParms.xml are much easier to work with. Here is an example:
<Workbook excelFile="c:\temp\test2.xls" sasFileType="Delimited" useFirstRowForVariableNames="true"
sasOutputDirectory="c:\temp" sasOutputFilePrefix="SaviCells_" />
This tells SaviCells to read in an Excel file called c:\temp\test2.xls, create a delimited file, use the first row of the file for variable names, write the resulting delimited file to the c:\temp directory and prefix the output file with SaviCells_ (SaviCells will then add the worksheet name to the end of the file also use the ‘.txt’ extension since it is a delimited file).
[edit] Sample Code
[edit] Instructions
The header and the footer for all XML will be the same for all sample XML in this section:
[edit] Header
<?xml version="1.0" encoding="utf-8"?>
<Actions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="SCParms.xsd"
log="c:\temp\SaviCells.log">
[edit] Footer
</Actions>
The SAS code will be the same for all samples:
options noxwait noxsync; x '"C:\Program Files\Savian\SaviCells\savicells.exe " "C:\temp\TestParms.xml"' ;
[edit] Samples
[edit] Simple import into SAS
<Import>
<Workbook excelFile="c:\temp\test2.xls" sasFileType="Delimited" useFirstRowForVariableNames="true"
sasOutputDirectory="c:\temp" sasOutputFilePrefix="SaviCells_" />
</Import>
[edit] Simple export from SAS
<Export>
<Workbook physicalLocation="c:\temp\test2.xls" use1904Dates="false">
<Worksheet name="Shoes" varNames="true" varLabels="false">
<Data sasLibrary="c:\temp" sasDataset="Shoes" formatHandling="0" dataType="sas7bdat"/>
</Worksheet>
</Workbook>
</Export>
[edit] Log File
Here is a sample log file showing the processing information: 10:20:21.21 --> Beginning conversion of Shoes to worksheet at c:\temp\test2.xls 10:20:21.21 --> Get sas data... 10:20:21.21 --> Create Excel worksheet... 10:20:21.21 --> Excel worksheet completed... 10:20:21.21 --> Conversion completed. 10:20:21.21 --> Duration : 94 ms 10:20:21.21 --> Rows Converted: 395 10:20:21.21 --> Getting import XML file. 10:20:21.21 --> 0 import items found.
Categories: Savian | Excel | Utility | IMPORT | EXPORT | SAS/ACCESS
