DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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;

 

 

 

 


Accepted Solutions
Solution
‎03-26-2018 04:40 AM
New Contributor
Posts: 3

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

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

 

 

 

 

 

 

View solution in original post


All Replies
Super User
Posts: 22,874

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

Posted in reply to New_2_this
Solution
‎03-26-2018 04:40 AM
New Contributor
Posts: 3

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

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

 

 

 

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 169 views
  • 2 likes
  • 2 in conversation