Date: Mon, 20 Nov 2006 17:45:42 -0500
Reply-To: Jeri Ji <jeri_ji@FREDDIEMAC.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jeri Ji <jeri_ji@FREDDIEMAC.COM>
Subject: Re: Import from Access
In-Reply-To: <A4F0DBF1F84D4F46A0E6D56405D3511989E7D1@HHAEXMB03.rf01.itservices.ca.gov>
Content-Type: text/plain; charset="US-ASCII"
5337 proc sql noprint;
5338 connect to odbc (dsn="test");
5339
5340 create table test as
5341 select * from connection to odbc
5342 (select * from 'Seller and Anchor GFee Information');
ERROR: CLI prepare error: [Microsoft][ODBC Microsoft Access Driver] Syntax
error in query.
Incomplete query clause.
SQL statement: select * from 'Seller and Anchor GFee Information'.
5343 disconnect from odbc;
5344 quit;
Did I write anything wrong?
Jeri
"Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
11/20/2006 05:38 PM
Please respond to
"Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
To
SAS-L@LISTSERV.UGA.EDU
cc
Subject
Re: Import from Access
Sorry about that I meant
from 'long table name'n
Paul Choate
DDS Data Extraction
(916) 654-2160
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Choate, Paul@DDS
Sent: Monday, November 20, 2006 2:33 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Import from Access
Jeri -
Did you try a name literal?
from 'long table name'$
Paul Choate
DDS Data Extraction
(916) 654-2160
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Jeri Ji
Sent: Monday, November 20, 2006 2:27 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Import from Access
Your code worked. Thank you very much! I still have one question left.
In
that Access database, the table that I wanted to import has five words
in
it with space in between. I tried, but didn't import it successfully.
What can I do? Thanks.
Jeri
"Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
11/20/2006 05:20 PM
Please respond to
"Terjeson, Mark" <Mterjeson@RUSSELL.COM>
To
SAS-L@LISTSERV.UGA.EDU
cc
Subject
Re: Import from Access
Hi Jeri,
Two things, I mentioned the wrong license module.
If you check your licensing, i.e. in your SAS editor
enter
proc setinit;
run;
to get your list. If you have "---SAS/ACC-ODBC "
then you are good.
To make an ODBC entry (which merely maps a
DSN token to a path/filename) go to START, Programs
(or via Control Panel), Adminitrative Tools and locate
the "Data Sources (ODBC)". On the "User DSN" tab
select "Add". Choose driver "Microsoft Access Driver (*.mdb)"
and push "Finish". You will get a popup window entitled
"ODBC Microsoft Access Setup. You only need two items!!!
In the "Data Source Name:" field put in a token of your choice,
e.g. xyz. Then in the "Database:" section, push "Select" and
merely point to your .mdb file. Hit the appropriate number
of "OK" buttons because you are done.
Then you are ready to talk to that .mdb by merely using the token
in the dsn=. e.g.
proc sql noprint;
connect to odbc (dsn="xyz");
create table abcdefghijklmnopqrstuvwxyz789012 as
select * from connection to odbc
(
/* inside these parentheses is sql text that
gets passed all the way over to your .mdb
and gets run there, so inside these parens
is Microsoft Access SQL syntax. You can
copy and paste directly to and from Access
query window to here. */
select * from [abcdefghijklmnopqrstuvwxyz7890123]
);
disconnect from odbc;
quit;
Hope this is helpful.
Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group
Russell
Global Leaders in Multi-Manager Investing
________________________________
From: Jeri Ji [mailto:jeri_ji@freddiemac.com]
Sent: Monday, November 20, 2006 2:06 PM
To: Terjeson, Mark
Cc: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Import from Access
Sorry that I was slow on this. I never used pass-through before. In the
example below, where should I put the path of the mdb file? Thanks.
Jeri
"Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
11/20/2006 04:55 PM
Please respond to
"Terjeson, Mark" <Mterjeson@RUSSELL.COM>
To
SAS-L@LISTSERV.UGA.EDU
cc
Subject
Re: Import from Access
Hi Jeri,
If you use a PROC SQL pass-through query,
you can fetch longer table names and write
them in SAS with shorter names. e.g.
proc sql noprint;
connect to odbc (dsn="db2");
create table abcdefghijklmnopqrstuvwxyz789012 as
select * from connection to odbc
(select * from abcdefghijklmnopqrstuvwxyz7890123);
disconnect from odbc;
quit;
The above example utilizes PC FileFormats license module
and a quick ODBC assignment for a "USER DSN" by creating
you own token name and assigning it to the .mdb file.
Hope this is helpful.
Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group
Russell
Global Leaders in Multi-Manager Investing
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Jeri Ji
Sent: Monday, November 20, 2006 1:33 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Import from Access
I need to import a table from Access. However, the name of that table is
too long and I cannot do it. Since I am not the owner of that database,
I cannot ask them to change the name of the table. Is there any way I
can import the table?
Currently, I export the table to excel and do the import.
Also, can I add condition when I do the import? For example, can I say
import when age=24 (suppose there is an age field in that table)?
Thank you very much!
Jeri
