Merge Multiple csv files into one dataset

[GijsTerBeek]

Hi,

I try to 

a) create a dataset of non consecutive .csv files in a certain folder

b) loop through this dataset and load each file into a master dataset

c) add one column to each row that contains the origin (filename) of that row 

Step (a) works like this:

%let servername=\\some\folder\;
libname out "&servername.Users\xxxx";
Filename filelist pipe "dir /b \\some\other\folder\*.txt"; 

 Data out.filelist; 
 Infile filelist truncover;
 Input filename $120.;
 Run; 

Step (b) however keeps not using the variable for the filename: 

data out.completeset_total;
set out.filelist;
do i=1 to 3;
filepath="&servername"||filename;
call symput(fpath,TRIM(filepath));
call symput(fname,TRIM(filename));
put fpath;
filename abc3 "&servername.more\sub\folders\&fpath..txt";
infile abc3 truncover lrecl=1001 firstobs=2 ; input
@1 abc_RS BEST1.
@2 abc_anothercol BEST4.
&fpath. 
end;
run;

Question: How can I make the filename statement loop through the variable fpath correctly? 

[Reeza · Accepted solution]

If they're all in the same folder and the same layout you can use the approach outlined here that does everything you were trying to do:

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

Or the INFILE statement has examples of keeping the file name used and/or reading from multiple files using the FILEVAR option and the FILENAME option to keep the name of the file.

See Example 5:

http://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n1rill4udj0tfun1fvce3j401plo.htm&docs...

---

@GijsTerBeek wrote:

Hi,

I try to 

a) create a dataset of non consecutive .csv files in a certain folder

b) loop through this dataset and load each file into a master dataset

c) add one column to each row that contains the origin (filename) of that row 

Step (a) works like this:

%let servername=\\some\folder\;
libname out "&servername.Users\xxxx";
Filename filelist pipe "dir /b \\some\other\folder\*.txt"; 

 Data out.filelist; 
 Infile filelist truncover;
 Input filename $120.;
 Run; 

Step (b) however keeps not using the variable for the filename: 

data out.completeset_total;
set out.filelist;
do i=1 to 3;
filepath="&servername"||filename;
call symput(fpath,TRIM(filepath));
call symput(fname,TRIM(filename));
put fpath;
filename abc3 "&servername.more\sub\folders\&fpath..txt";
infile abc3 truncover lrecl=1001 firstobs=2 ; input
@1 abc_RS BEST1.
@2 abc_anothercol BEST4.
&fpath. 
end;
run;

Question: How can I make the filename statement loop through the variable fpath correctly? 

---