topic Re: IMPORT all excel files in a folder in SAS Programming

IMPORT all excel files in a folder

gzr2mz39 — Fri, 05 Sep 2014 18:58:45 GMT

Hi,

Does anyone have code that will import all excel files (.xlsx) in a folder regardless of the name of the file?

All files have one sheet named ALL_INJ.

Also, is it possible to capture the name of the excel file in a macro or in each SAS dataset that is created?

Thank you.

Re: IMPORT all excel files in a folder

Hima — Fri, 05 Sep 2014 19:05:57 GMT

http://www.pharmasug.org/proceedings/2011/CC/PharmaSUG-2011-CC10.pdf

Re: IMPORT all excel files in a folder

gzr2mz39 — Fri, 05 Sep 2014 19:19:19 GMT

Where can I find the code for the macro (ie %xls2sas)?

Thank you.

Re: IMPORT all excel files in a folder

Hima — Fri, 05 Sep 2014 19:23:30 GMT

%macro xls2sas(folder=

,subfd=Y

,exclfd=

,startrow=);

You need to fill in those parameters.

Re: IMPORT all excel files in a folder

gzr2mz39 — Fri, 05 Sep 2014 20:20:05 GMT

Don't I need the SAS macro code in order to run the macro xls2sas?

Re: IMPORT all excel files in a folder

Hima — Fri, 05 Sep 2014 20:49:09 GMT

%macro <macroname> is the beginning of the macro

%mend is the end of the macro

All that use in between %macro and %mend is the what you want the macro to run

/* first step of the macro xls2sas*/

%macro xls2sas(folder=

,subfd=Y

,exclfd=

,startrow=);

and %mend is where the macro for xls2sas ends

The next step is to call the macro and this is how you call it.

%xls2sas

(folder=

,subfd=Y

,exclfd=

,startrow=);

Did this help?

Re: IMPORT all excel files in a folder

gzr2mz39 — Fri, 05 Sep 2014 20:59:04 GMT

Isn't there macro code to download so the macro can be run?

For example:

http://www.sascommunity.org/mwiki/images/e/ee/For.sas

Re: IMPORT all excel files in a folder

ruysolo — Thu, 03 Feb 2022 23:08:52 GMT

The %xls2sas macro from the article referenced above is INCOMPLETE.

The authors explain, amongst other things, that they make use of SAS functions such as DREAD.

However, the macro itself does not have this line of code.

Here is the %macro as I was trying to test it. Perhaps someone can tell me where I am wrong or what I am missing.

%macro dt_xls2sas (folder= ,subfd=Y ,exclfd= ,startrow=);

*- Local macro variable declaration;

%local _j _cnt _dsid _i _num _s_ext _s_name _filename _rc;

%let _rc=%qsysfunc(filename(filrf,&folder));
%if &_rc=0 %then
%do;

*- Open the folders;

%let _dsid=%sysfunc(dopen(&filrf));
%if &_dsid>0 %then
%do;

*- Ge the number of folders;

%let _num=%sysfunc(dnum(&_dsid));
%do _i=1 %to &_num;

%let _filename=%sysfunc(dread(&_dsid.,&_i.));
%put _filename=;

*- Check if the file extension is XLS. If so, open Excel workbook and read in the multi-sheets or named ranges;

%if %upcase(&_s_ext)=XLS %then
%do;
libname excellib excel "&folder\&_filename";
libname excellib clear;
%end;

*- If the file extension resolves to NULL, check if the read subfolder flag is Y and also it is not;
*- in the exclusion folder list. If so, call %xls2sas to open the subfolder and Excel files under it;
%end;
%end;
%end;

%else %if %scan(&_filename, 2, .)= and &subfd=Y and
%qsysfunc(indexw(&exclfd,&_filename))=0 %then
%do;
%dt_xls2sas(folder=&folder\&_filename,
subfd=&subfd,
exclfd=&exclfd,
startrow=&startrow)

%end;

%mend dt_xls2sas;

Re: IMPORT all excel files in a folder

Tom — Fri, 04 Feb 2022 03:51:15 GMT

Split the problem into pieces.

1) Make a dataset with the list of files. You can use DOPEN() and DREAD() if you have to , but do it in a data step and not in macro code. This will not only be a lot easier to code and debug but you end up with something you can actual use to track your progress.

2) Make a program that can convert one file into a dataset. Do it first as hardcode for one specific file so you know how to generate the code. Figure out what part changes. It might help to convert it to a macro that takes as input the parts that change. You might include a method for adding a column it you need it to indicate which file the data come from. But you might also do that later when you try to combine the files. If you are reading from EXCEL files then you probably will HAVE to keep the data from each sheet in a different dataset because the lengths of character variables will vary based on the particular values in that particular sheet. That is just part of "joy" of working with a tool that is designed for exploration of data and not management of data.

3) Use the dataset from step1 to generate code to import all of the files (at least the ones you want). You could use CALL EXECUTE() or just PUT the code to a file and use %INCLUDE to run it. I prefer the latter because it is so much easier to debug. Especially if you are running interactively (or even pseudo interactively using SAS/Studio or Enterprise Guide) where you can examine the results of one step before moving onto the nest.

Re: IMPORT all excel files in a folder

Kurt_Bremser — Fri, 04 Feb 2022 06:23:10 GMT

%if %upcase(&_s_ext)=XLS %then

You use the macro variable in the comparison, but you never set it to a value, so this condition will always be false.