How to import multiple csv files, but one by one as an input to the program.

[sreevatsan1991]

I have a SAS program which accepts csv files as inputs one at a time. I have a folder which gets populated on a a daily basis. I need help in such a way that If there are 3 input files today in the folder, I need these files to be imported one by one , and the program should run thrice. I have checked the communities and only found to import all files one at a time.

Example:

Inputs folder

1) inputfile_3873847

2)inputfile_ag1434

3)inputfile_288248

I need the above input files to be imported to the program one at a time  and the program should run thrice.

[Kurt_Bremser]

What do you mean by "program"? A .sas file, or just some steps in a project?

And how are the csv files created? If they are created by other programs run from a scheduler, have the scheduler run your program automatically.

[sreevatsan1991]

The incoming files are from a different team and not by a program. They
manually put those files. The program is in Enterprise guide. Currently it
important the file by name. I am just trying to include a program to
automate the import process.

[SuryaKiran]

Can you run PIPE commands? Do you also want to read files from sub-folder within a folder.

Check the links below, you may need to tweak as per your requirement.

http://support.sas.com/kb/45/805.html

http://support.sas.com/kb/41/880.html

After reading the files names you can load the data into SAS using a datastep or proc import.

[RW9]

Something like:

%macro imp (fname=,dsname=);
  data &dsname.;
    infile "...pathtofile.../&fname..csv" dlm=",";
    input...;
  run;
%mend imp;

%imp (fname=inputfile_3873847,dsname=want1);
%imp (fname=inputfile_ag1434,dsname=want2);
%imp (fname=inputfile_288248,dsname=want3);

Best I can suggest with the very little information provided.  Note this is only a start to the code, you need to add extra bits to the data step.

[Shmuel]

Try next code:

%macro imp(dsout_prefix, inp_names);
     %let n = %sysfunc(countw(&inp_names));
     %do i=1 %to &n;
            /*   your code where
                  input =  %scan(&imp_names , &i);
                 output = &dsout_prefix&i;
           */
     %end;
%mend imp;

%imp(want, inputfile_3873847 inputfile_ag1434 inputfile_288248);

/* output should be:  wan1, want2, want3 datasets */

[sreevatsan1991]

The input files 

inputfile_3873847 inputfile_ag1434 inputfile_288248

are just an example. the names going to be  keep on changing.

I need to pick up the all the files in a folder and import them one by one as an input to the program.

[Reeza]

---

@sreevatsan1991 wrote:

The input files 

inputfile_3873847 inputfile_ag1434 inputfile_288248

are just an example. the names going to be  keep on changing.

I need to pick up the all the files in a folder and import them one by one as an input to the program.

---

This involves several steps. 

1. Find the list of files in the folder - see the links someone else posted above. 

2. Create a program to import a file - PROC IMPORT or macro with data step that takes the filename/path

3. Import each file - macro or PROC IMPORT I assume. Once you have the file names, you can pass that to your import step via CALL EXECUTE() or a macro loop. I highly recommend CALL EXECUTE(). 

All the steps are here, links above explain how to do each step and CALL EXECUTE has a good example in the documentation.

[sreevatsan1991]

Thanks all for your suggestions. It really helped me. Now am able to import all the csv files as SAS datasets. But how do i dynamically run the program for different csv files.

Lets consider the same above example. I have an SAS program which runs with CSV file as input.

If have three input files, how do i make the SAS program to run thrice with three different inputs (Its not going to be three, it changes daily)? 

[Reeza]

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Make it work for a base case and then you automate it.

[sreevatsan1991]

I really appreciate  your quick response. In the given example the same procedure will be executed for different makes of car. But my case is different.

My current program runs by accepting a csv file.

If there are 3 csv files in the folder, then the program should pick the first csv file and run completely. After this has been completed, then its should pick the second file and run completely.

In your example, you are getting list of makes of car as a string and passing one by one. In my case its not a string. I have to pass the whole dataset as an input to import.

Thanks in advance.

[Reeza]

Your case can be done in more than one way. See the other example where I use exactly that methodology. The key is to make it the same. You can do it your way of course, but mine is also perfectly valid and works.

1. Create a data set with the list of files (similar to getting each car make/model).
2. Import each one.
3. Combine if necessary, but I think you said you wanted them as separate data sets.

Ultimately how you want to do it is your choice.

[sreevatsan1991]

Thanks so much for the response.

Your solution totally works for base program. But changing the current enterprise program into base is a tedious process. 

But in the enterprise guide, i am stuck in automation

1. Create a data set with the list of files (similar to getting each car make/model).  - Done
2. Import each one. - Done

But how to proceed after this approach in enterprise guide.

(in base we can just use an macro of the data-set like &r_make in the procedure and data steps)

[Reeza]

You posted this in the Programming forum, not EG. I have no way of knowing you were using EG until now. If you're trying to automate this using GUI in EG that's a different question entirely than what I've answered here. Otherwise, they're all programs and not sure how they're different at all.

[Reeza]

Here's another example of how to import all XLSX files in a specified folder.

https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type