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;
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.