Excel

5月 222012
 

On the SAS-L mailing list, a participant posed this question (paraphrased):

How can I tell which date format my Windows session is using: European format (with day first) versus USA format (with month first)? I'm reading in output from a Windows file listing, and need to know how to interpret the date value.

There may be more than one way to skin this cat, but one of the responses pointed at a Windows registry key as one cue to discover the "short date format", which can differ depending upon your regional settings. Following that path, the question then becomes: how can I query the Windows registry key from within my SAS program?

Answer: There is a REG QUERY command that is built into Windows. If you can run that command from within your program, then you can parse the result and get your answer.

Here's an example to discover the "short date" format on your Windows system:

/* Requires XCMD privileges */
filename reg pipe
  'reg query "HKEY_CURRENT_USER\Control Panel\International" /v sShortDate';
 
data _null_;
  infile reg dsd;
  input;
  if (find(_infile_,'sShortDate')>0) then
    do;
      result = scan(_infile_,-1,' ');
      call symput('SHORTDATE',result);
    end;
run;
 
%put Short date format is &shortdate.;

Result (with my United States regional settings):

Short date format is M/d/yyyy

The discovery of the REG QUERY command got some of the SAS-L participants kind of excited (you know, it doesn't take much...), and they began to wonder what other goodies could be gleaned from the Windows registry. One key of interest is the infamous TypeGuessRows key, which affects the behavior of PROC IMPORT with Microsoft Excel as it determines column types.

There are some tricky parts to deciphering this key. First, the relevant key was in one location in SAS 9.1 and earlier (which uses Microsoft Jet database engine), and in a different location in SAS 9.2m2 and later (using Microsoft ACE).

The second complication is that on 64-bit systems, the Windows registry is segmented into a 32-bit registry and a 64-bit registry. (That's yet another "gotcha" of 64-bit applications on Windows.) You must query the correct key for the process that will read the Excel file. If you're using 64-bit SAS to read Excel files using DBMS=EXCEL or the EXCEL library engine, query the 64-bit key. If you're using 64-bit SAS to read Excel files using DBMS=EXCELCS or the PCFILES library engine (thus using the 32-bit PC Files Server), then query the 32-bit key.

From a 64-bit process, you can query the 32-bit registry. The 32-bit key "address" will have a Wow6432Node level in the middle of it.

Here's an example of querying the 32-bit key from a 32-bit SAS session (good for SAS 9.1 and earlier):

%let key = HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel;
/* Requires XCMD privileges */
filename reg pipe
  "reg query ""&key"" /v TypeGuessRows";
 
data _null_;
  infile reg dsd;
  length result 8;
  format result 4.;
  input;
  if (find(_infile_,'TypeGuessRows')>0) then
    do;
      /* Value stored as hexadecimal */
      result = input(trim(scan(_infile_,-1,'x')),hex2.);
      call symput('TypeGuessRows_JET',result);
    end;
run;
 
%put Excel (Jet) TypeGuessRows is %trim(&TypeGuessRows_JET.);

Typical result:

Excel (Jet) TypeGuessRows is 8

For those running the 64-bit version of SAS 9.2M2 or later (including SAS 9.3), this example is probably more relevant:

%let key = HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel;
/* Requires XCMD privileges */
filename reg pipe
  "reg query ""&key"" /v TypeGuessRows";
 
data _null_;
  infile reg dsd;
  length result 8;
  format result 4.;
  input;
  if (find(_infile_,'TypeGuessRows')>0) then
    do;
      result = input(trim(scan(_infile_,-1,'x')),hex2.);
      call symput('TypeGuessRows_ACE',result);
    end;
run;
 
%put Excel (ACE) TypeGuessRows is %trim(&TypeGuessRows_ACE.);

And here's the typical result:

Excel (ACE) TypeGuessRows is 8

The key value in the code is very long and might be difficult to see in this code sample, so I'll repeat it here in bold: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel for Office 2007, and HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel for Office 2010.

Querying the registry is fun, interesting (if you like that sort of thing), and mostly harmless. Modifying the registry is a little bit risky. There is a REG ADD command that allows you to modify existing registry entries if you have the privileges to do so. That's all that I'll say about that -- if you're brave and ingenious, you can take it from here.

tags: 64-bit, excel, windows, Windows registry, xcmd
5月 022012
 

Many SAS customers are quickly adopting 64-bit versions of Microsoft Windows, and they are pleased-as-punch when they find a 64-bit version of SAS to run on it. They waste no time in deploying the new version, only to find that a few things don't work quite the same as they did with the 32-bit version. This post describes the top snags that end users encounter, and how to work around them.

Gotcha #1: Importing Microsoft Excel files

Imagine you have a program that looks like this:

proc import out=work.class
 datafile="c:\temp\class.xls"
 DBMS = EXCEL;
run;

On 64-bit SAS for Windows, you might be surprised to encounter this error:

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement
Connection Failed.  See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
  real time           0.11 seconds
  cpu time            0.04 seconds

This isn't limited to importing Excel files. It can happen when you use PROC EXPORT to export Excel files, or use DBMS=ACCESS for Microsoft Access database files, or when you try to use LIBNAME EXCEL to reference a local Excel spreadsheet as data.

The Cause:
Your 64-bit SAS process cannot use the built-in data providers for Microsoft Excel or Microsoft Access, which are usually 32-bit modules. In a previous blog post, I've provided a bit of explanation about this limitation.

The Fix:
Use DBMS=EXCELCS for Excel files, or DBMS=ACCESSCS for Microsoft Access. For LIBNAME access, try LIBNAME PCFILES. These approaches use the PC Files Server, which is a separate small application that is provided with SAS/ACCESS to PC Files. Note that you may need to go back and install this application, as it might not have been placed in your installation automatically. However, you can use the Autostart feature to skip having to configure it as a service, and thus minimize the changes to your SAS programs.

Alternatively, you can try DBMS=XLSX to remove the data providers from the equation.

NOTE: There are a few feature differences between the EXCELCS and EXCEL options. Read this SAS note to determine whether these differences will affect your work.

A Caution:
I've heard of a few customers who decide to workaround this limitation by installing the 64-bit version of Microsoft Office (and thus using the 64-bit data providers). That works, but it might introduce other incompatibilities with how you use your Microsoft Office applications. Microsoft recommends the 64-bit version of Office in only a few circumstances; consider the implications carefully before you head down this road.

Gotcha #2: Incompatible FORMATS catalog

Suppose that you have a library of user-defined formats that you once created by using PROC FORMAT. User-defined formats are stored in SAS catalogs, which are a sort of SAS-specific file system structure that SAS can access during your session.

If you created and used these user-defined formats with 32-bit SAS, you'll see this message when you try to use them with 64-bit SAS:

15         libname library "c:\datasources\32bit";
NOTE: Libref LIBRARY was successfully assigned as follows:
Engine:        V9
Physical Name: c:\datasources\32bit
16         proc print data=sashelp.air;
17         format date benefit.;
ERROR: File LIBRARY.FORMATS.CATALOG was created for a different
 operating system.
18         run;

The Cause:
For all intents and purposes, the move from 32-bit SAS for Windows to 64-bit SAS for Windows is like a platform change, and SAS catalogs are not portable across platforms. Even though you've just moved from one version of Windows to another, from a SAS perspective these files are different, with different internal structures.

The Fix:
SAS provides the utility procedures CPORT and CIMPORT to allow you to transfer catalog content across different operating environments, and you can certainly take that approach for this scenario.

If you have a mixed environment on your team where some people have 32-bit SAS and others have 64-bit SAS, it might be easier to decompose the format definitions down to data sets (by using PROC FORMAT and the CTLOUT option). You can then easily recreate the formats "on the fly" by using PROC FORMAT and the CTLIN option.

This works well because SAS data sets are compatible between the 32-bit and 64-bit versions of SAS...mostly. That brings us to the last "gotcha".

Gotcha #3: Different data set encoding triggers CEDA

If you use SAS data sets that were created by a 32-bit version of SAS, you can read them without modification in 64-bit SAS. But you might see a message like this:

NOTE: Data file TEST.HMEQ.DATA is in a format that is native to another host,
or the file encoding does not match the session encoding.
Cross Environment Data Access will be used, which might require additional
CPU resources and might reduce performance.

I've written about cross-environment data access before, and how it's a bit of SAS magic that helps with cross-platform compatibility. However, you might not have expected it to kick in when you upgraded to 64-bit SAS on Windows.

The Cause:
SAS data set files are written with an encoding that is specific to the SAS operating environment. In 32-bit SAS on Windows, the encoding is WINDOWS_32. On 64-bit SAS, it's WINDOWS_64. When the data set encoding differs from the native SAS session encoding, CEDA kicks in.

The good news is that in SAS 9.3, the SAS developers "taught" SAS for Windows to bypass the CEDA layer when the only encoding difference is WINDOWS_32 versus WINDOWS_64.

The Fix:
You don't have to do anything about this issue unless you want to update the data sets. And if you have SAS 9.3, you probably won't see this message at all...at least not when the data originates from 32-bit SAS for Windows.

If you decide to convert entire data set libraries to the new native encoding, you can achieve this by using PROC MIGRATE.

Parting bits

I'll finish this post with just a few general points to guide you:

  • 64-bit Windows is pervasive, and is a Good Thing. The 64-bit OS, combined with better hardware and more memory, can help to deliver more throughput.
  • In the not-so-distant future, all apps will eventually become native 64-bit. The incompatibility hiccups of today will be tomorrow's faint memory.
  • But for today, don’t automatically deploy 64-bit app "just because" such a version exists. Make it a deliberate business decision to consider.
  • And if you do go with the 64-bit app, budget the time/resources for 64-bit conversion, if necessary

Related posts about 64-bit topics

Myths about 64-bit computing on Windows
Are 64-bit client applications twice as good as 32-bit applications?
How do I export from SAS to Excel files: Let me count the ways
Should you care about 64-bit applications?

tags: 64-bit, excel
2月 122012
 

OpenCDISC, the first open source CDISC validator, is already in the toolbox of FDA reviewers (CDER/CBER, see CDISC Standards in the Regulatory Submission Process, 26 January 2012, P.33). The key features in OpenCDISC is a dichotomy of validation rules (XML based) and application logic. Currently OpenCDISC Validator (Version 1.2.1) officially supports the four following CDISC modules:

You can get the corresponding configuration files (validation rules) online or in the software folder (in ..opencdisc-validatorconfig with extension of .xml). Since SDTM 3.1.2 has the most rich set of validation rules from Janus, WebSDM and of course additional  OpenCDISC rules by itself, its configuration file (config-sdtm-3.1.2.xml) deserves more attention. Better understanding of config-sdtm-3.1.2.xml is the first step to customize the software according to business needs. Followings are some personal tips and tricks to play and even “torture” the file, using Notepad++, web browsers (IE and Firefox), Excel with MSXML and SAS XML Mapper.

1. DON’T use the Windows default Notepad to open and edit the xml file

XML_Notepad

while the reason:

if you use Notepad to open a XML file, almost you get nothing but strings and strings.

and another supporting reason, see bellowing picture.

2. USE Notepad++ or other REAL text editors to open and edit it

XML_Notepad

Notepad++ makes the difference. It supports multiple tabs view, XML syntax highlighting and XML tags match and other fancy stuff never in the plain Notepad. And like OpenCDISC, it’s free, both in sense of free beer and free speech.

Other real text editor, include Vim, UltraEdit and such, but for most users, I still think Notepad++ is the most handy one.

3. At first, use a web browsers to review it

XML_IE

It is the web view of config-sdtm-3.1.2.xml. The secret is a style file, define-1.0.xsl in ..opencdisc-validatorconfigschematron. This is another story of dichotomy. The config-sdtm-3.1.2.xml file itself is only used to store metadata (machine-readable), while the style file (also a XML file) used to instruct how to display it (human-readable). Within some proper internal interface, web browsers (I tested in IE and Firefox; Google Chrome doesn’t work). Excel can also render this XML file well (only test on Excel 2010 and 2007) while Web view is much better:

XML_Excel

4. The real awesome job: use Microsoft XML parser or other XML parsers to dig into XML structure

XML_Tags_Excel

I use Excel 2010 with Microsoft XML parser (MSXML 6.0. You can get the version of your MSXML by visiting this website in IE and you will get the different results when switching to other web browsers because Firefox and Chrome use other parsers).

You can also get a instance of each XML tag:

XML_Tags_Excel_preview

5. The real awesome job: use SAS XML Mapper to get the tabulation view

And you may want to exact all the tables in the XML file with tabulation view, ideally, in SAS dataset:

For example, the first few rows in config-sdtm-3.1.2.xml:

ODM_xml_tab

and the corresponding SAS dataset:

ODM_tab

Actually you can put all the data in XML into a big dataset but with lots of redundancies. To use SAS XML Mapper (the latest version is 9.3), you should design a mapping file to tell the structure of the XML file. For the simple ODM dataset, you indicate the table name, column name and path, type and length:

map

It never be fun to play with XML files. SAS XML Mapper is supposed to read CDISC ODM based XML files automatically (OpenCDISC XML files are called ODM compliant), but at least for this config-sdtm-3.1.2.xml, it failed and that’s why we should create a mapping file (see above) by ourselves. Fortunately you don’t need to write it from scratch (it would be thousands of lines of codes):

  • find a CDISC ODM based XML file that SAS XML Mapper can read automatically, e.g., in http://www.cdisc.org/define-xml, a file named define-example1.xml works well.
  • use AutoMap function in SAS XML Mapper to get the mapping file.
  • modify the mapping file to fit your needs.
  • for details, refer SAS XML mapping syntax.

6. Final Notes for Excel

Right click config-sdtm-3.1.2.xml then open with “Microsoft Excel”:

Excel1

Option 2 will go to section 3. If go with option 1:

Excel2

Option 1-1 and 1-2:   tabulation view in section 5

Option  1-3:  tag view in section 4.

2月 122012
 

I have a love-hate relationship with spreadsheet data. I am interested in finding data to analyze, and if it arrives in the form of a spreadsheet, I'll take it. And I like to deliver results and reports, but often my constituents ask for it as a spreadsheet that they can then manipulate further. <Sigh.>

A spreadsheet is not a database, so it can be a challenge to whip spreadsheet data into analysis-ready shape. Likewise, saving results as a spreadsheet can lose something in the translation -- usually value formatting, appearance attributes, or graphs.

SAS offers many ways to read from and write to Microsoft Excel spreadsheets. They each have pros and cons. This post is an inventory of the methods that I know about for creating Excel files from within SAS.

Some "bits" about 32-bit and 64-bit architecture

Before I get to the Big List, let me set the stage by describing a few terms and concepts.

In order to create Excel files directly from SAS, you need SAS/ACCESS to PC Files. This product enables all sorts of file exchanges between SAS and other PC-based applications, including Microsoft Excel, Microsoft Access, SPSS, and more.

SAS/ACCESS to PC Files includes a component called the PC Files Server. The PC Files Server is a service-based application that runs apart from SAS on a Windows node. It accepts requests from SAS to convert data to and from Excel (and other file types). Traditionally, this innovation allows SAS running on UNIX (where there are no native facilities for Excel data) to exchange data with PC-based data programs.

Recent changes in desktop computing have complicated the picture. Newer desktop machines all have 64-bit architecture, and most organizations are now adopting 64-bit versions of Microsoft Windows. All of your 32-bit applications (designed for x86 architecture) still can still run, of course, but there are a few hard-and-fast rules. One of those rules is that a 64-bit application cannot dynamically load 32-bit modules in its process space. And guess what? There is a better-than-even chance that the built-in data providers that you have for Microsoft Excel -- the bits that allow SAS to write to Excel on Windows -- are 32-bit modules. This means that the PROC EXPORT DBMS=EXCEL program that worked in your 32-bit SAS session will not work in your 64-bit SAS session.

There are two remedies for this bitness mismatch. First, you could install the 64-bit data providers (which accompany the 64-bit version of Microsoft Office). But you cannot have both the 32-bit and 64-bit versions of these data providers on the same machine; if you have 32-bit Microsoft Office, then you're stuck with the 32-bit providers for now.

The second remedy is to use the PC Files Server, right there on the same Windows machine where SAS is running. This allows a 64-bit SAS process to delegate the data exchange to a 32-bit PC Files Server process. Thanks to the out-of-process communication, this circumvents the bit architecture mismatch. To make this work you don't have to set up any additional software, but your SAS programs must change to use DBMS=EXCELCS. The EXCELCS keyword tells SAS to use the PC Files Server instead of attempting to use in-process data providers.

Exporting to Excel: ways to get there from SAS

With the architecture lesson behind us, here's my list for how to put SAS content into Microsoft Excel. I won't dive into much detail about each method here; you can follow the links to find more documentation.

These methods use features of SAS/ACCESS to PC Files:

LIBNAME EXCEL – reads/writes Excel files at the sheet level when the bitness of SAS (32- or 64-bit) matches the bitness of Microsoft Office installed (or more specifically, the ACE drivers that accompany Office). An Excel file is viewed as a SAS library, while sheets/ranges are the member tables. Requires exclusive lock on an existing Excel file.

LIBNAME PCFILES – does the same as LIBNAME EXCEL, but uses PC Files Server. Good for UNIX and for Windows configurations where bitness of SAS and Microsoft Office don't match.

PROC EXPORT DBMS=EXCELCS – uses PC Files Server to write an Excel file. There are various options to control the output behavior. Good for UNIX and for Windows configurations where bitness of SAS and Microsoft Office don't match.

PROC EXPORT DBMS=EXCEL - writes Excel files when the bitness of SAS (32- or 64-bit) matches the bitness of Microsoft Office installed (or more specifically, the ACE drivers that accompany Office).

PROC EXPORT DBMS=XLS – writes Excel (XLS) files directly, no driver or PC Files Server needed. Has limits on volume and format. Works on Windows and UNIX.

PROC EXPORT DBMS=XLSX – new in 9.3M1, writes Excel 2010 files (XLSX format) directly. No driver or PC Files Server needed. Works on Windows and UNIX.

The following methods do not require SAS/ACCESS to PC Files, so they are popular, even if they don't produce "native" Excel files:

PROC EXPORT DBMS=CSV – produces comma separated value files, most often used in Excel.

ODS TAGSETS.CSV (or just DATA step and FILE output) – produces comma separated value files, most often used in Excel.

ODS TAGSETS.EXCELXP – uses ODS to create an Office XML file. Provides a fair amount of control over the content appearance, but recent versions of Excel do not recognize as a "native" format, so user is presented with a message to that effect when opening in Excel.

FILENAME DDE – uses Windows messages to control what goes into an Excel file, down to the cell level. Works only when SAS for Windows and Excel are on the same machine. Doesn't work in SAS workspace servers or stored process servers (often accessed with SAS Enterprise Guide). It's an antiquated approach, but offers tremendous control that many long-time SAS users enjoy.

SAS client applications make it easier

While I've focused on the SAS programming methods for creating Excel files, applications like SAS Enterprise Guide and the SAS Add-In for Microsoft Office make the operation a breeze. SAS Enterprise Guide can import and export Excel files through point-and-click methods, and SAS/ACCESS to PC Files is not needed to make that work. (However, the work is not captured in a SAS program, so it cannot be run in SAS batch jobs or stored processes.)

SAS Add-In for Microsoft Office turns the problem on its head. By allowing you to access SAS data and analytics from within Microsoft Excel, you pull the results into your Excel session, rather than export them from your SAS session.

tags: excel, export, pc files server, SAS/ACCESS PC Files
12月 022011
 

We're having work done on our house at the moment. It's an older place that hasn't had as much maintenance as it should and so while it's not a full rebuild, it's still enough that there's a regular procession of tradespeople ("tradies", to most Australians) around the place. And, if there's one thing I've learned, it's that everyone does a bad job.

Don't get me wrong - most of the time, I can't even see anything out of place! Apart, that is, from our crooked steps that have been rebuilt three times now, but let's not go there ...

Every time anyone looks at work someone else did, we've inevitably heard about how badly it's been done, how they would have done it differently, and how they'd sack their labourers if they did such a dodgy job. At some stage you just have to laugh.

Analytics is the same, even if it seems unrelated. Everyone has a different definition of what "analytics" really means. To some, it's optimising SQL. To others, it's playing with data in Excel. Artificial intelligence, rules engines, statistical analysis, time series analysis, operations research ... if you get ten people in a room, you'll almost inevitably get ten different answers. It's no wonder that people get confused!

Cutting through this Gordian knot is simple - to me, they're all "analytics". Analytics is fundamentally about extracting insight from data, regardless of how that happens. It may be as simple as joining a series of tables to create a single view of customer or it may be as complex as doing integer-based non-linear optimisation to work out optimal shipping paths. Regardless of how sophisticated the analysis may be, they're all about transforming data into information.

The mind-blowing thing is that when you look at it like that, analytics is probably one of the largest technical domains in the world. We're all analysts in some way - the only thing that varies is the level of sophistication that we use. Much like my tiles, actually - I'm sophisticated enough to know a good outcome when I see it, even if I need someone's help to get there in the first place.

For a somewhat distracting (and hopefully entertaining) Friday activity, have a look around your life and count the number of applications of analytics you can find. Trying to optimise your path through the supermarket for Friday night's dinner? Analytics. Counting up Q4 sales and measuring against target? Analytics. Setting up an office pool and making sure everyone has an equal chance to win? Analytics.

It's pervasive. And, that's a good thing - analytics helps us make better decisions. It doesn't replace intuition, but it helps us understand what we do and don't know. It helps us discover those impossible situations that our data suggests exist but, on analysis, quite simply can't. It makes our lives less unsure, every day of the week.

We're all analysts now, even if we didn't realise it.

tags: analytics, excel, renovation, SQL
5月 192011
 
I hope that the following statement is not too controversial...but here it goes: Microsoft Excel is not a database system.

That is, I know that people do use it as a database, but it's not an application that supports the rigor and discipline of managing data in the same way that "real" database systems do. Even Excel's more boring and less popular cousin, Microsoft Access, supports the discipline of database management much better than Excel.

Still, Excel spreadsheets are widely used because they are so flexible and can contain rich content, including charts and fancy formatting. These might be assets when it comes to creating compelling output, but it doesn't help a bit when your task is to analyze the data.

That's why the first step to analyzing spreadsheet data within SAS is to import the data into a SAS data set. The Import process allows you to impose the oft-needed discipline to the spreadsheet data "on the way in" to your SAS environment. For example, you can:

  • Select the data sheet, or an exact range within a sheet, that you want to import
  • Apply formatting rules to the data values so that they are treated appropriately (as character, numeric, date or time values, for example)
  • Control the field names so that you have SAS-friendly variable names in the resulting data set
Done properly, these steps will reduce the amount of "clean up" you need to perform before you can proceed with analysis.

The Import Data task within SAS Enterprise Guide (menu: File->Import Data) can lead you through this easily. For well-organized data in spreadsheets, the default settings are usually acceptable and you can simply review them and click Finish with no tweaking. For spreadsheet data that are less organized, the level of control that the wizard offers can help a lot.

We often get questions about how the import process works within SAS Enterprise Guide. For example, does it require SAS/ACCESS to PC Files? Does it generate a SAS program that you can reuse in batch code or in a stored process? Here are the answers:

SAS Enterprise Guide does not require SAS/ACCESS to PC Files in order to import Microsoft Excel content. SAS Enterprise Guide uses local Windows components to read the data from spreadsheets and then transforms them into a text-based format that SAS can import via DATA step. (Note: the same is true when importing Microsoft Access databases, Lotus worksheets, or even Paradox files.)

This means that when the step is complete you have your data in SAS, but you don't have a SAS program that represents the entire process. SAS Enterprise Guide does some of the work behind the scenes. That's convenient if you don't have SAS/ACCESS to PC Files on your SAS server. It's less convenient if you are trying to build a standalone batch program.

SAS Enterprise Guide can use SAS/ACCESS to PC Files if you want. If you do have SAS/ACCESS to PC Files on your Windows SAS server, you can check a box in the Advanced Options of the Import Data task and get SAS Enterprise Guide to generate a working program that uses PROC IMPORT with DBMS=EXCEL. This is similar to how the Import Data wizard works in the SAS display manager environment. However, there are a few requirements:

  • The Excel file must reside in the file system of the SAS server (your local PC if using local SAS, or the remote Windows system if your SAS workspace is on a different machine).
  • This is supported only when connecting to SAS on Windows. It is possible to use SAS/ACCESS to PC Files on a Unix system, but it requires a PC Files Server and the DBMS=EXCELCS option, which the Import Data task does not generate for you.
If all of those planets align for you, then you can use the Import Data task to create a reusable SAS program.

Exporting SAS data in Excel format does not require or use SAS/ACCESS to PC Files. Again, SAS Enterprise Guide uses the Windows-based APIs to create XLS files (or XLSX files with this custom task), and PROC EXPORT is not used for this. If you need the step to happen inside a SAS program, you can write your own PROC EXPORT step in a program node.

I hope that this clears up some of the questions and misconceptions we encounter. But if this post doesn't do it for you, let me know in the comments.

5月 162011
 
In our last entry, we described reading Excel files. In this entry, we do the opposite: write native Excel files.

R

In R, the WriteXLS package provides this functionality. It uses perl to do the heavy lifting, and the main complication is to install this with the needed Perl modules. There are detailed instructions here. On Nick's mac (which came with perl already installed), he needed to run the additional command:

cpan -i Text::CSV_XS

to add the needed functionality. Once this was done, he ran the commands:

library(WriteXLS)
testPerl()

which yielded the encouraging output:

Perl found.
All required Perl modules were found.

To generate the spreadsheet, the WriteXLS() function was called with a character vector or list containing the data frames to export, along with the name of the spreadsheet to create. Here we want to write out the HELP data frame.

HELP = read.csv("http://www.math.smith.edu/r/data/help.csv")
WriteXLS("HELP", "newhelp.xls")

It might be necessary to write multiple sheets to a single file. Here, as an example, we make a new table with just female subjects, then create an Excel file with the whole data and just the women.

helpfemale = subset(HELP, female==1)
WriteXLS(c("HELP", "helpfemale"), "newhelp.xls")


SAS

Several options exist in SAS for writing Excel files. The simplest may be through using the libname statement, but this appears to be platform dependent and we've had trouble using it. We've had more success with proc export, shown below.

data help;
set "c:\book\help.sas7bdat";
run;

proc export data = help outfile = "c:\book\newhelp.xls"
dbms=excel;
run;

proc export data = help (where=(female=1))
outfile = "c:\book\newhelp.xls" dbms = excel;
sheet="Females only";
run;

The second proc export statement adds a new sheet to the existing Excel file, with the designated name. This sheet contains only women due to the data set option where (section 1.5.1).