topic Re: Merge CSV files in SAS Programming

Merge CSV files

Babloo — Wed, 16 Nov 2016 20:42:30 GMT

I've three different CSV files of same structure. Appreciate if someone tell me how to merge all those files into one CSV file via SAS program.

Re: Merge CSV files

LinusH — Wed, 16 Nov 2016 20:50:38 GMT

The answers is within the Programming 1 course.

I assume you mean concatenate,not merge.

Re: Merge CSV files

Reeza — Wed, 16 Nov 2016 20:54:21 GMT

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#n1tgk0uanvisvon1r26lc036k0w7.htm

Re: Merge CSV files

Babloo — Wed, 16 Nov 2016 20:55:47 GMT

You're right, need to concatenate

Re: Merge CSV files

Kurt_Bremser — Thu, 17 Nov 2016 09:04:29 GMT

You can combine multiple external files into one fileref:

filename inpfile ("filename1","filename2","filename3");

Re: Merge CSV files

Babloo — Thu, 17 Nov 2016 16:31:25 GMT

Thank you for the reply. However once again I need little help to accomplish this task.When I tried the following code, headers of each file is getting concatenated in the final output (want) file. I just wanted to combine values of each file with one header as headers are same across the file.

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

data _null_;
	file want
	infile have
	input;
		put _infile_;
run;

Re: Merge CSV files

Kurt_Bremser — Thu, 17 Nov 2016 16:55:49 GMT

You need to identify (with a proper substring) the header records and omit them with a subsetting if.

Re: Merge CSV files

Babloo — Thu, 17 Nov 2016 17:16:02 GMT

I couldn't understand. Appriciate if you could provide some outline in terms of program to achieve this.

Assume I've the output file as follows.

Name	Age
James	34
Name	Age
Sachin	51

I'm looking for output like,

Name	Age
James	34
Sachin	51

Thanks in advance for any inputs you offer me.

Re: Merge CSV files

Reeza — Thu, 17 Nov 2016 17:46:21 GMT

Example 1: Import All CSV Files That Exist within a Directory

http://support.sas.com/documentation/cdl/en/mcrolref/67912/HTML/default/viewer.htm#n0ctmldxf23ixtn1kqsoh5bsgmg8.htm

How do I write a macro to...import multiple text files that have the same format?

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-text-files-that-have/ta-p/223627

The second example explicitly deals with multiple files, that have multiple headers.

Read through it 🙂

Re: Merge CSV files

Kurt_Bremser — Thu, 17 Nov 2016 19:26:27 GMT

Just add

if substr(_infile_,1,4) ne 'Name';

before the

put _infile_;

Re: Merge CSV files

ballardw — Fri, 18 Nov 2016 22:36:52 GMT

You want to skip reading the header row. This can be done in a data step with the EOV option and some additional coding.

A very rough skeleton of the program would look like this:

data want;
   infile "C:\Path\*.csv" delimiter=','  DSD lrecl=32767 firstobs=2  eov=skip missover;
   /*anything else you may want befor the input such as Informat statements*/
   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*/
      ;
      /* any other code you may need*/
      output; /* explicitly write to the output set*/
   end;
run;

The resetting of the variable Skip, my name nothing magical, is required otherwise you can end up in an infinite loop.

The example requires you to put the variable names and such code.

Re: Merge CSV files

Babloo — Wed, 23 Nov 2016 18:28:30 GMT

Still I'm not getting the desired output. There is no change in the output file after adding the if clause before put statement. My code is

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;

Re: Merge CSV files

Reeza — Wed, 23 Nov 2016 20:43:48 GMT

You're not inputting to a variable.

I would suggest you work through my example step by step but utimately it's your choice.

Re: Merge CSV files

Patrick — Wed, 23 Nov 2016 21:37:07 GMT

The code you've posted will read the 3 .csv from source and write all the records except the ones having "Name" at the beginning of a line to a single output file named customer.csv

Your code as such will work IF you add semicolons after "file want" and "infile have"....

Re: Merge CSV files

Babloo — Mon, 28 Nov 2016 09:22:04 GMT

It is still not working after adding the semicolons in file and infile statements

Re: Merge CSV files

Kurt_Bremser — Mon, 28 Nov 2016 09:23:48 GMT

Define "not working".

If you get errors,

SHOW.THE.LOG.

Re: Merge CSV files

Babloo — Mon, 28 Nov 2016 10:17:11 GMT

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

Re: Merge CSV files

Reeza — Mon, 28 Nov 2016 10:20:01 GMT

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

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

Re: Merge CSV files

Babloo — Mon, 28 Nov 2016 10:30:32 GMT

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;

Re: Merge CSV files

Kurt_Bremser — Mon, 28 Nov 2016 10:34:37 GMT

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.