hi ... another idea using the libname engine in place of IMPORT... this assumes: same variables in all the spreadsheets; within each spreadsheet, data are in SHEET1 ... you could tweak this with the FORCE option on PROC DATASETS (different variables across spreadsheets) ...

* create a file with names of spreadsheets'; options noxwait; x 'dir z:\*.xls /b > z:\dir.txt';

* use Excel libname engine to spreadsheets to data set ALL; data _null_; infile 'z:\dir.txt' truncover; input name $30.; call execute(cat( 'libname x "z:\',trim(name),'";',

'proc append base=all data=x."sheet1$"n;', 'run;',

'libname x clear;' )); run;

-- Mike Zdeb U@Albany School of Public Health One University Place Rensselaer, New York 12144-3456 P/518-402-6479 F/630-604-1475

> 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 >> > >