DATA Step, Macro, Functions and more

Proc Import CSV from multiple sources

Reply
Contributor
Posts: 39

Proc Import CSV from multiple sources

Hi, I have this situation. I have to pull data for about six months, and the data is in csv but the way they are saved, it is not that straightforward. There is a folder for each date, and in them there are several csv files. CSV filenames start with date and some extension. I need one of these files going back about six months. I found macros which import CSVs from folders which are named this way, but this macro was pulling all the files, not just one,so kind of using wildcard. Any help will be greatly appreciated. I can probably write a macro to do this, but it will take me longer than I have.

Thanks much,

- CD

Super User
Posts: 17,750

Re: Proc Import CSV from multiple sources

Create a list of file names. If you know the structure of the naming system create a dataset with the names and then use CALL EXECUTE to import the files. If you don't know the data set names ahead of time, use an OS command to recursively list all the files and then filter that out to generate the list you need. Then use a basic macro to import.


Or use the filename option and a data step import - assuming all files have the same structure.


If you post more details you can get more help.

Super User
Super User
Posts: 7,392

Re: Proc Import CSV from multiple sources

Yes, clarify what you have, how you identify each file.  Its pretty simple once you have the logic, say for instance you want all files which have XYZ in the filename:

filename tmp pipe 'dir "c:\the_directory" /b /s';

data _null_;

     length buffer $2000;

     infile tmp;

     input buffer $;

     if index(buffer,"csv") > 0 and index(buffer,"XYZ") > 0 then call execute('data '||strip(scan(buffer,1,'.'))||'; infile ...      '); /* add in your import code after infile */

run;

Contributor
Posts: 39

Re: Proc Import CSV from multiple sources

So my folders are named by dates and the CSVs have similar naming.

Eg: "C:\...\01012015\01012015_my_stuff.csv",

"C:\...\01022015\01022015_my_stuff.csv"

.

.

.

"C:\...\07142015\07142015_my_stuff.csv"

Thanks,

CD

Respected Advisor
Posts: 3,887

Re: Proc Import CSV from multiple sources

You need first to create a directory listing with the files you're after. Let's say it's always the first of the month then a DIR command as below would work:

dir /b/s C:\temp\datefolders\*my_stuff.csv

You then use this dos command in a pipe, eg. as below, to create a directory listing

filename dirlist pipe 'dir /b/s C:\temp\datefolders\01*my_stuff.csv';

There are many examples in this community how to create a directory listing and then process the files found. A search will bring them up.

Contributor
Posts: 50

Re: Proc Import CSV from multiple sources

Hello

There's a few examples on the web. Check the sample on the link bellow:

windows:

24707 - Reading multiple files with PROC IMPORT

linux:

http://support.sas.com/kb/24/addl/fusion24707_1_unixsamp157.txt

Ask a Question
Discussion stats
  • 5 replies
  • 327 views
  • 0 likes
  • 5 in conversation