From: Jeri Ji on
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(a)RUSSELL.COM>
Sent by: "SAS(r) Discussion" <SAS-L(a)LISTSERV.UGA.EDU>
11/20/2006 04:55 PM
Please respond to
"Terjeson, Mark" <Mterjeson(a)RUSSELL.COM>


To
SAS-L(a)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(a)LISTSERV.UGA.EDU] On Behalf Of
Jeri Ji
Sent: Monday, November 20, 2006 1:33 PM
To: SAS-L(a)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
From: "Terjeson, Mark" on
Hi Jeri,
=20
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 "=20
then you are good.
=20
To make an ODBC entry (which merely maps a
DSN token to a path/filename) go to START, Programs=20
(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.
=20
=20
Then you are ready to talk to that .mdb by merely using the token
in the dsn=3D. e.g.
=20
=20

proc sql noprint;
connect to odbc (dsn=3D"xyz");

create table abcdefghijklmnopqrstuvwxyz789012 as
select * from connection to odbc
(
=20
/* 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=20
copy and paste directly to and from Access
query window to here. */
select * from [abcdefghijklmnopqrstuvwxyz7890123]
);

disconnect from odbc;
quit;






Hope this is helpful.=20


Mark Terjeson=20
Senior Programmer Analyst, IM&R=20
Russell Investment Group=20


Russell
Global Leaders in Multi-Manager Investing=20




=20
=20
=20
=20

________________________________

From: Jeri Ji [mailto:jeri_ji(a)freddiemac.com]=20
Sent: Monday, November 20, 2006 2:06 PM
To: Terjeson, Mark
Cc: SAS-L(a)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.=20


Jeri=20



"Terjeson, Mark" <Mterjeson(a)RUSSELL.COM>=20
Sent by: "SAS(r) Discussion" <SAS-L(a)LISTSERV.UGA.EDU>=20

11/20/2006 04:55 PM=20
Please respond to
"Terjeson, Mark" <Mterjeson(a)RUSSELL.COM>


To
SAS-L(a)LISTSERV.UGA.EDU=20
cc
Subject
Re: Import from Access

=09




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=3D"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(a)LISTSERV.UGA.EDU] On Behalf Of
Jeri Ji
Sent: Monday, November 20, 2006 1:33 PM
To: SAS-L(a)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=3D24 (suppose there is an age field in that table)?

Thank you very much!


Jeri
From: "Pardee, Roy" on
Rather than the single quotes around the table name, try square
brackets.

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of
Jeri Ji
Sent: Monday, November 20, 2006 2:46 PM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Re: Import from Access

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(a)DDS" <pchoate(a)DDS.CA.GOV>
Sent by: "SAS(r) Discussion" <SAS-L(a)LISTSERV.UGA.EDU>
11/20/2006 05:38 PM
Please respond to
"Choate, Paul(a)DDS" <pchoate(a)DDS.CA.GOV>


To
SAS-L(a)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(a)LISTSERV.UGA.EDU] On Behalf Of
Choate, Paul(a)DDS
Sent: Monday, November 20, 2006 2:33 PM
To: SAS-L(a)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(a)LISTSERV.UGA.EDU] On Behalf Of
Jeri Ji
Sent: Monday, November 20, 2006 2:27 PM
To: SAS-L(a)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(a)RUSSELL.COM>
Sent by: "SAS(r) Discussion" <SAS-L(a)LISTSERV.UGA.EDU>
11/20/2006 05:20 PM
Please respond to
"Terjeson, Mark" <Mterjeson(a)RUSSELL.COM>


To
SAS-L(a)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(a)freddiemac.com]
Sent: Monday, November 20, 2006 2:06 PM
To: Terjeson, Mark
Cc: SAS-L(a)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(a)RUSSELL.COM>
Sent by: "SAS(r) Discussion" <SAS-L(a)LISTSERV.UGA.EDU>

11/20/2006 04:55 PM
Please respond to
"Terjeson, Mark" <Mterjeson(a)RUSSELL.COM>


To
SAS-L(a)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(a)LISTSERV.UGA.EDU] On Behalf Of
Jeri Ji
Sent: Monday, November 20, 2006 1:33 PM
To: SAS-L(a)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
From: "Choate, Paul@DDS" on
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(a)LISTSERV.UGA.EDU] On Behalf Of
Jeri Ji
Sent: Monday, November 20, 2006 2:27 PM
To: SAS-L(a)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(a)RUSSELL.COM>
Sent by: "SAS(r) Discussion" <SAS-L(a)LISTSERV.UGA.EDU>
11/20/2006 05:20 PM
Please respond to
"Terjeson, Mark" <Mterjeson(a)RUSSELL.COM>


To
SAS-L(a)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(a)freddiemac.com]
Sent: Monday, November 20, 2006 2:06 PM
To: Terjeson, Mark
Cc: SAS-L(a)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(a)RUSSELL.COM>
Sent by: "SAS(r) Discussion" <SAS-L(a)LISTSERV.UGA.EDU>

11/20/2006 04:55 PM
Please respond to
"Terjeson, Mark" <Mterjeson(a)RUSSELL.COM>


To
SAS-L(a)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(a)LISTSERV.UGA.EDU] On Behalf Of
Jeri Ji
Sent: Monday, November 20, 2006 1:33 PM
To: SAS-L(a)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
From: Jeri Ji on
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(a)RUSSELL.COM>
Sent by: "SAS(r) Discussion" <SAS-L(a)LISTSERV.UGA.EDU>
11/20/2006 05:20 PM
Please respond to
"Terjeson, Mark" <Mterjeson(a)RUSSELL.COM>


To
SAS-L(a)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(a)freddiemac.com]
Sent: Monday, November 20, 2006 2:06 PM
To: Terjeson, Mark
Cc: SAS-L(a)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(a)RUSSELL.COM>
Sent by: "SAS(r) Discussion" <SAS-L(a)LISTSERV.UGA.EDU>

11/20/2006 04:55 PM
Please respond to
"Terjeson, Mark" <Mterjeson(a)RUSSELL.COM>


To
SAS-L(a)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(a)LISTSERV.UGA.EDU] On Behalf Of
Jeri Ji
Sent: Monday, November 20, 2006 1:33 PM
To: SAS-L(a)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
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: IML Error - Please Help
Next: Proc reg test not consistent