f



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 

0
3/2/2004 3:26:59 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

3 Replies
484 Views

Similar Articles

[PageSpeed] 36

Without having enough time to analyze excatly, what are your needs:
if there are no important reasons why the data must be keept in MS Access:
why you don't keep it all in SAS? If you are analyzing in SAS, there is no
real reason to have any other DB to hold the data.
For users you have many possibilities to give them comfortable possibilities
to enter data, in or outside SAS. In SAS, the most easy idea is to use
SAS/FSP. But that is not necessary. You can use nearly any tool for data
entry and bring the (standard formatted) data into SAS.
To get a consultant for realizing a proper solution might be a good idea. It
sometime is not easy trying to learn to program in SAS by developing a
productive solution, because it might be not easy to get it in time with all
the obstacles, there might be in your way. If you have time (and money)
enough, you should take the chance to learn how to do some steps further in
SAS programming together with a good consultant.



On Tue, 2 Mar 2004 10:26:59 -0500, Rob Cheshire <rob.cheshire@NOAA.GOV> wrote:

>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
0
ghellrieg (838)
3/2/2004 5:23:09 PM
You're describing my project with the Department of Education!  We had a
dbase database and used a Clipper application to massage it
into 'presentable' form.  And I thought I was on the bleeding edge (back
edge, that is) 8 years ago.

As with everything, it depends on what your main purpose is.  In my case,
it was to disseminate the information to as many people as possible in a
format that could be read by/into as many applications as possible.  So, we
converted our data to flat files and supplied an 'application' that
generated sas code that would read the flat files into a sas dataset
(complete with formats and labels), so the researcher could write their own
code to run against the sas dataset.  It sounds like your project isn't
quite as ambitious, but the reason I include this example is that it's hard
to predict what form data will take, say, 5 years from now.  I'll bet
you've already run into difficulties in using Clipper, and you'd like to
avoid these kind of problems the next time you update/analyze your data.
Sas has had some changes in going from one version to another--generally,
upgrading to the next version involves updating your dataset to the next
version's format.  So, if you're purpose is to archive your data for the
next iteration of your survey, unless you're going to be active in the
data, sas might not be the best choice for data storage.  Somewhat more
stable would be something like SQL server (cheap, functional for the size
data you're talking about) and can serve as a back end to MS Access if
you'd like.  You can also get slick with this and write a visual basic
front end to enter/edit your data, and it plays nicely with MS Access and
SQL Server.

One of my favorite questions is 'What is it you want?'  Do you want a
pretty way to reference or key in data by hand, and analyze it on the back
end?  Do you want a way to analyze the data prior to adding it to the
database (throw out outliers, cleanse, etc.)--the more complex this part
gets, the harder it is to build in data edits/outlier weeding with MS
Access/visual basic/SQL server.  Do you just want a convenient place to
store the data temporarily (more or less) while you perform your analysis,
and worry about archiving later?  Then sas is your best bet--just use sas
datasets.

To my knowledge, sas is fairly efficient using odbc drivers, and you
shouldn't have any issues reading the data from an external source,
regardless of where it's stored (can handle everything from sql server to
oracle to db2 to informix), and given the relatively small size of your
files (and I understand you may still be using a 386 machine if you're
still using Clipper  :-(  ), shouldn't have many performance related issues.

HTH,

CH


On Tue, 2 Mar 2004 10:26:59 -0500, Rob Cheshire <rob.cheshire@NOAA.GOV>
wrote:

>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
0
3/2/2004 7:10:46 PM
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
0
HERMANS1 (2683)
3/2/2004 8:39:38 PM
Reply: