Import from Access
in [SAS]
|
From: "Pardee, Roy" on 20 Nov 2006 16:38 Do you have sas/access for odbc or oledb licensed? If so, you should be able to do pass-through to solve both problems at once. Something like: Proc sql ; connect to oledb as msacc (Provider=Jet DataSource='\\server\dir\subdir\my_file.mdb') ; create table sas_friendly as select * from connection to msacc (select * from outrageously_long_table_name_holy_cow_can_you_believe_this where age = 24) ; Quit ; HTH, -Roy -----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 20 Nov 2006 16:40 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> 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 >
From: "Terjeson, Mark" on 20 Nov 2006 16:55 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 16:53 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> 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
From: Wensui Liu on 20 Nov 2006 16:56
Jeri, there are multiple ways to do so. I listed some on my blog: http://statcompute.spaces.live.com/blog/cns!39C8032DBD1321B7!127.entry HTH. On 11/20/06, Jeri Ji <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 > -- WenSui Liu (http://spaces.msn.com/statcompute/blog) Senior Decision Support Analyst Cincinnati Children Hospital Medical Center |