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);
Use %BQUOTE might help you.
%extractdata(scanfile=Fldrsnfiles, Field1=FLDR, Field2=%BQUOTE(file));
For more information this paper might be helpful to you.
http://www2.sas.com/proceedings/forum2007/152-2007.pdf
Use %BQUOTE might help you.
%extractdata(scanfile=Fldrsnfiles, Field1=FLDR, Field2=%BQUOTE(file));
For more information this paper might be helpful to you.
http://www2.sas.com/proceedings/forum2007/152-2007.pdf
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.