DATA Step, Macro, Functions and more

Single quote (') macro

Reply
N/A
Posts: 0

Single quote (') macro

Hello -

I'm trying to create a macro variable to read in the input file name and then passing that variable to an infile statement in which I have single quotes. I'm using sas to open the excel file. When I use the put statement to display the macro variable value it shows fine, but when I use it inside a datastep I get error message. Here's the code and the error message. Thanks!

%let inputfiles=F:\Store Risk Model\ABC\2009\Period_03\InputFiles;
%let in_filename=LOCN Extract - Latest Update.xls;

options noxwait noxsync;
x '"C:\Program Files\Microsoft Office\Office10\excel.exe"';

data _null_;
x=sleep(2);
run;

filename ddecmd dde 'excel|system';

data _null_;
file ddecmd;
put %BQUOTE(')[FILE-OPEN("&inputfiles.\&in_filename.")]%BQUOTE(');
run;

%put %BQUOTE(')[FILE-OPEN("&inputfiles.\&in_filename.")]%BQUOTE(');

The get the following error:

30 data _null_;
31 file ddecmd;
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL may allow recovery of the LINE and COLUMN where the error has
occurred.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, arrayname, #, (, +, /, //, ;, @, @@,
OVERPRINT, _ALL_, _BLANKPAGE_, _ODS_, _PAGE_.
32 put %BQUOTE(')[FILE-OPEN("&inputfiles.\&in_filename.")]%BQUOTE(');
-
200
ERROR: Undeclared array referenced: NAME.
ERROR 200-322: The symbol is not recognized and will be ignored.
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL may allow recovery of the LINE and COLUMN where the error has
occurred.
ERROR 22-322: Syntax error, expecting one of the following: a name, an integer constant, arrayname, #, $, &, (, +, /, //, :,
;, =, ?, @, @@, OVERPRINT, _ALL_, _BLANKPAGE_, _ODS_, _PAGE_, ~.
32 put %BQUOTE(')[FILE-OPEN("&inputfiles.\&in_filename.")]%BQUOTE(');
-
200
ERROR 200-322: The symbol is not recognized and will be ignored.
33 run;
Super Contributor
Super Contributor
Posts: 3,174

Re: Single quote (') macro

There are multiple techniques - here is one:

Code your DATA step PUT so that you make use of a SAS variable constant which contains the double-quote character:

data _null_;
retain dquote '"';
put "'[FILE-OPEN(" dquote +(-1) "&inputfiles.\&in_filename." dquote +(-1) ")]'";
run;


Scott Barry
SBBWorks, Inc.
N/A
Posts: 0

Re: Single quote (') macro

Thanks so much for the reply. The code does open up the file, howver, it inserts one double quote (") in the first cell of the opened file. Do you know what's causing this?

Thanks!
Super Contributor
Super Contributor
Posts: 3,174

Re: Single quote (') macro

No clue. Sorry.
N/A
Posts: 0

Re: Single quote (') macro

Thanks for helping me...atleast I can open the file now:-)

Actually, it does not insert double quotes in the first cell rather the first cell from where the data begins. I have some header information in the first 4 records and it inserts a single double quote in the 5th record (where the data for input begins). Which is strange, because I haven't even specified from which record to read the data. hmmm...and the quest continues.....
SAS Super FREQ
Posts: 8,740

Re: Single quote (') macro

Hi:
I don't use DDE that much, but I believe your problem is happening because you are not writing a text string to a file, but are instead writing a DDE command to the DDE conversation between SAS and Excel.

If you Google for any of the Forum papers by Koen Vyverman on using DDE, you will find that he has a lot of good information on using DDE and many good examples, In one of this papers, I found this snippet, in which he gets around the macro quoting issue, by making a variable called DDECMD and then his PUT statement writes the DDECMD variable. This allows for all the quotes to be in the right place in the command and for the macro variables to be resolved correctly as the text string is built (quote below from his paper "Excel Exposed: Using Dynamic Data Exchange to Extract Metadata from MS Excel Workbooks" Proceedings of the 10th South-Eastern SAS Users Group Conference, 2003.):
[pre]
When Excel has started, we proceed to open our ‘Sales
Demo.xls’ workbook:
data _null_;
file sas2xl;
length ddecmd $ 200;
put '[error(false)]';
ddecmd='[open("'||"&wb_path"||'\'||
"&wb_name"||'")]';
put ddecmd;
run;
[/pre]


cynthia
N/A
Posts: 0

Re: Single quote (') macro

Thank you! I'll check out Koen Vyverman's papers.
N/A
Posts: 0

Re: Single quote (') macro

Thank you...thank you...thank you to both of you! You have been great!

It finally worked with the following program.

data _null_;
file ddecmd;
put %unquote(%bquote('[FILE-OPEN("&inputfiles.\&in_filename.")]'));
run;
Ask a Question
Discussion stats
  • 7 replies
  • 628 views
  • 0 likes
  • 3 in conversation