f



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 formats.
>
> Any SAS-Lers out there that have encountered and conquered
> this problem?
>
> Thanks in advance to any help.
>
> I'm running SAS 9.2 on Windows XP and the MS Access is 2003.
>
>
>
Kevin,

I use ODBC for reading and writing db files on a regular basis; mostly Oracle and MySQL but also have worked with MS Access .mdb files as well.  It is not clear to me how your Access files are structured.  How are the SAS formats and labels stored in the Access db files?  I am sure there are others on the list who may be able to help you more than me, but if you describe your data with a little more detail I am certain someone will be able to help.

Dan

Daniel J. Nordlund
Washington State Department of Social and Health Services
Planning, Performance, and Accountability
Research and Data Analysis Division
Olympia, WA  98504-5204
0
NordlDJ (920)
1/9/2009 10:56:50 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

0 Replies
576 Views

Similar Articles

[PageSpeed] 6

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

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

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: Importing to SAS from MS Excel
Bangali, The (where=3D(varname NE " ")) is a standard where statement used as a data set option. The syntax in your example contains commas and would result in an error. (where=3D(Name and Sex and Age and Height and Weight ) is the same as (where=3D(Name ne ' ' and Sex ne ' ' and Age ne ' 'and Height ne ' ' and Weight ne ' ') My interpretation of your question was how to import the spreadsheet excluding the rows with missing data. When I suggested using (where=3D(varn= ame NE " ")) my intention was to test a key variable for missin...

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: 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 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
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: 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 #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 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: SAS/MS Experts: Pass Protecting Excel Sheet Created By SAS
On Fri, 26 Jan 2007 13:09:25 -0500, Gerry <gpauline@PACE.EDU> wrote: >SAS-Xperts: > > > >Is there a way password protect an excel file created by SAS ? Preferably as >part of the application's processing. Excel files themselves can be >password protected, but is there a way for SAS to engage this, or a third >party utility available ? > > > >We needed to do the same thing with PDF files, which I'm told will be a new >feature (password protecting) in v9.2. We found a utility that operates from >the command that will accomplish what we wa...

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: SAS/MS Experts: Pass Protecting Excel Sheet Created By SAS
Hi Gerry, Currently, Excel allows saving in several formats. Of course the .xls native format contains everything. The html and xml formats of Excel storage do not contain any of the "binaries", i.e. the security pieces, pictures, or charts. Virtually everything else related to the spreadsheets, data, and formatting cosmetics are there in the html and xml versions. The html allows *some* control when created literally by your SAS program. The xml allows your SAS program to control all the data,sheets,formats,cosmetics,print parameters,paging parameters, etc. The html can save off...

Re: What r the data types in SAS? in Base SAS , and SAS SQL
> From: Amar Mundankar > Sent: Tuesday, July 21, 2009 8:10 AM > To: sas-l@uga.edu > Subject: What r the data types in SAS? in Base SAS , and SAS SQL > > Hi all, > What are the different data types in Base SAS and SAS SQL?? character, lengths from 1 to 32,000+ numeric: lengths from 2 to 8 dates are a subtype of numeric and are identified by their date, datetime, or time formats the new proc TSPL (Table Server Processing Language) supports ANSI data types: bigint, tinyint, etc. http://support.sas.com/documentation/cdl/en/tsag/30878/HTML/default/a003 065339.htm http://s...

Re: SAS/MS Experts: Pass Protecting Excel Sheet Created By SAS #2
SAS-Xperts: I'd like to publicly acknowledge the following SAS-L members for there assistance concerning my inquiry about password protecting an Excel spreadsheet: Mark Terjeson (MTerjeson@Russell.Com) Data _NULL_ (datanull@gmail) Howard Schreier (Howard Schreier <hs AT dc-sug DOT org>) Both the mysterious Data _NULL_ and Howard (along with SI Tech support) suggested the DDE path. Data _NULL_ sent sample code, which I was not able to successfully run - I'm not at all Excel savvy, which is more than likely the cause. Mark noted some changes coming down the line from M$...

Re: from SAS to MS Access without Proc Export
Bill, proc export data=sasuser.cust outtable="customers" dbms=access97 replace; database="c:\myfiles\mydatabase.mdb"; run; HTH Toby Dunn -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Bill Droogendyk Sent: Monday, November 22, 2004 2:04 PM To: SAS-L@LISTSERV.UGA.EDU Subject: from SAS to MS Access without Proc Export Folks: I've an application running from my PC via SAS Connect to the Mainframe that harvests some MF data, downloads the data and uses proc export to build an MS Access file. Now and agai...

Re: Importing to SAS from ACCESS, limiting columns #3
Hi Alice, One alternative is to use PROC SQL which you can write to send a pass-through query over to the MSAccess database that *runs there* and then only your reduced results is returned over the wire to your PROC SQL dataset output. Hope this is helpful. Mark Terjeson Senior Programmer Analyst, IM&R Russell Investment Group Russell Global Leaders in Multi-Manager Investing -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Alice Loeffler Sent: Wednesday, April 05, 2006 11:01 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Importing to SA...

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: MS Access
Thanks Joe! Your posts always offer good info and they help me learn a lot. I only tried the data step update because it was something I read about in one of the many helpful SAS papers out there. That explains the log 'replace' note versus an 'update' note. So, here's what I did. I made a little temp data set with 2 obs and 3 variables: State_Project_Number Report_Month Payment_Disburse 0005-05-101 06-2009 111111 0008-14-110 06-2009 111111 Next, used SQL (my SQL knowledge being very limited): PROC SQL; UPDATE RADS.MONTHLY_S...

Re: Is it Possible to import directly into SAS a Access query
Hi Lei, You can read and write the tables directly. You can read queries directly. * READ MSACCESS TABLES FROM PC SAS ; %let mdbname=c:\temp\db1.mdb; * parse out mdb path ; %let xtmp=%sysfunc(reverse(%trim(%left(&mdbname)))); %let xtmp=%substr(&xtmp,%eval(%index(&xtmp,\)+1)); %let mdbpath=%sysfunc(reverse(&xtmp)); * build ODBC connection string ; %let noprompt=DRIVER=Microsoft Access Driver (*.mdb); %let noprompt=&noprompt%str(;)UID=admin; %let noprompt=&noprompt%str(;)UserCommitSync=Yes; %let noprompt=&noprompt%str(;)Threads=3; %let noprompt=&n...

Web resources about - Re: IMPORTING MS ACCESS DB TABLES INTO SAS - comp.soft-sys.sas

Viral Channels: Hi5 Adds Contact Importing to App Invite Flow
... social networking platform I’ve seen, hi5 announced tonight that it is adding the ability for users to invite non-hi5 friends to apps by importing ...

YouTube - iPad importing photos from floppy disk.
You are using an outdated browser, which YouTube no longer supports. Since some features on YouTube may not work, you are viewing a lightweight ...

Tony Abbott considers importing US-style school program with private industry involvement
Tony Abbott is considering an unprecedented Americanisation of the school education system with radical changes that could see HECS-style fees ...

Importing fuel will prove better for the consumer
Importing fuel will prove better for the consumer

Qantas airline steward faces jail for importing drugs
A former Qantas airline steward who worked business class flights to America for several months with a ''raging'' ice habit faces jail for importing ...

Importing more fads from failed systems
Fast-tracking talented professionals into the classroom is yet another imported fad from England and the US - both of which are failed education ...

Urine test fail for man facing drug importing sentence
A Canberra man who pleaded guilty to importing a commercial quantity of drugs to his front door has admitted&nbsp;taking a banned drug&nbsp;weeks ...

Hong Kong man charged with importing 150kg of crystal methamphetamine into Sydney
A Hong Kong national is charged with importing 150 kilograms of ice following a joint-agency investigation in Sydney.

Man, 91, charged with importing cocaine hidden in soap boxes into Australia
A 91-year-old Sydney man has been charged with importing cocaine hidden in soap into Australia, prompting police to warn travellers to beware ...

Google Drive adds support for importing OpenOffice (ODF) file formats
Google announced today that it’s now officially supporting ODF files in Google Drive allowing users to import all three major ODF file formats. ...

Resources last updated: 3/16/2016 1:50:03 PM