topic Re: Using macros to import 3 excel files located in the same folder in SAS Procedures

Using macros to import 3 excel files located in the same folder

aespinarey — Tue, 30 Jan 2018 05:00:39 GMT

Hello all,

I am having trouble using a macro to import 3 excel files located in the same folder. I have found a couple of examples online. I tried using about 4 different ways. But it does not give me an output. Can someone explain how to do so and the logistics behind each step? Some seem pretty extensive, which I don't understand because for what I see on the book, it seems that macros are used to reduce repetition.

Thank you.

Re: Using macros to import 3 excel files located in the same folder

Reeza — Tue, 30 Jan 2018 05:12:20 GMT

Pick one you want, post what you’ve tried here, explain what doesn’t work and we’ll help you through it. Otherwise, the SAS macro appendix has a macro reference library and the first is how to import all csv files but it can be easily adapted for XLSX files.

One thing - before you write a macro, have working code to accomplish at least your base case. From there you identify the parts that are dynamic, such as file name and you can start creating a macro.

Macro tutorial

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Macro appendix

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n1qvxz5u3uru7yn1nk7q64ohvwak.htm&docsetVersion=9.4&locale=en

@aespinarey wrote:

Hello all,

I am having trouble using a macro to import 3 excel files located in the same folder. I have found a couple of examples online. I tried using about 4 different ways. But it does not give me an output. Can someone explain how to do so and the logistics behind each step? Some seem pretty extensive, which I don't understand because for what I see on the book, it seems that macros are used to reduce repetition.

Thank you.

Re: Using macros to import 3 excel files located in the same folder

Satish_Parida — Tue, 30 Jan 2018 08:54:10 GMT

Create a data step and use PIPE to get the list of files in the directory, then you can filter out to get only excel files.
Now you can create a simple macro function to import the data from a sample excel file
1. (use filename as in a macro variable)
2. Use a dummy data set when creating the import to SAS
3. Have check for the first run (Check if the final DS exists, if not then create it using data step or proc sql create)
4. on each step append the imported data to the above final data set
Then run a data _null_ statement and call the step 2 created macro in the data step and pass the file name to it.

This is coad-able and simple and dynamic, it will work for more than 3 excels as well.

Re: Using macros to import 3 excel files located in the same folder

aespinarey — Wed, 31 Jan 2018 03:26:01 GMT

Thank you!

I found a backbone to my macro. I think it may be working, but I am not sure as SAS log is no longer giving me feedback. It was giving me feedback with errors; then I changed a couple of things and now the only feedback it is giving me is

"NOTE: The quoted string currently being processed has become more than 262 bytes long. You
might have unbalanced quotation marks."

Here is the program I used:

***********PART A:IMPORT FILES USING MACROS***********;

****** 1. Get file lists in the directory*;
filename dirlist pipe 'dir "C:\HW5"\';

****** 2. Resolve file names to SAS dataset "files" *;
data files;
length fname $9;
infile dirlist truncover length= reclen;
input fname $varying9. reclen;
run;

****** 3. Store the number of files we want to import into macro variable "nfile" *;
data _null_;
set files nobs= nobs;
call symput('nfile', nobs);
stop;
run;

******* 4. Import all the files we want into SAS dataset*;
%macro fileinput;
%local i;
%do i= 1 %to &nfile;
* Store import path and output filename into macro variables*;
data _null_;
set files(firstobs= &i obs= &i);
* The length of fpath can't be over 201 since windows limitation *;
call symput('fpath', "C:\HW5" || fname);
call symput('foutname', scan(fname, 1, '.') );
run;

* Excel file import*;
proc import out= work.file.&foutname
datafile= "&fpath"
dbms=excel replace;
range="sheet1$";
getnames=yes;
mixed=no;
scantext=yes;
usedate=yes;
scantime=yes;
run;
%end;
%mend;
%fileinput;

Re: Using macros to import 3 excel files located in the same folder

aespinarey — Wed, 31 Jan 2018 03:29:55 GMT

Thank you for your help!

I tried the program below; however, I am not sure if it has done anything. The only feedback the log is giving me is "NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks"

***********PART A:IMPORT FILES USING MACROS***********;

****** 1. Get file lists in the directory*;
filename dirlist pipe 'dir "C:\HW5"\';

****** 2. Resolve file names to SAS dataset "files" *;
data files;
length fname $9;
infile dirlist truncover length= reclen;
input fname $varying9. reclen;
run;

****** 3. Store the number of files we want to import into macro variable "nfile" *;
data _null_;
set files nobs= nobs;
call symput('nfile', nobs);
stop;
run;

Re: Using macros to import 3 excel files located in the same folder

Satish_Parida — Wed, 31 Jan 2018 08:45:35 GMT

Try Importing a single file with the sample import first before trying to automate the thing.
If there is an issue in single file reading then post the log here.

I have made some modifications to your code for all file reading, hope it will help.

filename dirlist pipe 'dir "C:\HW5"\';

data files;
length fname $9;
infile dirlist truncover length= reclen;
input fname $varying9. reclen;
run;

%macro fileinput(csvfile=);
	proc import out= workingset
	datafile= "&csvfile"
	dbms=excel replace;
	range="sheet1$";		*Not nesseasry ;
	getnames=yes;
	mixed=no;
	scantext=yes;
	usedate=yes;
	scantime=yes;
	run;

	%if %sysfunc(exist(workingset_finale)) %then %do;
		proc append base=workingset_finale data=workingset;
			 Run; 
	%end;

	%else %do;
		data workingset_finale;
			set workingset;
		run;
	%end;
%mend;

data _null_;
set files nobs= nobs;
call execute('fileinput(csvfile='||fname||');');
run;

Re: Using macros to import 3 excel files located in the same folder

aespinarey — Sat, 03 Feb 2018 02:50:58 GMT

Thank you!

I imported only 1 file without macros and it worked. The log response was

NOTE: The import data set has 49 observations and 5 variables.
NOTE: WORK.DATAFILE1 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.25 seconds
cpu time 0.01 seconds

Then I ran the macro again and got this as the log response:

NOTE: The infile DIRLIST is:
Unnamed Pipe Access Device,
PROCESS=dir "C:\HW5"\,RECFM=V,LRECL=32767

NOTE: 12 records were read from the infile DIRLIST.
The minimum record length was 0.
The maximum record length was 53.
NOTE: The data set WORK.FILES has 12 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.09 seconds
cpu time 0.01 seconds

I am trying to import 3 files and all files have more than 30 observations. I am not sure what it is referring to when it says "NOTE: The data set WORK.FILES has 12 observations and 1 variables."

The following is the whole program after the changes you had suggested.

****** 1. Get file lists in the directory*;
filename dirlist pipe 'dir "C:\HW5"\';

****** 2. Resolve file names to SAS dataset "files" *;
data files;
length fname $9;
infile dirlist truncover length= reclen;
input fname $varying9. reclen;
run;

******* 4. Import all the files we want into SAS dataset*;
%macro fileinput (csvfile=);

* Excel file import*;
proc import out= workingset
datafile= "&csvfile"
dbms=xlsx replace;
range="sheet1$";
run;
%if %sysfunc(exist(workingset_finale)) %then %do;
proc append base=workingset_finale data=workingset;
run;
%end;
%else %do;
data workingset_finale;
set workingset;
run;
%end;
%mend;
data _null_;
set files nobs= nobs;
call execute ('fileinput(csvfile='||fname||');
run;

Re: Using macros to import 3 excel files located in the same folder

Reeza — Sat, 03 Feb 2018 04:11:21 GMT

The macro didn't execute that's the log from the following:

****** 1. Get file lists in the directory*;
filename dirlist pipe 'dir "C:\HW5"\';
****** 2. Resolve file names to SAS dataset "files" *;
data files;
length fname $9;
infile dirlist truncover length= reclen;
input fname $varying9. reclen;
run;

NOTE: The data set WORK.FILES has 12 observations and 1 variables.

Your last data _null_ step is incorrect, you're missing the % for the macro call.

This code below is redundant. PROC APPEND doesn't require the base data set to exist already so you can use PROC APPEND directly.

%if %sysfunc(exist(workingset_finale)) %then %do; *delete;
proc append base=workingset_finale data=workingset;
run;
%end;*delete;
%else %do;*delete;
data workingset_finale;*delete;
set workingset;*delete;
run;*delete;
%end;*delete;

Why are you calling the macro parameter CSVFILE when it's XLSX?

Also, this is unlikely to work in the long run. When SAS imports an Excel file it has to guess at types, and it often guesses wrong. So this means that in one file a variable will be read as character and in another it will be read as numeric. When this happens you won't be able to append the data sets together so you'll need to first verify the types and correct for this.

Re: Using macros to import 3 excel files located in the same folder

Reeza — Sat, 03 Feb 2018 04:14:04 GMT

PS. When testing a macro, make sure to call it manually first so you can see if the macro is not working or if the CALL EXECUTE step isn't working.

Re: Using macros to import 3 excel files located in the same folder

aespinarey — Sat, 03 Feb 2018 17:03:06 GMT

Thank you!

I found another way without using pipeline. It worked.

%macro one (a, b, c);

proc import out= &a

datafile= "C:\HW5\&b"

dbms=xlsx replace;

getnames=yes;

run;

proc sort data=&a;

by &c;

run;

%mend one;

%one (datafile1, datafile1.xlsx, id);

%one (datafile2, datafile2.xlsx, id);

%one (datafile3, datafile3.xlsx, id);

%macro print_this (one);

Proc print data = &one;

Run;

%mend print_this;

%print_this (DATAFILE1);

%print_this (DATAFILE2);

%print_this (DATAFILE3);

Re: Using macros to import 3 excel files located in the same folder

Reeza — Sat, 03 Feb 2018 20:19:39 GMT

Yes, pipes were being used to find the list of files. If you know where they are or don't need to automate that step you can skip it.

Glad you have your problem solved, please mark the question as solved. Since your solution is the one that worked, you can set that as the answer.