BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
New_2_this
Fluorite | Level 6

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;

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
New_2_this
Fluorite | Level 6

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

2 REPLIES 2
New_2_this
Fluorite | Level 6

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

 

 

 

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 15298 views
  • 3 likes
  • 2 in conversation