Read and Merge multiple csv files with same first rows

[JoannaQQQ]

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!

[ballardw]

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.

[a_SAS_sin]

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;

[andreas_lds]

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

[a_SAS_sin]

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

[Ksharp]

%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;