Seeking information on compatibility of 64-bit Excel and 32-bit PC SAS

[Sas_Act_114]

Currently at my job, I use PC-SAS 9.4, 32-bit and Microsoft Excel 2010, 32-bit. The company is considering moving to Microsoft 365, which is a 64-bit version. This could be problematic, as in my job, importing and exporting between Excel and SAS is done a lot, with bringing in information from multiple tabs in excel files to manipulate data from another source (database), to then export to multiple workbooks.

I am, however, extremely unfamiliar with the whole 32-bit and 64-bit concept and am looking for information on compatibility issues that could occur between 32-bit SAS and 64-bit Microsoft Office/Excel. In particular, how would it affect using the PROC IMPORT and PROC EXPORT statements between the two, given the excel files are .xlsx and .xlsm (workbook and macro-enabled) formats. Any information in appreciated, thank you.

[ChrisNZ]

If you are just considering reading and writing files (no ODBC or OLEDB or DDE, no crazy VBA calls using external DLLs), I don't see how 32.bit or 64-bit should have any influence at all.

xlsx and xlsm files are the same whether created by 32-bit or 64-bit Excel.

See here for the kind of differences (in features, not in format) to expect: https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2010/ee691831(v=office.14...

[Tom · Accepted solution]

If you cannot get rid of the XLSM files then you will probably need to install PC Files server application from SAS.

For XLSX files you should be able to read those without an issue.  You can read those on Unix if you want.

[ChrisNZ]

@Tom Why PC Files Server? This all takes place under Windows no?

[Tom]

---

@ChrisNZ wrote:

@Tom Why PC Files Server? This all takes place under Windows no?

---

I assume it is because of way Windows handles 32bit and 64bit apps.  SAS's solution is to have your SAS session connect to another application that can connect to Excel.

[SASKiwi]

@ChrisNZ  - 32-bit SAS won't work with 64-bit Excel. Both need to be the same bitness unless you use the SAS PC Files Server to deal with the difference.

[ChrisNZ]

@Tom @SASKiwi 

I see. Indeed I found this https://support.sas.com/downloads/package.htm?pid=2167

I am confused now. I must be missing a piece of the puzzle.

SAS does not connect to Excel when it opens files using  libnames, proc import/export, or ods. Does it?

  It's just accessing the file on disk. Like it does for an MDB file or a CSV file.

And the file xlsx file format is exactly the same in 32-bit and 64-bit, isn't it?

   (except if you embed binaries like activex or use crazy VBA see here)

What am I missing?

[Tom]

How SAS accesses Excel files depends on what ENGINE you tell it to use. I think that it is only the XLSX engine that just reads and/or writes the file. That is why it can work on Unix.

[ChrisNZ]

@Tom  It seems that SAS uses Microsoft Access Connectivity Engine (ACE) to access these files.

They probably wanted to avoid dealing with the jungle of the undocumented MS Office file formats.

Excerpt:

If you are working with Microsoft Excel data, you can use one of the two SAS DBMS identifiers that have no dependency on the Microsoft ACE components.

You can use the DBMS=XLSX identifier with the IMPORT or EXPORT procedures and a LIBNAME statement (depending on your SAS release). Or, you can use the DBMS=XLS identifier with PROC IMPORT and PROC EXPORT.

No mention of ODS.

@Sas_Act_114 The same page also addresses the issue of 32-Bit SAS is installed with 64-Bit Microsoft Office

[Sas_Act_114]

So I looked up information on the PC Files Server, however I think this would apply if I had 64-bit SAS and was using Microsoft 32-bit. This is what I found from the SAS support website:

SAS PC Files Server enables:

• 64-bit SAS on Windows to access PC files when 32-bit Microsoft Office® is present.
• SAS on Linux or UNIX to access PC files stored on a Windows server.

However my situation is 32-bit SAS and 64-bit Microsoft Office, which I don't know if this will resolve. The marco enabled files (XLSM) can't be gotten rid of, they are a part of our process.

[Tom]

Ask SAS support, or just try it.

[Sas_Act_114]

So I did do just that and just got off the phone with them. You were correct Tom in that using the PC File Server would be the way to work with the two systems of different bit versions.

He made an important point too though, that with Microsoft 365, the program is automatically updated (since the actually software is in a cloud) and when that happens, there can be issues between it and SAS, to where the SAS development team needs to correct it, causing downtime. The recommendation was to keep the same bit version if possible, which is what I will push for.