topic How to Import multiple excel file at a time in SAS Programming

How to Import multiple excel file at a time

vThanu — Wed, 21 Nov 2018 10:59:26 GMT

How do we import multiple excel files for a particula library at a time in SAS with different extensions ?

eg: E:\than\proc.excel

E:\than\test.excel

E:\than\final.excel

E:\than\week.excel

..... so on.

I want to read all the above file at a time into SAS..?

Re: How to Import multiple excel file at a time

RW9 — Wed, 21 Nov 2018 11:12:54 GMT

"AS with different extensions " - what does this mean. Do you have xls, xlsx etc. files? If so you need a different dbms for each type. What operating system? For unix based systems you will need PCFILES server for instance. On Windows if you have Office installed it may be easier.

"I want to read all the above file at a time into SAS..?" - and how will this work, will you just be leaving it to proc import to guess what the data should look like - i.e. Garbage in garbage out? What you get from that will likely not be exactly what you think you will get.

As for how to do it, get a list of files from command prompt and then call a macro e.g. (windows);

filename tmp pipe 'dir "e:/than/*.*" /b';

data _null_;
  infile tmp dlm="¬";
  input;
  if index(upcase(_infile_),"XLS") then call execute(cats('%imp_xls (fnam=',_infile_,');'));
  else call execute(cats('%imp_xlsx (fnam=',_infile_,');'));
run;

Re: How to Import multiple excel file at a time

vThanu — Thu, 22 Nov 2018 06:35:38 GMT

In windows environment only and one specific library with differe file name with same extension...

using macro I want to import (read) mutliple file into SAS at a time.

Re: How to Import multiple excel file at a time

RW9 — Thu, 22 Nov 2018 08:11:33 GMT

Use the code provided, minor change if they are all xlsx files (remember that what you get from this maybe garbage - Excel is a bad data source, and proc import is a guessing procedure, combined they are not good):

filename tmp pipe 'dir "e:/than/*.xlsx" /b';

data _null_;
  infile tmp dlm="¬";
  input;
  call execute(cats('proc import file="e:/than/',_infile_,'" out=want',put(_n_,best.),' replace dbms=xlsx; run;'));
run;

This will create one proc import for each file in the given directory, and create an output dataset wantX where X is incremental.