topic Re: Read and Merge multiple csv files with same first rows in SAS Programming

Read and Merge multiple csv files with same first rows

JoannaQQQ — Thu, 26 Aug 2021 21:19:30 GMT

Hi. I have multiple csv files named as:

q1_2012_all

q2_2012_all

q3_2012_all

q4_2012_all

q1_2013_all

...

q2_2021_all

Does anyone know how to read them and combine them?

Thank you!

Re: Read and Merge multiple csv files with same first rows

ballardw — Thu, 26 Aug 2021 21:53:37 GMT

A recent similar request

https://communities.sas.com/t5/SAS-Programming/macro-for-load-in-multiple-files-csv/m-p/763795

The key bit here is getting the names of the files into a data set with the Path information.

The example uses SAS on Demand which starts the users path with ~ . If running in a windows system start with a drive letter.

If you are running on a server version of SAS then the files have to be in a location that the Server can see with the path which would likely not be the case of reading files on your computer unless a network share of some sort has been set up.

Re: Read and Merge multiple csv files with same first rows

a_SAS_sin — Thu, 26 Aug 2021 22:33:51 GMT

You can use a filename statement with wildcards to select the files and on the infile statement you need to make use of the following properties:
firstobs=2 (to start reading data from the second line)

eov specifies a variable that SAS sets to 1 when the first record in a file in a series of concatenated files is read .

The code below is a solution to your problem (it assumes you have 3 variables a,b,c and they are all numeric):

%let csvFolder= <Path to the directory containing the csv files>;
filename ttt "&csvFolder.\q*_all.csv" ;

data CSVIN;
	infile ttt truncover firstobs=2 dlm=',' dsd eov=_eov ;
	input @ ;/*Start read line and hold*/

	if _eov then do ; /*If if 1st row of file */
		_eov = 0 ;/* EOV = 1 after first row of next file and needs to be reset to 0 */
		delete ; /*delete the 1st row of each file because it is the header*/
	end ;

	input a b c;
run;

Re: Read and Merge multiple csv files with same first rows

andreas_lds — Fri, 27 Aug 2021 04:45:51 GMT

The option firstobs is not useful when reading multiple files with one infile statement, because the option does not work per file.

Re: Read and Merge multiple csv files with same first rows

a_SAS_sin — Fri, 27 Aug 2021 07:43:58 GMT

You need the firstobs option to remove the header of the first file.

Re: Read and Merge multiple csv files with same first rows

Ksharp — Fri, 27 Aug 2021 14:11:00 GMT

%let path=c:\temp ;

data _null_;
rc=filename('x',"&path");
did=dopen('x');
do i=1 to dnum(did);
 fname=dread(did,i);
 if scan(fname,-1,'.')='csv' then call execute(catt("proc import datafile='&path\",fname,
 "' out=",scan(fname,1,'.')," dbms=csv replace;run;"));
end;
run;