BookmarkSubscribeRSS Feed
smilingmelbourne
Fluorite | Level 6
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
7 REPLIES 7
Peter_C
Rhodochrosite | Level 12
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;
ArtC
Rhodochrosite | Level 12
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.
smilingmelbourne
Fluorite | Level 6
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
smilingmelbourne
Fluorite | Level 6
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) 😞
ArtC
Rhodochrosite | Level 12
You probably also determined that there is an extra . (dot/period) between the TFO and the %SCAN.
chang_y_chung_hotmail_com
Obsidian | Level 7
> 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]
vkabdwal
Obsidian | Level 7

Say y = %scan(&list, &k., ",")

 

Now if you need character output in double quotes, you just need to make the following change:

 

y = "%scan(&list, &k., ",")"

 

Also use %scan in a loop only

 

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
  • 2726 views
  • 0 likes
  • 5 in conversation