topic Re: Create a macro for loading multiple files in SAS Programming

Create a macro for loading multiple files

MCB1 — Wed, 16 Dec 2020 07:45:41 GMT

Hello, I need to create a macro that processes data files stored in different folders. The structure for storage of the files and their filenames are the same. The files are large and havmillions of observations and many variables. I have a solution if i copy all the files I need and paste them into the same folder. I prefer to avoid doing that. I would like to reach the original folder for each file with a macro. The output I need is a file for each iteration in the makro. Here is av very simple example of the data I have and what I want to do:

I have two datasets corresponding to data for 2018:

data dm18ir18;

input ID Inc;

datalines;

4 25

5 40

6 55

;

run;

data dm18vm18;

input ID weight;

datalines;

4 2

5 3

6 3

;

run;

Two datasets corresponding to data for 2017:

data dm17ir17;

input ID Inc;

datalines;

1 25

2 40

3 55

;

run;

data dm17vm17;

input ID weight;

datalines;

1 2

2 3

3 1

;

run;

If these files are stored in the same folder :

libname c 'd:\sasdata\test';

then a solution that works is the following:

%macro test(year);

data want&year;

merge c.dm&year.ir&year c.dm&year.vm&year ;

by ID;

run;

%mend;

%test(17)

%test (18);

The results in this case are two files : want17 and want18.

If I do not copy the files to a new folder as assumed above, the data would be in the following original folders:

'D:\sasdata\test\year17\V4' (many files corresponding data för 2017)

''D:\sasdata\test\year18\V4' (many files corresponding data för 2018)

I still want to create two files (want17 and want18) but by using the original stored files in stead of copying them into the same folder. Thank You!

Re: Create a macro for loading multiple files

andreas_lds — Wed, 16 Dec 2020 08:12:42 GMT

The problem is not clear: you need a macro for reading files or a macro merging datasets?

Re: Create a macro for loading multiple files

Kurt_Bremser — Wed, 16 Dec 2020 08:18:12 GMT

Add a LIBNAME to your macro:

%macro test(year);

libname inlib "D:\sasdata\test\year&year.\V4";

data want&year;
merge
  inlib.dm&year.ir&year
  inlib.dm&year.vm&year
;
by ID;
run;

libname inlib clear;

%mend;

%test(17)
%test(18)

Re: Create a macro for loading multiple files

ballardw — Wed, 16 Dec 2020 08:34:27 GMT

You can define a library that points to multiple folders. An example would look like:

Libname mult ("C:\thisfolder\thatfolder" "C:\thisfolder\otherfolder" "C:\thisfolder\differentfolder");

As long as none of the individual data set names are duplicated then there shouldn't be any problems with specific names.

From the example that you show then you may combine all of these data sets with something like:

data want;

merge mult.dm: ;

run;

I say may as there are some issues. The list of data sets resulting from the DM: (yes that colon is important) will attempt to use all of the datasets in the library whose name starts with DM.

Second issue: every single variable with a common name must be of the same type. And if variables are character you may have issues with lengths of commonly named variables if the length is not the same in each data set.

Really having a hard time understanding you placed the year in data set names twice.

Re: Create a macro for loading multiple files

MCB1 — Wed, 16 Dec 2020 10:41:42 GMT

Thank you! it works very well!

Re: Create a macro for loading multiple files

MCB1 — Wed, 16 Dec 2020 10:44:21 GMT

Thank you! I have got a suggestion that works very well:

%macro test(year);
libname inlib "D:\sasdata\test\year&year.\V4";
data want&year;
merge
inlib.dm&year.ir&year
inlib.dm&year.vm&year
;
by ID;
run;
libname inlib clear;
%mend;
%test(17)
%test(18);