Date:         Wed, 11 Jul 2007 11:54:19 -0400
Reply-To:     Yufei Wang <yufeiw@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Yufei Wang <yufeiw@GMAIL.COM>
Subject:      Re: Import access files to SAS
Comments: To: Arthur Tabachneck <art297@netscape.net>
In-Reply-To:  <200707110427.l6B25n8m006579@mailgw.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Thank you so much, Arthur.
The "pipe" works very well, I got the table which has the 'fullname' and
"lname", etc.
The only problem is the log says " the 'NY*******_***"' is not a valid SAS
name,...., libname exceeds 8 character."
I think it's because the database name is too long to be accepted by SAS,
any solution for that?
Yufei



On 7/11/07, Arthur Tabachneck <art297@netscape.net> wrote:
>
> Yufei wrote the following to me offline:
>
> >Thank you for your reply. I'm sorry I didn't make it clear. What I
> >wrote "county1,county2...." was trying to make difference. The real
> >structure of database is "C:\tem\NY###_***\":
> >in which "###" is three digit number and are not continuous, I don't
> >how it comes, but at least for NJ they are not continous, like "001,
> >003,006";
> >"***" are three letters, which is abbreviation of the county's name.
> >The reason I'm trying to using macro to read those *mdb files from
> >different directory is those database can be extended to whole Unites
> >states and obviously it's kind of silly to import those files one
> >by one.
>
> Yufei,
>
> You will always get better responses by posting directly to sas-l.
>
> I don't know if the table names (in your Access databases) all have the
> same table names (e.g., table1).  The following solution assumes that they
> are all labeled 'table1'.  Possibly someone else knows (1) how to get
> around not knowing the actual table names and/or (2) how to reach a
> solution without needing to build and submit an include file.
>
> My solution uses a pipe to get the database names, then builds a submits
> an include file to actually read the tables:
>
> filename fh pipe 'dir /s /b "C:\tem\NY*.mdb"';
> filename GetMSA temp;
>
> data filenames;
> infile fh lrecl=300 truncover end=eof;
> input fullname $255.;
> filename=scan(fullname,-1,'\');
> lname=tranwrd(filename, ".mdb", "");
> counter+1;
> if eof then do;
>    call symput('stop',input(counter,best12.));
> end;
> run;
>
> data _null_;
> file GetMSA;
> format test $255.;
> do i=1 to &stop.;
>    set filenames;
>    test=cat("libname ",trim(lname)," access ",'"',trim(fullname),'";');
>    put @1 test;
> end;
> put @1 'data want;';
> put @3 'set ';
> do i=1 to &stop.;
>    set filenames;
>    libNpath=trim(lname)||'.table1';
>    put @7 libNpath;
> end;
> put @3 ';';
> put @1 'run;';
> run;
>
> %include getMSA;
>
> Art
> ---------
> On 7/9/07, Arthur Tabachneck <art297@netscape.net> wrote:
> Yufei,
>
> Dependent upon the structure of your access databases, the task could be
> as simple as something like:
>
> %macro getfiles;
> %do i=1 %to 128;
>   libname county&i. access "C:\temp\NY_county&i..mdb";
> %end;
>
> data want;
>   set
>    %do i=1 %to 128;
>      county&i..table1
>    %end;
>   ;
> run;
> %mend;
>
> %getfiles
>
> Art
> --------
> On Mon, 9 Jul 2007 17:18:47 -0400, Yufei Wang <yufeiw@GMAIL.COM> wrote:
>
> >Is it same as importing multiple excel files to SAS?
> >like Wenshui's blog shows:
> >http://statcompute.blogspot.com/2005/09/read-multiple-excel-files-in-sas-
> using.html
> >My problem is the files are not in the same director. The access file
> stores
> >the attribute data for each county in each countie's directory, like
> this:
> >C:\temp\NY_county1\*.mdb
> >C:\temp\NY_county2\*.mdb
> >C:\temp\NY_county3\*.mdb
> >C:\temp\NY_county3\*.mdb
> >C:\temp\NY_county4\*.mdb
> >........
>