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