[讨论]SAS and Access

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

Dear Group:

I'm an SPSS user from way back and very happily use it to do almost everything in my office except make the coffee.  However, I know next to nothing about Microsoft Access and database queries.  Can you offer a summary as to what type of projects and procedures Access (or other database tools) might be useful for versus using SPSS?

I'm asking because some of my new staff members are long-time Access users but know nothing about SPSS.  I want them to be trained to use the best tools for the job, but don't know which tools are best, and when they're best.

Thanks,
Laura

Laura Berry, Ed.D.
Director of Student Success & Institutional Research
North Arkansas College
(870)391-3280

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏1 回帖

关键词：access Acces cces ACC CES 讨论 SAS access

Laura,

They are different animals, with definite roles and mutual relationships. You do not have to choose one or the other. Of course there are some areas of possible overlapping. Simple database management tasks could also be accomplished by SPSS, such as merging files, as there are some simple statistical tasks that can be done by Access, like getting a crosstabulations or an average, but on the whole each software should be used for the kind of task it was intended for. You can prepare a crosstabulation with either package (as you can with Excel too), but do not try Cox Regression on Access. Preparing a flat table or file with a combination of variables taken from several levels of a hierarchical/relational database is better done with Access, though it is awkwardly possible also in SPSS. Looking for individual cases fulfilling certain conditions can be done with SPSS, but it is easier if done with Access. Hector

Hello Laura,

The one is a saw and the other a hammer. We use several different products. We us Blaise to make forms to do the interview with the participants of a survey. The outcome thereof is converted to SPSS to draw conclusions of the
gathered data. We use MSAccess for 'administration' purposes. All the personal data of the particpants are in Access and we us it to keep track of the dataflow, like who has not got a follow up yet and who did or did someone forget to return the CSA-meter etc. So Access is really a database type product and very suitable for that, SPSS is a product to get the statistics out of the gathered data.

Just my eurocent.

Kees
___________________________________
Ing. C.P.J. de Boer
EMGO, VUmc
afd. Datamanagement & Systeembeheer
BS-7 D-451
(020) 44 49828

Laura,

Another use for Access is in the creation of data entry forms.  Fairly complex error checking scripts can operate in the background ensuring that data values are appropriate at the variable and record levels.When your are finished, the Access data table can easily be imported into SPSS for analysis.


Victor Kogler

Hi Larua: Without getting too technical Ms Access is a good tool to store vast amounts of data in table format and comes with built in wizards/tools to create forms to get the data into those tables. If its manually data entry. Their are other methods to automate to the entry into the database. A quite and dirty explaination of 1 to Many relationships.....

Ms Access has a way to enforce integrity of the data by creating relationships amongst the tables. For example, 1 Director may have Many Staff members. In this case we have a table called Directors with only directors stored in it. and a table called Staff with only staff stored in it. Each director would only be stored once in the director table and is unique. This director can be stored in the Staff table many times denoting the staff associated with that director. Hence a 1 to Many Relationship. For Using both SPSS and Access database. Note: I have renamed some field names here to hide the some of the structure of my database. But the concept here remains the same.. Maybe you could leverage the skill sets and work with both tools hand in hand. This example shows how your Skilled Ms Access people would create a query for you to access an Access database to grab some data and push into the SPSS data editor. Then maybe you could use your SPSS skills to do the rest of the magic.

GET DATA /TYPE=ODBC /CONNECT= 'DSN=MS Access Database;DBQ=C:\SomeDataBase.mdb;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;' /SQL = " SELECT IIF(IsNull(P.DECEASED), 0, IIF(P.DECEASED= 'N', 0, IIF(P.DECEASED= 'Y', 1))) AS 'Staff Died', " " (( IIF(T.StaffFailed= 'Y', T.DATE_Failed , DATE() ) - T.StaffFailed) / 365 ) AS 'Staff Survival' , " " (( IIF(P.DirectorFailed= 'Y', P.DATE_Failed, DATE() ) - T.DirectorFailed) / 365 ) AS 'Director Survival', " " IIF(IsNull(T.StaffFailed0, IIF(T.StaffFailed= 'N', 0, IIF(T.StaffFailed= 'Y', 1))) AS 'Staff Failed', " " YEAR(T.StaffHired ) AS 'GroupYear', " " FROM STAFF P, Directors T, Class D " " WHERE T.ID = P.ID " " AND T.DirectorID= P.DirectorID " " AND D.ClassID= T.ClassID" " AND P.StaffType IN('2') " " AND T.DirectorsFromHere IN('Y') " " AND D.ClassPeople IN('N') " " AND T.StaffHired BETWEEN #2000-01-01# AND #2005-12-31# " " ORDER BY T.StaffHiredDate ASC " /ASSUMEDSTRWIDTH=255 . CACHE. EXECUTE. I hope this helps answers your question and helps with your decision.

Cheers, Terry

I would caution people that Access (and Excel) can store vast amounts of data in the row direction, but it can only hold 255 columns in any table or query. This is a limit we've recently exceeded in some of the genetic work we're doing, and I'm relying more and more on SPSS's data/merge commands to assemble the output of several different queries into a single dataset. Databases like mySQL, MS SQL, and, I assume, Oracle are not as limited. Another caution is that Access is more liberal about field names than the SQL standard. It's quite possible to have fully functional tables and queries that cannot be called from outside Access. We have many dozens of queries that have evolved over the years that we would like to call directly from SPSS using an ODBC connection (there's a database wizard for this), but because the variable names use hyphens or other "special" characters, they cannot be called without modification. Most of the time we export data to files, then import those files into SPSS, when a database call directly from SPSS to Access would be much more efficient. This can be avoided with good planning and design. We use Access mostly for its tables and queries; for database management. For us, data entry takes place either by importing an Excel spreadsheet into an Access table (how we work with collaborators) or through a web-based form interface that runs with ColdFusion and Microsoft's webserver, IIS (how our clinicians enter data). I should also mention that it is possible to manipulate data programmatically by coding in Visual Basic for Applications, and we've created a few functions that perform simple statistical tasks, though for the heavy lifting we rely on SPSS (and StatView).

Eric

This is true. Another note on this. Access can only store upto 2GB of data. Oracle or SQL Server would be a wise and Truely RDMS. For SQL Server we can have up to 1024 columns for each row. Not sure on Oracle, tho they may be the same. As with anything in the deveopment world, their are more than one way to complete the same task. One would compare price , existing skill sets -->with requirements/desired functionality here. Thanks Eric.

Terry

Dr. Berry, Access is a user-friendly database program. It's both used free-standing, and also commonly used as a "front end" by which an IT department can provide us amateurs access to big databases which exist in SQL-Server or Oracle, without us having to learn Structured Query Language (SQL). Now here the question for you is: Do you know what a relational database is, what it's good for and how to use databases? There is much more than I can explain in a brief email, but with SPSS, all your data for a project is normally in one "flat file". With a relational database, your data is in several files which will be usually being joined to use them. The concept of joining files is very important. Here is an example of a relational database: Your College’s registrar might have one file listing students, and another listing grades by class and student, a third listing professors, and a fourth listing classes and class titles. This is much more efficient than one huge flat file, BECAUSE if the college used a single flat file then for each class and grade it would need to have a line of data which repeated everything about that student (address, next of kin, etc. ) and class (title, professor). That's a lot to re-copy if the goal is just for the registrar to record one grade. So when it is time to issue a transcript, the file with the student's address is joined to the files with the class and grades. The joins are done with fields holding common data, such as a student's ID number and a class number. So the grades are associated with the student's name and address using the ID number which appears in both files. Working in databases takes one to another level of capability. Complex projects become simple and impossible ones become possible. Access can be used to find, update and clean data too. For instance in another job a few years ago I used to prepare mailings to a (frequency program) customer list. I would first use Access to find and list the customers who fit the criteria for the promotional offer, then to count them and rank them to pick, say, the best 11,000 (10,000 to mail to and 1,000 for a control group), then to prepare the cleaned file for mailing (extra spaces taken out of names and addresses, missing addresses not to be mailed to at all) and then to export a file to the mailing house which would produce the physical pieces to be mailed. At a later date I would assess the results of the mailing by extracting the business the customers did with us, from the company’s transaction records. I would compare the transactions done during the offer period by the mailed group with those done by the control group, and perhaps also with their previous habits. All this was done with Access. What Access is NOT, is a statistical program. It has some simple statistical functions, for example it will produce a mean. It will also crosstab, though it won't produce crosstabs as neat and explanatory as SPSS Tables module does or, even better, a specialized crosstab program. I frequently use Access to prepare data for analysis with SPSS. Ian Straus Manager, Statistics & Analysis VIA Metropolitan Transit San Antonio, Texas

Here is the essence of the problem. Access is a relational data base. It does not have "columns" (that is until you get to designing report and talk about the placement of fields on a page). It is not a spreadsheet or a word processor. It requires a different way of think of the data. While a table can contain only 255 fields, a data base can contain many tables that can be and should be linked by a common field. Therefore you can reference many times 255 "columns". Also Access is sold and designed by Uncle Bill to be for "smaller" data sets. Microsoft SQL is designed by Uncle Bill for "larger" data sets. I have millions of records in my Access data bases. I know a very good accountant who does all his letters in Lotus. I also know a very good secretary who keeps large tables of number that she does mathematical calculations, on in WordPerfect. Both are convinced to the bone that their way is the "right way." It is what they know and "when all you have is a hammer you treat everything like a nail." If you want to do statistical analysis by using statistical analysis package, and if you want to store and sort data by using a data base package, just make sure they "talk" to each other when doing what they do best.

。没怎么看懂。。。。这是哪里转来滴捏？
好早的帖子啊。。估计不会知道了