DATA Step, Macro, Functions and more

Dynamic Libname pcfiles statement with single quote in workbook name

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Dynamic Libname pcfiles statement with single quote in workbook name

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


Accepted Solutions
Solution
‎10-31-2016 09:02 PM
Frequent Contributor
Posts: 141

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

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

Thanks,
Suryakiran

View solution in original post


All Replies
Solution
‎10-31-2016 09:02 PM
Frequent Contributor
Posts: 141

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

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

Thanks,
Suryakiran
Occasional Contributor
Posts: 10

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

Posted in reply to SuryaKiran
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!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 236 views
  • 1 like
  • 2 in conversation