Re: Importing all Files in Folders and creating a Graph

M0m007

Hey there,

i'm new to SAS. I got files from several devices from several Customers. The folderstructure is like this :

Customer01

Data

Device1.txt

Device2.txt

.....

Customerdata01.txt

Customer02

Data

Device1.txt

Device2.txt

.....

Customerdata02.txt

....

So every Customer has their own folder and in every customerfolder is a data folder with devicefiles.

I tried a lot to import all device files in every folder recursiv but it doesn't work.

After that i need to clean up the data, but i already got a macro for that.

Then i want to analize all devicefiles of an customer at once. For example get the Meanvalue, Standardderivation or a graph of all devices of the folder

Is this possible?

I hope you can help me. I tried it for weeks and now im not sure if this is even possible...

Thank you very much in advance.

M0m007

PaigeMiller

This article shows how to do it. It imports all .csv files in a folder, but since your files are .txt then you can just change .csv to .txt

https://www.lexjansen.com/mwsug/2013/RF/MWSUG-2013-RF01.pdf

--
Paige Miller

Tom

The best way depends on whether the file structures are the same or not.

First step is to get the list of files. If your SAS session is allowed to issue operating system commands then just use the PIPE engine to run the appropriate operating system command to list the file names and read the names from the output that command generates. Otherwise use SAS commands to open the directories and read the filenames. You can use this macro for that:

https://github.com/sasutils/macros/blob/master/dirtree.sas

Once you have the list of files you can use it to drive the process. Either by driving the data step that reads in the files directly by using the FILEVAR= option of the INFILE statement.

Or perhaps if the files have a complex structure and you already have written a macro that reads in one file use the list of files to generate a series of calls to that macro.

Make sure the resulting dataset has information that distinguishes the files so that you can match the observations in the final dataset. For example in your description it looks like the customer identifier is built into the directory ("folder") name and the device identifier is built in to the text file name. If the file itself does not have those identifiers (or an equivalent) then remember to generate extra variables to store those two identifiers (and perhaps other identifiers like a date that might appear in the filenames).

Then you can combine all of the datasets into one. Perhaps just using PROC APPEND.

Let's assume your macro to read in one file is named READ and takes as inputs the following two parameters: INFILE, OUTDS then the program might look something like this:

%let topnode=~/toplevel ;
%dirtree(&topnode)
data _null_;
  set dirtree;
  where scan(filename,-1,'.')='txt' and scan(dname,-1,'/')='Data');
  call execute(cats('%nrstr(%read(infile=',catx('/',dname,filename),'outds=next))'));
  call execute('proc append data=next base=all force;run;');
run;

So basically that program first gets the names of ALL of the files in that toplevel directory tree. It then use a WHERE clause to take in only those that end in .txt and live in a directory whose name is Data and uses those names to call the macro to generate a dataset and then use PROC APPEND to aggregate the data from all of the files into one dataset.

Now you can use normal SAS code, like PROC MEANS, to analyze the data.

Ksharp

The most simple way is using OS command, if your sas could run it.

Here is an example:

%let path_in= c:\temp\a;  *the fold you need to search;


filename x pipe %sysfunc(quote(dir "&path_in."\*.txt /s /b));  *import all text file under this fold includ sub-fold;
data x;
 infile x truncover;
 input path $2000.;
 call execute(catt('proc import datafile="',path,'" out=',scan(path,-2,'.\'),' dbms=csv replace;delimiter=","; run;'));
run;

Importing all Files in Folders and creating a Graph