SaviCells

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).

Code Download

SaviCells

Requirements

SaviCells can either be used standalone or in conjunction with SAS directly. In the standalone mode (no SAS is needed on the PC), users will need to rely on the SAS OleDb client which can be found here:

SAS Providers for OleDb

This SAS client is free. SaviCells also uses the .NET Framework 3.5.

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

Types of Uses

SaviCells can be used in 1 of 2 ways:

1. Have a SAS program generate the XML necessary for SaviCells to operate and then run SaviCells using the X command. (Hint: if using this method, it is suggested that you also use SaviPut which makes wrapping XML, or other code, easily in put statements)
2. Run SaviCells standalone and have it use a local XML file.

Quick Start for Method 1

Please see this post on SAS-L:

SAS Generating the XML

Quick Start for Method 2

1. Create a directory called c:\temp
2. Copy the shoes.sas7bdat and the class.sasbdat from the SAS Help files and place them in the c:\temp directory
3. 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:

1. Test2.xls Excel workbook (native format)
   This should contain the shoes and class datasets now as worksheets with some test formatting.
2. SaviCells_shoes.txt
   The shoes worksheet as a delimited file ready to be brought into SAS.
3. SaviCells_class.txt
   The class worksheet as a delimited file ready to be brought into SAS.
4. SaviCells.log
   A log file showing what was processed.

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.

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.

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’.

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).

 

Sample Code

Instructions

The header and the footer for all XML will be the same for all sample XML in this section:

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">

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"' ;

Samples

Simple import into SAS

  <Import>
    <Workbook excelFile="c:\temp\test2.xls" sasFileType="Delimited" useFirstRowForVariableNames="true"
     sasOutputDirectory="c:\temp" sasOutputFilePrefix="SaviCells_"  />
  </Import>

 

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>

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.