|
Subject: Summary of my post: PC SAS --> MS Access
Date: Thu, 29 Aug 1996 17:57:00 -0400
From: "Hu, Raymond" <rch@S-3.COM>
Newsgroups: comp.soft.sas
Dear SAS-Lers:
Many thanks to all of the people who responded to my question. The
following is a summarization of the answers received and what I plan to do:
Question:
>Dear SAS-Lers:
> I want to send data from a SAS session to a MS Access table during the
>run-time. "During the run-time" is important to me because in this way I
>can keep everything in one SAS program and the program can be run 100
times.
> I checked the SAS Companion for the Microsoft Windows Environment, SAS
>technical support doc TS 325, and talked to SAS Institute consultant, the
>answer is no since SAS cannot send data to MS Access by DDE.
> The only hope is that pre-coding some Access codes in MS Access, which
>can read a ascii file, then sending a system command from SAS to MS Access
Suggestions:
1. Buy SAS/ACCESS and use ODBC interface to MS Access
(by Karsten Self, Amy Miceli, Darryl Hunter).
2. Buy DBMSCopy (www.conceptual.com) and translate SAS datasets to MS Access
(by Karsten Self).
3. Output a delimited (column or comma, preferred) file and import to Access
(by Karsten Self).
4. Buy DBMS/Engines and then read and write MS Access table directly.
Option #4 is what I am planning to use right now. The following are my
reasons for selecting DBMS/Engines:
a) DBMS/Engines and SAS/Access can almost do the same thing.
b) DBMS/Engines is easy to use not only with MS Access but also with other
formatted files. So far I have used this software to read Paradox, MS
Access, Excel 5, Quattro Pro for DOS, FoxPro(dBaseIII), and SPSS data files
successfully. However, I am having some problems reading Quattro Pro for
Windows and am working on that right now. For example, I am trying to use a
Paradox table, equip.db:
libname mypara dbpdox 'c:\data';
proc print data=mypara.equip (obs=20);
run;
c) DBMS/Engines is a one-time cost of $295, unlike the SAS annual fee.
d) I didn't opt for #2 or #3 because they are short-term, one-time
solutions, and I need to send data from SAS to a MS Access database
throughout the year. Efficiency and fewer steps are preferable.
Thank you again for your responses. SAS-L is a great place to learn and to
find the best solutions.
Raymond Hu, Ph.D.
Social and Scientific Systems
Statistician/Programmer Analyst
7101 Wisconsin Avenue, Suite 1300
Bethesda, MD 20814
Office: 301-986-4870 ext 279
Fax: 301-986-8051
E-mail: RCH@S-3.COM
Subject: Re: MS ACCESS to SAS DATASET
Date: Sun, 7 Dec 1997 16:55:31
+0000
From: Peter Crawford
Peter@crawfordsoftware.demon.co.uk>
Organization: Crawford Software Consultancy Limited Newsgroups: comp.soft-sys.sas
In article <66950c$knu$1@flood.weeg.uiowa.edu>, Shan Putnam <sputnam@blue.weeg.uiowa.edu> writes
>
>Can anyone shed some light on getting SAS to read MS access tables?
>
>The SAS desktop allows me to import MS Excel files, not MS Access file.
So
>I end up having to convert all MS Access tables to separate MS Excel
files
>and then import one at a time.
>
>Somewhere during this multi-conversion step I lose bunches of data. For
>example, it seems that if I have a text field in MSA that happens to
have a
>number in the first obs, SAS ends up converting that text field into
a
>numeric var and sets all char info to missing in the final dataset.
>
>I can't seem to find info in the SAS help or SAS books about importing
or
>reading MS Access file directly.
>
>Any suggestions would be very, very helpful.
>
>Shan
>
>
I know how to do this with DDE. The difficult bit was iscovering all the
bits for the DDE triplet and for the infile options eg
FILENAME ddedat2 DDE "msACCESS|DATAMODEL;query ENTITY-ATTR!ALL";
to read query ENTITY-ATTR from database DATAMODEL or
FILENAME ddedat2 DDE "msACCESS|DATAMODEL;table ENTITY!ALL";
to read the table ENTITY
That does the trick of reading the column headers.
Instead of OBS=1 use firstOBS=2 to read the data.
So, with DDE phasing out perhaps, what are the corresponding methods to infile through OLE.
Surely it won't be necessary to buy the SAS/access product ?
--
Peter Crawford
If the transfer from MS-Access to the SAS System is specific (the MS-Access
table/query is known and its columns informats known) you can use the windows
dde access to support the transfer.
I can't remember where I captured the following example.
When you use the infile dde method, you will need source database open in MS-Access.
It has been said that you can't use this (dde) method to go the opposite
direction ( out of the SAS System into MS-Access ) with a datastep.
--
Peter Crawford
/*************************/
Copyright (C) 1997 by SAS Institute Inc., Cary, NC, USA.
SAS (R) is a registered trademark of SAS Institute Inc.
SAS Institute does not assume responsibility for the accuracy of
any material presented in this file.
/**************************/
/*** DDE--Example using Microsoft Access ***/
/* MS Access cannot have a '.' or missing value for numerics.
Have the user enter a number that could be out of range such as -99 or 0
for missing values then do a if-then to convert it to '.' Must use a firstobs=2
to bypass the column names on the MS Access data that is being read in clipboard.
You must select the datatable from the TABLE window then do a 'Edit' 'copy'.
You cannot do it from the datatable itself by hightlighting certain rows
and cols. It's all or nothing. This is the only way the DDE triplet can be
seen.
*/
filename in dde 'MSAccess|I:\VENDORS\MSACCESS\DDE.MDB;Table Table1!All';
data test;
format dob date.;
length name sex $20;
infile in firstobs=2 dlm='09'x missover truncover notab;
input name $ age sex $ dob mmddyy8. height weight;
if weight=0 then weight =.;
if height=0 then height =.;
proc print; run;
Subject: Re: SAS and Microsoft Access
From: Jason Jones <jjones@VNUSINFO.COM>
Date: 1998/07/25
Message-ID:
<000201bdb7d2$db3f0f00$08c37d80@vnuswk1.hsc.usc.edu>
Newsgroups: comp.soft-sys.sas
Bill,
Although I have never used it myself, I was under the understanding that DBMS Copy could handle Access *.mdb's without any trouble, so perhaps you do just need an upgrade. There are, however, two other solutions that I have used that work very nicely: DDE and ODBC. I'm not sure how familiar you are with either, so I will attempt a brief but useful description of how to get Access data to SAS via ODBC and feel free to ask question if you need more information. Although the solution seems fairly straightforward to me now, there was some frustration along the way.
I) You have to set up an ODBC data source using the ODBC administrator. I usually use a "System DSN" for this (the other options are to set up "User" or "File" DSNs).
1) From the ODBC administrator (in the Control Panel on a Win '95/NT machine), click on the "System DSN" tab.
2) Click the "Add" button.
3) Choose the driver: in this case "MS Access."
4) Specify a name for the Data Source--I'll use "Test" in this example (this is what you'll use to identify the data from within SAS).
5) Click the "Select" button and specify the *.mdb file you want to access from SAS.
6) Click "OK" and you're done with step "I".
II) Get the data from within SAS using PROC SQL.
Here is some sample code that will return all the data from the table "tblTest" in the database specified by the "Test" DSN set up above:
PROC SQL NOPRINT;
CONNECT TO ODBC AS cnctn (DSN="Test" UID=admin);
CREATE TABLE work.sastable AS
SELECT *
FROM CONNECTION TO cnctn
(SELECT *
FROM tblTest);
DISCONNECT FROM cnctn;
QUIT;
Notes:
1) You can see where the Data Source Name (DSN) was important in the CONNECT statement.
2) The (SELECT * FROM tblTest) is a valid Access SQL statement and could certainly be more elaborate.
...
Sorry, I've run out of time.
I'll leave it here for now mostly because I'm going to be late for something if I don't. There are several things that could go wrong along the way or things that might not make sense or give you exactly what you want. Like I said, I hope this is helpful, but feel free to contact me (or I'm sure many others on SAS-L) if it doesn't quite work for you.
Jason Jones
Subject: Free SAS Tool Exports Datasets to Excel, Word, and
ASCII
Date: Wed, 26 Aug 1998 17:17:18 -0700
From: nospam@qlx.com (Alan Gilman)
Organization: IBM.NET
Newsgroups: comp.soft-sys.sas
FOR IMMEDIATE RELEASE
Free Software Tool Exports SAS Datasets to Excel, Word, and ASCII.
Cary, August 26, 1998. Qualex Consulting Services, Inc., a SAS Institute Quality Partner, has significantly improved the functionality of its Exporter SAS tool.
EXPORTER is a SAS/AF application that allows the export of SAS datasets to Excel, MS-Word, and ASCII.
EXPORTER is part of Qualex's increasing number of free SAS software tools.
To download EXPORTER, go to the Qualex web site at http://www.qlx.com.
E-mail delivery is also available by contacting:
tools@qlx.com