CODEWITHSUNDEEP
sas
Robert
Robert

Reputation: 2231

Reading n files into SAS to create n datasets

I have just started learning SAS, and I'm using the following code to read xlsx files:

proc import out = data_lib.dataset_1
  datafile = 'C:\data_folder\data_file_1.xlsx'
  dbms = xlsx replace;
  sheet = 'Sheet1';
  getnames = yes;
run;

This has been working fine for me, but I'd like to supply the code with a list of filenames to read and a list of dataset names to create, so that the code need only appear once. I have looked at several instructional web pages about using macros, but I've been unable to translate that information into working code. Any help would be greatly appreciated. I'm using SAS 9.4, 64 bit.

Upvotes: 0

Views: 68

Answers (2)

DWal
DWal

Reputation: 2762

I'd offer a modified version of kl78's suggestion, avoiding macros. Again, assuming you have the file names in a SAS data set, use a data step to read the list of file names and use call execute to run your proc import code for each file name.

data _null_;
  set t_list;
  call execute (
   "proc import out = " || datasetname || "
      datafile = '"|| filename ||"'
      dbms = xlsx replace;
      sheet = 'Sheet1';
      getnames = yes;
    run;");
run;

Upvotes: 1

kl78
kl78

Reputation: 1666

So, suppose you have your filenames and datanames in a table called t_list with variablename datasetname and filename, you could try something like this:

 %macro readexcels;

 data _null_;
  set t_list (nobs=nobs);
  call symputx(cat("libname_",_n_), datasetname);
  call symputx(cat("filename_",_n_), filename);
  if _n_=1 then
   call symputx("nobs", nobs);
  run;

%do i=1 %to &nobs;

proc import out = &&libname_&i;
datafile = "&&filename_&i"
 dbms = xlsx replace;
 sheet = 'Sheet1';
 getnames = yes;
run;
%end;
%mend;

%readexcels;

In the datastep you read every entry of your table with datasetname and listname and create macrovariables with a numeric suffix. You only need to create a macrovariable for the number of entries once, so i did it when n = 1, you could also do this at eof.

Then you have a do loop, and with every loop you read the specific excel and write it in the specific dataset. You need to write it like &&libname&i, because at first this resolves to &libname_1, and after this resolves to the variablevalue...

Upvotes: 1

Related Questions