Searching and importing all .csv files in a target directory

I have to scan a target directory, and import every CSV file there into SAS. I am to use separated macros to fix names of those files (they are intentionaly problematic for SAS) and actually import them. I have XCMD disabled, so pipe solution isn't usable for me (the only solution my course provided me, so several exercises are a bit problematic for me). My attempt so far is this:

%let path=my_dir

%macro fixname(badname);
    %if %datatyp(%qsubstr(&badname,1,1))=NUMERIC
        %then %let badname=_&badname;
    %let badname=
        %sysfunc(compress(
            %sysfunc(translate(&badname,_,%str( ))),,kn));
    %substr(&badname,1,%sysfunc(min(%length(&badname),32)))
%mend fixname;

%macro importcsv(file);
    options nonotes nosource;
    proc import
        datafile="%superq(file)"
        out=%fixname(%scan(&file,-2,.\)) replace
        dbms=csv;
    run;
options notes source;
%mend importcsv;

filename folder "&path";

data FilesInFolder;
    length Line 8 File $300;
    List = dopen('folder');
    do Line = 1 to dnum(List);
        File = trim(dread(List,Line));
        output;
    end;
    drop list line;
run;

proc sql noprint;
    select * into :file1-
        from FilesInFolder
            where File like '%.csv'
    ;
quit;

%macro loop;

    %do i=1 %to &sqlobs;
        call execute(cats('%importcsv('&path.\&&file&i')'));
    %end;

%mend loop;
%loop;

Looking at logs there seems to be something wrong with call execute line, but for the life of me I cannot fix it.

Solution

As regards the requested call execute line, this will not work.

call execute(cats('%importcsv('&path.\&&file&i')'));

The cats is not properly constructed. It's unclear why you're even using cats, as you aren't really concatenating anything. It's clear why you need to use it, but not why you're currently using it.

First: you have single quotes around the string, which means it won't resolve the macro variables. That is a problem for you. In particular, you want &i to resolve. You also, though, don't want %importcsv itself to resolve.

Second: you have single quotes inside single quotes, or else you have unquoted text.

The right way to do this is:

call execute(cats('%importcsv(',"&path.\&&file&i.",')'));

The goal here is to make sure %importcsv doesn't resolve - so we wrap it in single quotes - but get the macro variables to resolve - wrapped in double quotes. It would be okay for &path not to resolve (as it's a global variable), but &&file&i absolutely needs to, since it's what you are looping over.

The final ')' is unnecessary (it could be included inside the double quotes), but I like it for readability (it makes it obvious that it's the end of the macro invocation). In this case it's unimportant, but sometimes the middle bits have more parentheses in them, and it becomes unclear when you have the right number.

All this aside, you're making this much too complicated. Here:

proc sql noprint;
    select * into :file1-
        from FilesInFolder
            where File like '%.csv'
    ;
quit;

Why not simply put the macro invocation itself?

proc sql noprint;
    select cats('%importcsv(',"&path.\",file,')')
       into :filelist separated by ' '
        from FilesInFolder
            where File like '%.csv'
    ;
quit;
&filelist

Now you have &filelist which contains all of the macro invocations, and you can just invoke them, no macro loop needed.

You can check out my presentation on Writing Code With Your Data if you want to know more about how that works.

Of course, if this is homework and you have to do the first way, it's fine, but in the real world the second way is superior unless you have > 60k characters worth of macro calls.