SAS - How can I import data from information in an existing table

Question

I need to import several files to a library each month and thought I would make it simple by creating a .csv with all of the file information (path, name, worksheet, type) and use a macro to loop through and bring in the information; however it doesn't like it when I pass it a macro variable. The proc import works fine if I just put the information in there. I've looked around and saw that the macros are compiled differently than the proc statements; but I refuse to believe that I have to write out 30 proc import statements just to get these files in. It'd be much easier to manage from a .csv. Please take a look and offer your advice. Thanks!

PROC IMPORT OUT= FileInfo
        DATAFILE= 'C:\folder' 
        DBMS=CSV REPLACE;
 GETNAMES=YES;
 DATAROW=2; 
RUN;

%Macro BringIt();

DATA _NULL_;
    IF 0 THEN SET FileInfo NOBS=X;
    CALL SYMPUT('RCRDS',X);
    STOP;
RUN;

%Do i=1 %to &RCRDS;

    data _null_; 
        set FileInfo (firstobs=&i);

            call symput('Fname',FLEname);
            call symput('Ftype',FLEtype);
            call symput('sheet',trim(Worksheet)||'$');
            call symput('Slib',SASlib);
            call symput('Sname',SASname);
            call symput('loc',FLELocation||'\'||FLEname||fletype);

        stop;
    run;


    *if &Ftype='.xlsx' then
        do;
            PROC IMPORT OUT=&sname
                    DATAFILE=&loc
                    DBMS=EXCEL REPLACE;
                 RANGE=&sheet; 
                 GETNAMES=YES;
                 MIXED=NO;
                 SCANTEXT=YES;
                 USEDATE=YES;
                 SCANTIME=YES;
            RUN;

        *end; 
    /*end*/
%end;
%mend BringIt;
%BringIt();
run;

Anton Anton · Accepted Answer · 2014-02-15T09:37:11

I do not know what is in your Worksheet column but from this statement:

call symput('sheet',trim(Worksheet)||'$');

I can guess it is unquoted string while RANGE option in PROC IMPORT wants quoted string:

RANGE=&sheet;

Consider enclosing &sheet in double quotes. PROC IMPORT can throw some funny errors if it cannot find the range specified.

[EDIT] P.S. It is a classic example where CALL EXECUTE would be more handy.

SAS - How can I import data from information in an existing table

1 Answers