Import from Access
in [SAS]
|
From: Jeri Ji on 20 Nov 2006 17:06 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 20 Nov 2006 17:20 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 20 Nov 2006 17:58 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 20 Nov 2006 17:32 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 20 Nov 2006 17:26 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 |