topic Re: error in import of access database in SAS Programming

error in import of access database

mermaid — Wed, 29 Aug 2018 07:33:07 GMT

Good morning,

With SAS9.3 i used to import an Access table using the following syntax:

PROC IMPORT OUT= TB_KEY (keep=R_fPatient DDN Sexe N_Adm

rename=(N_Adm=ADMINO)

)

DATATABLE= "TbPatient"

DBMS=ACCESSCS REPLACE;

DATABASE="\\Sih-fs\programmes\CPTCR\BD_CPTCR.mdb";

SCANMEMO=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

Since upgrading to SAS 9.4, this syntax is not working anymore and I cannot access the database anymore.

Can you please help me?

I cannot provide a sample as it is patient confidential data.

Thanking you in advance,

Aline

Re: error in import of access database

Kurt_Bremser — Wed, 29 Aug 2018 08:01:00 GMT

If the PC Files Server is not installed on the same machine where the SAS process runs, you need to supply at least a server name to the import procedure if you use dbms=ACCESSCS.

From the 9.3 documentation of ACCESSCS for PROC IMPORT:

SERVER= PC-Files-Server-name

specifies the name of the PC Files Server, where PC-Files-Server-name can be either the computer name or the associated IP address. You must bring up the listener on the PC Files Server before you can establish a connection to it. You can also configure these items:

the service name
the port number
the maximum number of concurrent connections
specifications to indicate whether data encryption will be used

Alias: SERVER_NAME

Restriction: Available only for the client/server model.

Note: You can omit this option if you are running SAS and the PC Files Server on the same machine. Omitting this option under this condition causes the PC Files Server to start automatically in the background.

Re: error in import of access database

RW9 — Wed, 29 Aug 2018 08:02:09 GMT

I am going to assume this is a 32/64 bit problem. Basically when Windows moved to 64bit, a lot of the proprietary binary files no longer worked - including SAS catalogs. There have been some suggestions made like using PCfiles. However I am of the opinion that this is a really good time to bin these dreadful, archaic file types in favour of far more open, portable sources. For instance, dump out your data from Access into a CSV file - CSV is plain open text, which will work on any system and is not tied to a proprietary format.

If you absolutely have to use Access (why?), then you will need to migrate that file and software to 64bit to match the bit of SAS you are using or setup an export process from it to an open file format. Me I would not be using any Office software as a data store, invest in a proper database, or use a proper method of data transfer, I cannot express how much of an issue Office suite is in the world.