How to Import multiple Excel files in SAS?

This example shows you how to Import multiple Excel files in SAS with the same variable names from a folder and then merge data from all the data sets into a single one.

Data Step Method to Import multiple Excel files in SAS

filename indata '/home/9to5sas/external_files/region/*.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=_test base=_merged force; run;
');
			output;
		end;
	filename fnames clear;
	drop er1 sysrc;
run;

Using SAS Macro to Import multiple Excel files in SAS

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, restricting users from submitting 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.

filename indata '/home/subhroster20070/9to5sas/external_files/region/*.xlsx';

%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/subhroster20070/9to5sas/external_files/region/, out=merged);

We hope this article helped you import multiple Excel files in SAS and create a single dataset.

[ You might also like: Generating Multiple Excel Files ]

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

Download the entire code from this post here.