BookmarkSubscribeRSS Feed
Rookie_123
Fluorite | Level 6

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;


 

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

Rookie_123
Fluorite | Level 6

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

I get the error 

NOTE: Statement not executed due to NOEXEC option.

Tom
Super User Tom
Super User

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

Reeza
Super User
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?
Rookie_123
Fluorite | Level 6

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

Reeza
Super User

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

 

 

Rookie_123
Fluorite | Level 6

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)

 

Reeza
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 8 replies
  • 1778 views
  • 3 likes
  • 4 in conversation