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?
108 Comments
Maybe I'm a bit thick, but surely proc export has nothing to do with importing?
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.
Pingback: Query the Windows registry within your SAS program - The SAS Dummy
What do you mean by "For LIBNAME access, try LIBNAME PCFILES."? Please forgive the silly question as I am a novice. Thanks
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.
Hi! I have a basic problem.
The SAS PC FILE SERVER don't open in windows 7 64bit. This software does not execute.
I recommend that you contact SAS Technical Support. Also review this SAS Note 43802.
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.
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.
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
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.
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
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.
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
Pablo - yes, the same should work with 9.4. Good luck!
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
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.
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?
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.
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.
Jared - great to hear!
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.
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.
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.
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.
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.
Yes, you should be able to use DBMS=XLSX to read and write the Excel file with a wide number of columns.
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?
Suhel,
This has nothing to do with 64-bit SAS. The problem is that you have quotes around the progpath value. Try:
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.
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
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.
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.
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.
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.
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.
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)?
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.
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)
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.
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.
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)
Pingback: Transitioning to 64-bit SAS on Windows—some resources - SAS Users
If I install an office 64 bit would I be able to use the excel libref ? for example
Libname myxl excel "C:\Users\Desktop\test.xlsx" ;
The reason I am asking is to update my office to 64 bit and would be able to import data from multiple excel tabs.
Thanks
-H
Yes, that's true. But you should be able to do this with your 32-bit Excel today:
If you have the PC Files Server component installed.
Thank you Chris.
-H
Hey Chris I ran the Install Checker.exe in C:\Program Files\SAS 93\SAS Add-in\x86\SASAddinforMicrosoftOffice\5.1 and the report said I have 64 bit operating system but I'm not running as a 64 bit process? I have Office 64bit installed, EG 64 bit. Does this mean im not really utilizing 64bit?
OS Windows 7
Version 6.1.65536.7601
Service pack Service Pack 1
Windows system directory C:\Windows\system32
Running on a 64 bit version operating system True
Running as a 64 bit process False
Hi Jim,
Actually, if you ran InstallChecker.exe, then the process being reported on is...InstallChecker.exe. It still yields good information for SAS Tech Support diagnostics, but for an accurate in-process picture you should launch this from Help->About->Configuration Details within the SAS application you're checking (EG or SAS Add-In). Make sense?
I've tried both dbms=excelcs and dbms=xlsx but now I get
'Couldn't find range in spreadsheet
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.'
The range (sheet) is correct, so something else is wrong. Any ideas?
Chelly,
It's difficult to guess what's going on here; we'll need more details. Consider posting your question -- with sample data and details of your SAS version -- to one of our support communities. Or, if your data are too proprietary or you think the error might be very dependent on your particular configuration, open a track with SAS Technical Support.
Hey Chris,
Having an issue here where SAS support site not giving much detail how to fix. I am running windows 7 with 64bit EG and 64bit Office. I do not have local base installed and I connect to a Unix server running 9.3. I checked out setinit to verify we had SAS/ACCESS to PC files and we do. But when I try and get data out of access database using the following code:
PROC IMPORT OUT=work.test
DATATABLE = BOB
DBMS=ACCESSCS REPLACE;
DATABASE="C:\Users\ub59360\Desktop\TEST.accdb";
server="itg-chdc-rdatav";
port=9621;
RUN;
I get this error:
ERROR: Unable to open file C:\Users\ub59360\Desktop\TEST.accdb. It does not exist or it is already opened exclusively by another user, or you need permission to view its data.
So what am I missing here?
System birthday: 07NOV2014.
Operating System: LIN X64 .
Product expiration dates:
---Base SAS Software 30JUL2015
---SAS/STAT 30JUL2015
---SAS/GRAPH 30JUL2015
---SAS/ETS 30JUL2015
---SAS/IML 30OCT2014
---SAS/SHARE 30JUL2015
---SAS/CONNECT 30JUL2015
---SAS/SHARE*NET 30JUL2015
---SAS OLAP Server 30JUL2015
---SAS Enterprise Miner 30JUL2015
---MDDB Server common products 30JUL2015
---SAS Integration Technologies 30JUL2015
---SAS Enterprise Miner Server 30JUL2015
---SAS Enterprise Miner Client 30JUL2015
---SAS Credit Scoring 30JUL2015
---Unused OLAP Slot 30JUL2015
---SAS Enterprise Guide 30JUL2015
---SAS/ACCESS Interface to Oracle 30JUL2015
---SAS/ACCESS Interface to PC Files 30JUL2015
Since your SAS server is on Unix, you need to reference the accdb file in terms of the Unix file system. You can't use your local C: drive. The PC Files Server component will take care of streaming the data from Unix to the PC and back again.
So follow up question, if you have base SAS and PC file server installed on same windows machine then the files (access/excel) would not need to be uploaded to unix system?
My other question is if you have the choice between using PC File server and SAS odbc ( assuming the bitness for both office and SAS are the same) then what is the better or preferred method?
If you have SAS 9.3 or 9.4, I'd first look at DBMS=XLSX, which doesn't need any additional PC Files Server. But if that doesn't work for you...
If SAS and Office are the same bitness and everything is on the same machine, DBMS=EXCEL works and no additional moving parts are needed.
If you're importing data like Excel files or MS Access databases, I always recommend the SAS/ACCESS to PC Files approach (including PC Files Server if needed). If you're updating database tables in MS Access, then you could use SAS/ACCESS to ODBC for that. ODBC works for Excel too, but I consider that a workaround and not a best practice.
Jim
i have the same environment. No sas foundation on a pc but a linux server (in 32 bits).
You must understand that you must have on you pc the individual install of pcfile server even if you don't have sas on it.
Then on your linux server you may submit code like that (here excel samples) in your at distance session.
proc import datafile="\\172.nn.21.nnn\public\classexp.xlsb" out=lecturede2010 dbms=Excelcs replace; SERVER="pc_0nnnn.inxx.fr"; port=9621; sheet='toR'n; run;Even this is too possible without excelcs.
but I haven't test with the equivalent recent access files.
Now EG is running on your server and there is perhaps another way to reach your goal as
PCfIle server is working with sas foundation
http://www.sas.com/offices/europe/uk/support/sas-hints-tips/unix_windows.html
http://www.umass.edu/statdata/software/sas/SASPCFilesServer_Install.pdf
is working with a linux 9.3.2 32 bits server
but you have to download the 64 bit version of sas pcfile server to try:
http://support.sas.com/downloads/package.htm?pid=893
HTH
Andre
I was getting the same error after installing Office 2013. Everything pointed me to this article, some links took a step or two but all led here. My SAS, MS Office, and Windows OS are all 32 bit so I was very frustrated that this was the only article about the error. Eventually I got sick of begging for help whenever I got the error and called SAS. They fixed the problem by getting me to update my MS access database engine here: http://www.microsoft.com/en-us/download/details.aspx?id=13255
Apparently, the folks that installed office on my machine did not update the engine so I got all kinds of DDE and Class not registered errors.
Thanks for sharing your experience -- install errors (or incomplete installs) can be a confounding problem! Hope your experience here helps others.
The code below produces an error when run in SAS 64 bit. The blog seems to focus on Excel but I am trying to export directly to an Access table:
PROC EXPORT DATA=sasview
OUTTABLE="tblSasview"
DBMS=ACCESS2003 REPLACE;
DATABASE="M:\LS\LSDatabase\SASView\SASView.mdb";
RUN;
I think you might need to use DBMS=ACCESSCS instead, and see if that works. That also uses the PC Files Server, so that component would need to be present/installed. You might already have it in place.
Thanks for the warnings. Especially since the Excel error you showed wouldn't have helped me understand what went wrong.
Question: if a format catalog won't work for a different created in a different "bit-iness" of SAS won't work, what about stored functions? If I create a function with PROC FCMP in 32-bit SAS and save it to a dataset, can a 64-bit version then use the CMPLIB system option to access those functions?
I assume so, since each row in the dataset seems to be just a piece of info or user-written line for a function, but you know what they say about assumption.
I believe that FCMP also uses catalogs, and those are specific to 32-bit and 64-bit, so those catalogs would need to be ported/regenerated.
Thanks for the post - I no longer get the red error but I get some green warnings now when using ACCESSCS for an export:
WARNING: During insert: [Microsoft][ODBC Microsoft Access Driver]Fractional truncation
I think this can happen with datetime or time values. When transferring from SAS to another database, some fractional portion of the time value can be cut off. The database might support different time precision than SAS.
Ok thanks - the data essential to the export are probably OK then? I'm assuming it needs to be checked but it's a very large file.
Jon, yes, I'd agree with that. You might try a test with limited data rows and exclude the time/datetime columns to see if it eliminates the warning. Then you'll know for certain that's the cause. Then you can spot check the time/datetime values in your actual export file.
No luck, the export to Access is not working with DBMS=ACCESSCS (still get green truncation errors). Even if the files are similar I need to be able to run the export with no errors.
I don't understand the fix for the "incompatible formats catalog" section. What does it mean to "decompose the format definitions down to data sets (by using PROC FORMAT and the CNTLOUT option)"? I use 64 bit SAS to run code originally written on 32 bit SAS with lots of data formatting so I am assuming this is part of the reason why DBMS=ACCESSCS is not working for the data export.
By this, I mean to export your format definitions (from the source system) as SAS data sets using the CTLOUT= option in PROC FORMAT. Then you can rebuild them on the target system using PROC FORMAT and the CTLIN= option.
I'm not sure that this is what is preventing your ACCESSCS= attempt from working though. You might need to work with SAS Technical Support and share details about your operating environment.
Hi Chris,
So if you have 64-bit SAS with 64-bit Excel, will the code you talk about above (pasted below) work? or will there still be trouble? Or will I be forced to update all of my DBMS= statements?
proc import out=work.class
datafile="c:\temp\class.xls"
DBMS = EXCEL;
run
Yes, it should work. No additional setup would be necessary if 64-bit SAS and 64-bit Excel (or the 64-bit ACE components from Microsoft) are on the same machine.
what is it about SDD created datasets that don't work with the old SAS Viewer? Is it the 64/32 bit difference? Is there anything we can do to the dataset inside or outside of SDD to make it work? I hate Universal viewer and love my old viewer. Thanks,
Michelle, maybe this technique using Windows PowerShell would work for you?
Thanks. The DBMS=XLSX fixed the problem for me.
Hi Chris
Hopefully you see this: I have just started using SAS EG 5.1 in a 64bit server which uses UNIX (sigh). However the BASE version of SAS have PC Files installed best I can see from the setinit.
My issue now that I am on EG is that I want to use the Excel Libname to import an entire workbook of Excel.
For example I am used to using libname myfile "c:\myfile.xlsx"; and then I will see a connected libref in SAS, and inside each worksheet appears as a dataset.
However in EG this does not appear possible, I do't want to have to use Proc Import and then write a loop etc....Is it not possible to just use the Excel Libname ?
Cheers
Colm
Colm,
For this to work you'll need to use LIBNAME PCFILES, which requires a PC Files Server instance somewhere on your network. This will allow the Unix SAS to connect to a PC Files Server to "delegate" the reading of the Excel file.
In SAS 9.4 Maint 2 or later, you can use LIBNAME XLSX. No additional PC Files Server is needed in that case.
Hi Chris, Great post! EXCELCS helps me to read in most xls file in SAS 9.4. But I recently tried to read in a tab from an xls file, which is actually with embedded macro. I got the following error message:
" ERROR: During read: [Microsoft][ODBC Excel Driver]Invalid datetime format on column number 36
(VarName)
ERROR: Import unsuccessful. See SAS Log for details."
Any solutions? Hopefully you can see it.
Best,
Liang
I know that the Microsoft driver can have trouble with certain datetime values. Can you try using DBMS=XLS (for xls files) or DBMS=XLSX? That will use native SAS code instead of the Microsoft drivers.
I have a set of project files created in SAS 9.3 using SAS/ETS 32 bit. We are migrating to SAS 9.4 64 bit and my ETS project files cannot be read by SAS 9.4 64 bit. How do you get my ETS project files created in SAS 9.3 to be read by ETS in SAS 9.4? Thanks.
When you say "ETS projects", are you referring to the Time Series Forecasting System? I suspect that much of that information is in SAS catalogs, which need to be converted using PROC CPORT or perhaps PROC MIGRATE. You might need to work with SAS Tech Support for the best practice on these types of project files.
Yes, I'm referring to the Time Series Forecasting System.
Pingback: Let me help you with that! A SAS technical resource guide - SAS Users
Thank you so much for this post- it fixed my problem immediately!
Hi Chris,
I am using SAS 9.4 , SAS EG 7.1 and Access 2013. SAS and ACCESS both are 64 bit. I tried using ACCESSCS to export SAS Dataset to ACCESS Database but got the error that "Failed to conenct to the server". Also, I cannot see ACCESS option in export wizard in SAS EG. Can you please suggest what would be the reason and how to resolve this? Thanks.
If you have 64-bit MS Access and 64-bit SAS, then you should probably try DBMS=ACCESS (not ACCESSCS), since SAS should be able to export directly without PC Files Server.
The 32-bit version of SAS Enterprise Guide can save data as MDB (the older MS Access format). But this is not available in the 64-bit version, due to the architecture differences. However, if you have 32-bit SAS Enterprise Guide and 64-bit MS Office, that might interfere with the MDB export.
Thank you Chris. But using ACCESS gives me "Connection Failed. See log for details." error.
The code I am using is
proc export data=test
dbms= ACCESS
outfile="M:\AFolder\BFolder\test1.mdb"
replace;
run;
And what might be the reason of missing of ACCESS export in the wizard.
Thanks
Oh I am sorry. I missed out this line "The 32-bit version of SAS Enterprise Guide can save data as MDB (the older MS Access format). But this is not available in the 64-bit version, due to the architecture differences." So do you mean I wouldn't be able to export data as MDB as I am using 64-bit SAS EG?
That's right. Another method you could try: if you have SAS/ACCESS to ODBC, you could try to update your MS Access table using a 64-bit MS Access ODBC driver.
Thank you so much Chris. And I am sorry as I am bothering you with my stupid questions one after the another. When you say "update your MS Access ..... " so do you mean I need to save the SAS dataset in ODBC dataset and then exporting the dataset into Access using "External Data -> ODBC Database" ?
Many Thanks !!
And Also, I am using SAS on Remote Windows System. I tried importing the data using SAS EG wizard and it works but the code doesn't. I am wondering, most of the users had been using SAS EG export wizard to export the data as a step in Project in 32-bit SAS (linux) old platform, now when we have migrated to SAS 9.4 64-bit (Windows), what can we do that they do not need to change their old code processes and redo the work?
Thank you so much !!
Thanks. Your topic got me part of the way. I was using proc import for access datatables on my 32 bit mascine at work. It didnt work on my 64 bit laptop so I started using DBMS=ACCESSCS REPLACE which fixed the problem for some databases. For another (on an encrypted drive, that I have made sure is accessed when importing) I get this:
ERROR: Unable to transcode data to/from UCS-2 encoding.
ERROR: Import unsuccessful. See SAS Log for details.
Except for the "CS" I am using the same code that worked on my 32 mascine. All I can find about this error is that it has to do with chinese. I am Danish, but I have made sure our strange letters (we have 3 of them) are not in any names.
What to do? I can find nothing on this error
Are you running SAS with UTF-8 encoding? You'll need that in order for SAS to handle character data from multiple character sets. See this SAS Note for how to get started.
I have no idea. I will see what our data manager says about that
Tried it now, didnt work. Still getting this:
ERROR: Unable to transcode data to/from UCS-2 encoding.
ERROR: Import unsuccessful. See SAS Log for details.
It is really weird. I have no trouble with this on my laptop:
PROC IMPORT OUT=WORK.APO
TABLE='SAS'
DBMS=ACCESSCS REPLACE;
DATABASE="P:\APO\2 registrering\Indtastning APO.accdb";
RUN;
But this one is trouble:
PROC IMPORT OUT=WORK.Flexi2
DATATABLE='Accuracy'
DBMS=ACCESSCS REPLACE;
DATABASE="Z:\Flexicult.accdb";
run;
The Z drive is the encrypted drive, but I have no trouble with this on my stationary.
I figured out the problem. SAS on my laptop was unable to import data containing strange Danish letters (æ,ø,å).
I am running a Proc Export and in the past I use the .xlsx format and my DBMS=EXCEL, I updated it to DBMS=EXCELCS, but when I try to open the file in excel after the export I get the following error.
Excel cannot open the file "file name.xlsx" because the file format or file extension is not valid. Verify that the file has not been corrected and that the file extension matches the format of the file.
If you can, I'd try DBMS=XLSX -- it's a simpler approach that generates a native XLSX file. Supported in later Maint releases of SAS 9.3 and in 9.4.
Pingback: Using LIBNAME XLSX to read and write Excel files - The SAS Dummy
Pingback: My favorite SAS tips and tools from 2012 - The SAS Dummy
Pingback: I'm a WUSS and proud of it - The SAS Dummy
Hi Chris,
Please can you advice how can we export CSV file to PC file server ...!!
Thanks
Hi Pradeep, PC Files Server doesn't really have a role to play here. Use PROC EXPORT DBMS=CSV or ODS CSV or simple DATA step to create a CSV file. If your challenge is to get the CSV file from your SAS server over to your PC, you might need to target a network file path that is shared by Windows, or (if using SAS Enterprise Guide) use the Copy Files task to copy the file for you.
Thanks Chris. Actually we are trying to export CSV and TXT files from SAS(Linux)/EG to Windows PC file server.
We are able to export xls but not csv and txt files. Tried DBMS=CSV, DBMS=DLM and DELIMITER... But nothing is working...
data abc;
set sashelp.class;
run;
proc export data=abc outfile="C:\Users\adm\Documents\test.csv"
dbms=CSV replace;
SERVER='xxxxxxxxxxxxxxxx.com';
serveruser='xxxxxxxxx';
serverpass='xxxxxxxxx';
PORT=9621;
run;
Getting below error messages:
31 SERVER='xxxxxxxxxxxxxx.com';
2 The SAS System 15:30 Tuesday, June 27, 2017
______
180
32 serveruser='xxxxxxxxx';
__________
180
33 serverpass='xxxxxxx';
__________
180
34 PORT=9621;
____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
Your advice will be really helpful.
Thanks
Hi Pradeep, PC Files Server is specifically designed to convert PC-data files (like Excel, MS Access) to/from SAS data sets. It's not needed (or used) for text-based files like CSV. Your program should work without the SERVER= and related statements.
Hi Chris,
I'm hoping you can help me despite how old this blog post is!
I have a 64 bit SAS, 64 bit Windows, and 64 bit Office 2016. I for some reason cannot (either with the import wizard or code) import an excel data file.
I get the error "ERROR: Failed to connect to the Server"
Here is my code:
LIBNAME PCFILES PATH='C:\Users\lsalt\OneDrive\Documents\School\HLTH 5150';
PROC IMPORT OUT= Bodyfat
DATAFILE= "C:\Users\lsalt\OneDrive\Documents\School\HLTH 5150\Large.Bodyfat.xlsx"
DBMS=EXCELCS REPLACE;
RUN;
I have also tried many other proc import code variations, once I get the code right, I get the exact same "Failed to connect to the server error".
I have double checked that all my software is the 64-bit version, so now I am unsure what I can do. I have tried various DBMS=xxx options (EXCEL, XLSX, EXCELCS).
Any help would be much appreciated!
That particular error can happen with DBMS=EXCEL or EXCELCS if SAS cannot communicate with either the Microsoft data access libraries (ACE engine) or the SAS PC Files Server (which must be installed and configured for autostart). However, I always recommend DBMS=XLSX for SAS 9.4 and later -- this does not rely on either of those external items, but instead can read/interpret XLSX files directly. If you're having problems with DBMS=XLSX, then I suspect you're getting a different error. Can you share those details or else open a SAS Tech Support case?
Hi Chris, ever seen this sort of warning (shown below) upon exporting data to Microsoft Access from the 64 bit version of SAS 9.4 running on a 64 bit Windows platform. I'm using DBMS=ACCESSCS to export to 32 bit Access files. The exact same code (and data) running with a 32 bit version of SAS 9.4 does not give me a warning.
WARNING: During insert: [Microsoft][ODBC Microsoft Access Driver]Fractional truncation
Jerry, I'm not sure why the behavior would be different between the environments, especially if using ACCESSCS in both cases. There is a SAS note on how this can happen when you have a floating point value that's being exported to an integer field. But I'd expect the same warning to occur in both environments.
Both 32-bit SAS EG and 64-bit SAS EG are installed on my computer. I prefer to use 64-bit because I use 64-bit Excel due to the memory restrictions in the 32-bit version.
I have pinned a shortcut to 64-bit EG on my Windows taskbar. But when I double-click on an .egp file, it opens in 32-bit EG -- even after trying to update the default program to open the file with by browsing to the path in the shortcut pinned to my taskbar.
Is there a way to change this?
Thanks.
Usually you can Shift+Right-Click and select Open With..., and then navigate to Choose other App, then find the 64-bit version of EG. (SEGuide.exe in C:\Program Files\SASHome\SASEnterpriseGuide )
Yep, that's exactly what I did, but the file association doesn't hold for some reason.
Maybe I need to be logged in as an admin...
Thanks.