topic Reading access database with infile statement in SAS Programming

Reading access database with infile statement

Rookie_123 — Fri, 14 Sep 2018 13:23:29 GMT

TLDR:

I want to read my access database via infile statement to have complete control over format,informat,length of variables/columns

I have an access database which has around 2 million records.Currently I am importing it with PROC IMPORT.

The problem:

From what I have read that PROC IMPORT uses around 32k rows to set format,informat,length for variables I feel that data may be getting truncated.

Another problem is all my character variables have format and informat as $255.and length as 255.I dont want my all variables to be of this length,format,informat.

Some I want to be more than 255 and some to be less than that.

My current code:

PROC IMPORT OUT= Input.access_db 
DATATABLE= "DATABASE" 
DBMS=ACCESSCS REPLACE; 
DATABASE="\\Input\Entire_Records.accdb"; 
SCANMEMO=YES; 
USEDATE=YES; 
SCANTIME=NO; 
RUN;

I want to do above with an infile statement.

I have a code to import my csv via infile statement.But I am not getting how do I modify it to make it to read my access database

data Input.Real;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile 'Input\Real.csv'
delimiter = ',' 
MISSOVER 
DSD 
lrecl=32767 
firstobs=2 ;
informat DATE 11.;
informat IDENTIFIER $255. ;
informat LINE_CODE $255. ;
informat ACTION $255. ;

format DATE 11.;
format IDENTIFIER $255. ;
format LINE_CODE $255. ;
format ACTION $255. ;

input

DATE 
IDENTIFIER $
LINE_CODE $
ACTION $ ;

if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
RUN;

Re: Reading access database with infile statement

RW9 — Fri, 14 Sep 2018 14:06:35 GMT

Infile is for reading files from disc. Access databases, whilst files, are in MS file format. As you say accdb its probably the most recent database file format, but it is definately not a flat file ready for reading. You would need to export from Access a flat file format, like CSV to be able to read in the via infile.

A better method (well, debatable as using any other an Office product would be better), would be to use odbc connection to access file and then read from access as if it was any other database connection:

https://communities.sas.com/t5/SAS-Programming/How-to-connect-SAS-to-MS-Access-query/td-p/459120

Re: Reading access database with infile statement

Reeza — Fri, 14 Sep 2018 14:46:20 GMT

The issues you're mentioning originate when the data is not in a database that enforces types whereas an Access DB does enforce types and other data integrity rules. For the variables that are set to 255 characters in SAS what is the length set in the Access DB?

Re: Reading access database with infile statement

Rookie_123 — Fri, 14 Sep 2018 14:52:46 GMT

The database is so large that I am not able to find its length in Access.I cant run any code on it to find its length outside SAS

Re: Reading access database with infile statement

Reeza — Fri, 14 Sep 2018 14:54:57 GMT

@Rookie_123 wrote:

The database is so large that I am not able to find its length in Access.I cant run any code on it to find its length outside SAS

I don't know how you're importing your data but you can look into PCFILES or ODBC connections which may help with the field truncations. You do need to be sure that the truncation isn't in the ACCESS DB first though. For example if a field was set to limit 255 in Access that may be all that's present.

After you can scan your character variables using the LENGTH function and resize as necessary because you are correct that character variables do take up more space.

Re: Reading access database with infile statement

Rookie_123 — Fri, 14 Sep 2018 15:03:11 GMT

My main aim is whatever is in Access Database.I should get it in SAS database when I import.Basically I am not understanding that how SAS is setting 255 for character variables.

Is it because max length is 255 in the access database or is it because that's the max length it can set(Even if access database has say 300 in it)

Re: Reading access database with infile statement

Reeza — Fri, 14 Sep 2018 15:10:12 GMT

@Rookie_123 wrote:

Is it because max length is 255 in the access database

Yes

is it because that's the max length it can set(Even if access database has say 300 in it)

This can depend on how you import the file and your version of SAS. I don't believe using ODBC will have this issue.

Re: Reading access database with infile statement

Rookie_123 — Fri, 14 Sep 2018 15:38:44 GMT

When I follow the link to connect to Access from SAS.

I get the error

NOTE: Statement not executed due to NOEXEC option.

Re: Reading access database with infile statement

Tom — Fri, 14 Sep 2018 16:00:38 GMT

@Rookie_123 wrote:

When I follow the link to connect to Access from SAS.

I get the error

NOTE: Statement not executed due to NOEXEC option.

Not sure how you "follow a link" to connect to Acess, but that note makes it look like SAS has set the NOEXEC option. Probably in response to early errors that have occured.

Try just re-starting SAS and doing whatever you are trying again.