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)
Content-Type: text/plain; charset="us-ascii"
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
this copies 25 rows from all 129 datasets in SASHelp into a MDB file:
libname acctbl access 'Access Test.mdb' ;
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.
DDS Data Extraction
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Sent: Tuesday, February 07, 2006 10:47 AM
Subject: convert multiple SAS datasets to One single MS Access(.mdb)
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.
How to automatically use the name of each SAS data set as the name of
each table in the .mdb file?
To convert 3 following SAS data sets into 1 Access file (say,
So final.mdb will contain 3 tables (abc, def, and ghi).
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
All input are greatly appreciated!