Desktop productivity for business analysts and programmers

Exporting files from SAS and importing them into Access error

Accepted Solution Solved
Reply
Regular Contributor
Posts: 216
Accepted Solution

Exporting files from SAS and importing them into Access error

Hi

In the Enterprise Guide GUI area, I have exported a data set in various formats (i.e. txt, csv, xlsx.) and have tried to import the same file into an Access 2010 database. When I try to import the file, I am getting this error: "Cannot open database ". It may not be a database that your application recognizes, or the file may be corrupt."

Has anyone gotten this before?

Paul


Accepted Solutions
Solution
‎12-24-2012 11:28 AM
PROC Star
Posts: 7,434

Re: Exporting files from SAS and importing them into Access error

Paul: You very likely have exceeded the MS Access limitations.  It can only handle 32768 records and must be under 2gb.  The specs can be found at: http://office.microsoft.com/en-ca/access-help/access-2010-specifications-HA010341462.aspx

View solution in original post


All Replies
Super User
Posts: 19,157

Re: Exporting files from SAS and importing them into Access error

Why not export directly to an access DB?

Anyways, can you open the data in a text editor or Excel?

Can you import other files into Access that work and not just this one?

If so it's an Access issue, not a SAS issues?

Regular Contributor
Posts: 216

Re: Exporting files from SAS and importing them into Access error

I have been able to import other SAS files from this SAS program into Access, so it is not Access.

With this particular file, I tried to eliminate columns to see if it was due to a data issue, but I got the same error regardless of which columns were in the file.

I was not aware you could export directly into a DB. I only see file export options? Actually, I don't know if I have the SAS DB software to be able to do this actually.

Paul

Super User
Posts: 19,157

Re: Exporting files from SAS and importing them into Access error

Typically if you can export into an excel file you can into Access.

Can you connect to your db?

libname mydb ACCESS 'path to your accessdb.mdbx' here

Access may be accesscs or something else for 2010, not sure.

Community Manager
Posts: 2,889

Re: Exporting files from SAS and importing them into Access error

You can use SAS Enterprise Guide to export to a new MDB directly.  Use the Export menu above the data grid viewer to begin, and then select Microsoft Access (mdb) as a target file.  For that, you do not need SAS/ACCESS to PC Files.

If you want to export your table to an existing database, or a Microsoft Access 2007/2010 database, then you will need to use PROC EXPORT (DBMS=access or, for 64-bit SAS, DBMS=accesscs).  You can also use a LIBNAME statement with PCFILES or even ODBC (if you have a DSN defined for your Access database).

As to why one of the other "raw formats" (txt, csv, XLSX) didn't work as a "transport" between EG and your Microsoft Access database, I can't say.  But if you can skip that step in between, that's probably better.

Chris

Regular Contributor
Posts: 216

Re: Exporting files from SAS and importing them into Access error

Hi

I am trying to export to a MS Access 2010 DB using the below statement and am getting an error for "ERROR: FILE= or TABLE= is required and must be specified.". However, I do have a file name indicated. I was just going to create a new table within this DB for the data set, do I need to do something different?

Paul

proc export data=SASd.court_events

dbms=access file="F:\MetricFiles\2013_Changes\metrics.accdb";

run;

Contributor
Posts: 27

Re: Exporting files from SAS and importing them into Access error

Coincidentally, I am trying to do something very similar but from a Unix workspace server. I was able to follow Chris' advice and Export a dataset from Unix to mdb. However, although I could select more than one dataset, only the first was accepted by the wizard. When I try to export the remaining datasets, there is no option to add them to the existing mdb.

Not surprisingly, running the proc export below from a Unix session does not work:

proc export data = homedata.groupmemgroups_info dbms=accesscs file = '\\todnfs02.tsa.bmo.com\userdata2$\afarrer\home\My SAS Files\UserAdmin\GROUPMEM_INFO.mdb' ;

gives 'ERROR: Server Name is invalid or missing.' since Unix does nor recognize the Windows location.

I am considering Exporting each dataset to a separate XLS which I then import using Access but I will lose the indexes that I was hoping to keep.

Regular Contributor
Posts: 216

Re: Exporting files from SAS and importing them into Access error

I was able to somewhat successfully run the export using the below Proc, however, about half way through the file, SAS stopped and printed the below error in the log file. I tried to load the second half of the file into a different table in the same DB starting with the records it stopped on and am getting an error for "Error attempting to CREATE a DBMS table. ERROR: Execute: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt..".

When I look at the specific record that the export originally stopped on, it doesn't appear to be any different than others. The records up to the stopping point appear to have loaded successfully. The record set being exported is quite large, 7.3 GB. Has anyone run into this?

Paul

PROC EXPORT DATA=SASd.court_events

            OUTTABLE="CourtEvents"

            DBMS=ACCESS REPLACE;

     DATABASE="F:\access.accdb";

RUN;

15         PROC EXPORT DATA=SASd.court_events

16                     OUTTABLE="CourtEvents"

17                     DBMS=ACCESS REPLACE;

18              DATABASE="F:\access.accdb";

19         RUN;

1 The SAS System                                                      08:55 Monday, December 24, 2012

ERROR: Execute: Unable to insert row

WARNING: File deletion failed for _IMEX_.CourtEvents.DATA.

NOTE: PROCEDURE EXPORT used (Total process time):

      real time           1:01:39.14

      cpu time            1:01:26.30

     

ERROR: Export unsuccessful.  See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

20        

21        

22         GOPTIONS NOACCESSIBLE;

23         %LET _CLIENTTASKLABEL=;

24         %LET _CLIENTPROJECTPATH=;

25         %LET _CLIENTPROJECTNAME=;

26         %LET _SASPROGRAMFILE=;

27        

28         ;*';*";*/;quit;run;

29         ODS _ALL_ CLOSE;

30        

31        

32         QUIT; RUN;

33        

Solution
‎12-24-2012 11:28 AM
PROC Star
Posts: 7,434

Re: Exporting files from SAS and importing them into Access error

Paul: You very likely have exceeded the MS Access limitations.  It can only handle 32768 records and must be under 2gb.  The specs can be found at: http://office.microsoft.com/en-ca/access-help/access-2010-specifications-HA010341462.aspx

Regular Contributor
Posts: 216

Re: Exporting files from SAS and importing them into Access error

Hi Art

That is exactly the stopping point and the issue. I guess I assumed Access was more capable than that. Thanks for the link also. The link gave me an idea though, which is to link tables in multiple Access DBs. I only have Access for the time being so linking may have to do.

Paul

Contributor
Posts: 61

Re: Exporting files from SAS and importing them into Access error

hi,

In SAS base, I am able to export up to 2938410 obs* 37 columns to a single Access table. This took 8 minute real time.

PROC Star
Posts: 7,434

Re: Exporting files from SAS and importing them into Access error

Chang,

I was incorrect on the 32,767 records limitation, but the 2gb limitation is definitely there.  Yes, there is no defined limit to the number of records.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 6780 views
  • 7 likes
  • 6 in conversation