03-24-2016 10:05 AM
I'm quite new in SAS programming, and I have to export a SAS file to MS-Access accdb-File.
I use SAS Enterprise Guide 7.11 under Windows 8.1 Enterprise 64 bit.
In my old SAS-Version (Enterpise Guide 4.2) under Windows XP Professional, the export worked without problems with the following code:
proc export data=TestData
The result of this code is an access file "example.mdb" with a table "Test_Table" in it. The Access table contains the data from the SAS file "TestData".
Unfortunately in our new SAS-Version, this code does not work anymore.
I tried a lot of code, an asked google for hours, but I do not find the solution.
Could anybody give me a short code example, that shows, how it works? Maybe anybody can edit the above example, so that it works in Enterprise guide 7.11?
Thanks and best regards,
03-24-2016 10:14 AM - edited 03-24-2016 10:16 AM
Could you be a bit more specific about "does not work anymore"?
Does the step fail with a ERROR message, or is the result non-digestible for MS Access?
I have the suspicion that your switch to Windows 64 causes a bitness problem.
03-24-2016 10:28 AM
If I use the code, the following Error occures:
ERROR: "'c:\test\example.mdb'" is not a valid name.
It does not depend on which database-Path I use. The same Error occures, when I use a server path like this:
I tried also the old Access-suffix "mdb" but it's the same.
The path is definately valid, the export to excel to the same path works without any problems.
Thanks again and best regards,
03-24-2016 10:29 AM
Is your EG connected to a server or running locally? Does it work if you export via the GUI?
Try changing your DBMS to Access or AccessCS
proc export data=TestData outtable="Test_Table" dbms=ACCESSCS replace; database='C:\test\example.accdb' run; proc export data=TestData outtable="Test_Table" dbms=ACCESS replace; database='C:\test\example.accdb' run;
03-24-2016 10:38 AM - edited 03-24-2016 10:41 AM
The eg is connected to a server. The export via the GUI is not possible, access is not in the list of export formats.
May this be the reason? Does the EG generally not support the export to access? Or do I have to install any export-plugin?
I tried "ACCESS" and "ACCESSCS" => the same error.
Thanks and best regards,
PS: sorry for my bad english...
03-24-2016 10:41 AM - edited 03-24-2016 10:42 AM
When you right-click on the server in the server list in EG, select Properties (Eigenschaften), and then select the "Software" tab, which operating system is reported?
03-24-2016 10:46 AM
Operation System: Linux
Operation System Family: LIN X64
My local EG-Installation runs under Windows 8.1 Enterprise 64bit
Thanks a lot again and best regards,
03-24-2016 10:55 AM
My experience with SAS/ACCESS to PC Files and MS formats lets me think that accessing pre-XML MS Office formats is only possible on Windows, not on the UNIX platforms (including Linux). On UNIX, one needs the PC Files Server running on a Windows machine and has to use ACCESSCS.
Did you switch from a 1-machine to a multi-tier (client/server) setup?
03-24-2016 11:05 AM
Our old SAS-System was installed on a windows based server. The new System runs on a Linux based system.
But the old system was not a "1-machine".
I hope it will be possible to export to Access. As I've written before, the export to ms-excel works without problems.
03-24-2016 11:10 AM
As I've written before, the export to ms-excel works without problems.
The question is, how do you export to Excel? With the XML-based .xlsx format, SAS 9.4 has a platform-independent access mechanism.
03-24-2016 11:05 AM
Yep, according to http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#p0psac3j16cioen1nq... you will need to set up a PC Files Server and use DBMS=ACCESSCS
03-24-2016 11:20 AM
Here is the statement which exports a SAS-File directly to Excel:
proc export data=bk.T8776_ZZ_Final
03-24-2016 11:34 AM
Hello once more!
Obviously in the currunt SAS-Configuration it is impossible to export into Access files.
that's not exactly what I had hoped to learn, but at least it's an answer...
I will tell it our system admin, I hope it will be possible to install the PC Files Server.
Many thanks for your answers and your time again!!
03-24-2016 02:22 PM
Hi Bjoern, please report back on the performance of PCFILES Server to connect with MS-Access. My own experience trying to connect from SAS-64bits to Access-32bits (both on Win7) is that certain functionnalities are lost. In particular, the ability to perform UPDATE and INSERT operations. The only way we found to do INSERT and UPDATE was to use Pass-Through queries.
I wish you better luck than we had.