DATA Step, Macro, Functions and more

Merge CSV files

Accepted Solution Solved
Reply
Super Contributor
Posts: 435
Accepted Solution

Merge CSV files

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.


Accepted Solutions
Solution
‎11-28-2016 06:52 AM
Super User
Posts: 7,763

Re: Merge CSV files

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 5,424

Re: Merge CSV files

The answers is within the Programming 1 course.

I assume you mean concatenate,not merge.

Data never sleeps
Super Contributor
Posts: 435

Re: Merge CSV files

You're right, need to concatenate

Super User
Posts: 7,763

Re: Merge CSV files

You can combine multiple external files into one fileref:

filename inpfile ("filename1","filename2","filename3");
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 435

Re: Merge CSV files

Posted in reply to KurtBremser

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;

 

 

Super User
Posts: 7,763

Re: Merge CSV files

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

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 435

Re: Merge CSV files

Posted in reply to KurtBremser

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.

Super User
Posts: 19,772

Re: Merge CSV files

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

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

 

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-tex...

 

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

Read through it Smiley Happy

Super User
Posts: 11,343

Re: Merge CSV files

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.

Super User
Posts: 7,763

Re: Merge CSV files

Posted in reply to KurtBremser

Just add

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

before the

put _infile_;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 435

Re: Merge CSV files

Posted in reply to KurtBremser

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;
Super User
Posts: 19,772

Re: Merge CSV files

You're not inputting to a variable.

 

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

 

 

Respected Advisor
Posts: 4,173

Re: Merge CSV files

[ Edited ]

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"....

Super Contributor
Posts: 435

Re: Merge CSV files

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

Super User
Posts: 7,763

Re: Merge CSV files

[ Edited ]

Define "not working".

If you get errors,

SHOW.THE.LOG.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 23 replies
  • 999 views
  • 5 likes
  • 6 in conversation