topic Re: A macro to merge various xlsx files from the same subfolder in SAS Programming

A macro to merge various xlsx files from the same subfolder

dukem — Mon, 10 Sep 2018 15:26:10 GMT

I'm trying to import several xlsx files from the same subfolder using macro. Then I would like to join them. I've read several previous posted and losing my mind. I'm new to using macro and want to challenge myself and to expand my SAS knowledge. The long version is as follows (then with proc sql to join the datasets).

proc import

replace

dbms=xlsx datafile='C:\Operations\CT\ Copy of ts_201706_final.xlsx"

out=Fer_fin__Jun17;

run;

proc import

replace

dbms=xlsx datafile='C:\Operations\CT\Copy of ts_201707_final.xlsx"

out=Fer_fin__Jul17;

run;

proc import

replace

dbms=xlsx datafile='C:\Operations\CT\ Copy of ts_201708_final.xlsx"

out=Fer_fin__Aug17;

run;

proc import

replace

dbms=xlsx datafile="C:\Operations\CT\ Copy of ts_201709_final.xlsx"

out=Fer_fin__Sep17;

run;

proc import

replace

dbms=xlsx datafile='C:\Operations\CT\ Copy of ts_201710_final.xlsx"

out=Fer_fin_Oct17;

run;

I'm trying to follow the steps posted by previous members

https://communities.sas.com/t5/General-SAS-Programming/How-to-import-multiple-Excel-file-to-SAS/td-p/151469/page/2

/* 1. Get file lists in the directory*/
filename dirlist pipe 'dir "D:\..." /b';

/* 2. Resolve file names to SAS dataset "files" */
data files;
length fname $20;
infile dirlist truncover length= reclen;
input fname $varying20. 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', "D:\..." || fname);
call symput('foutname', scan(fname, 1, '.') );
run;
/* Excel file import*/
proc import out= work.&foutname
datafile= "&fpath"
dbms=excel replace;
range="sheet1$";
getnames=yes;
mixed=no;
scantext=yes;
usedate=yes;
scantime=yes;
run;
%end;
%mend;
%fileinput;

Re: A macro to merge various xlsx files from the same subfolder

Reeza — Mon, 10 Sep 2018 15:32:19 GMT

So what is the question you need help with? Something here not working as expected?

Here's my version of that process, pretty similar though but it's tested. And do you mean merge (add columns) or append (add rows).
https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

I'm sure you've seen in previous threads mentioned this is unlikely to work because Excel guesses at types so it's like to read one file with a variable X as character and variable X as numeric in a different one causing issues when you try and append them.

Re: A macro to merge various xlsx files from the same subfolder

dukem — Mon, 10 Sep 2018 15:46:01 GMT

Thank you Reeza.

My question is I'm looking for program that allows me to import 12 xlsx files from the same subfolder and joins (or merge) them together.

I saw your program and it's above my level of SAS understanding. Could highlight areas that require inputs from me* Where I should replace your program with specific

My level of SAS is beginner to intermediate and have very little knowledge of macros.

Re: A macro to merge various xlsx files from the same subfolder

Reeza — Mon, 10 Sep 2018 15:50:31 GMT

They run in the order shown and the only line you should change is this portion:

%list_files(c:\_localData\temp, xlsx);

Add your path into that macro call and the extension type you're looking to import. This will not join the files, you haven't clarified how that should happen, see my original response. I would recommend importing all and then figuring that out. Once you have it figured out you can likely go back and modify the code to include it.

The only parts of that code that are complex is the first macro that lists the files. It's not the most efficient design but should work on any OS.

Re: A macro to merge various xlsx files from the same subfolder

dukem — Mon, 10 Sep 2018 16:38:10 GMT

Thank you for detail explanation.

Yes, I plan on importing the files first then joining them.

Question: What do you mean by "extension type" you're looking to import? I have the path

%macro list_files(C:\Users\savanahb\Downloads, Copy of ts_2017&i_final.xlsx);

Re: A macro to merge various xlsx files from the same subfolder

Reeza — Mon, 10 Sep 2018 16:44:45 GMT

When you list the files you need to specify the type/extension of files to list because you can have multiple different file types in the same folder. In this case, the file extension is used. The macro creates a data set with the file names. If you've created a list of file names why would you expect to call it with each file name? That's part of the process....the file names really don't matter, just the extension/type.

If you're not sure what a file extension is:

https://techterms.com/definition/fileextension

You only need to change the single line I indicated, which is not what you've posted below. That's part of the macro declaration that SHOULD NOT be changed.

@dukem wrote:

Thank you for detail explanation.

Yes, I plan on importing the files first then joining them.

Question: What do you mean by "extension type" you're looking to import? I have the path

So

%macro list_files(C:\Users\savanahb\Downloads, Copy of ts_2017&i_final.xlsx);

Re: A macro to merge various xlsx files from the same subfolder

dukem — Mon, 10 Sep 2018 17:10:58 GMT

Thank you soooo much. You brought tears to my eyes when the program ran flawless. I pray the 10x the rewards comes your way.

14 observations were read.....PERFECT! There were 14 xlsx files

Next, is how do I join (or merge) these 14 files vertically. Each files has a TP_ID as the unique identifier but also the ref_period.

Re: A macro to merge various xlsx files from the same subfolder

Reeza — Mon, 10 Sep 2018 17:18:10 GMT

I think you want to append them but I'm not sure. Like I mentioned before, are you adding columns or rows. Given your naming structure I suspect Append.

This is a quick way to append all files if they have a naming sequence, which yours did:

data want;
set data201801-data201812;
run;

Re: A macro to merge various xlsx files from the same subfolder

dukem — Mon, 10 Sep 2018 17:26:35 GMT

I believe I'm adding rows. The columns are the same between all the files.

Re: A macro to merge various xlsx files from the same subfolder

Reeza — Mon, 10 Sep 2018 17:41:28 GMT

Then something like above data step will work.

These are some of the ways you can shortcut list data sets and variables.

https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

@dukem wrote:

I believe I'm adding rows. The columns are the same between all the files.

Re: A macro to merge various xlsx files from the same subfolder

dukem — Wed, 12 Sep 2018 14:34:54 GMT

Thank you kindly!