Rob:
MS Access imports data easily from dBase (*.dbf) files. I see no advantage
in moving to SAS for that feature, especially if you would prefer to use the
basic DE form controls in MS Access to develop what should be a simple DE
capability. Although ODBC transfers of data from MS Access (*.mdb) databases
has some pitfalls, as does OLEdb or other middleware (in particular,
conversions of integers to floating point numbers, date/time values, and
nulls/missings), a bit of Q/C will help you iron out the wrinkles and set up
a reliable connection. It often helps as well to separate a data collection
operation from a data analysis group. Even on a small scale, periodic
publishing of analytic datasets usually works better than giving researchers
continuous access to primary data files. Continuous access makes it more
difficult to check estimates for consistency and to replicate earlier
results. A separate database for data collection operations also makes it
easier to implement a sound database design for operations. Data analysts
prefer to restructure data to fit analytic procedures, and have little
appreciation of data integrity constraints and data validation.

Fortunately you do not have to choose between MS Access (or another DBMS)
and SAS. SAS has particular value on the analytic side of your project(s).
Access to a data source with a good relational database design guarantees
that a SAS programmer can restructure data quickly and accurately into the
structures that analysts need. Analysts will likely want to 'snapshot' data
into SAS datasets so they can run frequencies and create statistical models.


Starting from where you are, I would agree that you should keep it simple at
this stage. When the project evolves to the point that you will need Web DE
and distributed databases, SAS may have the integrated system components
that you will need. Of course, others on the list have different views and
may convince you to develop a SAS database. I have done that many times as
well, but I usually reserve that effort for very large and complex databases
that serve as data warehouses for disparate data from many different
sources.
Sig

-----Original Message-----
From: Rob Cheshire [mailto:rob.cheshire@NOAA.GOV]
Sent: Tuesday, March 02, 2004 10:27 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: help SAS/MS ACCESS, general database questions


Hi to all,
        I need some help deciding on the best database system for our
specific application. OPTIONAL INFO:  We currently have 2 separate projects
that need to be updated from dos-based dBase.  I'll only describe one of
these but the other is comparable in size.  The project currently has about
32 files.  The 2 largest are about 30,000 records and 5 columns include data
from the last 30 years.  The other 30 files are created by year and are
about 1000 records and 10 columns.  There are only 2 or 3 people keying in
data, never at the same time.  We also have a sonic digitizer (measures
distance between age rings on fish scales) from which data is fed into the
dBase form.  I think the data transfer program is written in C and the dBase
form is written in Clipper. We currently have MS Access and SAS.  My
suggestion was to design the same type of user entry forms in MS Access and
place the files on a shared drive on our network server (already in place
and routinely backed up).  If the amount of data or number of users
increased we could consider going to a database server with Access as the
"front end."  Most of the analysis is performed in SAS which can readily
import Access tables. I was met with some opposition: SAS/MS ACCESSS/SERVER
QUESTIONS: There was a strong objection to using ODBC to get data into SAS
(when discussing database servers).  Does SAS Import wizard use ODBC? Are
there inherent problems with ODBC?  Is there any advantage to having data
analyzed in SAS in SAS data sets?  Any problems adding a database server in
the future? SAS QUESTIONS: Some of the work creating the SAS data entry
forms is already completed (by someone outside our agency who is unavailable
now).  I did not know SAS even had database  software until I started on
this project.  I currently have used SAS only for statistics and have only
the intro. Programming course.  What SAS training would I need to get up to
speed on creating forms and using SAS to manage this type of database?  The
SAS packages we currently have are:  STAT, AF, ETS, IML, SHARE GIS,
SHARE*NET, INTEGRATION TECH.,APPDEV STUDIO, AND ACC/ORACLE.  Would we need
to purchase other packages to use SAS as our database software?

The last piece of advice given to me at the meeting was "keep it simple."
Obviously I am not a database administrator (nor am I paid like one).  I am
an entry level biologist that has used MS Access to manage a desktop
database.  I don't mind learning the SAS database system if it provides a
better solution.  OR IS THERE A BETTER SOLUTION OUT THERE?  Should we
consider hiring a consultant? I am posting this to both the SAS and ACCESS
user groups to get the range of responses. Thanks in advance and sorry for
the long post, Rob