So far, I have created a series of macros that creates a dataset containing a list of file names and the subfolder in which they reside within a folder. Thanks to http://www2.sas.com/proceedings/sugi26/p093-26.pdf. In my dataset Fldrsnfiles there are two variables: Fldr and File. As an example, Fldr=Apr 2016; /* the value of the Fldr field in my dataset */ File=04-01-16 pk trf's.xlsx; /* the value of the file field in my dataset - problem area*/ The goal now is to use a similar macro to dynamically change a libname statement to read all the files in the main sub the file. But some of the file names have singe quotes in them!!!! YUCK!! This is creating an unquoted string problem when I run the macro below. I need away for the program to ignore single quotes in the File variable. Thank you everyone. %macro extractdata(scanfile, Field1, Field2); /* first obtain the number of records in the folderNames dataset*/ data _NULL_; IF 0 then SET &SCANFILE NOBS=X; Call SYMPUT('RECCOUNT',X); STOP; RUN; /* Loop from one to number of records */ %DO I=1 %To &RECCOUNT; /* advanced to the Ith record*/ DATA _NULL_; SET &SCANFILE (FIRSTOBS=&I); /*store the foldername and filename in macro variables*/ CALL SYMPUT('SubFolder', FLDR); CALL SYMPUT('File', FILE); STOP; RUN; /* perform getting all the file names with name of subfolder into a dataset*/ libname ACH pcfiles Path= "&Location&LoopFldr\&LoopFile"; title "&SubFolder&File"; Proc print data= ACH.'Sheet1$'n; run; title; libname ACH clear; %end; /*end of Do */ %MEND extractdata; %extractdata(scanfile=Fldrsnfiles, Field1=FLDR, Field2=file);
... View more
