Importing multiple Excel files to create a single SAS dataset

This example shows you how to import multiple excels files with the same variable names from a folder and then merge data from all the data sets to a single data set.

Data Step Method

filename indata '/home/9to5sas/inputs/*.xlsx';

data path_list_files;
length fpath sas_data_set_and_path $100;
retain fpath;
infile indata truncover filename=sas_data_set_and_path;
input;

if fpath ne sas_data_set_and_path then
do;
fpath=sas_data_set_and_path;
sysrc=filename('fnames', quote(trim(fpath)));

if sysrc ne 0 then
do;
er1=sysmsg();
error 'filename failed: ' er1;
stop;
end;
call execute('
proc import dbms=xlsx out=_test
datafile= fnames replace;
run;
proc append data=_merged base=_merged force; run;
');
output;
end;
filename fnames clear;
drop er1 sysrc;
run;

Using SAS Macro

The 2nd method uses the pipe command to run the Unix command. Some of you may get the below error while executing this macro.

ERROR: Insufficient authorization to access PIPE.

By default, SAS sets the NOXCMD option which restricts users to submit any OS-level commands from their SAS sessions/codes.

To resolve the issue, you need to ask your SAS admin to click the check box “Allow XCMD” within SAS Management Console -> Server Manager -> SAS App – Workspace Server -> properties -> Options ->Advanced Options -> Launch Properties tab.

%macro multiexcel(dir=, out=);
    %let rc=%str(%"ls &dir.%");
    %put &rc.;
    filename myfiles pipe %unquote(&rc);
    %put path=%sysfunc(pathname(myfiles));

    data list;
        length fname $256.;
        infile myfiles truncover;
        input myfiles $100.;
        fname=quote(cats("&dir", myfiles));
        out="&out";
        call execute('
  proc import dbms=xlsx out= _test
            datafile= '||fname||' replace ;
  run;
  proc append data=_test base='||out||' force; run;
  *proc delete data=_test; run;
');
    run;

    filename myfiles clear;
%mend;

%multiexcel(dir=/home/9to5sas/inputs/, out=merged);

We hoped this article helped you to append multiple excel files and create a single SAS dataset.

[ You might also like: Generating Multiple Excel Files ]

You may also want to see our article on How To Import Data Using Proc Import? and  Coding Efficiently Using SAS Macros as well.

Download the entire code from this post here.

Every week we'll send you SAS tips and in-depth tutorials

JOIN OUR COMMUNITY OF SAS Programmers!

Subhro Kar is an Analyst with over five years of experience. As a programmer specializing in SAS (Statistical Analysis System), Subhro also offers tutorials and guides on how to approach the coding language. His website, 9to5sas, offers students and new programmers useful easy-to-grasp resources to help them understand the fundamentals of SAS. Through this website, he shares his passion for programming while giving back to up-and-coming programmers in the field. Subhro’s mission is to offer quality tips, tricks, and lessons that give SAS beginners the skills they need to succeed.