Merge Multiple csv files into one dataset

GijsTerBeek — Wed, 07 Feb 2018 16:15:02 GMT

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?

Re: Merge Multiple csv files into one dataset

Reeza — Wed, 07 Feb 2018 16:23:04 GMT

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-text-files-that-have/ta-p/223627

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&docsetVersion=9.4&locale=en#n0jglo3zfq5c0un10w8m07gpmanp

@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?

topic Re: Merge Multiple csv files into one dataset in SAS Procedures

Merge Multiple csv files into one dataset

Re: Merge Multiple csv files into one dataset