BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Babloo
Rhodochrosite | Level 12

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

23 REPLIES 23
LinusH
Tourmaline | Level 20

The answers is within the Programming 1 course.

I assume you mean concatenate,not merge.

Data never sleeps
Babloo
Rhodochrosite | Level 12

You're right, need to concatenate

Babloo
Rhodochrosite | Level 12

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;

 

 

Babloo
Rhodochrosite | Level 12

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.

Reeza
Super User

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 🙂

ballardw
Super User

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.

Babloo
Rhodochrosite | Level 12

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;
Reeza
Super User

You're not inputting to a variable.

 

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

 

 

Patrick
Opal | Level 21

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

Babloo
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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