BookmarkSubscribeRSS Feed
bjoern_krueger
Calcite | Level 5

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 

13 REPLIES 13
Kurt_Bremser
Super User

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.

bjoern_krueger
Calcite | Level 5

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

 

 

 

 

Reeza
Super User

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;

 

 

bjoern_krueger
Calcite | Level 5

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

Kurt_Bremser
Super User

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?

bjoern_krueger
Calcite | Level 5

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

Kurt_Bremser
Super User

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?

bjoern_krueger
Calcite | Level 5

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

 

 

Kurt_Bremser
Super User



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

bjoern_krueger
Calcite | Level 5

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

 

 

 

 

bjoern_krueger
Calcite | Level 5

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

PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 20604 views
  • 0 likes
  • 4 in conversation