f



Re: help SAS/MS ACCESS, general database questions #2

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
comp.soft-sys.sas 142827 articles. 3 followers. Post Follow

0 Replies
1126 Views

Similar Articles

[PageSpeed] 42

Reply:

Similar Artilces:

Re: help SAS/MS ACCESS, general database questions
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 yea...

Re: help SAS/MS ACCESS, general database questions #3
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 collecti...

help MS ACCESS\SAS, 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...

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

Re: SAS 9.1.2 difference from SAS 8.2 Proc import from MS Access
Hi, We are having the same problems. We have some macros that are srewed up. Is there a way to configure SAS 9 so that it can use version 8 values when importing from MS access? Thanks very much. wz ...

SAS 9.1.2 difference from SAS 8.2 Proc import from MS Access
The following code produces different results between version 8.2 and version 9. Not really wrong in either - just different. The file truefalse.mdb has two columns, the first is a "YesNo" field with the value in the first row checked and the value in the second row not checked. With 8.02 TS02M0 a "YesNo" field imports as -1 when true With 9.1.2 TSLevel1M2 a "YesNo" field imports as 1 when true. Both -1 and 1 evaluate as TRUE, but 1 is more consistent with the result from SAS logical operators. %let version=8; /* this was run under Windows 2000 */ PROC IMPOR...

Re: How to access automatically in SAS a MS Access table name #2
Hi Jean, It looks like your problem is that you've got a quote in a table name: Additional'n=20 If it is possible to rename that table, that might work. =20 Also, if you use the import feature in SAS, it will write code for you = that you can save and put in a program- you might try that to see if it = can handle this kind of file name. I'd rename the table in Access, = though- a quote in a table name is likely to contiously cause you = problems. -Mary ----- Original Message -----=20 From: JKamgang@Yahoo.com=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Wednesday, January 16...

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: SAS to MS Access #2
Try this...you need the pre-existing database to load into, but it can be empty. PROC EXPORT DATA= SASHELP.Workers OUTTABLE= "workers" DBMS=ACCESS2000 REPLACE; DATABASE="C:\Workers.mdb"; RUN; Paul Choate DDS Data Extraction (916) 654-2160 -----Original Message----- From: Zhang, Jianying [mailto:Jianying.Zhang@UMASSMED.EDU] Sent: Friday, September 19, 2003 6:06 AM To: SAS-L@LISTSERV.UGA.EDU Subject: SAS to MS Access Hi, Dear SAS_L: Is there a way to create a MS Access table from SAS without going through generating a text file or Excel table...

Re: old SAS guy with new SAS question #2
On Sun, 16 Nov 2008 21:18:36 -0600, John F. Regus <jfregus@IX.NETCOM.COM> wrote: >You three were of the most help to an old man in simplifying how to get output from my PC file into a SAS dataset. However, when I did use the INPUT statement I coded INPUT $. thinking this would put everything into its natural place in the output SAS table. Wrong. It only got the first variable of 5 characters. > >I went through my "Little SAS Book" trying to find the answer to this but the "Little SAS Book" has an appropriate title...it is little and does not cover a lot...

Re: Excel to SAS, general questions #2
Folks: To answer Nat's question with less than certainty, to the best of my limited knowledge, we're talking mainframe SAS and not SAS/PC. BTW, I did scan the archives but didn't think what I found could solve this particular problem. Thanks again, Bernie Zimmerman beezee@erols.com -----Original Message----- From: Nathaniel_Wooding@dom.com [mailto:Nathaniel_Wooding@dom.com] Sent: Wednesday, January 26, 2005 12:45 PM To: The Zimmerman's Subject: Re: Excel to SAS, general questions Bernie, Will PC SAS be involved ? The rest of the list would probably like to know, also. ...

Re: SAS/Access to Oracle questions #2
Clint, Have you tried using the SASTRACE option to help answer these question? This will show SQL that is sent to Oracle. I have found it useful -- look it up in the documentation as there are several different ways to use it -- I think you want options sastrace=,,,d ; but I'm not sure. I think the libname engine actually generates SQL passthrough (which you can observe by using the sastrace option), so you might not see much difference in efficiency between the two. Sarah Whittier On Mon, 21 Nov 2005 18:05:38 -0500, Rickards, Clinton (GE Consumer Finance, consultant) <clinton.ric...

Re: MS Access and SAS datasets #2
Hi David - I agree with Howard that CNTLOUT will give you your formats to develop lookup tables in Access. Alternately if your data isn't large and you don't care about normal form, you could process your variables through your formats with put statements and create parallel formatted data. In v9 there's VLABEL, VLABELX, and VARLABEL, etc. You can use VLABEL and array all your _numeric_ and then _character_ columns and create a label dataset. There's also the data dictionary which you can query and use the into: command to load a macro, or just dump the labels in a datase...

Re: SAS to MS Word help #2
One solution is, for the first time you set up the word document, manually delete the extra page and then link the CGM file to the document as you insert it. That way, everytime you re-run your SAS program, all you have to do is refresh the document. Conveniently, word 97 even allowed users to 'automatically' update the link, so you didn't even have to go through the tedium of opening the document and refreshing it. Word 2000 may be set up that way too, but I'm not too sure it is for CGMs. cheers, Stephen ...

Re: sas macro question help #2
I'm not sure exactly what the criteria for select a variable is. I choose any variable with a level ge 96 percent. You can use PROC FREQ and a data step. I don't think you need a macro as the program is very simple. * Create test data with 100 variables; data test; array categorical[100] $1; do id = 1 to 100; do _n_ = 1 to dim(categorical); categorical[_n_] = substr('ABC',rantbl(232323,.95,.02),1); end; output; end; run; ods listing close; proc freq; ods output OneWayFreqs= OneWayFreqs; tables categorical:; run; ods lis...

Re: SAS/Warehouse Administrator Problem?? or SAS/ACCESS problem?? #2
What's the question? I know it you presented it earlier, but you can't expect people to find the pieces of a problem and put them together. Look at the various intermediate results and find the point where they first diverge from what is expected. Then look at the code which generates that particular result. De-macroize that code and prepare some test data so that anybody can run it easily. Then present that isolated version of the problem (if the answer has not already emerged during this process). On Mon, 24 Apr 2006 11:19:35 -0700, saivs28@GMAIL.COM wrote: >The following code ...

Re: SAS 9.2 Update on problem re Help files #2
SNOTES is an approximately daily email from SAS STech Support listing the new or changed usage notes since the last email. So the item pertaining to you was: 32792 - SAS prompts you to rebuild the index when you press F1 to request help while running a SAS procedure in SAS 9.2 Release(s) Reported: 9.2 Release(s) Fixed: Date Created: 28JUL2008 Date Last Updated: 11AUG2008 http://support.sas.com/kb/32792.html To subscribe, go to http://support.sas.com/techsup/news/snotes.html You'll get an extra email on most workdays, but if you sub...

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

Re: Old SAS Guy needs help with some of these new fangled SAS #2
It's been a while since I used the Import Wizard but as I recall, there is a window which asks for a file name where SAS can store the input statments for your future use. Nat Wooding Environmental Specialist III Dominion, Environmental Biology 4111 Castlewood Rd Richmond, VA 23234 Phone:804-271-5313, Fax: 804-271-2977 Jim Groeneveld <jim.1stat@YAHOO. COM> To Sent by: "SAS(r) SAS-L@LISTSERV.UGA.EDU Discussion" ...

License question SAS/SHARE and SAS/ACCESS vs. SAS/IntrNet
Hello, I was wondering if any of you experienced SAS users could help. My small clin. software company is in the process of reviewing their SAS license, i.e. finding a cheaper solution than the constellation we are currently using: SAS / ACCESS + SAS / Share + SAS / Connect + SAS / IntrNet Our SAS server is currently handling some (not all) reporting duties for the main J2EE-type application that has a back-end DB2 database. We are using SAS/ACCESS to copy our db2 tables and then performing roll-ups / summaries and proc means in batch at night. SAS then delivers tabulated reports and gra...

Re: Import MS Access data to SAS #2
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_string=3D"Provider =3D Microsoft.Jet.OLEDB.4.0; Data Source=3D c:\temp\demo.mdb"; data demo; set OLEmdb.tblDemo; run; libname _all_ clear...

Re: Help needed with some SAS interview questions! #2
Number 10 made me laugh. Is the answer "so that I don't have to document my sas programs"? -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Howard Schreier <hs AT dc-sug DOT org> Sent: Thursday, March 30, 2006 8:38 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Help needed with some SAS interview questions! If you know nothing about any of these, then perhaps it would be a disservice to the prospective employer to coach you. More likely, you do know some things about some of these topics. Then you should articulate what you do...

Re: How to access a MS SQL file in SAS? #2
Hi Mayukh, 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=&am...

Re: How to create index in Access database out of SAS? #2
Hi Tom, PS: You can also use DDE to communicate as well. http://listserv.uga.edu/cgi-bin/wa?A2=ind0503E&L=sas-l&P=R2243 Mark -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Terjeson, Mark Sent: Thursday, January 31, 2008 7:35 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: How to create index in Access database out of SAS? Hi Tom, Refering to http://listserv.uga.edu/cgi-bin/wa?A2=ind9908C&L=sas-l&P=R15027. You can see that FROM CONNECTION TO xxx () sends an SQL text over to the SQL Server and then *returns* a table. The EXECUT...

Web resources about - Re: help SAS/MS ACCESS, general database questions #2 - comp.soft-sys.sas

West Lothian question - Wikipedia, the free encyclopedia
He illustrated his point by pointing out the absurdity of a Member of Parliament for West Lothian being able to vote on matters affecting the ...

Tina Fey tired of "Are women funny?" questions
Interviewers, you've been warned: Think of something else to talk about

Justice Thomas asks questions for 1st time in 10 years, stuns Supreme Court crowd
Chicago Tribune Justice Thomas asks questions for 1st time in 10 years, stuns Supreme Court crowd Chicago Tribune Justice Clarence Thomas ...

Clinton Talks To Traveling Press After 88 Days Of Avoiding Questions
Clinton Talks To Traveling Press After 88 Days Of Avoiding Questions

Here's a thought, CNN. Sign DiCaprio as guest-moderater to ask climate questions at Sunday's debate
You’ve probably seen this snippet from Leonardo DiCaprio’s Best Actor acceptance speech at the Academy Awards Sunday night: x YouTube Video ...

‘Nightline’s Dan Harris To Host ‘500 Questions’ Season 2 On ABC
Nightline co-anchor Dan Harris has been tapped as host for Season 2 of ABC 's event game show 500 Questions , from Mark Burnett and Mike Darnell. ...

Food Trucks FYI: Your Questions About Owning One Answered
Two food trucks on a quiet day (or least a quiet moment) in Seattle. Photographer: James S. Russell/Bloomberg It used to be that if you were ...

Justice Clarence Thomas breaks 10-year streak, asks question in court
TIME Justice Clarence Thomas breaks 10-year streak, asks question in court fox2now.com Justice Clarence Thomas reflected on his long journey ...

Justice Thomas asks questions in court, 1st time in 10 years
Justice Thomas asks questions in court, 1st time in 10 years

Microsoft president reaffirms support for Apple, calls digital privacy the ‘fundamental question’ of ...
Microsoft president Brad Smith brought an adding machine on stage to illustrate the age of laws affecting our current technology. Image via RSA ...

Resources last updated: 3/1/2016 10:38:46 PM