BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
7 REPLIES 7
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
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!
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
No clue. Sorry.
deleted_user
Not applicable
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.....
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
Thank you! I'll check out Koen Vyverman's papers.
deleted_user
Not applicable
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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1442 views
  • 0 likes
  • 3 in conversation