Hello!
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
outtable="Test_Table"
dbms=ACCESS2002
replace;
database='C:\test\example.mdb'
run;
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,
Bjoern
Hi!
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.
Hi!
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:
/www/sas/Data/1/Tchibo-Analytics/Tchibo_Analytics_Includes/Analytic/example.accdb
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,
Bjoern
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;
Hello!
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,
bjoern
PS: sorry for my bad english...
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?
Hello!
OK:
Operation System: Linux
Operation System Family: LIN X64
Version: 2.6.32-504.23.4.el6.x86_64
SAS-Version: 9.4
My local EG-Installation runs under Windows 8.1 Enterprise 64bit
Thanks a lot again and best regards,
Bjoern
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?
Hello again!
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.
Best regards,
Bjoern
@bjoern_krueger wrote:
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.
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
Here is the statement which exports a SAS-File directly to Excel:
proc export data=bk.T8776_ZZ_Final
outfile=' /www/sas/Data/1/Tchibo-Analytics/Tchibo_Analytics_Includes/Analytic/Export_AT.xlsx'
dbms=XLSX;
run;
Best regards,
Bjoern
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!!
Best regards,
Bjoern
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.