*********************************************************************;
* Multifileimporterwin1.sas                                         *;
* Version 1.12                                                      *;
* Date 2002/08/28                                                   *;
* Function - to import any .txt file or any .xls file in a directory*;
* and read it into the SAS system                                   *;
* It also has an option to append all the files into a final dataset*;
* Current limitations, filenames must be two level names such as    *;
* fred.xls or myfile.txt  , it does not handle                      *;
* fred.barney.xls, or myfile.filename.txt                           *;
* It will only read the first alphabetical sheet in the xls file    *;
* or named range in the xls file                                    *;
* To read multiple sheets see macroimporter6a program               *;
*                                                                   *;
* This program assumes that the variable names are in the 1st row   *;
*                                                                   *;
* PLEASE LOOK AT THE SECTIONS OF THE PROGRAM YOU MUST CHANGE        *;
*                                                                   *;
* There are 3 sections at the top and one section at the bottom     *;
* that you must change                                              *;
* Features or Fixes added:                                          *;
* 2002/08/29 - added the ability to specify the libref where        *;
*              individual datasets are stored                       *;
*            - added a new routine to clean out the master dataset  *;
* 2002/10/21 - added csv support                                    *;
* 2004/10/21 - dbf support added                                    *;
*              fixed bug with scan function                         *;
*********************************************************************;

dm wpgm 'clear log' wpgm;
* first change the directory of the file you want to read here  *;
* note if you have spaces in your directory you must bound them *;
* with double quotes like this                                  *;
* filename fred pipe 'dir k:\"program files"\"my sas files" '    *;
***** >>>>>>>>>>>>> Change the directory here <<<<<<<<<<<<<<<<<<< *;
filename fred pipe 'dir c:\"testsas"';
data temp1;
* use the same directory here note the extra \ used however *;
***** >>>>>>>>>>>>>>>>>>>>> Change the directory here <<<<<<<<<<< *;
dir1="c:\testsas\";

**** >>>>>>>>>>>>>>>>>>   Change the value here <<<<<<<<<<<<<<<<< *;
* change the value to what you want to read, tab, csv, dbf or excel97 *;

db='excel97';

infile fred truncover;
length var1 $ 150;
input var1 $ 1-150;
put _infile_;
run; quit;

* this strips out the headers found in the data *;

data temp2;
set temp1;
if _n_ > 7;
ordervar=_n_;
run; quit;

*********************************************************************;
* We then sort the results descending to get put the trailer data   *;
* on the top                                                        *;
*********************************************************************;

proc sort out=temp3;
by descending ordervar;
run;

*********************************************************************;
* Then we strip out the trailer data                                *;
*********************************************************************;

data temp3; set temp3;
if _n_ > 2;
run;

* then resort it back *;

proc sort data=temp3;
by ordervar;
run;

*****************************************************************;
* Then we setup the data to be processed                        *;
* We substring out the dataset name and get the extension       *;
* so we know what to do                                         *;
*****************************************************************;

data preset; set temp3;
length dbms $ 10;
dsname=reverse(scan(reverse(left(var1)),1,' '));
dsext=scan(dsname,2,'.');
sasname=scan(dsname,1,'.');
quoter='"';
fullname=left(trim(quoter))||left(trim(dir1))||left(trim(dsname))||left(trim(quoter));
if dsext='txt' then dbms='tab';
else if dsext='xls' then dbms='excel97';
else if dsext='csv' then dbms='csv';
else if dsext='dbf' then dbms='dbf';
run;

data setup; set preset;
if dbms=db;
run;
quit;


********************************************;
* Now to start the macro                   *;
* the first loop here takes all the        *;
* dataset names and figures out how many   *;
* there are and creates a counter          *;
* see the actual macro call for the        *;
* explanation of the parameters            *;
********************************************;

options macrogen symbolgen mprint;
 %macro imploop(lib1,appender,replacer,lib2,final);

data _null_;
set setup end=last;
call symput('dsnamem'||left(_n_),trim(fullname));
call symput('dbmsm' ||left(_n_),trim(dbms));
call symput('sasnamem' ||left(_n_),trim(sasname));

if last then call symput('counter',_n_);
run;


%do i= 1 % to &counter;
%put &&dsnamem&i;
%put &&dbmsm&i;
%put &&sasnamem&i;

proc import datafile=&&dsnamem&i
out=&lib1..&&sasnamem&i
dbms=&&dbmsm&i replace;
run; quit;


%end;

%if &replacer=yes %then %do;
proc datasets library=&lib2;
delete &final;
run; quit;
%end;

%if &appender=yes %then %do;
%do i= 1 % to &counter;
proc append base=&lib2..&final data=&&sasnamem&i force;
run; quit;
%end;
%end;

%mend imploop;
*****************************************************************;
* now call the macro itself                                     *;
* there are five components                                     *;
* ALL OF THESE COMPONENTS MUST BE SPECIFIED WHETHER YOU USE     *;
* THAT PARTICULAR FEATURE OR NOT, THEY WILL BE IGNORED IF       *;
* THE FEATURE IS NOT USED BUT THE MACRO STILL REQUIRES THEM     *;
*                                                               *;
* 1. the libref where the individual datasets should be stored  *;
* 2. the append routine is called if the value is yes           *;
*    this places all the individual datasets into one final     *;
*    final dataset                                              *;
* 3. whether we should create a new master dataset to append    *;
*    the datasets into or whether we should use an existing one *;
*    if it is available                                         *;
*    Yes means delete the existing master and start again for   *;
*        the run appending to it                                *;
*    No means add to the existing master for the run            *;
* 4. the libref where the final dataset should be stored        *;
* 5. the name of the final dataset                              *;
*****************************************************************;
%imploop(work,yes,yes,work,final);