topic Re: Create a macro to import and merge thousands CSV files into a single dataset in SAS Data Management

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

tritringuyen — Wed, 25 Jan 2017 19:15:22 GMT

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

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

Kurt_Bremser — Wed, 25 Jan 2017 19:18:20 GMT

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

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

Reeza — Wed, 25 Jan 2017 19:20:36 GMT

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-text-files-that-have/ta-p/223627

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

tritringuyen — Wed, 25 Jan 2017 19:26:48 GMT

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

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

tritringuyen — Wed, 25 Jan 2017 19:30:40 GMT

The picture of sample is attached. Thanks

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

Reeza — Wed, 25 Jan 2017 19:59:03 GMT

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.

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

PGStats — Wed, 25 Jan 2017 20:10:29 GMT

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 ?

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

tritringuyen — Wed, 25 Jan 2017 20:23:27 GMT

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.

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

PGStats — Wed, 25 Jan 2017 20:38:57 GMT

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.

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

ballardw — Wed, 25 Jan 2017 20:50:24 GMT

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

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

Reeza — Wed, 25 Jan 2017 20:52:38 GMT

Have you tried the solution I proposed?

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

tritringuyen — Wed, 25 Jan 2017 22:51:32 GMT

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!

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

tritringuyen — Wed, 25 Jan 2017 22:53:51 GMT

Hi Ballardw, thank you very much, but I dont really get what you mean. 😞

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

tritringuyen — Wed, 25 Jan 2017 22:56:03 GMT

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.

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

Reeza — Wed, 25 Jan 2017 22:56:17 GMT

Have you figured out how to read one file? That's your starting point. If you figure that out and then come back to my post it will be more clear.

Here's a quick tutorial on how to read files:

http://www.ats.ucla.edu/stat/sas/library/SASRead_os.htm

More tutorials are here, including a link to the first e-course which is free.

http://support.sas.com/training/tutorial/

In fact, if you're using SAS UE, there were some posts recently on how to use SAS Studio to automate a process multiple times using the point and click interface.

https://communities.sas.com/t5/SAS-Communities-Library/SAS-Studio-Custom-Task-Tuesday-Macro-Proc-Execution/ta-p/320589

Take the time to learn the software if you plan on using it.

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

Reeza — Wed, 25 Jan 2017 22:57:24 GMT

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

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

tritringuyen — Wed, 25 Jan 2017 23:10:17 GMT

Thank you very much for your codes.

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

TomKari — Wed, 25 Jan 2017 23:13:35 GMT

I could take this on for you. Send me a private message if you wish to discuss further.

Tom

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

Patrick — Thu, 26 Jan 2017 00:14:14 GMT

@tritringuyen

Using SAS Enterprise Guide you can use the IMPORT task for one of your data sources. This will generate the input code for you.

Such code will give you a very good starting point to then use @Reeza's code and add the input snippet to where it's documented in the code sample.

*Otherwise go to the import step and read the files;
else input

*Place input code here;

<variable 1 to n>

N.B: Don't use the EG generated code as is. You will certainly have to modify the LENGTH assignments for all character variables so that they are long enough for all your data and not only for the specific file you've used for generating the code.

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

tritringuyen — Thu, 26 Jan 2017 00:19:52 GMT

Thank you very much for the responses of all of you. Speical thanks to @Reeza for the code. I think it works. I already know how to use it, but still learn how to fix the issue around the length of variables. Maybe I will go back to the pointers to read datalines. Thanks.