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?

Thanks,

Joe.

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