Import from Access
in [SAS]
|
From: "Choate, Paul@DDS" on 20 Nov 2006 17:38 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: Jeri Ji on 20 Nov 2006 17:45 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: Jeri Ji on 20 Nov 2006 18:09 It worked! Thank you very much. I think Mark mentioned it in his email, too. I didn't pay attention. Thank you everybody for the quick response! Have a good night. Jeri "Pardee, Roy" <pardee.r(a)GHC.ORG> Sent by: "SAS(r) Discussion" <SAS-L(a)LISTSERV.UGA.EDU> 11/20/2006 05:58 PM Please respond to "Pardee, Roy" <pardee.r(a)GHC.ORG> To SAS-L(a)LISTSERV.UGA.EDU cc Subject Re: Import from Access 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: Yu Zhang on 21 Nov 2006 09:49
Anyone know why libname solution is not working for this problem? I use this method alot, but can't figure out why it falis. Thanks! Yu On 11/20/06, Jeri Ji <jeri_ji(a)freddiemac.com> wrote: > > > I got the following error message. Can you tell me what I did wrong? Thank > you. > > > libname AccessIn 'g:/pricing2/deal negotiations/programs/monthly > gfees/monthly gfees.mdb'; > > ERROR: Library ACCESSIN is not in a valid format for access method RANDOM. > ERROR: Error in the LIBNAME statement. > > > Jeri > > > *"Yu Zhang" <zhangyu05(a)gmail.com>* > > 11/20/2006 04:40 PM > To > "Jeri Ji" <jeri_ji(a)freddiemac.com> cc > SAS-L(a)listserv.uga.edu Subject > Re: Import from Access > > > > > I used to use libname statement to specify the ACCESS database I want to > import. > > libname AccessIn 'path........./dbname.mdb'; > > after you run this statement you should be able to view the table inside > the database in the explore window. also those table names should be in the > sashelp.vcolumn view. > > then you can use proc SQL to do the data subsetting or manipulation. > > But be careful, if you perform any operations directly on those table, any > changes will be applied to that table. > > HTH > > Yu > > > On 11/20/06, *Jeri Ji* <*jeri_ji(a)freddiemac.com* <jeri_ji(a)freddiemac.com>> > wrote: > 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 > > |