Re: How to access automatically in SAS a MS Access table name
i am not sure if SAS will support long file name if you use libname. a workaround is to use passthrough. On Jan 16, 2008 12:31 PM, JKamgang@Yahoo.com <JBKamgang@gmail.com> wrote: > Dear All, > > > > Happy New Year , > > > > Please, I need your help in figuring out how to access in SAS a MS > Access > table name which has more than 32 characters. As this program merges 5 > databases, I need to have almost everything automated. That's why I > don't want to rename manually the table names. > > > > Please find the program below. > > > > Thanks in advance for your kind assistance, > > > > Jean Baptiste > > > > The program: > > - - - > > > > options VALIDVARNAME=ANY ; > > libname DBsource access 'S:\Data > Management\External\Projects\MTCT-Plus\MTCT-Plus November 2007 > Data\Programs\Data\MTCT-PlusData All.mdb'; > > /* Creating the Pediatric HIV result information additional */ > > > > data* pedResultadd ; > > set dbsource.'tblInfantHIVTestResultsAdditional'n (keep = > > testid studyid labid i2 i2_da i2_mo i2_yr i2a i2b i2text > > ); > * > > run*; > > - - - The log - - > > 1636 > > 1637 /* Creating the Pediatric HIV result information additional */ > > 1638 > > 1639 data pedResultadd ; > > 1640 set dbsource.'tblInfantHIVTestResultsAdditional...

Re: How to access automatically in SAS a MS Access table name #3
Jean - Unfortunately you are stuck - SAS only allows dataset names up to 32 bytes long - using name literals or conventional names. Defining short-named queries of your tables within MSAccess is a very good idea. The SAS libname engine will read the queries as tables and it doesn't necessitate changing your table names. Paul Choate DDS Data Extraction (916) 654-2160 -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of JKamgang@Yahoo.com Sent: Wednesday, January 16, 2008 9:32 AM To: SAS-L@LISTSERV.UGA.EDU Subject: How to access automatically in SAS a MS Access table name which has more than 32 characters? Dear All, Happy New Year , Please, I need your help in figuring out how to access in SAS a MS Access table name which has more than 32 characters. As this program merges 5 databases, I need to have almost everything automated. That's why I don't want to rename manually the table names. Please find the program below. Thanks in advance for your kind assistance, Jean Baptiste The program: - - - options VALIDVARNAME=ANY ; libname DBsource access 'S:\Data Management\External\Projects\MTCT-Plus\MTCT-Plus November 2007 Data\Programs\Data\MTCT-PlusData All.mdb'; /* Creating the Pediatric HIV result information additional */ data* pedResultadd ; set dbsource.'tblInfantHIVTestResultsAdditional'n (keep = testid studyid labid i2 i2_da i2_mo i2_yr i2a i2b i2text ); * run...

How to access automatically in SAS a MS Access table name which has more than 32 characters?
Dear All, Happy New Year , Please, I need your help in figuring out how to access in SAS a MS Access table name which has more than 32 characters. As this program merges 5 databases, I need to have almost everything automated. That's why I don't want to rename manually the table names. Please find the program below. Thanks in advance for your kind assistance, Jean Baptiste The program: - - - options VALIDVARNAME=ANY ; libname DBsource access 'S:\Data Management\External\Projects\MTCT-Plus\MTCT-Plus November 2007 Data\Programs\Data\MTCT-PlusData All.mdb'; /* Creating the Pediatric HIV result information additional */ data* pedResultadd ; set dbsource.'tblInfantHIVTestResultsAdditional'n (keep = testid studyid labid i2 i2_da i2_mo i2_yr i2a i2b i2text ); * run*; - - - The log - - 1636 1637 /* Creating the Pediatric HIV result information additional */ 1638 1639 data pedResultadd ; 1640 set dbsource.'tblInfantHIVTestResultsAdditional'n (keep = --------------------------------------------- 211 ERROR 211-185: Invalid data set name. 1641 1642 testid studyid labid i2 i2_da i2_mo i2_yr i2a i2b i2text 1643 ); Jean Baptiste, --- Jean Baptiste KAMGANG, Lead Data Manager - CDC Cameroon US Centers for Disease Control and Prevention Mutengene, SouthWest Province Cameroon, Africa E mail: JBKamgang@cm.cdc.gov. Tel: + 237 3 335 1690 Fax: + 237 3 335 1692 Cell: + 237 9 984 79 14 --- .. Ca...

Re: SAS and MS Access #2
On Wed, 5 May 2004 23:33:43 -0400, Peter Hruby <hrubyp@SYMPATICO.CA> wrote: >Hi SAS-L, > >I am wondering what choice do I have to access tables in MS Access datbase. >Can I use SAS/Access to ODBC or SAS/Access to PC Files or DDE or WB scripts? >I appreciate any experience and examples. Hi, Peter, If you have SAS/Access OLE DB, then it gives you more control. The following code shows that you can use an access table as if it is a sas dataset. If field name has embedded blanks, you can use something like, 'field name'n as the variable name. dbmax_text dataset opti...

Accessing 2 tables having same name but different databases ms-access
Hi, I have an application with MS-Access as backend and JSP/ tomcat as frontend/webserver. For getting better response time(as access is very slow compared to other RDBMS's), I have split the main DB into two DB's on different disks on my server for better performance.I have kept the table names same but changed the DB names. How do I access tables from both the DB's? This is what I have done; I made different connection to the respective DB's i.e. con and con2. When I want to access data, I am using 2 result sets for retriving data from the tables, but getting no result. Here is my code: rs2_1=stmt2_1.executeQuery("select * from Requests where Status='Released' AND LoginName='"+login+"' order by Req_No"); rs=stmt.executeQuery("select * from Requests where Status='Released' AND LoginName='"+login+"' AND Req_No> 5500 order by Req_No"); So, rs & stmt are for latest records and rs2_1 & stmt2_1 correspond the records from older DB. I think, I'm forgetting some small thing but cant get it. Please advice. What do you mean "no result"? Does "next()" return false for both rs2_1 an= d rs? If you're really trying to improve response time, you should probably be ru= nning the two queries in separate threads ... but as you acknowledge, Acces= s may not have as high performance as other RDBMSs. You would also see rel= iability adv...

Re: SAS/ACCESS
DBMAX_TEXT does the trick; thank you all who helped! Matt -----Original Message----- From: Jack Hamilton [mailto:jfh@stanfordalumni.org] Sent: Sat 1/24/2009 1:42 AM To: Pettis, Matthew (Prof II&RS) Cc: SAS-L@LISTSERV.UGA.EDU Subject: Re: [SAS-L] SAS/ACCESS - Oracle: setting field lengths in SAS datasets returned from passthrough Does the DBMAX_TEXT option do what you want? http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a003113591.htm -- Jack Hamilton jfh@alumni.stanford.org Videtis illam spirare libertatis auram On Jan 22, 2009, at 8:57 am, Matthew Pettis wrote: > Hi, > > > > I have a CLOB coming back from an Oracle passthrough query that is > >1024 > in length. How do I specify that my receiving variable in a SAS > dataset > be longer than 1024 characters to accommodate this? By default, I get > SAS thinking that this is 1024 characters, when it is really a CLOB > and > I want to set some large default length on this variable length... > > > > Thanks, > Matt ...

Re: Spaces in MS Access table name #2
Hi SB, I haven't tried this for a few years, but if I recall you can use the string literal syntax in SAS such as: 'tbl my access table'n in the SAS code to include the embedded spaces. Hope this is helpful. Mark Terjeson Senior Programmer Analyst Investment Management & Research Russell Investments 253-439-2367 Russell Global Leaders in Multi-Manager Investing -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of SB Sent: Tuesday, February 17, 2009 8:31 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Spaces in MS Access table name Hi all, I need to use some data stored in a MS Access table, so I am using a libname to connect to it (example: "libname mydb odbc dsn = myaccessdb;") That works fine but I have no idea how to use the table because of the spaces in the name ("tbl my access table"). I know SAS is going to "create" its own name for the table but how do I find out what that name is? I've tried looking the properties in the explorer window and proc datasets but all I see is the name with the space, which I know won't work. Renaming the table in access is not an option so I need to figure out how to handle it in SAS. (I'm using 9.2 for windows) Thanks! SB ...

Re: IMPORTING MS ACCESS DB TABLES INTO SAS
> -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On > Behalf Of Kevin F. Spratt > Sent: Friday, January 09, 2009 2:07 PM > To: SAS-L@LISTSERV.UGA.EDU > Subject: IMPORTING MS ACCESS DB TABLES INTO SAS > > I have a project in which much of the data has been entered into a MS > Access database. > > I have used Stat/Transfer version 7 to import the tables and > this works in that > all of the variables are read in with the labels specified within the > data base. > Variables with "internal' SAS formats (e.g., MMDDYY8.) are > also attached. > > However, when I import the tables using sas proc import, I > get the data and > the "internal SAS formats, but none of the labels. The SAS online > documentation > for Proc Import has not been very helpful. For example, I somehow > (probably from > the list) learned to include the usedate=yes option to remove the > time part of a > data when importing, but this option is not in the proc > import documentation. > > Since there are a lot of tables, I would prefer to use SAS import, or > some other SAS procedure (e.g., ODBC?) to get the data, but I would > like to obtain both > the labels and have both the internal SAS formats and the MS > Access defined > relationships between values and their meanings(e.g., Sex 1=Male > 2=Female -9=missing) > used to create study specific SAS forma...

Re: Exporting Multiple SAS files to MS Access #2
Kim - open the MDB with the Access libname engine and use proc copy. libname mdb 'file.mdb'; proc copy in=sashelp out=mdb mt=data; select c:; run; libname mdb clear; You can list the files in the select statement or just drop it if you are copying a whole library. The MDB has to already exist, unlike the Excel libname the Access libname does not create files - so you can create it by hand, or trick SAS into making it. data dummy; dummy=''; run; filename MakeMDB 'file.mdb'; proc export data=dummy outtable="dummy" dbms=access2000 replace; database=MakeMDB; run; filename MakeMDB clear; proc delete data=dummy; run; libname acctb access 'file.mdb'; proc delete data=acctb.dummy; run; libname acctb clear; hth Paul Choate DDS Data Extraction (916) 654-2160 -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Kim Brown Sent: Friday, December 05, 2008 9:47 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Exporting Multiple SAS files to MS Access Hi all, Export from SAS to MS Access: Is there a way to export 20 Data sets from SAS into MS Access without writing separate export statement? This is the syntax, I’m using now. PROC EXPORT DATA= K.HDHS_APR08 OUTTABLE= "HDHS_apr08" DBMS=ACCESS REPLACE; DATABASE="LOCATION"; RUN; Any help is appreciated, Thanks. Kim ...

Re: MS Access
DATA <whaterver>; is a 'replace/create' action. If you want to update, use UPDATE in PROC SQL. proc sql; update rads.monthly_status ... ; quit; Just because you are using UPDATE inside the datastep, it doesn't mean that it is not still creating a new dataset [or in this case, MSAccess table]. PROC SORT does the same thing; it creates a [theoretically new] dataset sorted in the order requested, with the original dataset as input. Now, here ODBC may be part of the issue; when I use LIBNAME EXCEL for excel, it requests Excel do the sorting [which should work...

Re: from SAS to MS Access without Proc Export #2
You got sas/access for odbc? It's easy to do w/that: libname mdb ODBC required = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\Documents and Settings\pardre1\Desktop\DocEMails.mdb" ; libname scratch "\\ctrhs-dbserver\warehouse\sasdata\crn_vdw\scratch" ; data scratch.DocEMails ; set mdb.DocEMails ; run ; -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Bill Droogendyk Sent: Monday, November 22, 2004 12:04 PM To: SAS-L@LISTSERV.UGA.EDU Subject: from SAS to MS Access without Proc Export Folks: I've an applicat...

Re: help SAS/MS ACCESS, general database questions #2
You're describing my project with the Department of Education! We had a dbase database and used a Clipper application to massage it into 'presentable' form. And I thought I was on the bleeding edge (back edge, that is) 8 years ago. As with everything, it depends on what your main purpose is. In my case, it was to disseminate the information to as many people as possible in a format that could be read by/into as many applications as possible. So, we converted our data to flat files and supplied an 'application' that generated sas code that would read the flat files into a sas dataset (complete with formats and labels), so the researcher could write their own code to run against the sas dataset. It sounds like your project isn't quite as ambitious, but the reason I include this example is that it's hard to predict what form data will take, say, 5 years from now. I'll bet you've already run into difficulties in using Clipper, and you'd like to avoid these kind of problems the next time you update/analyze your data. Sas has had some changes in going from one version to another--generally, upgrading to the next version involves updating your dataset to the next version's format. So, if you're purpose is to archive your data for the next iteration of your survey, unless you're going to be active in the data, sas might not be the best choice for data storage. Somewhat more stable would be something like SQL server (cheap, ...

Re: SAS table locked
cyril.faulconnier@CAPGEMINI.FR wrote: >I have a problem with a SAS table locked. Here is the description of my >problem. > >There is a SAS table that is filled by Power Center Informatica thru a >plug in. This ETL filled this table every minute. > >On the other hand, there is a AF program which check every 10 seconds >if new records have been inserted in this SAS table. In the AF program, >I developped a control that checked if this SAS table is used by an >another process. I use the open() AF function to proceed this test. >If the return code is different from...

Re: SAS/ACCESS Libname: Reading Tables with Names Longer than 32 #3
Dan, If you're correct he could capture the original name, use the Window's ren command to shorten it, and then reset the true filename. Art ------- On Tue, 25 Aug 2009 17:56:29 -0700, Nordlund, Dan (DSHS/RDA) <NordlDJ@DSHS.WA.GOV> wrote: >> -----Original Message----- >> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of >> Arthur Tabachneck >> Sent: Tuesday, August 25, 2009 5:39 PM >> To: SAS-L@LISTSERV.UGA.EDU >> Subject: Re: SAS/ACCESS Libname: Reading Tables with Names Longer than 32 >> Characters >> >>...

Re: SAS/ACCESS Libname: Reading Tables with Names Longer than 32 #6
Does this LIBNAME option have any effect on your problem? preserve_tab_names=yes On 8/27/09, Paul Miller <pjmiller_57@yahoo.com> wrote: > Hi Dan, and Art, and others, > > Yes, the problem is that the SQL Server table names are too long. My libname > > libname sqllib oledb provider=SQLOLEDB datasource='xxxx' user=xxxx password=xxxx properties=("Initial Catalog"=xxxx) > prompt=yes schema=dbo dbmax_text=32767; > > only allows me to read tables with names up to 32 characters long. > > The problem is that I'm routinely receiving SQl Server...

Re: SAS/access for Oracle and MS SQL server are different license? #2
The way they do this is pretty confusing (at least to me). But my understanding is that the Access products are licensed separately. The terminology "SAS/Access for RDBMS" is an all encompassing term to describe the product line and not the components of the product line. Many shops will license ODBC or OLE DB to access databases in a more generic way. Access for Oracle and Access for SQL Server are (I think) "native" drivers. I've never seen much difference in performance between native, OLE DB and ODBC to be honest. Finally, you can get a complete set of Access drivers for WPS as part of the base product. In other words, you don't have to pay additional fees to access the databases with WPS. The list of drivers for WPS can be found at: http://www.teamwpc.co.uk/products/wps/features/data_access Philip Rack MineQuest, LLC SAS & WPS Consulting and WPS Reseller Tel: (614) 457-3714 Web: www.MineQuest.com Blog: www.MineQuest.com/WordPress -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ya Huang Sent: 04/28/2009 3:03 PM To: SAS-L@LISTSERV.UGA.EDU Subject: SAS/access for Oracle and MS SQL server are different license? Hi there, I ran proc setinit and see we have SAS/ACCESS Interface to ORACLE, but not MS SQL server. Does it mean we need to pay the license of SQL server in order to access SQL server? I see people sometimes simply say "SAS/access for RDBMS", does it mean all RDBMS (Orac...

Re: SAS and MS Access
If you don't then you could perhaps consider bolting on your own JDBC interface in Java? (JDBC is free SAS ACCESS to PC is not...) On Thu, 6 May 2004 07:27:13 +0100, =?iso-8859-1?q?Ari=20Toikka?= <toikkari@YAHOO.CO.UK> wrote: >Hi, > >if you have ACCESS to PC Data files licensed, >you can use the import / export wizards (File / Import Data) >You can then save the generated program, which >might look for example like this: > >Proc import out = sasuser.test > datatable="h456test" > dbms =ACCESS97 replace; > DATABASE="T:\h345t...

Re: SAS access to Oracle tables
For our Windows system accessing a UNIX oracle table, I create a file on my local drive like this: %let oracle_string=%str(user="abcdef" password="abcdef" path="opath") ; substituting the real values for user,pass and opath of course. My program issues a %include 'c:\filename.sas' to load that file. In my passthrough to Oracle query I use connect to oracle (&oracle_string) ; Now this gets round what I find is the main concern of security depts (and I have this problem at EVERY client site I use SAS/ACCESS at). Their concern is having passwords ...

Re: SAS -> MySQL without SAS/ACCESS
XML, OleDb driver for SAS (free), ODBC driver from SAS (free). SAS/Access is only required to get data from a source, not the other way around. Alan Alan Churchill Savian www.savian.net -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of RolandRB Sent: Friday, June 13, 2008 1:50 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Q: SAS -> MySQL without SAS/ACCESS What is the easiest and neatest way to get a sas data set into MySQL without using SAS/ACCESS? I am hoping there are some useful URLs for this. ...

Re: SAS 9.2 Access Oracle reqirement on Client PC with Vista #2
Hello HXDAIC (and other SAS-L) Friend, While I think that SAS-L is a wonderful resource that more folks should use, installation questions similar to yours is really better directed to SAS Tech Support, by phone or web. It doesn't cost anything to ask, so please ask! - Michael At 08:13 PM 11/6/2008 -0800, HXDAIC <HXDAIC@GMAIL.COM> wrote: >Hi Howard, > >I searched in SAS installation center. > >http://support.sas.com/documentation/installcenter/sasinstall/9.2/win/win/config.pdf > >It mentioned: >Configuring SAS/ACCESS Interface to Oracle Software >Before you can use SAS/ACCESS Interface to Oracle software, the >following products are required: >� Base SAS software >� SAS/ACCESS Interface to Oracle software >� Oracle Server, Release 8.1.7 or later >� Oracle Client, Release 8.1.7 or later > >But I am not sure which version Oracle product can run on Vista? > >Thanks. >No virus found in this incoming message. >Checked by AVG - http://www.avg.com >Version: 8.0.175 / Virus Database: 270.9.0/1772 - Release Date: 11/6/2008 >8:23 PM Michael L. Davis Ambler PA E-Mail: michael.davis@alumni.duke.edu ...

Re: SAS/Access for Teradata #2
Sorry I left off the Other relevant information Windows platform I believe we are using TTU8.0 We have tested drivers outside of SAS and they appear to be working - at least that is what I am told. (I'm not the server person, only the designated SAS person so I am kind of in the middle, plus neither of us are Teradata sysadmin which adds an additional layer of complexity) I am definitely going to go back and see if I can verify this myself (seeing is believing as the theory goes) We have also defined the appropriate tdpid names in our Hosts file although we are configured so we should not have to. Session still hangs We normally run stuff with EG but have been testing libnames definition directly in DMS SAS Just a word - (neither fact nor fiction). My feeling is that there is something the way teradata is working/changed from previous version that is causing the problem but can neither verify nor deny so we are in the battle It's SAS versus It's teradata Thanks Linda ...

Re: SAS/ACCESS with Bloomberg #2
Thanks a lot Gordon! I am actually interested in the intraday (tick) data from Bloomberg. I was told that SAS/Access is able to download the data directly from Bloomberg. But, due to my limited knowledge on this, I have no idea how to get it done. You mentioned about the SAS/FTP, but I am not very familiar with this. So, could you please help me on this in your convenience? I would very much appreciate it if you could share your experience with me. Thanks again! Warmest Regards, Thomas ...

Re: SAS/Access Opinions #2
Hi All, Karl has some good comments. I can offer one slight tweak in that while going to each workstation and setting an ODBC token map is commonly done. For SAS programs it isn't always necessary, you can put it in your SAS string on-the-fly. e.g. locate NOPROMPT in the example archive posts: http://listserv.uga.edu/cgi-bin/wa?A2=ind0710B&L=sas-l&P=R39045 Hope this is helpful. Mark Terjeson Senior Programmer Analyst Investment Management & Research Russell Investments Russell Investments Global Leaders in Multi-Manager Investing -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of KarlK Sent: Friday, April 25, 2008 10:38 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: SAS/Access Opinions Ken, we've used SAS Access to ODBC for, gosh, 15 years now, both to SQL Server and to Oracle, and have had absolutely no problems, either for local or remote databases accessed over the internet. (Parenthetically, we have, on rare occasions, tried to use the pass thru facility to send SQL Server specific commands to the server, and ODBC seemed to have a problem with it, but, it's rare, and right now, I can't even remember what the problems were). Having said that, ODBC is pretty old and OLE DB is considered the more modern way to go, at least for Windows shops. There is one practical difference (as far as SAS is concerned) that weighs in favor of OLE DB: for ODBC, you need to have an ODBC data source set up f...

Re: Using SAS with large datasets (linking SAS and Access)
I have very little experience with using this but I have found that if you have SAS Access for PC Products licensed, just as you can issue a libname statement pointing to an Excel workbook, you can also issue one for an Access database. Try doing this and then look at your SAS explorer window where you will see an icon for the Access file and will then be able to look at the Access tables as if they were SAS data sets. Nat Wooding Environmental Specialist III Dominion, Environmental Biology 4111 Castlewood Rd Richmond, VA 23234 Phone:804-271-5313, Fax: 804-271-2977 ben.powell@CLA.CO .UK Sent by: "SAS(r) To Discussion" SAS-L@LISTSERV.UGA.EDU <SAS-L@LISTSERV.U cc GA.EDU> Subject Re: Using SAS with large datasets 06/23/2008 06:01 AM Please respond to ben.powell@CLA.CO .UK If you haven't already, export the data out of Access asap as mdb operations tend to be several orders of magnitude slower than SAS. Export to tab or csv and import into SAS. The export procedure from Access is relatively painless. If you've already done that and are still facing problems you should provide more info on variabl...