Hi SAS Community, Please could someone assist me? I am new to SAS. My Objective: To import many excel files into sas. My Understanding: 1. a macro can be used. 2. I am able to import a single excel file into sas. 3. I have not edited the code correctly due to lack of understanding Background: Each excel file has a unique name. All excel files are stored in a FOLDER_GENERIC_NAME I want to import all excel files stored in FOLDER_GENERIC_NAME to sas What I have done thus far: 1. Attempted to adapt the code found online to meet my objective. Below is the code I attempted to customize to meet my objective. /*Scanning Excel Files*/ data playpen_dirlisting; drop rc did i; rc=GENERIC_FOLDER_NAME("NAME_OF_FOLDER_WHERE_EXCEL_FILES_ARE_FOUND" , "\\ FILE_LOCATION"); did-DOPEN("NAME_OF_FOLDER_WHERE_EXCEL_FILES_ARE_FOUND"); IF did > 0 then do; do i=1 to DNUM(did); name=DREAD(did,i) OUTPUT; END; rc=DCLOSE(did); ENDl; ELSE PUT 'Could not open dir'; run; /*Creating Names for data sets when reading in many excel files*/ libname mcrsheet "%sysfunc(trim(&&NAME_OF_EXCEL_FILE&c))"; proc sql noprint; create table outsheet&c as select distinct memname from playpen_dirlisting; where libname="macrsheet" And nametype="DATA" And INDEX(memname,'$'); quit; data outsheet&c; set outsheet&c (rename=(memname=sheet)); fileloc="&&NAME_OF_EXCEL_FILE&c"; name="&&EXCEL_FILE_NAME&c"; run; libname mcrsheet clear; /*Importing the actual excel files*/ libname newdata "%sysfunc(trim(&&FILE_LOCATION&c))"; data work.&& dataset_name&c (drop=obs); set newdata."%sysfunc(trim(&&sheet&c)) $" n; obs=_n_; if obs>0 then call symputx ("rep" , "yes"); run; libname newdata clear;
... View more