DATA Step, Macro, Functions and more

%SCAN inside double quotes - how can I get around this?

Reply
Contributor
Posts: 73

%SCAN inside double quotes - how can I get around this?

Hi everyone,

I am trying to import a series of files with different names. The macro didn't work and I sort of know why, but I don't know how to get around this still using a revised macro (and not avoiding the macro). The error originates from using the macro quoting function %SCAN inside double quotes, in which everything is treated as a character/string of characters, and as a result, this function is useless. How can I fix this error? Thank you very much.

Here is the code

%macro import_tfo;
%local filenames filecount;
%let filesnames= address_type
country
/*a lot of filenames in here*/

;
%do filecount=1 %to 32 %by 1;
proc import datafile="C:\TMP\tfo.%scan[/b](&filenames, &filecount).out"
out=mf.tfo_%scan(&filenames, &filecount)
dbms=dlm replace;
delimiter='|';
getnames=no;
run;
%end;
%mend import_tfo;

Message was edited by: smilingmelbourne

Message was edited by: smilingmelbourne Message was edited by: smilingmelbourne
Valued Guide
Posts: 2,177

Re: %SCAN inside double quotes - how can I get around this?

Posted in reply to smilingmelbourne
although I'm tempted to say "don't use the PROC", let me just address the macro

add a local mVar holding the value you need.....
(and remember that the OUT= parameter needs a valid name so no special characters, other than underscore, in the &filenames )
;
> %do filecount=1 %to 32 %by 1;
%local ref ;
%let ref = %unQuote(%scan(&filenames, &filecount)) ;
> proc import
datafile="C:\TMP\tfo.&ref..out"
out=mf.tfo_&ref
> dbms=dlm replace;
> delimiter='|';
> getnames=no;
> run;
> %end;
> %mend import_tfo;
Valued Guide
Posts: 634

Re: %SCAN inside double quotes - how can I get around this?

Posted in reply to smilingmelbourne
I am interested in how the %SCAN function is failing. While there can be timing issues when working with paths, there is not a problem with macro functions inside of double quotes.
Contributor
Posts: 73

Re: %SCAN inside double quotes - how can I get around this?

Hi all,

Let me briefly describe the problem and what I want to do.

I have 32 data files in a directory. Normally, I just import one by one, but now I want to write a macro to import them all into a library. All these 32 files have different names, of course, but they have 2 common things: TFO at the front, and OUT at the end. For example, tfo.address_type.out, or tfo_country.out. My idea for the macro is that I put the middle parts of the names of these files into a macro variable called "filenames", then I use the %SCAN macro function to extract the MIDDLE parts of those names. So, I put in this piece of codes:

proc import datafile="C:\MF\Quarterly_Full_History\tfo.%scan(&filenames, &filecount).out"

To my surprise, the log windows always warns that it cannot find the physical file, i.e. the %SCAN macro function doesn't work. BUT it doesn't work in a way that doesn't make sense to me. It looks like below:

MPRINT(IMPORT_TFO): proc import datafile="C:\MF\Quarterly_Full_History\tfo..out" out=mf.tfo_
dbms=dlm replace;
MPRINT(IMPORT_TFO): ADLM;
MPRINT(IMPORT_TFO): delimiter='|';
MPRINT(IMPORT_TFO): getnames=no;
MPRINT(IMPORT_TFO): run;

ERROR: Physical file does not exist, C:\MF\Quarterly_Full_History\tfo..out.

And this is NOT what I thought of as why the error came, i.e. %macro function inside double quotes that treat everything inside as merely characters, because otherwise I would expect to see the full fragment "%scan(&filenames, &filecount) not resolved and treated as nothing like a string of characters. What's more, the %SCAN function inside the double quotes doesn't have the usual blue color font (syntax highlighting), so it doesn't work.

Could you please help? Sorry if my question is not clear or so. I have used SAS for almost 1 year now, but only have begun to learn to use macro just recently.

Thank you so much Message was edited by: smilingmelbourne
Contributor
Posts: 73

Re: %SCAN inside double quotes - how can I get around this?

Posted in reply to smilingmelbourne
Thank you all for your help. After hours of trying to figure out where the error comes from, I finally have found it. It's a typo!!! Filename sometimes get spelled as fileSname... The problem is that even the log window doesn't really help much for a beginner/inexperienced user (unlike Matlab, Stata) :-(
Valued Guide
Posts: 634

Re: %SCAN inside double quotes - how can I get around this?

Posted in reply to smilingmelbourne
You probably also determined that there is an extra . (dot/period) between the TFO and the %SCAN.
Regular Contributor
Posts: 241

Re: %SCAN inside double quotes - how can I get around this?

Posted in reply to smilingmelbourne
> Hi everyone,
>
> I am trying to import a series of files with
> different names. The macro didn't work and I sort of
> know why, but I don't know how to get around this
> still using a revised macro (and not avoiding the
> macro). The error originates from using the macro
> quoting function %SCAN inside double quotes, in which
> everything is treated as a character/string of
> characters, and as a result, this function is
> useless. How can I fix this error? Thank you very
> much.
>
> Here is the code
>
> %macro import_tfo;
> %local filenames filecount;
> %let filesnames= address_type
> country
/*a
> /*a lot of filenames in here*/
> ;
> %do filecount=1 %to 32 %by 1;
> proc import
> datafile="C:\TMP\tfo.%scan[/b](&filenames
> , &filecount).out
"
> out=mf.tfo_%scan(&filenames, &filecount)
> dbms=dlm replace;
> delimiter='|';
> getnames=no;
> run;
> %end;
> %mend import_tfo;

...
A better way to write a macro for something like this is to write two macros, instead of one. One for doing a task (here importing a file), and another for looping over a list of items. In this way, you first write the first one and test it out, and then when it works well, you concentrate doing the second step (or not). Hope this helps a bit.
[pre]
%macro doOne(datafile=);
%if %superq(datafile)= %then %return;
%*-- do something with datafile --*;
%put datafile=&datafile;
%mend doOne;

%macro doAll(list=);
%local el i;
%let i = 1;
%let el = %scan(&list, &i);
%do %while(&el^=);
%doOne(datafile=&el)
%let i = %eval(&i + 1);
%let el = %scan(&list, &i);
%end;
%mend doAll;

%*-- you can test one or two --*;
%doOne(datafile=a)
%doOne(datafile=b)
%*-- on log
datafile=a
datafile=b
--*;

%*-- if it works well and do a lot --*;
%doAll(list=c d e f g)
%*-- on log
datafile=c
datafile=d
datafile=e
datafile=f
datafile=g
--*;
[/pre]
Ask a Question
Discussion stats
  • 6 replies
  • 518 views
  • 0 likes
  • 4 in conversation