DATA Step, Macro, Functions and more

how to export a sas file to MS-Access?

Reply
Occasional Contributor
Posts: 7

how to export a sas file to MS-Access?

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 

Super User
Posts: 7,856

Re: how to export a sas file to MS-Access?

[ Edited ]
Posted in reply to bjoern_krueger

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: how to export a sas file to MS-Access?

Posted in reply to KurtBremser

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

 

 

 

 

Super User
Posts: 19,869

Re: how to export a sas file to MS-Access?

Posted in reply to bjoern_krueger

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

 

https://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#p0psac3j16cioen1n...

 

 

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;

 

 

Occasional Contributor
Posts: 7

Re: how to export a sas file to MS-Access?

[ Edited ]

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...

Super User
Posts: 7,856

Re: how to export a sas file to MS-Access?

[ Edited ]
Posted in reply to bjoern_krueger

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: how to export a sas file to MS-Access?

Posted in reply to KurtBremser

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

Super User
Posts: 7,856

Re: how to export a sas file to MS-Access?

Posted in reply to bjoern_krueger

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: how to export a sas file to MS-Access?

Posted in reply to KurtBremser

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

 

 

Super User
Posts: 7,856

Re: how to export a sas file to MS-Access?

Posted in reply to bjoern_krueger



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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,856

Re: how to export a sas file to MS-Access?

Posted in reply to bjoern_krueger

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: how to export a sas file to MS-Access?

Posted in reply to KurtBremser

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

 

 

 

 

Occasional Contributor
Posts: 7

Re: how to export a sas file to MS-Access?

Posted in reply to bjoern_krueger

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

Respected Advisor
Posts: 4,934

Re: how to export a sas file to MS-Access?

Posted in reply to bjoern_krueger

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. Smiley Happy

PG
Ask a Question
Discussion stats
  • 13 replies
  • 3878 views
  • 0 likes
  • 4 in conversation