Importing multiple Excel files in SAS

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;

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

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

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));
        call execute('
  proc import dbms=xlsx out= _test
            datafile= '||fname||' replace ;
  proc append data=_test base='||out||' force; run;
  *proc delete data=_test; run;

    filename myfiles clear;

%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.

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



Subhro provides valuable and informative content on SAS, offering a comprehensive understanding of SAS concepts. We have been creating SAS tutorials since 2019, and 9to5sas has become one of the leading free SAS resources available on the internet.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This Post Has 3 Comments

  1. Malick

    HI, Hope you re going well.
    I tried the first method, The program run but it only take the last excel file at the end. the other ones are not considered. could you helps me please ?

    1. Subhro Kar

      Hi Malick,
      It is appreciated that you pointed out the error.
      You need to interchange the dataset names in Proc Append Step.

      proc append data=_test base=_merged force; run;