|
Bingo!
You only have to put two items into ODBC if you
are going to put an entry in. The DSN (a token name
of your choice), and the specified path-n-filename.
Then when using the ODBC "entry" just use the
DSN="token" )of course substitute whatever spelling
you entered.
It's really complicated (har har, just kidding). You
put in a token of ABC and it is associated with C:\XYZ.MDB
into the ODBC and then in your program's refer to ABC.
The ODBC goes and looks up C:\XYZ.MDB via the ABC token.
-or- you can skip the odbc lookup entry and use the noprompt=.
Mark
-----Original Message-----
From: Jim Agnew [mailto:Agnew@HSC.VCU.EDU]
Sent: Monday, May 14, 2001 8:45 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Microsoft Access data into sas data:
Does this mean the odbctoken is the actual name of the odbc data source, and
NOT the c:\...\filename.dsn??? maybe that's where I
went astray...
Jim
"Terjeson, Mark" wrote:
>
> Hello All,
>
> It is mentioned below, that ODBC needs to be installed and configured.
> Installed, yes. Configured (assumed to mean putting a token and
> its associated path-n-filename into the odbc lookup table), is optional.
>
> See below for the noprompt= option where you can set the desired
> path-n-filename right in your program code on-the-fly, with no odbc
> entry needed (odbc still needs to be installed and ready to be used).
>
> SAS Tip -- Syntax for connecting to ODBC (v6 & v8)
>
> ---------------------------------------------
> syntax for version 6.12 and 8.x
> ---------------------------------------------
>
> * get database table ;
> proc sql noprint;
> connect to odbc (dsn="odbctoken");
>
> create table mytable as
> select * from connection to odbc
> (select * from table1);
>
> disconnect from odbc;
> quit;
>
> ------------------------------------------
> syntaxes for version 8.x only
> ------------------------------------------
>
> * assign library reference ;
> libname hfs ODBC dsn=odbctoken;
>
>
----------------------------------------------------------------------------
> --------------------------------------
> above you will find ODBC connection method using ODBC table
> lookup
> <odbc entry required>
>
----------------------------------------------------------------------------
> --------------------------------------
>
>
----------------------------------------------------------------------------
> --------------------------------------
> below you will find ODBC connection method without using ODBC table lookup
> <odbc entry not needed>
>
----------------------------------------------------------------------------
> --------------------------------------
>
> ****************************;
> * *
> * User Defined Variables *
> * *
> ****************************;
>
> * full path of Access database ;
> %let mymdb=c:\mydir\mydatabase.mdb;
>
> ***************************;
> * *
> * End User Defined Area *
> * *
> ***************************;
>
> * parse out mdb path ;
> %let xtmp=%sysfunc(reverse(%trim(%left(&mymdb))));
> %let xtmp=%substr(&xtmp,%eval(%index(&xtmp,\)+1));
> %let dbpath=%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=&noprompt%str(;)SafeTransactions=0;
> %let noprompt=&noprompt%str(;)PageTimeout=5;
> %let noprompt=&noprompt%str(;)MaxScanRows=8;
> %let noprompt=&noprompt%str(;)MaxBufferSize=2048;
> %let noprompt=&noprompt%str(;)ReadOnly=0;
> %let noprompt=&noprompt%str(;)FIL=MS Access;
> %let noprompt=&noprompt%str(;)DefaultDir=&dbpath;
> %let noprompt=&noprompt%str(;)DBQ=&mymdb;
> %let noprompt=%bquote(&noprompt);
>
> * assign library reference ;
> libname mymdb odbc noprompt="&noprompt";
>
> Hope this is helpful,
> Mark Terjeson
> Washington State Department of Social and Health Services
> Division of Research and Data Analysis (RDA)
> mailto:terjemw@dshs.wa.gov
>
> -----Original Message-----
> From: Jim Agnew [mailto:Agnew@HSC.VCU.EDU]
> Sent: Monday, May 14, 2001 6:53 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: Microsoft Access data into sas data:
>
> Now, it would be really, really nice for people in production situations
to
> be able to specify the Odbc DSN right in the code, and
> bypass the mouse clicks!!!
>
> ;-)
>
> (I know this is a small point, but running and running the same code, it
> does add up, and to give out analysis tasks to data entry,
> it requires more training, security and passwords notwithstanding.)
>
> Jim
>
> By the way, Aldi, glad to help, even I learn from these threads..
>
> Aldi Kraja wrote:
> >
> > Hi,
> > First of all, thank you to all of you that provided thoughtful
> > suggestions!
> > For someone that will ask the same question here is a detailed solution:
> > With the following program I am creating a permanent sas dataset.
> > (sasuser.grepairdat)
> > The Microsoft Access table, one of many in a specific database (*dbm) is
> > named (Repair data).
> >
> > The promt will activate a pop-up menu, where the user can select the
data
> > base source.
> > The ODBC driver has to be installed in your PC and configured.
> >
> > (For more information see also the note on www.sas.com
> > (by searching for TS589C or Importing Microsoft Access tables to SAS
> > datasets)).
> >
> > libname sasuser 'e:\mywork\lule';
> > proc sql;
> > connect to odbc (prompt);
> > create table sasuser.grepairdat as
> > select * from connection to ODBC
> > (select * from "Repair data");
> > disconnect from ODBC;
> > quit;
> >
> > Thank you,
> > Aldi
> >
> > Aldi Kraja wrote:
> >
> > > Hi,
> > >
> > > I am working on a database kept in Microsoft Access. I want to turn it
> > > in sas datasets.
> > >
> > > Does anybody has a good advice for me how to migrate it to SAS?
> > >
> > > A very long one is open each table and save it as csv, and then read
> > > them all in SAS and from there arrange them in a better shape to my
> > > purpose of the work.
> > >
> > > Thank you in advance for any suggestions.
> > > Aldi
> > >
|
