Date:         Fri, 30 May 2008 08:31:59 -0500
Reply-To:     "data _null_," <datanull@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "data _null_," <datanull@GMAIL.COM>
Subject:      Re: Excel Import Question
Comments: To: "Treder, David" <dtreder@geneseeisd.org>
In-Reply-To:  <AC8B27AA4545474BB8F063FDFE5F2C5503FBF201@GISDSERV03.geneseeisd.local>
Content-Type: text/plain; charset=ISO-8859-1

There is an old hillbilly saying.  "Just gen some more bits".  Or is
it "pass the moonshine."

Notice the subtle changes:
  New fileref 003
  Moved original file statement inside loop
  Add file statement for 003
  Put set statement syntax into new file 003.

I included code to ID the data source in anticipation of your next question.

filename ft35f001 'Path to XLS files';
filename ft35f002 temp;
filename ft35f003 temp;
data _null_;
   length path command datafile out in $256;
   path = pathname('FT35f001');
   command = catx(' ','dir /b',cats(quote(trim(path)),'\*.xls'));
   infile dummy pipe filevar=command end=eof;
   do until(eof);
      input;
      putlog _infile_;
      datafile = catx('\',path,_infile_);
      out      = scan(_infile_,1,'.');
      in       = cats('_IN_',out);
      file ft35f002;
      put 'PROC IMPORT replace';
      put +3 DATAFILE=:$quote300.;
      put +3 OUT= 'DBMS=excel;';
      put +3 'run;';
      file ft35f003;
      put +6 out '(' in= ')';
      end;
   run;
%inc ft35f002 / source2;

data all;
   length from $32;
   set %inc ft35f003 / source2;; *note two simicolons;
   array _in[*] _in_:;
   _n_ = index(cats(of _in[*]),'1');
   from = substr(vname(_in[_n_]),5);
   run;


On 5/30/08, Treder, David <dtreder@geneseeisd.org> wrote:
> excellent - thanks
>
> Though not really sure what the code is doing, it works.
>
> One small issue: it creates separate files.  As a second step, any way
> to create a single dataset that combines all the datasets from a single
> library, without listing each dataset separately in the set statement?
>
> Something like:
> Data all; set 'all the datasets in library X';
>
> Thanks Again,
> Dave
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> data _null_,
> Sent: Thursday, May 29, 2008 5:02 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: Excel Import Question
>
> This does PROC IMPORT code gen for the XLS files in a directory.
> Other methods might be more appropriate.
>
> filename ft35f001 'path to xls files(directory name)';
> filename ft35f002 temp;
> data _null_;
>   file ft35f002;
>   length path command datafile out $256;
>   path = pathname('FT35f001');
>   command = catx(' ','dir /b',cats(quote(trim(path)),'\*.xls'));
>   infile dummy pipe filevar=command end=eof;
>   do until(eof);
>      input;
>      putlog _infile_;
>      datafile = catx('\',path,_infile_);
>      out      = scan(_infile_,1,'.');
>      put 'PROC IMPORT';
>      put +3 DATAFILE=:$quote300.;
>      put +3 OUT= 'DBMS=excel;';
>      put +3 'run;';
>      end;
>   run;
> %inc ft35f002 / source2;
>
>
> On 5/29/08, Treder, David <dtreder@geneseeisd.org> wrote:
> >
> >
> >
> >
> > Is there a way to import multiple excel files from a single folder, as
> > one can do with "*" in an infile statement? Something like:
> >
> >
> >
> > PROC IMPORT
> >
> > DATAFILE= 'c:\datafile\*.xls'
> >
> > OUT = a1
> >
> > DBMS=excel ;
> >
> > run; quit;
> >
> >
> >
> > (which, of course, doesn't work)
> >
> >
> >
> > Thanks for any suggestions,
> >
> > Dave
> >
> >
> >
> > Scanned by GenNET AV out
> >
>
>
> Scanned by GenNET AV in
>
>
> Scanned by GenNET AV out
>