f



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

---

..


0
JKamgang (3)
1/16/2008 5:31:30 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

1 Replies
695 Views

Similar Articles

[PageSpeed] 35

Can you make queries in MSACCESS (shorter names) that reference the
MSACCESS tables(longer names) ?
Then use SAS to go after the queries?


On Jan 16, 12:31=A0pm, "JKamg...@Yahoo.com" <JBKamg...@gmail.com> wrote:
> Dear All,
>
> =A0 =A0 =A0 =A0 =A0 =A0 Happy New Year ,
>
> =A0Please, 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=3DANY ;
>
> 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 =3D
>
> 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 =3D
>
> ---------------------------------------------
>
> 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: JBKamg...@cm.cdc.gov.
>
> Tel: + 237 3 335 1690
>
> Fax: + 237 =A03 335 1692
>
> Cell: + 237 9 984 79 14
>
> ---
>
> .

0
dmorgan (106)
1/16/2008 6:47:17 PM
Reply:

Similar Artilces:

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 #2
Hi Jean, It looks like your problem is that you've got a quote in a table name: Additional'n=20 If it is possible to rename that table, that might work. =20 Also, if you use the import feature in SAS, it will write code for you = that you can save and put in a program- you might try that to see if it = can handle this kind of file name. I'd rename the table in Access, = though- a quote in a table name is likely to contiously cause you = problems. -Mary ----- Original Message -----=20 From: JKamgang@Yahoo.com=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Wednesday, January 16, 2008 11:31 AM 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=3DANY ; 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.'tblInfantHIVTest...

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...

SAS/ACCESS Libname: Reading Tables with Names Longer than 32
Hello Everyone, =A0 I've recently learned about the SAS/ACCESS libname and manged to write the = code for reading SQL Server tables that appears below. I'm receiving databa= ses that=A0contain tables with names=A0substantially longer than the 32 cha= racter=A0limit and was just wondering if anyone=A0knows a way of dealing wi= th this. My hope was that there might be an option that would tell SAS to t= runcate any table names that go beyond the 32 character=A0limit,=A0but I ha= ven't been able to find anything like that. =A0 Thanks, =A0 Paul=A0 =A0 libname sqllib oledb provider=3DSQLOLEDB datasource=3D'xxxx' user=3Dxxxx pr= operties=3D("Initial Catalog"=3Dxxxx) prompt=3Dyes schema=3Ddbo dbmax_text= =3D32767; =A0 =A0 =A0=0A=0A=0A _________________________________________________________= _________=0AYahoo! Canada Toolbar: Search from anywhere on the web, and boo= kmark your favourite sites. Download it now=0Ahttp://ca.toolbar.yahoo.com. ...

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...

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: 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...

Is there anyone who knows how to convert MS Access tables to SAS datasets in Unix?
Hi, I've several huge mdb sitting on my red-hat linux box. I'd like to convert them into sas or stata without taking them off the server. I don't have a PC file server and a manual download to a PC or Mac and then converting is not an option. My Sas is 9.1.3. Can anyone help? thanks, Carolina ...

Accessing Oracle Tables from MS Access
How difficult is this? I'm having a tough time getting a simple select to work. I am using MS Access 2002 and Windows XP. What do I have to do to make a simple select statement like this work: select id from emp I have stumbled upon many websites although none straight to the point. Any help in how to get this done, or pointing me in the right direction of a book or website, would be greatly appreciated! berj Yes, I do have the table links working. But I did want to run the select for such things as grabbing the next number off of a sequence. As in: select test.nextval fro...

MS Access and SAS datasets
I'm trying to move several SAS datasets to a single MS Access database. When I use proc copy to move the data, I get the following note: NOTE: Copying SCFSAS.PT_MISC to SCFFAS.PT_MISC (memtype=DATA). NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. My problem is that I'm interested in keeping the variable labels and the formats as well. Is it possible for SAS to automatically create the "lookup tables" from the formats? Also, is there a way to keep the variable labels when I move from SAS to Access? David Neal Use PROC FORMAT with t...

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...

How to access MS-Access metadata tables (eg MSysObjects)
Dear all, I have an MS-Access MDB-file with hundreds of tables and want to know when they were created and/or modified. The MS-Access metadata table MSySObjects includes this information, but I have not managed to read it from SAS. (CLI describe error tells me that I have no reading access) --> Does anybody have a working code example for this? I have tried the libname ACCESS and Libname ODBC variants. Regards Dietrich -- DIETRICH ALTE, Dipl.-Statistiker, Dr. rer. med. Wiss. Projektmanager "Study of Health in Pomerania (SHIP)" Institut f�r Community Medicine - SHIP/KEF EMA-Universit�t Greifswald - Medizinische Fakult�t Walther-Rathenau-Str. 48, D-17475 Greifswald, Germany URL ship.community-medicine.de Phone ++49(0)3834-867713, Fax ++49(0)3834-866684 ...

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 -> 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. ...

SAS/Warehouse Administrator Problem?? or SAS/ACCESS problem??
hello, I've built a process in SAS/Warehouse Administrator, where i'm trying to extract distinct cities from the 3 tables(source schema) in Oracle into a new table (target schema) called CITY and again into UniqueCity (target schema) bcos of some redundancy. The process looks like this. Unique City | | mapping | | CITY ...

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...

Re: How to access MS-Access metadata tables (eg MSysObjects)
The answer is given here: Problem Note 7495: No read permission on System Tables when using SAS/ACCESS Interface to ODBC with a Microsoft Access ODBC Driver http://support.sas.com/kb/7/495.html Regards Dietrich Dietrich Alte schrieb: > Dear all, > > I have an MS-Access MDB-file with hundreds of tables and want to know > when they were created and/or modified. > The MS-Access metadata table MSySObjects includes this information, > but I have not managed to read it from SAS. > (CLI describe error tells me that I have no reading access) > --> Does anybody have a working code example for this? > I have tried the libname ACCESS and Libname ODBC variants. > > Regards > > Dietrich > -- DIETRICH ALTE, Dipl.-Statistiker, Dr. rer. med. Wiss. Projektmanager "Study of Health in Pomerania (SHIP)" Institut f�r Community Medicine - SHIP/KEF EMA-Universit�t Greifswald - Medizinische Fakult�t Walther-Rathenau-Str. 48, D-17475 Greifswald, Germany URL ship.community-medicine.de Phone ++49(0)3834-867713, Fax ++49(0)3834-866684 ...

SAS/ACCESS
Hi, =20 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... =20 Thanks, Matt ...

SAS / ACCESS
hi i hope that i will have an explicit answer I try to get relation between SAS ans a data base file(*.mdb)(office access) i wrote this command : libname name_library access path=' c:\mybase.mdb' and unfortunately i had this message in the log : Erreur : connect Erreur dans l'instruction libname. thanks Hi Charfi, Libname can be a maximum of 8 characters. Please reduce the library name and also check if you have license to SAS / Access if you face any more problem while assigning the library. The license can be checked by running the following code: Proc setinit; Run...

Import MS Access data to SAS
Hi all, What is a good way to transfer MA Access data to SAS. I used proc import (or import wizard), but some data formats were lost. Thanks! some code copied from my blog and hope it useful: *********************************************************** * METHODS TO READ DATA FROM ACCESS DATABASE INTO SAS * * DATE : JUL-30, 2005 * ***********************************************************; *********************************************************** * METHOD 1: OLEDB (SAS/ACCESS INTERFACE REQUIRED) * ***********************************************************; libname OLEmdb oledb init_strin...

MS Access
SAS 9.1, Windoz XP. Used ODBC administrator and created user DSN (tested - works). SAS can successfully connect: libname rads odbc datasrc='txdot_rads' user=******* pw=******** schema=dbo readbuff=32767; I can read the data sets just fine. However, I have never before had this situation where I need to update the MS Access tables from SAS. Tried: DATA RADS.MONTHLY_STATUS; UPDATE RADS.MONTHLY_STATUS SASUSER.TEST; BY STATE_PROJECT_NUMBER DESCENDING REPORT_MONTH; /* WHERE STATE_PROJECT_NUMBER EQ '0005-05-101';*/ RUN; The transaction data set, Sasuser.test, ...

How to Restrict SAS table Access in the same library
Hi, How can we restrict the users from accessing a particular table in the same library ?? Thanks | Sriman ...

SAS/Warehouse Administrator Problem?? or SAS/ACCESS problem?? #2
hello, I've built a process in SAS/Warehouse Administrator, where i'm trying to extract distinct cities from the 3 tables(source schema) in Oracle into a new table (target schema) called CITY and again into UniqueCity (target schema) bcos of some redundancy. The process looks like this. Unique City | | mapping | | CITY ...

Web resources about - How to access automatically in SAS a MS Access table name which has more than 32 characters? - comp.soft-sys.sas

Facebook Users Automatically Checked In To Events They RSVPed Yes To
A reader tipped us off that Facebook is automatically checking in users at events that they RSVPed they would attend. continued… New Career ...

Now Users Can Remove Contacts Automatically Saved by Facebook’s Friend Finder
Two weeks ago, many Facebook users began asking questions about curiously good recommendations suddenly appearing in Facebook’s “People You May ...

App Store - Attachments.me- Gmail inbox software to efficiently manage emails, automatically send/upload ...
Get Attachments.me- Gmail inbox software to efficiently manage emails, automatically send/upload files to cloud storage(Dropbox, Box, and G Drive), ...

Automatically organize your desktop icons into shaded areas called Fences! - YouTube
Fences® is the most popular desktop organization tool used by millions of users worldwide. Create shaded areas called "fences" to automatically ...

Lesbian mothers to be automatically recognised on birth certificates in SA - AdelaideNow Search Search ...
BOTH lesbian mothers will be recognised on their child’s birth certificate — not just the woman who gives birth — under changes approved by Parliament’s ...

Optus switches on customers' global roaming automatically
Optus has begun automatically switching on global roaming for many of its mobile customers, the latest move in an increasing trend that has consumer ...


Emailing porn at work not automatically sackable, court finds
Australia's federal court has upheld a ruling that emailing pornography in the workplace is not automatically a sackable offence.

Shazam iPhone app now listens for music, TV shows automatically
Shazam has updated its iPhone app to tag songs, TV shows and more on its own, no longer requiring users to open the app and tap a button.

App of the day: Human for iPhone automatically tracks your movements
Human for the iPhone is an activity tracker that automatically distinguishes between different types of movement.

Resources last updated: 3/17/2016 1:43:53 PM