Excel

8月 042015
 

“Spreadsheets are familiar tools, which are relatively simple to use. However, the downside is that they result in fragmented thinking.” -- Ken Franklin, Performance Measurement Program Manager, Highway Division of Oregon Department of Transportation (ODOT) JMP customers use an array of tools and processes for exploratory data analysis to make […]

The post Beyond spreadsheets: Ken Franklin, Oregon Department of Transportation appeared first on JMP Blog.

7月 012015
 

The data you want to import into JMP often requires some manipulation before it’s ready to be analyzed in JMP. Sometimes data is arranged so that a row contains information for multiple observations. To prepare your data for analysis, you must restructure it so that each row of the JMP […]

The post How to stack data for a Oneway analysis appeared first on JMP Blog.

6月 242015
 

“I want my students focusing on statistical methods, not on software.” -- Bruce McCullough, Professor, LeBow College of Business, Drexel University Beyond Spreadsheets is a blog series that highlights how JMP customers are augmenting their tools and processes for exploratory data analysis to make breakthrough discoveries. We talk with JMP […]

The post Beyond Spreadsheets: Bruce McCullough, Drexel University appeared first on JMP Blog.

6月 092015
 

Here at JMP, we love pets. So we were thrilled to hear that a young scientist used our software to explore data about pet adoptions from local animal shelters. The project is adorably titled "Furever Friends." How young is this scientist? She is 10 years old, and her name is […]

The post Why are some dogs adopted faster than others? appeared first on JMP Blog.

5月 212015
 

When you weren't watching, SAS did it again. We smuggled Yet Another Excel Engine into a SAS release.

SAS 9.2 Maintenance 2 added the XLSX engine, which allows you to read and write Microsoft Excel files as if they were data sets in a library. The big advantage of using this engine is that it accesses the XLSX file directly, and doesn't use the Microsoft data APIs as a go-between. (LIBNAME EXCEL and LIBNAME PCFILES rely on those Microsoft components.) That means that you can use this engine on Windows or Unix systems without having to worry about bitness (32-bit versus 64-bit) or setting up a separate PC Files Server process.

The XLSX engine does require a license for SAS/ACCESS to PC Files. Are you a SAS University Edition user? The SAS/ACCESS product is part of that package, so this technique works there. It's an easy way to get well-formed Excel data into your SAS process.

/* because Excel field names often have spaces */
options validvarname=any;
 
libname xl XLSX '/folders/myfolders/sas_tech_talks_15.xlsx';
 
/* discover member (DATA) names */
proc datasets lib=xl; quit;
 
libname xl CLEAR;

Example output:

xl_contents
Once the library is assigned, I can read the contents of a spreadsheet into a new SAS data set:

/* because Excel field names often have spaces */
options validvarname=any;
 
libname xl XLSX '/folders/myfolders/sas_tech_talks_15.xlsx';
 
/* read in one of the tables */
data confirmed;
  set xl.confirmed;
run;
 
libname xl CLEAR;

And here's the result in my SAS University Edition:

xl_confirmed
Sometimes you need just one value from a spreadsheet. That's a common use case for dynamic data exchange (DDE), which isn't as feasible as it once was. You can use FIRSTOBS and OBS options to control how much data you retain:

/* read in just one value */
data _null_;
  set xl.confirmed (firstobs=6 obs=6 keep='Job title'n);
  call symput('VALUE','Job Title'n);
run;
%put &value;

Output:

 76         %put &value;
 Testing Manager,  Quality-driven User Experience Testing

You can also use the XLSX engine to create and update XLSX files.

libname xlout XLSX '/folders/myfolders/samples.xlsx';
 
data xlout.cars;
  set sashelp.cars;
run;
 
data xlout.classfit;
  set sashelp.classfit;
run;
 
data xlout.baseball;
  set sashelp.baseball;
run;
 
data xlout.air;
  set sashelp.air;
run;
 
libname xlout clear;

Here is my output in Microsoft Excel with all of these data sets now as sheets:

xl_xlsxout
Remember, you can also create Microsoft Excel files with Base SAS by using ODS EXCEL -- experimental in 9.4 Maintenance 2 but production in Maintenance 3 (coming soon).

The XLSX libname is different from the EXCEL and PCFILES engines in other ways. For example, the XLSX engine does not support Excel named ranges (which can surface a portion of a spreadsheet as a discrete table). Also, you won't see the familiar "$" decoration around the spreadsheet names when they are surfaced in the library within SAS. If you need that sort of flexibility, you can use PROC IMPORT to provide more control over exactly what Excel content is brought into SAS and how.

One other IMPORTANT caution: The XLSX engine is a sequential access engine in that it processes data one record after the other. The engine starts at the beginning of the file and continues in sequence to the end of the file. Some techniques to MODIFY the data in-place will not work. Also, some SAS data viewers cannot render the data from the XLSX engine. SAS VIEWTABLE and SAS Enterprise Guide and even SAS Studio can't open these tables directly in the data grid view. VIEWTABLE gives you a nice message, but SAS Enterprise Guide simply "hangs" in the attempt. For that reason, I recommend using DATA step to copy the Excel content that you want to another SAS library, then CLEAR the XLSX library to avoid accidentally opening a table in a viewer that won't support it. (This is currently a bug in SAS Enterprise Guide that should be fixed in a future release.)

I have found LIBNAME XLSX to be a quick, convenient method to bring in Excel data on any SAS platform. If you have SAS 9.4 Maintenance 2 or later, try it out! Let me know how it works for you by sharing a comment here.

tags: excel, ODS EXCEL, SAS 9.4, SAS University Edition, xlsx

The post Using LIBNAME XLSX to read and write Excel files appeared first on The SAS Dummy.

10月 142014
 

Hello, 1992 called. They want their DDE Excel automation back.

DDE broken?Perhaps the title of this article is too pessimistic. Of course your SAS programs that use DDE (dynamic data exchange) can still work perfectly, as long as you situate your SAS software and its DDE "partner" (usually Microsoft Excel) to run on the same Windows PC. DDE is still documented and supported in SAS 9.4.

But for the growing number of users who modernize to a centralized SAS environment, the legacy of DDE is a big challenge to bring forward. Your new environment might include the benefits of SAS Grid Computing, SAS Visual Analytics, stored processes, and more manageable security. You might run your SAS programs using SAS Enterprise Guide or even a web browser. But with all of that shiny new tech and its distributed architecture, the simple local arrangement that allows DDE to function...well, it falls apart.

Let's look at how DDE works. This data exchange relies on two consenting Windows processes, both running on a single machine, to communicate with each other using special Windows messages. In SAS programs, this usually takes the form of SAS spawning Microsoft Excel, sending a command to reference a particular cell or range of cells in a sheet, and poking in some values that were computed in SAS. If your SAS session is now running on a remote machine (often a non-Windows machine), then SAS cannot use DDE to talk to your local Microsoft Excel application. As the man says, "what we've got here...is failure to communicate."

For those customers who have a lot invested in DDE (some have hundreds of programs!) and no time/budget to adjust processes away from it, I usually recommend a simple stop-gap approach: maintain a designated machine with SAS for Windows to do your DDE grunt work, even as you move other processes to an enterprise SAS environment.

But going forward, I encourage customers to look at the alternatives supported by new features in SAS that are more in line with today's topology (SAS on a remote server, SAS Enterprise Guide or other local client on the PC). These features can replace many (but not all) of the legacy DDE uses that are out there.

DDE is still supported in Microsoft Excel. We cannot say for how long, as Microsoft has put more emphasis on VBA, .NET, or PowerShell for Excel automation instead. But since DDE works only between two Windows processes (SAS and Excel in this case) on the same machine, it's an outmoded approach that's incompatible with many IT setups.

Some DDE alternatives

The SAS Add-In for Microsoft Office offers the most flexible method for complete control over the format and structure of your spreadsheet. It turns the problem on its head: instead of relying on a batch SAS program to push content into Excel, you simply use Excel to pull your SAS content into the spreadsheet, exactly where and how you want it. You can use SAS stored processes to encapsulate whatever SAS operation you need.

Within the SAS Add-In, you can also use specific cells and ranges as input into stored processes; it's just as flexible to inject your Excel content into SAS.

You can use Office scripting to automate the process (like a batch job), so the process can happen unattended. The consumers of your Excel documents do not need the SAS Add-In for Microsoft Office in order to view the results.

I know that not everyone is lucky enough to have the SAS Add-In. For batch SAS users, ODS tagsets.ExcelXP -- or its SAS 9.4 descendant ODS EXCEL -- can be used to place formatted report content into an Excel spreadsheet. Here's an example use for ODS EXCEL, which works as a "one-way" push from SAS into Excel.

Also, PROC EXPORT now supports adding sheets to existing files, or replacing entire sheets in place. That's flexibility that didn't exist before, when some users turned to DDE to fill the gap.

Aside from these SAS-centric approaches, creative programmer types can use script (VBS, PowerShell, and other) to plug their SAS data results into a spreadsheet as a post-process (which many customers spawn within their SAS programs).

Thinking of DDE as technical debt

Weaning yourself off of DDE is easier said than done, I'll admit. Twenty years ago DDE was a boon for SAS developers who needed to deliver Excel content to picky constituents. I played my part: I wrote the SAS Companion for Microsoft Windows (Release 6.11!) and crafted some of the examples that still exist in the documentation.

However, I now consider DDE programs to be a form of "technical debt" that organizations will have to pay off sooner or later.

If your "DDE bill" hasn't come due yet, you're fortunate. But if you're planning to write another SAS program that relies on DDE, consider the future generations. Today's kids are writing SAS programs in iPads and web browsers, and DDE is about as hip as a rotary-dial phone. (Yes, it still works, but it does make your fingers tired.)

tags: DDE, dynamic data exchange, excel, SAS Add-In for Microsoft Office, SAS Enterprise Guide
9月 252014
 

As you can tell from my recent posts (see here and here), I've been working with SAS and Microsoft Excel files quite a bit. I'm really enjoying the ability to import an XLSX file in my 64-bit SAS for Windows without any additional setup.

After one long afternoon of back-and-forth between Excel and SAS, I ran into this alarming error message:

ERROR: Error opening XLSX file -> C:ProjectsMyData.xlsx .  It is either not 
an Excel spreadsheet or it is damaged.   Error code=80001019
Requested Input File Is Invalid
ERROR: Import unsuccessful.  See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

It's a scary message, and I panicked -- but just briefly. I soon remembered that Microsoft Excel likes to place an exclusive file lock on spreadsheet, so if you have it open in Excel, nothing else will be able to read it. My file wasn't "damaged" -- it was simply in use.

I could wish for a better error message like: "Excuse me sir, but it appears that file is already opened by another application. Would you mind closing it so that we can read the data? I mean, whenever it's convenient for you."

Once I closed Microsoft Excel and reran my SAS program, all was good. Whew!

tags: excel, proc import, xlsx
9月 222014
 

I recently wrote about my foray into the experimental world of ODS EXCEL. Using this new destination in SAS 9.4, I was able to create multisheet workbooks -- containing reports and graphs -- in the XLSX format. I outlined some of the benefits -- such as being part of Base SAS and its ability to create native XLSX files. But I also pointed out some of the shortcomings that I experienced, such as slow performance with large amounts of data.

I'm working on a consulting project in which I needed to provide my client with an Excel-based report. This report requires an easy-to-read summary sheet, such as might be created with PROC MEANS and PROC TABULATE. That's a perfect job for ODS EXCEL. But I also needed to include subsequent sheets that contained different versions of detailed data -- tables with lots of columns and many thousands of rows. In my experience, this can bog down ODS EXCEL; I find that PROC EXPORT with DBMS=XLSX performs much better. So...I decided to see if I could combine the two approaches to create a single file.

The process worked perfectly for my purposes. It relies on the following behaviors/assumptions:

  • ODS EXCEL creates a new XLSX file. That's okay, because each time I run my process I want to replace the XLSX that I had before. If I wanted to retain previous versions, I could tack a date suffix onto the target file name.
  • PROC EXPORT with DBMS=XLSX can add/update a sheet within an existing XLSX file. That's a relatively recent feature, added in SAS 9.4 (and perhaps worked in SAS 9.3M2). This means that I can create the file with ODS EXCEL, then update the same file using PROC EXPORT, all within a single SAS program. (Remember, PROC EXPORT with DBMS=XLSX requires SAS/ACCESS to PC Files.)

I would like to show an example of the output, but I'm sure that my customer wouldn't approve of me featuring their data details on my blog. So instead I'll risk alienating a different group of acquaintances: my Facebook friends.

Years ago I built an app that turns your Facebook friend data into a SAS program. Even though eons have passed (in social media time), the app still works. (You can try it yourself: it runs as a custom task in SAS Enterprise Guide or as a standalone program on your Windows PC.) I ran it just now to gather my latest Facebook friend data and create SAS data sets with my friend details. Then I used ODS EXCEL to create a summary sheet, and PROC EXPORT to create several detailed sheets. Here's an example of the summary:
fbsummary

And then one of the detailed sheets, produced by PROC EXPORT of one of the detailed data sets my Facebook program created:
fbdetail

Here's the SAS program that produces this output. First, I run the program that is generated by my Facebook app. Then I follow up with this program that generates the Microsoft Excel spreadsheet file.

/* Create a new FB summary and report workbook for this snapshot 
   of Facebook data                                             */
filename fbout "c:tempFBReport_&SYSDATE..xlsx";
 
/* A little ODS style trick to make headings in my sheet */
ods escapechar='~';
%let bold_style=~S={font_size=12pt font_weight=bold}~;
 
/* CREATES a new XLSX file */
ods excel (id=fb) file=fbout
  /* choose a style you like */
  style=pearl
  /* SHEET_INTERVAL of NONE means that each PROC won't generate a 
     new sheet automatically                                     */
  options (sheet_interval="none" sheet_name="Summary")
  ;
 
ods noproctitle;
ods text="&SYSDATE. Friend Report for &myFacebookName";
proc sql;
     select count(distinct(UserId)) as 
         Number_Of_Friends into: NumberOfFriends
	 from friends;
quit;
 
ods text="&bold_style.Count of friends by gender";
proc freq data=frienddetails
	order=internal;
	tables gender / 
	nocum   
	scores=table;
run;
 
ods text="&bold_style.Calculated Ages based on Graduation years";
proc means data=ages
	min max mean median p99;
	var age;
	class how;
run;
 
ods graphics on / width=800 height=300;
ods text="&bold_style.Count of friends by Relationship Status";
proc freq data=frienddetails
	order=internal;
	tables relationshipstatus / 
	nocum   
	scores=table plots(only)=freqplot;
run;
 
ods excel (id=fb) close;
 
/* ADDS new SHEETS to the existing XLSX file */
proc export data=frienddetails
  dbms=xlsx
  outfile=fbout replace;
  sheet="Friend Details";
run;
 
proc export data=schoolfriends
  dbms=xlsx
  outfile=fbout replace;
  sheet="Schools";
run;
 
proc export data=statusprep(keep=name date message)
  dbms=xlsx
  outfile=fbout replace;
  sheet="Latest Status";
run;

I could achieve a similar result using other methods, of course. But I like to take advantage of my consulting opportunities to explore new features in SAS software. I find that if I can learn a new feature "on the job", I can produce a good result for my customer while also adding to my bag of SAS tricks.

Related articles

Experimenting with ODS EXCEL to create spreadsheets from SAS
How do I export from SAS to Excel files: Let me count the ways
Running PROCs on Your Facebook Friends (2011 version)

tags: excel, facebook, ODS EXCEL, PROC EXPORT, xlsx