SAS libref not recognized in macro loop

0
votes

I've run into an odd SAS quirk that I can't figure out - hopefully you can help.

I have a simple macro loop that imports CSV files and for some reason if I use a libref statement in the "out=" part of the import procedure, SAS doesn't recognize the libref as a valid name. But if I use the same libref in a data step, it works just fine.

The specific error it gives is: "ERROR: "TESTDB." is not a valid name."

I'd like to figure this out because I work with pretty big files and want to avoid reading through them more times than is necessary.

Here's the code that works, with some comments in it. I got around the issue by reading in the files, then writing them to permanent SAS datasets in a second step, but ideally I'd like to import the files directly into the "TESTDB" library. Any idea how to get SAS to recognize a libref in the "out=" statement of the import procedure?

libname testdb "C:\SAS test"; 
%let filepath = C:\SAS test\;

%macro loop(values); 
    %let count=%sysfunc(countw(&values)); 
    %do i = 1 %to &count; 
        %let value = %qscan(&values,&i,%str(,));
        proc import datafile = "&filepath.&value..csv" 
            out = &value dbms=csv replace; getnames=yes;
                     /*"out=testdb.&value" in the line above does not work*/
        run; 
        data testdb.&value; set &value; run; 
                 /*here the libref testdb works fine*/
    %end; 
%mend; 
%loop(%str(test_a,test_b,test_c));

Thanks in advance for your help!

john

2
sassas-macro
Do you get the same error if you use something other than a comma as the delimiter character for your list of files? – user667489
Can you post the relevant portion of the log with mprint and symbolgen options turned on? – Reeza
Why are you using %QSCAN()? Are there characters in the filenames that need quoting? Also instead of using commas as the delimiter in the macro variable VALUES use something like | (pipe) that is invalid in a filename. – Tom

2 Answers

2
votes

Perhaps try:

out=testdb.%unquote(&value)

Sometimes the macro language does not unquote values automatically. With result that the extra quoting characters introduced by a quoting function (%qscan %str %bquote %superq etc) cause problems.

0
votes

Strange error. I am not able to pin it. My guess is that it has something to do with how the value macro variables are being created. When I moved the value variable creation to a data step and used Call Symputx, it works. 

%macro loop(files); 
    /* Create macro variables for files.*/
    data _null_;
        count = countw("&files.",",");
        call symputx("count",count,"L");
        do i = 1 to count;
            call symputx(cats("file",i),scan("&files.",i,","),"L");
        end;
    run;

    /* Read and save each CSV as a sas table. */
    %do i=1 %to &count.;
        proc import datafile = "&filepath.&&file&i...csv" 
            out = testdb.&&file&i. dbms=csv replace; getnames=yes;
        run; 
    %end;
%mend; 

%loop(%str(test_a,test_b));