Dynamic Libname pcfiles statement with single quote in workbook name

KRusso — Mon, 31 Oct 2016 19:01:57 GMT

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);

Re: Dynamic Libname pcfiles statement with single quote in workbook name

SuryaKiran — Mon, 31 Oct 2016 19:54:11 GMT

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

Re: Dynamic Libname pcfiles statement with single quote in workbook name

KRusso — Tue, 01 Nov 2016 01:05:01 GMT

Elegant, simple, and excellent reference to handle future issues with macros and quotes. I know that I'll come across this again and be sharing it with others. 1000 Thank yous!

topic Dynamic Libname pcfiles statement with single quote in workbook name in SAS Programming

Dynamic Libname pcfiles statement with single quote in workbook name

Re: Dynamic Libname pcfiles statement with single quote in workbook name

Re: Dynamic Libname pcfiles statement with single quote in workbook name