f



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
>
0
snoopy369 (1724)
2/17/2009 4:49:05 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

0 Replies
544 Views

Similar Articles

[PageSpeed] 42

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

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

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 I believe its something like this. data sasset ; set 'access table with spaces'n ; run; Ray Wallace Institutional Research Washington State University On Feb 17, 8:30=A0am, SB <shannon.blu...@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 =3D > myaccessdb;") =A0That works fine but I have no idea how to use the table > because of the spaces in the name ("tbl my access table"). =A0I know SAS > is going to "create" its own name for the table but how do I find out > what that name is? =A0I've tried looking the properties in the explorer > window and proc datasets but all I see is the name with the space, > whi...

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

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 V9.1 SQL table name re-use issue
Thanks to Peter and Bas and I will start using undopolicy=none should I like to replace a table. Regards, T J On Wed, 7 Sep 2005 12:08:10 -0400, Peter Crawford <peter1.crawford@CITIGROUP.COM> wrote: >On Wed, 7 Sep 2005 11:42:25 -0400, T J <tjpush@YAHOO.COM> wrote: > >>Hi everyone, >> >>Before SAS 9 I have lots of SQL codes that re-uses an existing table name >>and there was no warning msg. >>-------------------------------------------------------- >>data A; do i = 1 to 5 ; x= ranuni(0); output ; end; run; >> >>data b; do i ...

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 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: experiences with changing a name space or operating two different name spaces?
>>>>> ">" == =?iso-8859-1?q?Hanspeter=20Hagg?= <iso-8859-1> writes: >> hi all, does anyone have experiences with changing a name space >> or operating two different name spaces? Of course! >> the only way of achieve such a mayjor change is by operating a >> parallel dns-infrastructure and to migrate applications and >> systems via lifecycle processes. so does anyone have >> experience with operating two different name spaces? Of course! Though to maintain everyone's sanity, the best approach is to move everything to a single name space. >> i think the new dns-installation is the easiest part of the >> entire change. That may well be true since changing name spaces does not have to mean changing DNS infrastructure. The same servers can serve both. Moving or merging name spaces is easy in principle. A little bit of scripting can generate new zone files from old ones. Or both sets of zone files from some sort of metafile or back-end database. However the detail is a different story. Domain names get embedded in application software. And in configuration files and ACLs. And shell scripts. And users' .login and .cshrc files. Old URLs get stored in web browser's bookmarks. Old domain names live in HTML and links from other web sites. Things like software licences and certificates have domain names embedded in them. Cle...

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: MS Access 2003: Linked Tables
Dear Informixers, a while ago I posted a question about a problem linking IDS 7.31UD8 tables into MS Access 2003 using the Informix ODBC driver from I-Connect 2.81TC3. This driver is in the file "iclit09b.dll" in the INFORMIXDIR\bin directory and has version # 3.82.0.11811. In the meantime, I found that the ODBC driver from the previous Connect version 2.81TC2 works; it has version # 3.82.0.11600. Just copying the older dll over the newer one did the job. I still wonder why IBM/Informix cannot get their act together and supply ODBC drivers that just work. From sporadic postings in this group, I gather that even the ODBC driver from the newest 2.90 program suite still has problems, especially with Access. However, from the low number of postings about this topic, I assume that not many peoply bother using Informix's driver... Regards, Richard Richard Spitz wrote: > I still wonder why IBM/Informix cannot get their act together and > supply ODBC drivers that just work. From sporadic postings in this > group, I gather that even the ODBC driver from the newest 2.90 > program suite still has problems, especially with Access. However, > from the low number of postings about this topic, I assume > that not many peoply bother using Informix's driver... I don't use the ODBC drivers with Access so can't comment on your original problem. I somewhat agree with the sentiment about CSDK. We have a serious ...

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 #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: 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: synchronizing MS access and postgresql tables
The big gotcha on linked tables in Access is that Access will "Background f= etch" the entire table of any objects that reference that table on an open = form. They try to throttle it so you won't notice, but you might. Also, if you have Access queries that reference linked tables, Access will = fetch the entire contents of all referenced tables before performing any jo= ins/restrictions. That can really suck. Another poster alluded to this when they suggested moving objects into Post= gresql. Things like views. It works fine, generally. Expect lots of harmless ...

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: deleting records from MS Access table
Jeremy: Would you consider a slightly different strategy? - copy the metadata shell of the table; - delete the table; - rename the empty shell of the table to the name of the original table; - append the new data to the shell. The SQL create table new like old; copies the shell of the table. The DROP TABLE old; query deletes the old table. CREATE TABLE old FROM new; renames the new table. Enclose the queries that operate on metadata (do not yield a tabular object) in an EXECUTE() function. I suspect that this strategy will work faster than one that requires deletions at the row ...

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: Exporting Multiple SAS files to MS Access
Easiest way I can think of is to use a macro: %macro exporttoaccess(dset); PROC EXPORT DATA= K.&dset. OUTTABLE= "&dset" DBMS=ACCESS REPLACE; DATABASE="LOCATION"; RUN; %mend; and then you call it once for each table you want exported. It's technically doing a separate export, though you don't have to rewrite the code each time. (Note, it assumes all of your datasets are in library K - if this is not true it needs to have a library macro variable passed as well). I suspect you could also use LIBNAME to do this, but I am not familiar with how to use LIBNAMEs with access so I'll let someone more export to answer that. -Joe On Fri, Dec 5, 2008 at 11:47 AM, Kim Brown <kibrown@ladhs.org> wrote: > 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: Finding Table names in an Access *.mdb file
Yes--set a sas/access libname to the mdb & then query the table names out of dictionary.tables w/proc sql. Like so: libname mdb ODBC required = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\Documents and Settings\pardre1\Desktop\MENUReporter.mdb" ; proc sql ; create table mdb_tables as select memname from dictionary.tables where libname = 'MDB' ; select memname from mdb_tables ; quit ; Note that that gets you msaccess' system tables too--to screen those out, use this where clause: where libname = 'MDB' AND memname not l...

Web resources about - Re: Spaces in MS Access table name - comp.soft-sys.sas

Space - Wikipedia, the free encyclopedia
This article is about the general framework of distance and direction. For the space beyond Earth's atmosphere, see Outer space . For the keyboard ...

My virtual living room: Setting up a social VR space in the house
... call "home-appropriate"—meaning, it's pretty ornate and complicated, but not so much that you need to dedicate an entire lab or office space ...

Developers seek to give Columbia County 'downtown' space
With boundless recreational opportunities, stellar schools and a swelling population of upwardly mobile residents, Columbia County seems to have ...

Bezos unveils his plans for space program
KENT, Wash. — Long before he ever conceived of the idea to sell books on the Internet, he was obsessed with space. It started at age 5 when he ...

Year in space astronaut hangs up his spacesuit, retires
Business Insider Year in space astronaut hangs up his spacesuit, retires Business Insider FILE - In this Wednesday, March 2, 2016 photo provided ...

European Space Agency launches Mars mission
The European Space Agency has just launched its mission to Mars. Its aim: find evidence of rare gases to show whether there has ever been life ...

European, Russian space agencies launch mission to Mars
DARMSTADT, Germany (AP) — Europe and Russia have launched a joint mission to explore the atmosphere of Mars and hunt for signs of life on the ...

Folding, full-size keyboard won’t hog bag space
iClever Tri-folding Keyboard We’re always looking for ways to take our tech on the go without filling up our limited bag space, and we’ve found ...

STX Entertainment Swaps Around ‘Bad Moms’ & ‘The Space Between Us’
STX Entertainment is swapping the release dates between Bad Moms and The Space Between Us . Bad Moms aka the untitled Lucas/Moore comedy is moving ...

How Satellites Find Shipwrecks From Space
Distinctive linear plumes of these particles extend as far as 2.5 miles downstream from shallow shipwreck sites.

Resources last updated: 3/16/2016 11:41:26 AM