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

[Reeza]

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

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

[tritringuyen]

Thank you very much for your codes. 

[TomKari]

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

Tom

[Patrick]

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

[tritringuyen]

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.

[rogerjdeangelis]

This does not solve your problem but might give you some ideas.

I would stick with the original  XLSX format. It is rarely a good idea to
transform original data.

* create three xlsx workbooks;
libname xel "d:/xls/class1.xlsx";
data xel.class1;
  set sashelp.class(obs=6);
run;quit;

libname xel "d:/xls/class2.xlsx";
data xel.class2;
  set sashelp.class(firstobs=6 obs=12);
run;quit;

libname xel "d:/xls/class3.xlsx";
data xel.class3;
  set sashelp.class(firstobs=13 obs=19);
run;quit;

libname xel clear;

libname xel ("d:/xls/class1.xlsx" "d:/xls/class2.xlsx" "d:/xls/class3.xlsx");

proc contents data=xel._all_;
run;quit;

data class;
  set
      xel.'class1$'n
      xel.'class2$'n
      xel.'class3$'n
  ;
run;quit;

libname xel clear;

NOTE: There were 6 observations read from the data set XEL.'class1$'n.
NOTE: There were 7 observations read from the data set XEL.'class2$'n.
NOTE: There were 7 observations read from the data set XEL.'class3$'n.
NOTE: The data set WORK.CLASS has 20 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              597.56k
      OS Memory           16360.00k
      Timestamp           01/25/2017 07:51:42 PM
      Step Count                        325  Switch Count  0

[tritringuyen]

Thank you very much! 🙂

[Malathi13]

Hi,

I can use a macro to import as many xls or xlsx files (excel format) as possible. Here's the code for a macro to import xls file. I'm not sure if you can use that for a csv files, may be with little modification it could be possible.

%let input=C:\samplefile\file;

%let output=C:\sampleexcel\Output.xls;

libname t "&input";

%macro Cour1 (infile, outfile);

PROC IMPORT OUT= &outfile

           DATAFILE= &infile

           DBMS=EXCEL REPLACE;

     RANGE="Course1$"; GETNAMES=YES; MIXED=NO;SCANTEXT=YES;USEDATE=YES;SCANTIME=YES; RUN;

%mend Cour1;

%Cour1 ("C:\samplefile\excelfile\Atlan.xlsx" ,WORK.Apre1);

%Cour1 ("C:\samplefile\excelfile\Nash.xlsx",WORK.Npre1);

%Cour1 ("C:\samplefile\excelfile\Richm.xlsx",WORK.Rich1);

/*the &outfile is the name of the dataset that you want to create in the work library (e.g.work.Apre1) and &infile is the path where the file is located in quotations (e.g. ("C:\samplefile\excelfile\Richm.xlsx")).

You can add as many files as possible at the end giving the path for the infile and the work.name for the outfile. For csv files you can save them to xls format and import them using this macro or modify the above program as per the CSV format.

Malathi