sas macro to pull data from multiple files

Question

I'm trying to import data from 15 different txt files into sas. I want to feed the different file names into an array and then use the array elements inside a macro to bring in all the data into the work folder. The following did not work; any help is much appreciated !!

%macro DATAIMP;
array filenames(3) visit visit_event department
%do i =1 %to 3 %by 1
proc import
datafile="C:\Users\AR\Documents\data\&filename(i).txt"  
OUT= &filenames(i)_1
dbms=dlm replace;
delimiter=";";
getnames=yes;
run;
end;
%mend DATAIMP;

%DATAIMP;
run;

user3645882 user3645882 · Accepted Answer · 2014-06-03T08:57:14

array is a statemenet within a data step, you cannot use it like that. What you can do is create a data set containing all your file names and create macro variables from that:

data file;
input filename $50.;
datalines;
visit 
visit_event 
department
;
run;


%macro DATAIMP;
data _NULL_; /*local macro variables called FILENAME1, FILENAME2,...*/
    set file end=fine;
    call symput("FILENAME"||compress(_N_),filename);
    if fine then call symput("NF",compress(_N_));
run;
%DO I=1 %TO &NF;
    proc import
    datafile="C:\Users\AR\Documents\data\&&FILENAME&I...txt"  
    OUT= &&FILENAME&I.._1
    dbms=dlm replace;
    delimiter=";";
    getnames=yes;
    run;
%END;
%mend DATAIMP;

%DATAIMP;

Remember that && resolves to & and for each macro resolution you need a . to mark the end of the macro variable.

sas macro to pull data from multiple files

1 Answers