topic Re: Proc Import CSV from multiple sources in SAS Programming

Proc Import CSV from multiple sources

cd2011 — Mon, 13 Jul 2015 00:04:51 GMT

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

Re: Proc Import CSV from multiple sources

Reeza — Mon, 13 Jul 2015 01:11:37 GMT

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.

Re: Proc Import CSV from multiple sources

RW9 — Mon, 13 Jul 2015 08:54:27 GMT

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;

Re: Proc Import CSV from multiple sources

cd2011 — Mon, 13 Jul 2015 21:53:39 GMT

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,

Re: Proc Import CSV from multiple sources

Patrick — Mon, 13 Jul 2015 23:41:30 GMT

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.

Re: Proc Import CSV from multiple sources

BrunoSilva — Tue, 14 Jul 2015 11:30:05 GMT

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