SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Create a macro to import and merge thousands CSV files into a single dataset

Reply
Contributor
Posts: 33

Create a macro to import and merge thousands CSV files into a single dataset

Hello everybody,

 

I have a sample of data which is attached. I want to do the following tasks:

 

1. Import thousands of CSV files similar to the sample (with the same format). Then merge files into a single SAS file. I want a macro to import and merge files.

 

2. Translate variable T_connected and Tenure_firm into numbers (or create new variables based on variable T_connected and Tenure_firm ). In this case, I suppose the translated value is 0 for T_connected; and 2 and 0 for Tenure_firm 

 

3. Translate variable Startdate into a variable with year only. In original files, the Startdate variable sometimes appears with year only, sometimes with month and year, sometimes with date month and year, sometimes it states "current" (current year is 2014). I want a numeric variable with year only (e.g. 2012).

 

Could anyone can help me on this issue? Please suggest codes which particularly fit my data. I really appreciate your help. Thank you very much!

 

Cheers, Thierry

Super User
Posts: 7,809

Re: Create a macro to import and merge thousands CSV files into a single dataset

Posted in reply to tritringuyen

Your attachment is not a csv. Please post a sample from the csv as text (use the {i} icon).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 33

Re: Create a macro to import and merge thousands CSV files into a single dataset

Posted in reply to KurtBremser

Hi, it is an excel file. The web does not allow to attach CSV files. But you can convert it to CSV file easily.

Respected Advisor
Posts: 4,925

Re: Create a macro to import and merge thousands CSV files into a single dataset

Posted in reply to tritringuyen

Rename the csv to txt. Many (most) members refrain from downloading xlsx files which are considered unsafe.

 

Also, please tell us how the data files are organized within your directory structure. Can they be listed with a simple wildcard name such as c:\myFiles\*.csv ?

PG
Super User
Posts: 19,817

Re: Create a macro to import and merge thousands CSV files into a single dataset

Posted in reply to tritringuyen

Here's instructions on how to read a bunch of CSV files that all have the same format. 

 

If they're not stored in the same folder look at the FILEVAR option on the INFILE statement. I'm not about to do all of your work/homework so give it a shot first and ask questions that are more specific than your entire problem. 

 

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

Contributor
Posts: 33

Re: Create a macro to import and merge thousands CSV files into a single dataset

Posted in reply to tritringuyen

The picture of sample is attached. Thanks


Untitled.png
Super User
Posts: 19,817

Re: Create a macro to import and merge thousands CSV files into a single dataset

Posted in reply to tritringuyen

Upload file as txt instead of CSV, Excel can change the data inside a CSV so it's not a good transfer mechanism. 

Images are not helpful, since we won't take the time to type out the data. 

Contributor
Posts: 33

Re: Create a macro to import and merge thousands CSV files into a single dataset

Posted in reply to tritringuyen

Thank you very much for your responses. Here is the txt file. All files are stored in the same folder (in my PC). Thank you very much!

 

Best wishes. Thierry.

Respected Advisor
Posts: 4,925

Re: Create a macro to import and merge thousands CSV files into a single dataset

Posted in reply to tritringuyen

The approach suggested by @Reeza should work for you, except that you should add option DLM='09'x to your INFILE statement, as your data seems to be TAB delimited and not comma delimited.

PG
Super User
Posts: 11,343

Re: Create a macro to import and merge thousands CSV files into a single dataset


PGStats wrote:

The approach suggested by @Reeza should work for you, except that you should add option DLM='09'x to your INFILE statement, as your data seems to be TAB delimited and not comma delimited.


And looks like some form of double-byte character set as well.

Contributor
Posts: 33

Re: Create a macro to import and merge thousands CSV files into a single dataset

Hi Ballardw, thank you very much, but I dont really get what you mean. Smiley Sad
Contributor
Posts: 33

Re: Create a macro to import and merge thousands CSV files into a single dataset

Thank you PG. They are CSV files. Although your suggestion should be valuable. However, I am still cannot process further with @Reeza codes. I need codes as detailed as possible.
Super User
Posts: 19,817

Re: Create a macro to import and merge thousands CSV files into a single dataset

Posted in reply to tritringuyen

tritringuyen wrote:
I need codes as detailed as possible.

Then you hire a consultant or do the research and at least make an attempt. There are several programmers here for hire if you're interested. 

Super User
Posts: 19,817

Re: Create a macro to import and merge thousands CSV files into a single dataset

Posted in reply to tritringuyen

Have you tried the solution I proposed?

Contributor
Posts: 33

Re: Create a macro to import and merge thousands CSV files into a single dataset

Dear Reeza, Thank you very much for your code. I suppose that i should work. However, SAS is quite new for me. I wonder if you could more specific about "*Otherwise go to the import step and read the files;
else input

*Place input code here;".

Actually, I dont know how to process further. Thank you very much!
Ask a Question
Discussion stats
  • 22 replies
  • 3870 views
  • 10 likes
  • 9 in conversation