Merge CSV files

[Babloo]

I'm not getting any errors. Output is produced with too many headers as I've too many values/rows from each of the file. I'm looking for only one header as file structure is same across the files

[Reeza]

Post your code. Did you use the EOV option as illustrated or another method? 

Not working means nothing, provide details of what is occurring. 

[Babloo]

I've used the following code and it is produced customer.csv file with 3 headers and 3 rows of values as I've 1 record per file. I"m looking for 1 header and 3 rows of values.

filename have ("/sas/customer1.csv", "/sas/customer2.csv", "/sas/customer3.csv");
filename want '/sas/customer.csv';

data _null_;
	file want;
	infile have;
	input;
        if substr(_infile_,1,4) ne 'Name';
        put _infile_;
run;

[Kurt_Bremser]

So, I did the following:

File $HOME/sas/customer1.csv:

Name,sales
Jack,5
Joe,3
Jill,4

File $HOME/sas/customer2.csv:

Name,sales
Frank,6
Richard,4
Britt,2

File $HOME/sas/customer3.csv:

Name,sales
Bruce,8
William,4

Code:

filename have ("$HOME/sas/customer1.csv","$HOME/sas/customer2.csv","$HOME/sas/customer3.csv");
filename want "$HOME/sas/customer.csv";

data _null_;
infile have;
file want;
input;
if (substr(_infile_,1,4)) ne 'Name';
put _infile_;
run;

Resulting file $HOME/sas/customer.csv:

Jack,5
Joe,3
Jill,4
Frank,6
Richard,4
Britt,2
Bruce,8
William,4

As you see, the code works perfectly well with data as specified.

Either you made an error copying the code or the data does not look as you described.

[Babloo]

I got your point, however in your output file customer.csv, there is no header. I need one header atleast. Could you please tell me how will you tweak your code now?

[Reeza]

data want;
infile "/sas/customer**.csv" delimiter=',' DSD lrecl=32767 firstobs=2 eov=skip truncover;

input @; /*this reads in the "current" line and holds. This sets the value
of the EOV (End Of file Variable) */
if Skip then do;
skip = 0;
end;
else do;
input 
/* input variable list*/
name $ sales
;
/* any other code you may need*/
output; /* explicitly write to the output set*/
end;
run;

[Babloo]

Thanks a ton!, this is what I except. Only thing which I would like to know is how to write the values in a .csv file instead of creating the dataset? I don't need the dataset, I need only the flat file .csv. with all values in the one file. I know we can add proc export after the data step which you've provided. Then it will be like a two step approach, any possibilities to get it done in one step?

[Kurt_Bremser · Accepted solution]

If you tweak my code very slightly, you preserve the first header line:

filename have ("$HOME/sas/customer1.csv","$HOME/sas/customer2.csv","$HOME/sas/customer3.csv");
filename want "$HOME/sas/customer.csv";

data _null_;
infile have;
file want;
input;
if (substr(_infile_,1,4)) ne 'Name' or _n_ = 1;
put _infile_;
run;

[Reeza]

The SAS documentation actually has a really good section on 'Combining Data'. 

I suggest your review it. It helps if your terminology aligns with the rest of industry 😉

Typically a merge is adding data side by side and append is used to denote stacking data. 

In general, there are alway exceptions...

http://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#n1tgk0uanvisvon1r26l...