BookmarkSubscribeRSS Feed
cd2011
Calcite | Level 5

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

5 REPLIES 5
Reeza
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

cd2011
Calcite | Level 5

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

Patrick
Opal | Level 21

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.

BrunoSilva
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1142 views
  • 0 likes
  • 5 in conversation