Date:         Thu, 25 Feb 2010 19:07:32 -0600
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: create one dataset out of multiple access tables
Comments: To: Sdlentertd <sdlentertd@gmail.com>
In-Reply-To:  <f4647686-fcd3-4e34-a5b4-675beade374e@g10g2000yqh.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1

Sure.  Is there a consistency of naming in the tables?

In general, assuming you have PC FILES licensed on your (Windows) machine,
you can do :

libname whatever access "file.mdb";

At that point depending on the version of SAS you have and the naming scheme
there are different ways you can proceed.

If the naming scheme is TABLE1 - TABLE100, you can use a variety of DO loops
such as:

%macro load_tables;
%do i = 1 %to 100;
whatever.table&i
%end;
%mend load_tables;

data stuff;
set %load_tables; ;
run;

If they're huge, it might be better to use PROC APPEND in a similar fashion.

If they are named inconsistently, you can use PROC SQL to get into the
contents of the access table, something similar to:
proc sql;
select cats('whatever.',memname) into :tables separated by ' '
  from dictionary.tables
  where libname='WHATEVER';
quit;
and then
data test;
set &tables;
run;
or similar with proc append.

If you have 9.2 you can also use the : operator ('starts with') if they are
all named something starting with the same letters:

data test;
set whatever.table:;
run;

And a few dozen more options :)

-Joe
On Thu, Feb 25, 2010 at 4:25 PM, Sdlentertd <sdlentertd@gmail.com> wrote:

> I have an Access Database with 100 tables with exact same fields, only
> values are different.  I need to create one big SAS dataset out of
> those 100 tables.
> is there a way to do it without 200 lines of repetitive code?
>
> Thank you
>