LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (June 1997, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Sat, 28 Jun 1997 18:57:49 +0100
Reply-To:   Joe Clarke <josephclarke@TINET.IE>
Sender:   "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:   Joe Clarke <josephclarke@TINET.IE>
Subject:   Re: Using dde to read MS Access into SAS
Comments:   To: Jianmin Liu <liu@ARE.BERKELEY.EDU>
Content-Type:   text/plain; charset=ISO-8859-1

Thanks Jianmin

I've used DDE before as a method of reading in data from Access (I don't have SAS ACCESS for ODBC). Unfortunately, I find that SAS crashes after 15 minutes or so of reading in a table of about 100,000 records. I can find no reason for this though. It gives the usual fatal I/O error but this doesn't help explain the problem. Any ideas?



---------- : From: Jianmin Liu <liu@ARE.BERKELEY.EDU> : To: Multiple recipients of list SAS-L <SAS-L@UGA.CC.UGA.EDU> : Subject: Using dde to read MS Access into SAS : Date: 28 June 1997 18:17 : : Using SAS odbc driver with MS Access interface is one way : to read MS Access into SAS. It works fine with me. : : An alternative approach works almost equally well in my case : using dde. Here's the code. First, let's say you have an : MS Access file called subq.mdb with a Table name 'subq', and the Table is : not too large (say <= 120,000 observations with 40-50 : variables). Then the following code would do the job just fine: : : options ls=80 nocenter nodate; : filename myfile dde 'MSAccess|g:\mlsdata\subq.mdb;Table subq!All'; : : data one; : infile myfile firstobs=2 missover notab dsd dlm='09'x; : input variable_names; : run; : : The above code may not be documented (I'm not aware of) by SAS : but it works well in my case. What if your MS Access file is : bigger than I mentioned? You can still work around it. Here's : how. : : There is a limit on data file size in dde imposed by MS, which : SAS has to work with. I contacted SAS for the specification : of this limit without success. I was told to contact MS. : However, based on what I found, the same number of variables, : about 50, when I tried to read in 180,000 obs, I got an error : message in SAS saying that an error was generated when SAS : accessing the input/output device, and a fatal I/O error. : : But, here's a simple way to work around it if you don't have : access to SAS ODBC drive for MS Access. You can split your : MS Access file table into several parts depending the size of : the original table. I'm not MS Access expert but it is very : easy to do. Let's say for the same Table name Subq, I split : it into two parts. The first part I named it: part1 of subq, : the second part: part2 of subq. Keep the window open and : you can see (by clicking table button) table subq and the : other two parts. Highlight subq table and then click edit and : then copy. Then stop and leave the window open (you can minimize : it). Then run the following code in SAS: : : options ls=80 nocenter nodate; : filename part1 dde 'MSAccess|g:\mlsdata\subq.mdb;Table part1 of subq!All'; : filename part2 dde 'MSAccess|g:\mlsdata\subq.mdb;Table part2 of subq!All'; : : data one; : infile part1 firstobs=2 missover notab dsd dlm='09'x; : input x y z ; : : run; : : data two; : infile part2 firstobs=2 missover notab dsd dlm='09'x; : input x y z ; : : run; : : data three; : set one two; : run; : : I have read in a MS Access data file of 216,000 obs with : 50 variables by spliting the Access Table into 5 parts. : It worked quite well, and I haven't found any SAS documentation : on this. The issue is if you need to read a multimillion : records MS Access file into SAS, this might be too much : preparation to do. Under this circumstance, I would suggest : to use ODBC driver to read in the large data file in one piece. : : Just my two cents. : : Jianmin Liu

Back to: Top of message | Previous page | Main SAS-L page