The top gotchas when moving to 64-bit SAS for Windows

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 CNTLOUT option). You can then easily recreate the formats "on the fly" by using PROC FORMAT and the CNTLIN 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

39 Comments

  1. Bob
    Posted May 1, 2012 at 4:47 pm | Permalink

    Maybe I'm a bit thick, but surely proc export has nothing to do with importing?

    • Chris Hemedinger Chris Hemedinger
      Posted May 1, 2012 at 4:56 pm | Permalink

      You're right! Sorry about that, that was a copy/paste error in my first example. I've replaced it with a PROC IMPORT snippet.

  2. Andy
    Posted March 20, 2013 at 4:45 pm | Permalink

    What do you mean by "For LIBNAME access, try LIBNAME PCFILES."? Please forgive the silly question as I am a novice. Thanks

    • Chris Hemedinger Chris Hemedinger
      Posted March 21, 2013 at 8:49 am | Permalink

      Check the LIBNAME PCFILES syntax. This allows you to use SAS/ACCESS to PC Files, with a PC Files Server, to access Excel files from a 64-bit SAS or SAS on UNIX.

  3. Ronei Frota
    Posted August 13, 2013 at 10:08 am | Permalink

    Hi! I have a basic problem.
    The SAS PC FILE SERVER don't open in windows 7 64bit. This software does not execute.

  4. Haikuo
    Posted October 31, 2013 at 4:41 pm | Permalink

    Chris, This maybe a silly question, do I need to have MS office installed to use PCFILES engine? The reason I am asking is that we have a SAS server that is running on a Windows 2008 64x server box that does not have MS office.

    • Chris Hemedinger Chris Hemedinger
      Posted November 1, 2013 at 7:48 am | Permalink

      Haikuo, no, you don't need to have MS Office installed. The process uses the ACE drivers that come with the operating system; the SAS installation process will ensure they are present as a prerequisite.

  5. Pablo
    Posted December 9, 2013 at 1:51 pm | Permalink

    Sr.
    Thanks for helping in this issue. I have installed a 64 bit version of SAS with office 32 bits.
    I would like to understand which is the solution to use the import data option.
    Can I use it or must I write a small program to invoke the excel file ?
    Thanks
    Pablo

    • Chris Hemedinger Chris Hemedinger
      Posted December 9, 2013 at 1:55 pm | Permalink

      Pablo,

      You have two options. One is to use PC Files Server and DBMS=EXCELCS, which will use a 32-bit process to read in your Excel file.

      Alternatively, if your file is an XLSX file, you might be able to use PROC IMPORT DBMS=XLSX. This requires SAS 9.3m1 or later.

      • Pablo
        Posted December 9, 2013 at 5:05 pm | Permalink

        Thank Chris for the answer
        I will refine my question: can I use the import wizard (File/Import data) or not anymore?

        Thanks in advance for your cooperation. Best regards.
        Pablo

        • Chris Hemedinger Chris Hemedinger
          Posted December 10, 2013 at 8:22 pm | Permalink

          Pablo,

          Are you talking about the Import Wizard in SAS (not SAS Enterprise Guide)? You can still use it with the PC Files Server, as described in this note.

          With SAS Enterprise Guide, you can use File->Import Data regardless of "bitness" -- it just works.

          • Pablo
            Posted December 11, 2013 at 6:19 am | Permalink

            Thanks again Chris for your kind reply
            I am sorry for bothering with this simple questions

            Yes I was talking about the Import Wizard in SAS
            I did check the PC Files Server but it only refers to Sas 9.3 It did apply for SAS 9.4 too ?
            If so I will follow the instruction and download the PC Files Server
            Best

            Pablo

          • Chris Hemedinger Chris Hemedinger
            Posted December 11, 2013 at 9:34 am | Permalink

            Pablo - yes, the same should work with 9.4. Good luck!

          • Pablo
            Posted December 12, 2013 at 12:26 pm | Permalink

            Dear Chris
            I am sorry for keep bothering you
            Now I do get a new error message
            The system fail to connect to the server
            Which should be the server name: the port is 9621
            Thanks once more
            Pablo

          • Chris Hemedinger Chris Hemedinger
            Posted December 12, 2013 at 4:55 pm | Permalink

            Pablo, I think you want to use the Autostart feature of the PC Files Server, which allows you to omit the server/host and port name on the syntax.

  6. Kat
    Posted December 20, 2013 at 11:52 am | Permalink

    Thank you for all this good advice! Once I have *finally* imported a 32 bit Access Office Table into 64 bit SAS, how can I export again? I used the PC Files Server, but have been having trouble with export syntax since I'm not on a server, just my local drive using it to translate between bit-ness. Any advice?

    • Chris Hemedinger Chris Hemedinger
      Posted December 20, 2013 at 1:12 pm | Permalink

      I have a survey of several methods for exporting on this blog post. If you have SAS 9.3M1 or later you can use DBMS=XLSX and skip the PC File Server. Or use PROC EXPORT DBMS=EXCELCS to engage the PC Files Server as you did with the import.

  7. Posted February 12, 2014 at 11:06 am | Permalink

    Having just switched to a 64-bit environment, I came across the #1 Gotcha. A quick search came across this helpful blog post. Thanks to the info you provided, I was up and running again in just a couple of minute!

    For the record, I was using SAS 9.4 1M0 and was importing a MS Access database.

    • Chris Hemedinger Chris Hemedinger
      Posted February 12, 2014 at 11:07 am | Permalink

      Jared - great to hear!

  8. CD
    Posted February 13, 2014 at 6:34 pm | Permalink

    Thank you Chris. Very informative.
    But I have to point out that the solutions are far from optimal. The alternative DBMS= options do not support the MIXED or (more importantly) the GETNAMES options, without which reading in big datafiles is a pain. Plus, every time I want to take my programs from one computer to another, I'd have to change them rather extensively. That's REALLY crappy. Trust SAS to not have a better solution to this.

    • Chris Hemedinger Chris Hemedinger
      Posted February 14, 2014 at 4:12 pm | Permalink

      CD, If you can use the DBMS=XLSX option (good for .xlsx files, but not xls), you have some more choices. GETNAMES is supported and you don't need the PC Files Server.

      Another option, which several customers have used, is to install a 32-bit version of SAS for Windows. This avoids the bit architecture issue altogether. You might have an instance of 32-bit SAS on one machine used for moving data in-and-out of Excel with no code changes, and have another instance of 64-bit SAS in the event that you need the extra addressable memory. With SAS 9.4m1, the 32-bit version of SAS continues to be available and supported. If the compatibility need is paramount, I recommend the 32-bit version.

      In SAS 9.4m1, you can also begin to play with ODS EXCEL (output only), which creates native XLSX files from Base SAS. It works similar to the ExcelXP tagset.

  9. Tony
    Posted March 28, 2014 at 1:35 pm | Permalink

    Hi
    have an issue migrating a 32 bit SAS application over to Windows 64bit. Application uses a customised sasuser.profile which includes several WSAVE elements. In previous migrations \ transfers within 32 bit windows I'm used to the fact that a CPORT \ CIMPORT wont transfer the WSAVE's across and have to use a PROC CATALOG copy specifying ET=WSAVE to move these over. However, now with the OS upgrade I cant find a way of moving these elements of the sasuser.profile catalog. I've successfully transferred all other catalogs using CPORT\CIMPORT. I've also used CEDA with Connect hookup between a 32 bit machine running SAS 9.1 and 64 bit machine running 9.3 - attempted with PROC MIGRATE and PROC CATALOG within this - all other elements within the sasuser.profile will migrate across into 64 bit SAS 9.3 but still no joy with the WSAVE's. In fact when looking at the remote library assigned in 9.3 at the 9.1 library within the profile catalog the WSAVE's dont appear (though everything else is still there). Is there some method i am missing to transfer these or is it simply not possible (have made multiple searches on this but can find no information about such a limitation).
    Thanks for any guidance you can give here.

    • Chris Hemedinger Chris Hemedinger
      Posted March 31, 2014 at 7:23 am | Permalink

      Tony,

      I recommend you work with SAS Tech Support on this one. I don't know what the portability of WSAVE entries are with the different bit architecture.

  10. Pat
    Posted April 28, 2014 at 6:42 pm | Permalink

    I need to import (and eventually export) data from (to) 32-bit MS Excel that contains up to 360 numeric variables. It appears that the default limit is 255 or so. Can this be changed in PROC IMPORT (or otherwise)? Thanks!

    Additional information is that I'm running 64-bit SAS 9.4 on Windows 7.

    • Chris Hemedinger Chris Hemedinger
      Posted April 28, 2014 at 9:30 pm | Permalink

      Yes, you should be able to use DBMS=XLSX to read and write the Excel file with a wide number of columns.

  11. Suhel
    Posted May 15, 2014 at 2:20 am | Permalink

    Hi,

    When I try to use %include to include a remote directory, like this:

    %let progpath = "/remote1/test/code/";
    %include "&progpath.file.sas";

    I get the following error:
    %include "&progpath.file.sas";

    ERROR 180-322: Statement is not valid or it is used out of proper order.
    12 +/************************************************************************/

    ---
    180

    Do you know why this is happening in 64 bit?

    • Chris Hemedinger Chris Hemedinger
      Posted May 16, 2014 at 8:26 am | Permalink

      Suhel,

      This has nothing to do with 64-bit SAS. The problem is that you have quotes around the progpath value. Try:

      %let progpath = /remote1/test/code/;
      %include "&progpath.file.sas";
      

      When you assign a value to the macro variable, the quotes are included as part of the value. Since you then quote it in your %INCLUDE statement, you have a doubling of the quotes, which results in the syntax error.

  12. Martin
    Posted July 7, 2014 at 4:01 pm | Permalink

    Hi Chris,

    Thanks for the info. Regarding using libname access, how would I specify the PC File Server engine when creating a library pointing to an excel spreadsheet in metadata - SAS MC? The PC File Server option is not avaialbe in the new library wizard as a Resource Template.

    Any thoughts?

    Thanks,

    Martin

    • Chris Hemedinger Chris Hemedinger
      Posted July 7, 2014 at 4:12 pm | Permalink

      Martin, there isn't a "PCFILES" template in SAS Management Console. You can accomplish this by using the Generic Library template though. Specify "PCFILES" as the engine, add the Excel file as a new Path, and then specify the SERVER/PORT values in the Options field (and any other options you would normally put on the LIBNAME statement.

      I recommend that you don't mark the library as Pre-assigned though, so that the library isn't assigned automatically when you connect. With Excel files, one user accessing the library can have the effect of locking out others -- since an Excel file requires exclusive access to read the contents into SAS.

  13. Rhonda Crate
    Posted August 6, 2014 at 2:45 pm | Permalink

    Is there ever an instance where the excel 32-bit and SAS 64-bit error for libname would not occur? I'm asking because my co-workers ran into this problem, but it processed fine on my computer and I had the same exact file and version of SAS that they do.

    • Chris Hemedinger Chris Hemedinger
      Posted August 6, 2014 at 2:49 pm | Permalink

      Yes, a few ways:

      - If you're using DBMS=XLSX, then the Microsoft data libraries aren't used at all, so no conflict.
      - If you use DBMS=EXCELCS, then the PC Files Server kicks in and processes it for you.
      - If you have the 64-bit version of the ACE libraries, which SAS does distribute and might have been installed behind the scenes. Then DBMS=EXCEL could work. However, this does have the potential to create compatibility issues with Microsoft Office maintenance.

  14. Collins
    Posted August 18, 2014 at 9:22 am | Permalink

    Hi,
    Please I have a portable SAS 9.1.3 running on 32bit computer, how can I convert it to run on 64bit.
    Please anybody help me.

    • Chris Hemedinger Chris Hemedinger
      Posted August 18, 2014 at 9:25 am | Permalink

      SAS 9.1.3 can run on a 64-bit Windows machine in 32-bit mode. However, you will need to check the system requirements to ensure that your target Windows system can be used to install your version of SAS.

  15. Ray Shell
    Posted August 22, 2014 at 12:58 pm | Permalink

    There are staff in my office that use the EG Import Data Task multiple times as part of a very large EG project. With the upgrade to EG 6.1 (64-bit) in conjunction with the upgrade to SAS 9.4, these tasks fail for exactly the reasons above. Is the only way of retrofitting that project to 64-bitness is to convert those Import Data Tasks to written Proc Import code? Is there any other way to salvage all of the choices made in those tasks (i.e specifying particular variables to be character rather than numeric)?

    • Chris Hemedinger Chris Hemedinger
      Posted August 25, 2014 at 8:45 am | Permalink

      Ray,

      If importing Excel, then I would expect your import tasks to continue working. However, if importing MS Access data, then the EG bitness must match Microsoft Office. EG 6.1m1 offers a 32-bit version, and if you can take that option -- that's the best chance of reusing the work you've already captured. There's really no downside to using the 32-bit version of EG 6.1 -- it will work fine with the 64-bit version of SAS.

  16. Michael A. Raithel
    Posted September 4, 2014 at 2:07 pm | Permalink

    Chris,

    We are currently running 32-bit SAS 9.3 on several hundred Windows workstations and plan to move to 64-bit SAS 9.4 early next year. We have thousands of SAS 9.3 catalogs--mostly format catalogs--across hundreds of project directories.
    My question is: How can our staff programmatically tell the difference between a 32-bit and a 64-bit SAS catalog? We don't want them to find out the hard way when a program errors-out. And, it is not likely that so many staff members will be able to systematically coordinate the change of all of the existing catalogs from 32-to-64-bits at one time.
    So, what can they use to tell the difference between the 32 and 64-bit catalogs?

    Thank you.

    ----MMMMIIIIKKKKEEEE
    (aka Michael A. Raithel)

    • Chris Hemedinger Chris Hemedinger
      Posted September 4, 2014 at 4:51 pm | Permalink

      Mike, that's a large-scale transition -- kudos to you for planning for it! (You know, instead of just pulling the trigger and then seeing what breaks...)

      There is probably a cleaner way to do this, but you could "inventory" the catalog files by reading the header out of the SAS7BCAT file. I've cobbled together a little test program that works for WIN32, WIN64, and Linux catalog files. These assume all catalog files are V9, at least.

      filename test "c:\projectpath\formats.sas7bcat";
      data enc (keep=encoding); 
       length encoding $ 20;
       infile test truncover scanover; 
       input @'9.0' enc $14.; 
       encoding = '9.0' || enc; 
      run; 
      

      Result is something like: "9.0202M0Linux" or "9.0401B0X64_7PRO" or "9.0401B0W32_7PRO". A top SAS programmer like yourself can probably scale that program to work across your entire inventory of catalogs.

      • Michael A. Raithel
        Posted September 5, 2014 at 1:47 pm | Permalink

        Chris,

        Oh hey; thank you for the program; it will definitely help!
        I had already created such a program for SAS for Windows--doing the same type of thing; so we can identify Windows 32 and 64 bit catalog files. But, I was hoping for a better way to do this such as an indicator variable on the catalog's CONTENTS listing or some such facility. Not complaining; but that would have been nice.
        And, we have been baffled on how to do this for Linux.
        Thanks again; I can't wait to take your code for a test drive.

        ----MMMMIIIIKKKKEEEE
        (aka Michael A. Raithel)

2 Trackbacks

  1. [...] 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 [...]

  2. […] The top gotchas when moving to 64-bit SAS for Windows […]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>