CODEWITHSUNDEEP
loopsimportmacrossas
sonu
sonu

Reputation: 29

How to import csv files using macro loops in sas?

I am trying to write a SAS macro that loops through several csv files to the library. Unfortunately, I have not been very successful in making this work. This is what I have so far: 

%let list = "cat and dogs" "monkeys" "humans";
%macro loop;
%do _i=1 %to %sysfunc(countw(&list.));
%let read_list = %scan(&list., &i.)
    proc import datafile="[path] - &read_list." 
        out=displayfile&read_list. dbms=csv replace;
    run;
%end; 
%mend;

Update:

I updated my code to 

%macro read; 
%let list = (humans, cats and dogs) ;

%do i= 1 %to 7; 
%let fnames = %scan(&list., &i.); 
    proc import datafile= "path- &fnames..csv" 
        out=&fnames. dbms=csv replace;
    run; 
    %end; 
%mend;

This works for the files ending with "humans," but not for the files ending in "cats and dogs" due to the space in the string. Is there a workaround?

Upvotes: 1

Views: 664

Answers (2)

Wilson Jimenez
Wilson Jimenez

Reputation: 60

I agree with the last answer. Data Set Names must start with a letter or underscore, subsequent characters can be letter, underscores, or numeric digits. One of names of list is cats and dogs. I think when you run the code, you will see an error in the log. But I run the last code with comments in proc import data step and it's according below.

%macro read;

%let list = (humans, cats and dogs);

%do i= 1 %to 7; 

%let fnames = %scan(&list., &i.); 

/*

proc import datafile= "path- &fnames..csv" 

    out=&fnames. dbms=csv replace;

run; 

*/

%put &fnames;


%end; 

%mend; 

%read;

SAS Log show this:

humans

cats

and

dogs

Upvotes: 1

Tom
Tom

Reputation: 51566

I would recommend using a different delimiter in your list. Something like | that cannot be part of a filename.

%let list =cat and dogs|monkeys|humans;
... %sysfunc(countw(&list,|)) ...
... %scan(&list,&i,|) ...

The other problem is that you need to make sure that you are generating valid SAS dataset names. It might be better to use the number in the dataset name and use the string as part of the label.

out=csvfile&i (label="&fnames")

The problem with both of your examples is you are telling the %scan() function (and countw() function) to use their default set of delimiter characters. Which on ASCII systems are these characters:

blank ! $ % & ( ) * + , - . / ; < ^ ¦

So in your first example the only delimiters in your string are the blanks so the words in your string are:

"cat
and
dogs"
"monkeys"
"humans"

Notice how the quotes are part of the words that %scan() finds since they are not delimiters. So even the values like "humans" will not work as you are trying to use them because of the extra quote character they include. And splitting "cat and dogs" into three words adds the extra problem of unbalanced quotes.

In your second you also have three other characters, (,), which will be treated as delimiters so the words are:

humans
cats
and
dogs

If you really wanted to process a space delimited list of quoted strings then tell %scan() to use blank as the delimiter and add the q modifier to allow quoted strings. You probably will also want use the dequote() function to remove the quotes from around the values.

Try this test program to see how to do it.

%macro test(list);
%local i word ;
%do i=1 %to %sysfunc(countw(&list,%str( ),q));
  %let word=%sysfunc(dequote(%qscan(&list,&i,%str( ),q)));
  %put i=&i word=&word;
%end;
%mend test;
%test("cat and dogs" "monkeys" "humans")

Upvotes: 3

Related Questions