Date:         Tue, 7 Feb 2006 16:15:28 -0800
Reply-To:     "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Subject:      Re: convert multiple SAS datasets to One single MS Access(.mdb)
              file
Comments: To: Jerry <greenmt@GMAIL.COM>
Content-Type: text/plain; charset="us-ascii"

Jerry -

To reiterate Howard's reply - the libname Access engine works great.

You can even assign the library with the wizard and then using your SAS
Explorer library window group drag and drop from your SAS dataset to the
MDB database opened with the libname wizard, accomplishing your task
without a single line of code.  I'd recommend taking the time to write
the code so you have an error log and can test what you do and save it
for future use.

Also using libname you can set options to use labels as column headers
and such.  Also if you want to transform the data or reorder the PDV
with a retain statement you can with a data step while writing out the
data.

See:
http://support.sas.com/onlinedoc/913/getDoc/en/acpcref.hlp/pclibname.htm

this copies 25 rows from all 129 datasets in SASHelp into a MDB file:

options obs=25;
libname acctbl access 'Access Test.mdb' ;
proc copy
     in=sashelp
     out=acctbl
     memtype=data;
run;
libname acctbl clear;


The one hitch is that you need to create a target MDB file first.
(Right click, <New MS Access App>)

You'll see that it fails where there are more than 256 columns in a
table and that indexes are not preserved.  In the 256+ case you'll need
to group columns in multiple files keeping the id's in all sub-files.
This is easy with a datastep and a simple keep statement.

HTH

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Jerry
Sent: Tuesday, February 07, 2006 10:47 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: convert multiple SAS datasets to One single MS Access(.mdb)
file

Hi,

My task is:
On a PC (have SAS/Access), convert hundreds of SAS data sets (they all
are in 1 direcotry) into One single Access file, and one SAS data set
corresponds to one table in the big .mdb file.

1st question:
How to automatically use the name of each SAS data set as the name of
each table in the .mdb file?

For example:

To convert 3 following SAS data sets into 1 Access file (say,
final.mdb).

abc.sas7bdat
def.sas7bdat
ghi.sas7bdat

So final.mdb will contain 3 tables (abc, def, and ghi).


2nd question:
To convert a SAS data set to an Excel (.xls) file, the "Libname Excel
Engine Method" is easy to use and works well for me.

Is there a similar libname approch for converting a SAS data set to an
Access file?

All input are greatly appreciated!

Jerry