Date:         Mon, 7 Jul 2008 14:55:32 -0500
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Input All Files in a Folder
Comments: To: Joey Engelberg <j-engelberg@KELLOGG.NORTHWESTERN.EDU>
Content-Type: text/plain; charset="iso-8859-1"

Here's some code that I use to import many sheets from an Excel File- I have one sheet that has all the names of the other sheets, so I read that first, then use those names to read the rest of the sheets.  You could either setup a file which had all the names of your files, or possibly write some code to grab that (I'm not sure how to do that, but I've seen it here). 

Anyway, here's how to drive importing a lot of sheets from one Excel Workbook; reading a lot of files from one directory would be similar.  This does give a bit more control than some of the other approaches.  

-Mary

%macro import_data(study_nbr);

PROC IMPORT OUT= WORK.set1 

DATAFILE= "C:\Work_Activities\injections_study_patients\injection_study.xls" 

DBMS=EXCEL REPLACE;

SHEET="&study_nbr"; 

GETNAMES=YES;

MIXED=YES;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;



data set1_all;

set set1_all set1; 

run;

%mend import_data;

data patient_info_all;

run;


data set1_all;

informat study_nbr $20.;

if study_nbr=' ' then delete;

run;

%macro import_batch;

PROC IMPORT OUT= WORK.patient_info_batch 

DATAFILE= "S:\Users\howardm\Database\injection_study\injection_study.xls" 

DBMS=EXCEL REPLACE;

SHEET="Patient_Info"; 

GETNAMES=YES;

MIXED=YES;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

data patient_info_batch;

set patient_info_batch;

obsnum + 1;

if study_nbr=' ' then delete;

run;

%Local I;

proc sql noprint;

select count(*) into :model_count

from patient_info_batch;

quit;

%Do I = 1 %To &model_count;

proc sql noprint;

select study_nbr into :study_nbr

from patient_info_batch

where obsnum =&i;

quit;

%import_data(&study_nbr);

%End ;

%mend import_batch;



%import_batch; 



  ----- Original Message ----- 
  From: Joey Engelberg 
  To: SAS-L@LISTSERV.UGA.EDU 
  Sent: Monday, July 07, 2008 2:23 PM
  Subject: Input All Files in a Folder


  I have a question that I suspect has an answer, but I have seached online
  for quite a long time and have yet to find one.

  Suppose I have a folder called "MySasFolder" and inside MySasFolder I have
  two files: "dog.txt" and "cat.txt".  Inside the dog.txt file and cat.txt
  files I have data that I want to read into a SAS dataset.

  Is there a way that SAS can loop through all files in a folder and input
  them into one SAS dataset without me having to tell SAS the names of each
  file (I am working on a case where I have 1800 files in a folder)?  When I
  input each file into the SAS dataset I also need to keep the name of each
  file as a variable (i.e. I would like to keep dog.txt and cat.txt in a
  variable called FileName in my SAS dataset).

  Any help would be much appreciated!!!

  Thanks,

  Joey