f



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



--
===============================
WenSui Liu
Statistical Project Manager
ChoicePoint Precision Marketing
(http://spaces.msn.com/statcompute/blog)
===============================
0
liuwensui (937)
1/16/2008 5:34:00 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

0 Replies
812 Views

Similar Articles

[PageSpeed] 8

Reply:

Similar Artilces:

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

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

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

Re: Q: SAS -> MySQL without SAS/ACCESS
If the dataset is not huge, then converting the database table into a CSV file may be the way to go. MYSQL's own CSV read/write facility is extremely efficient (or was when I used it three years ago). On Fri, Jun 13, 2008 at 3:50 PM, RolandRB <rolandberry@hotmail.com> wrote: > 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. > ...

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

Re: MS Access and SAS datasets #6
You are right, the "slightly" was an understatement on my part.(I guess I was being nice.) I believe ACCESS is limited to 256 (or 255 I forget which) columns so I am required to do a bit of reshaping anyway. I will be working with(reshaping) the data in SAS and then move it into ACCESS. I feel much more comfortable tweaking it in SAS and then moving it to ACCESS. David -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Howard Schreier <hs AT dc-sug DOT org> Sent: Tuesday, May 24, 2005 5:26 PM To: SAS-L@LISTSERV.UGA.EDU Subject: R...

Re: MS Access
Hit Send too fast. Forgot to mention that these are all licensed: ---SAS/ACCESS Interface to ORACLE ---SAS/ACCESS Interface to SYBASE ---SAS/ACCESS Interface to PC Files ---SAS/ACCESS Interface to ODBC ...

Re: MS Access and SAS datasets #5
I suspect that you don't need a "slightly" different approach. You probably need a significantly different one. Wide tables are clumsy to work with in SAS, but I think in Access the problems will be much more severe. I would think that the occasion of a port from one platform to another provides the opportunity to reshape and streamline. You will probably find it easier to do this before porting, rather than after. On Tue, 24 May 2005 09:26:21 -0800, David Neal <afdbn@UAA.ALASKA.EDU> wrote: >Thanks for the input. Unfortunately, the datasets are quite wide. One >o...

Re: Newbie
On Tue, 11 Nov 2003 19:09:45 GMT, Zalek Bloom <ZalekBloom@HOTMAIL.COM> wrote: >I am learning SAS and want to create a report from an Access table. >Can sameone post a sample of a such program? >Thanks, > >Zalek Hi, Zalek, How about this one? The final.mdb has a table called FinalStatus. The following makes a frequency table of the [status] field in the table. Cheers, Chang libname final oledb provider = "Microsoft.Jet.OLEDB.4.0" properties=('Data Source'='c:\final.mdb') preserve_tab_names=yes ; proc freq data=final.FinalStatus(dbm...

Re: MS Access and SAS datasets #4
MS Access also has a CAPTION field that behaves more like a SAS variable label. However, I have not found a way to write to that field from SAS. Ed Edward Heaton, SAS Senior Systems Analyst, Westat (An Employee-Owned Research Corporation), 1600 Research Boulevard, RW-3541, Rockville, MD 20850-3195 Voice: (301) 610-4818 Fax: (301) 610-5128 mailto:EdHeaton@Westat.com http://www.Westat.com -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Howard Schreier <hs AT dc-sug DOT org> Sent: Tuesday, May 24, 2005 11:21 ...

Re: How to access a MS SQL file in SAS?
Are you opening your .csv file with Excel? that would truncate the number of records. You may want to create a libname for the SQL table or use Proc SQL pass-through. You'll need the SAS Access for your database, such as SAS Access for Relational Databases to use ODBC. If you create a libname for the table you can read it directly in either a datastep or Proc Sql using SAS commands. If you use Proc SQL pass-through you will need to be aware of how Transact SQL works and you'll need an odbc connection. Example: Pass-through where yourdatasourcename, etc are the names of the database ...

Re: MS Access and SAS datasets #7
David, I believe SQL Server Express (which is free) can handle 1024 columns. You may want to consider that approach if you have to move it anyway. Thanks, Alan Savian "Bridging SAS and Microsoft Technologies" -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of David Neal Sent: Tuesday, May 24, 2005 6:54 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: MS Access and SAS datasets You are right, the "slightly" was an understatement on my part.(I guess I was being nice.) I believe ACCESS is limited to 256 (or 255 I forget which) column...

Re: Spaces in MS Access table name
Say you have a dataset named: My Dataset You can access it with: 'My Dataset'n so, in this case it would be mydb.'My Dataset'n This is necessary in Excel libname all of the time ($ is at the end of all sheet names). Or, my preferred option, berate the client/coworker who gave you the badly named access table until they promise never to do so again ... :) -Joe On Tue, Feb 17, 2009 at 10:30 AM, SB <shannon.blumer@us.ngrid.com> wrote: > 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: SAS/ACCESS
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: Convert Access Datetime to SAS Datetime when Access data type #4
don't know what you have in ACCESS, but you should not mix DATE and DATETIME! The one is a number=days from 01jan1960, the other is SECONDS since 01jan1960:00:00:00. In your code you use FLOOR which seems to be a thing with 0.123 seconds (?) to cut the 0.123 off. That seems to be really a DATETIME value (DATE values are integers without fraction). Together with "30dec1899"d (a date- constant=days) that is nonsense. I think adding "30dec1899:00:00:00"dt might be better. What I'd do: store the datetime 01jan1960:00:00:00 in ACCESS and have a look what is the number...

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

Web resources about - Re: How to access automatically in SAS a MS Access table name - 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:30 PM