topic Re: Best way to import Access Table? in SAS Procedures

Best way to import Access Table?

BETO — Sun, 15 Apr 2012 03:23:12 GMT

Hi I've a two part question I currently have a access table that I need to import into sas. The table has over 100k records . The Access table is in a share directory which takes a long time to do an import (Proc Import- (currently it freezes my laptop) I tried Libname an its brings in everthing fast ( which is great) the issue is there are a lot tables in the the mdb that when i click on the libname it takes on avg 25-30 min to open. Is there a way I can use libname to refer to one table named "TEST" only ? and not everything in there. What I want to do is on table 'TEST" I need to pull the last 30 days worth of data. Is there a way to use proc import and do a "Date" -30 or in libname select table "TEST" and do a -30? Thanks for your assistance

Re: Best way to import Access Table?

PGStats — Mon, 16 Apr 2012 01:51:45 GMT

Try ACCESS=READONLY and DEFER=YES in your libname statement. It might speed things up.

Another way would be to try the SQL pass-through facility.

Another way would be to create a small ACCESS database with only the tables that you want as linked tables.

Re: Best way to import Access Table?

BETO — Mon, 16 Apr 2012 03:13:57 GMT

Is there a way in doing the libname I can filter for only last 30 days worth of data? Where would I insert ACCESS=READONLY and DEFER=YES what I hV E NOW IS " libname Access c:\ Complinance.mdb;"

Re: Best way to import Access Table?

PGStats — Mon, 16 Apr 2012 11:41:18 GMT

libname myLib Access "c:\ Complinance.mdb" READONLY DEFER=YES;

Data selection cannot be done in the libname statement.

Re: Best way to import Access Table?

PGStats — Mon, 16 Apr 2012 16:40:08 GMT

The best method would be to use the SQL Pass-through facility because the subsetting would be done by Access. On my machine (Win-XP, Access 2007, SAS 9.3) the following works:

proc sql;
connect to Access as db (path="C:\Documents and Settings\******\myDatabase.accdb");
create view myData as
select * from connection to db (select * from myTable where myTable.date >= int(now())-30 );

quit;

proc print data=myData; run;

The query being stored in a view, it is run only when you run the proc print. Note that the SQL in parenthesis must be valid Microsoft SQL, not SAS SQL.

Re: Best way to import Access Table?

BETO — Tue, 17 Apr 2012 13:26:22 GMT

Hi PG Stats

I plug in your code an did test run on a access db I 'v on my desktop an it worked great. When I tried it on the real Access db I rec this error.Does it matter type of access it was to a .mdb not a accdb? The "Settle" is in a date/ time format dd/mm/yy 00:00:00. The real Path is to a shared directory

ERROR: Prepare: Syntax error in query. Incomplete query clause.

SQL statement: select * from "tbl_dep" where tbl_dep.Settle

>= int(now())-30

ERROR: SQL View WORK.MYDATA could not be processed because at least one of the data sets, or views,

referenced directly (or indirectly) by it could not be located, or opened successfully.

Thanks Again for all your help

Re: Best way to import Access Table?

PGStats — Tue, 17 Apr 2012 14:39:05 GMT

"tbl_dep" is not a valid table name in MS-Access, use [tbl_dep] or simply tbl_dep.

Re: Best way to import Access Table?

BETO — Tue, 17 Apr 2012 16:10:06 GMT

i Hi PG STATS,The [] worked the issue is when I try to create a simple table based of the Mydata table(view). I get a error I enclosed error log. Since its a view could I use it like a normal table in SAS? or is it limited ? Thanks again

proc sql;
33 create table depcount as
34 (select
35 Terminal_ID,
36 Count(Terminal_ID) As Repeat
37 from Mydata
38 Where ON_SCHEDULE eq 'False'
NOTE: This SAS session is using a registry in WORK. All changes will be lost at the end of this
session.
NOTE: This SAS session is using a registry in WORK. All changes will be lost at the end of this
session.
ERROR: Maximum level of nesting of macro functions exceeded.
ERROR: Maximum level of nesting of macro functions exceeded.

Re: Best way to import Access Table?

PGStats — Tue, 17 Apr 2012 16:33:10 GMT

I can't make sense of that log. I remember getting the same type of messages when I tried to run two sessions at once.

But yes, SAS views can be used like normal tables. Sometimes, it is preferable to transform them into local temporary tables (replace the word view with table in the query) to avoid querying the remote database each time you use them.

Re: Best way to import Access Table?

BETO — Tue, 17 Apr 2012 22:33:39 GMT

Hi PGStats,

I change it to table from view an now its avg about 1hr to pull data. I might have to learn to live with that unless you have any suggestion it has over 500k records an grows everyday a little... I tried creating a count of all the TID that have "false" an it states remerging what do you think is causing that?

175 create table depcount as

176 (select

177 Terminal_ID,

178 Count(distinct
Terminal_ID) As Repeat

179 from Mydata

180 Where ON_SCHEDULE eq
'False'

181 )

182 ;

NOTE: The query requires
remerging summary statistics back with the original data.

NOTE: Table WORK.DEPCOUNT
created, with 15 rows and 2 columns.

Re: Best way to import Access Table?

PGStats — Wed, 18 Apr 2012 01:25:32 GMT

Your query should read:

create table depcount as

select Terminal_ID, count(Terminal_ID) as Repeat

from MyData

where ON_SCHEDULE = "False"

group by Terminal_ID;

This counts the number of records for each Terminal_ID that have ON_SCHEDULE = "False".

Can you post the part of the SQL job that pulls the MS-Access data into a local table? I find it strange that it takes so long. Please include the table record count as well.

Re: Best way to import Access Table?

BETO — Wed, 18 Apr 2012 06:48:10 GMT

Part1)This takes 1 hr on avg to pull the table has 500k records. When the table gets created with the 30 filter it produces 23k records

procsql;

connectto Accessas db (path="g:\Shared Databases\CCMS 2\CCMS2Production.mdb");

createtable myDataas

select *from connection to db (select * from [tbl_deposit]

where tbl_deposit.Scheduled_Settle >= int(now())-30 );

quit;

2nd Part) If I want to add an additional criteria " And Repeat >1" is just an "AND"?

SAS is a funny program how would I add wild card too pick up all types of False(I.E false,False,FALSE)

procsql;

createtable depcountas

select Terminal_ID,Count(Terminal_ID)As Repeat

from Mydata

Where ON_SCHEDULE ="FALSE"

groupby Terminal_ID;

quit;

tHANKS AGAIN PGSTATS

Re: Best way to import Access Table?

BETO — Thu, 19 Apr 2012 20:20:41 GMT

Thanks PG Stats for all your help

Re: Best way to import Access Table?

BETO — Thu, 19 Apr 2012 20:26:03 GMT

PGSTATS,

How do I select for correct answer ?

Re: Best way to import Access Table?

PGStats — Thu, 19 Apr 2012 20:35:00 GMT

Hello BETO, sorry to take so long...

Part 1) Now I understand. It is the part where Access, running on your machine, accesses your database on a network that takes so long. The only way to improve that I can think of, would be to have an index on variable Scheduled_Settle,. if it doesn't already exists.

Part 2) where upcase(on_schedule) = "FALSE" and repeat > 1

good luck

Re: Best way to import Access Table?

BETO — Tue, 24 Apr 2012 13:00:15 GMT

Hi PGStats,

Thank you so much for your assistance with my question... BTW I fig out how to reduce the time of the import from 1hr to 7 min.. I went into the table an change all the text columns in access to 'Unicode compress" .... How can I select that the question has been answer? Thanks a bunch

Re: Best way to import Access Table?

PGStats — Tue, 24 Apr 2012 15:29:30 GMT

Great. I hope this can also be useful to others. Network transit was the real bottleneck here.

To mark a question as answered, you must open the message (not just a preview) and select one of the green stars that says "Correct answer".