topic Re: A macro to import multiple Excel files from a folder into SAS? in SAS Programming

A macro to import multiple Excel files from a folder into SAS?

New_2_this — Mon, 19 Mar 2018 11:18:29 GMT

Hi SAS Community,

Please could someone assist me? I am new to SAS.

My Objective:

To import many excel files into sas.

My Understanding:

1. a macro can be used.

2. I am able to import a single excel file into sas.

3. I have not edited the code correctly due to lack of understanding

Background:

Each excel file has a unique name.

All excel files are stored in a FOLDER_GENERIC_NAME

I want to import all excel files stored in FOLDER_GENERIC_NAME to sas

What I have done thus far:

1. Attempted to adapt the code found online to meet my objective.

Below is the code I attempted to customize to meet my objective.

/*Scanning Excel Files*/

data playpen_dirlisting;
drop rc did i;
rc=GENERIC_FOLDER_NAME("NAME_OF_FOLDER_WHERE_EXCEL_FILES_ARE_FOUND" , "\\ FILE_LOCATION");
did-DOPEN("NAME_OF_FOLDER_WHERE_EXCEL_FILES_ARE_FOUND");
IF did > 0 then do;
do i=1 to DNUM(did);
name=DREAD(did,i)
OUTPUT;
END;
rc=DCLOSE(did);
ENDl;
ELSE PUT 'Could not open dir';
run;

/*Creating Names for data sets when reading in many excel files*/

libname mcrsheet "%sysfunc(trim(&&NAME_OF_EXCEL_FILE&c))";

proc sql noprint;
create table outsheet&c as
select distinct memname
from playpen_dirlisting;
where libname="macrsheet" And nametype="DATA" And INDEX(memname,'$');
quit;

data outsheet&c;
set outsheet&c (rename=(memname=sheet));
fileloc="&&NAME_OF_EXCEL_FILE&c";
name="&&EXCEL_FILE_NAME&c";
run;

libname mcrsheet clear;

/*Importing the actual excel files*/

libname newdata "%sysfunc(trim(&&FILE_LOCATION&c))";

data work.&& dataset_name&c (drop=obs);
set newdata."%sysfunc(trim(&&sheet&c)) $" n;
obs=_n_;
if obs>0 then call symputx ("rep" , "yes");
run;

libname newdata clear;

Re: A macro to import multiple Excel files from a folder into SAS?

Reeza — Mon, 19 Mar 2018 12:34:43 GMT

Here’s an example

https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

Re: A macro to import multiple Excel files from a folder into SAS?

New_2_this — Thu, 22 Mar 2018 10:35:02 GMT

Hi @Reeza,

i trust you are well.

Thank you for the swift response to my question.

I thought it would be befitting to share what i have learned such that other individuals new to SAS can benefit =).

I have learned the following wrt macros:

It is a code generator which can be used to make repetitive coding more efficient.

Below are the steps i took to code a macro:

Step 1

Hard code the SAS program you are keen to make efficient with a macro.

Step 2

Ensure the code in step 1 runs correctly.

Step 3

Add a macro

%macro

Step 4

Name your macro

%macro MyFirstMacro;

Step 5

End the macro

%mend;

Step 6

Place code from step 1 between %macro MyfirstMacro; and %mend;

%macro MyFirstMacro;

insert Code From Step 1 Here.

%mend MyFirstMacro;

Step 7

Run Step 6.

SAS will not output any result yet.

Step 8

Call the assigned macro by it`s name.

%let MyFirstMacro;

Step 9

Add Variable (s).

%let MyFirstMacro (variable 1);

%let MyFirstMacro (variable 2);

Step 10

Add placeholder where the called macro will look in; to generate code.

%macro MyFirstMacro (general descriptor name given to describe variable 1 & variable 2);

Step 11

Add ampersand (&) where applicable to the code in step 1 which have been inserted between %macro MyFirstMacro; AND %mend;

Step 12

Add a second full stop to end the macro. You can have to full stops next to each other like this ..

Macro looks like this:

%macro MyFirstMacro (month);

Title "Q2";
proc import out =Months_Q2
Datafile= "\\string\&month..xls"
DBMS= Excel2010 replace;
Sheet='Sheet1';
GETNAMES=YES;
MIXED=NO;
run;
%mend MyFirstMacro ;

%MyFirstMacro (Variable1);
%MyFirstMacro (Variable2);

Hope this helps.

Regards,

New_2_this