topic Re: Pulling files from an output folder without knowing the file names and merging them into a datas in SAS Programming

Pulling files from an output folder without knowing the file names and merging them into a dataset

kmardinian — Mon, 22 Jul 2019 16:29:08 GMT

Hi,

I am trying to pull multiple files from an output folder where each are in this format; SUBJECTXXX_datetime.csv. So each folder has a different Subject ID and a different date stamp. There can multiple files in this one folder.

I'd like to pull all the files that are in this folder into SAS and merge them into one dataset, but I am unsure how to do this without calling each file individually. Once those files are in that one dataset, I'd like to move them to another folder, to then run my program again for the next batch of files

I appreciate any help, thank you!

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

Reeza — Mon, 22 Jul 2019 16:33:20 GMT

You can read all files at once or read each folder at a time. First you need to create a list of the files and a working program to read one file.
Do you have either of those? Once you do, making it happen for every file is a pretty trivial task 🙂

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

kmardinian — Mon, 22 Jul 2019 16:36:49 GMT

Hi Reeza,

Thank you!

I do not have that in my program yet, I was not sure how to call all the files without having to name them all individually.

Is there a good way of doing that?

I had found this online:

   %include "&dir\&name" /source2;

   Filename filelist pipe "dir /b /s c:\temp\*.sas"; 
                                                                                   
   Data _null_;                                        
     Infile filelist truncover;
     Input filename $100.;
     Put filename=;
   Run;

Would that work well for what I need to do?

And then I can merge them without calling them separately as well?

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

Tom — Mon, 22 Jul 2019 16:39:09 GMT

You can read all of the files in one data step if you want.

Use a wildcard in the INFILE statement to select the files that match the pattern.

Use the FILENAME= option on the INFILE statement to have SAS store the actual file name into a variable.

You will need to copy the filename to a new variable if you want to keep the filename as the variable named in the INFILE statement is not kept.

If you files have header rows then you can test when the file name changes to know when to skip the header row.

data want ;
  length fname $200;
  infile "&mdir/*.csv" dsd truncover filename=fname ;
  input @;
  if fname ne lag(fname) then input;
  length subject $10 datetime $10 ;
  fname = scan(fname,-1,'/\');
  subject=scan(fname,1,'_');
  datetime=scan(fname,2,'_.');
  .... rest of code to read the actual data ...
run;

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

Reeza — Mon, 22 Jul 2019 16:45:47 GMT

@kmardinian wrote:

Hi Reeza,

Thank you!

I do not have that in my program yet, I was not sure how to call all the files without having to name them all individually.

Is there a good way of doing that?

I had found this online:
   %include "&dir\&name" /source2;
   Filename filelist pipe "dir /b /s c:\temp\*.sas"; 
                                                                                   
   Data _null_;                                        
     Infile filelist truncover;
     Input filename $100.;
     Put filename=;
   Run; 
Would that work well for what I need to do?

And then I can merge them without calling them separately as well?

I would modify it to save it to a data set - check that it lists all the files you need and not the directories.

You may need to filter it if you have other files in the same folder.

The reason I'm not suggesting wildcards, as @Tom is, is because you have files in multiple folders and I don't think wildcards can handle that without multiple calls. And I'm assuming by 'merging' you mean append and add in the subject name as a field rather than merge to a wide data set because that doesn't makes sense to me. You can use his approach and repeat it several times or you can find all the file names and read them into a single data set in one step.

The infile documentation has some examples but once you provide the code to read a single file we can help you convert it to the code you need to read all files.

Check that this gives you the file list with all the files you need to read.

   Filename filelist pipe "dir /b /s c:\temp\*.csv"; 
                                                                                   
   Data myFileList;                                        
     Infile filelist truncover;
     Input filename $256.;
   Run;

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

ballardw — Mon, 22 Jul 2019 16:51:15 GMT

If ALL of the files in a folder that start with SUBJECT and have the same layout then this is not that hard. The key bit is ALL. If you want not to read some that start with SUBJECT or any of them have different file layouts then this gets more complicated.

Do you have a working data step to read one of these files?

The following is a skeleton of reading a bunch of CSV files that have one header row in each;

data dummy;
   infile "<path>\subject*.csv" dsd lrecl=32767 firstobs=2 eov=skip;
   /*<informat or attribute statement(s) of variables>*/
   input @;
   if skip=1 then skip=0;
   else do;
     input
     /* variable list*/
     end;
     /* other code wanted*/
    output;
  end;
run;

The firstobs=2 skips a header row for the first file. The Option EOV sets a variable, named skip in this example, to 1 when the first file in a series is read.

The input @. is basically to set the skip value and have it for testing. If it is equal to 1 we do not want to read that line. Because the value would only change on reading we set to 0 for the next iteration of the step. The actual input is in a DO End loop with an OUTPUT statement as we don't want output when encountering a header row.

If you have more than one header row then you need a different value for FIRSTOBS and an instruction to read past the header such as the following to read past a second header row.

if skip then do;

skip=0;

input;

end;

else do;

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

kmardinian — Mon, 22 Jul 2019 17:12:06 GMT

I was able to read in each file with this:

%let path1=C:\Users\K.M\Desktop\Reads\;

libname path1 "&path1";

%let dir=&path1;

filename xlfile pipe "dir ""&dir"" /tc /a-d" lrecl=200;

data in;
infile xlfile length=len end = eof;
input char $varying400. len;
if index(char,'.csv');
filename=scan(char,5,' ');
if filename =:'SUBJECT-';
run;

So now I have an output dataset with the variables char and filename. So now I have to figure out how to use the variable filename to call each file so I can merge them into one dataset. Is there a good way of doing this?

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

Reeza — Mon, 22 Jul 2019 17:14:04 GMT

Do you have the code to read a single file? I'll help you put them together once you post that code.

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

Tom — Mon, 22 Jul 2019 17:19:43 GMT

So if you have a dataset named IN with a variable named CHAR that has the name of the file to read then you can use that to drive reading all of the files in one step.

data want ;
  set in ;
  fname=char ;
  infile csv filevar=fname dsd firstobs=2 truncover end=eof;
  do while (not eof);
    ... code to read the data from a line ...
    output;
  end;
run;

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

kmardinian — Mon, 22 Jul 2019 17:55:05 GMT

Hi Tom,

I don't quite understand how this code will import all of the individual files one by one?

And what do you mean by this line "

... code to read the data from a line ...

Thank you,

Kristina

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

Reeza — Mon, 22 Jul 2019 18:01:20 GMT

data want ;
  set in ; *data set with list of file names and paths to read in;

 *store filename so you can get information later;
  fname=char ;


  infile csv filevar=fname dsd firstobs=2 truncover end=eof; 
*note fname is used here as filevar, which controls which file is read;
*note eof which says that its the end of a file and avoids reading first line of new file;

  do while (not eof);
    INPUT tested input data statement to read your file;
    output;
  end;
run;

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

Tom — Mon, 22 Jul 2019 18:03:04 GMT

The main data step will read in the dataset with the file names one observation at a time.

It then tells the INFILE statement which variable contains the NAME of the file to read. It also tells the INFILE statement to skip the first line of the file (assuming your files have a header row) and set the variable EOF to be true when it has read the last line from that file.

The DO loop will then let you read each line from the file. You will need to have a explicit OUTPUT statement since each iteration of the data step will read an entire file and not just one observation.

I left out the part about how to read the data from the files since you did not supply the information needed to do that. In general it is trivial to write code to read from a delimited file. First define the variable (easiest if you define them in the order they appear in the file). The assign any NEEDED informats or WANTED formats using INFORMAT and/or FORMAT statement. Then read the line using an INPUT statement. Simple example:

length id $20 age 8 dob 8 class $20 ;
informat dob date.;
format dob date9.;
input id -- class;

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

kmardinian — Mon, 22 Jul 2019 18:17:45 GMT

I'm sorry for not understanding this clearly. But the do loop only needs to include an input statement listing all the variables in the files?

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

kmardinian — Mon, 22 Jul 2019 18:19:01 GMT

Hi Tom,

You mentioned like Reeza that I have failed to supply the information needed to read in the data. What information do you need for that exactly? A proc import statement or an infile statement? Isn't that already in my line of code?

Thank you!

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

Tom — Mon, 22 Jul 2019 18:36:20 GMT

@kmardinian wrote:

Hi Tom,

You mentioned like Reeza that I have failed to supply the information needed to read in the data. What information do you need for that exactly? A proc import statement or an infile statement? Isn't that already in my line of code?

Thank you!

You don't want to use PROC IMPORT to read multiple delimited files. It will guess at how to define the variables based on the limited sample of data that appears in the one file it is currently processing. So you will end up with incompatible datasets. Plus you will need to run a separate PROC step for each individual file.

You did not supply any INPUT statements (or other code) to read ANY of the files. You did supply code to get the LIST of files, so that is why I said start with the dataset that has the list of files.

Here is an example of a program that reads delimited data. I will use in-line data using | as the delimiter to make the example clearer.

data want;
  infile cards dsd dlm='|' truncover firstobs=2;
  length name $20 age 8 dob 8 class $20 ;
  informat dob date.;
  format dob date9.;
  input name -- class;
cards;
Name|Age|DOB|Class
Fred|20|01JAN1999|Computer Science
Jill|23|02-JAN-1997|Mathematics
;

If you have many files all in the same format then you should have somewhere a document that describes the order of the fields and definition of each field. Use that to help you write the code. If you don't have such a document then try running PROC IMPORT on just one of the files and look at the code it generates. It will be ugly with many unneeded (unwanted) formats and informats attached to the variables, but it should let you have a starting point. You will also want to perhaps pad the lengths of any character variables in case future files have longer values.

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

Reeza — Mon, 22 Jul 2019 19:49:13 GMT

@kmardinian wrote:

I'm sorry for not understanding this clearly. But the do loop only needs to include an input statement listing all the variables in the files?

You should have the Length or Format statements as indicated by Tom, but it's why I've said from the start you need two things to automate this:

1. File list -> now solved

2. Working code to read one file, via a data step -> outstanding

The code to read the single file, whether it includes INFORMATS, FORMATS, LENGTH statements, must include an INPUT statement.

So within that do loop you place the code you've tested as working to read the file. I would start by limiting your input data set and testing it, add the obs=4 to the SET statement to test it out on a small set of files first. Once it's working, remove that option.

set in (obs=4);

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

kmardinian — Mon, 22 Jul 2019 19:59:03 GMT

Is there anyway that I can just ask SAS to spit out all the variables without listing their individual lengths and formats. I have over 50 variables in each of these files, so I was able to copy and paste them all into the input statement but writing out each length and format would be super tedious.

Also, it does not seem to be recognizing my file path for some reason.

55
56 data want ;
57 set in ;
58 fname=filename ;
59 infile xlfile filevar=fname dsd firstobs=2 truncover end=eof;
60 do while (not eof);
61
62 input studyID study Date readID --------------------------------------------------------------------- ;
63
64 output;
65 end;
66 run;

ERROR: Physical file does not exist, C:\SAS\Config\Lev1\SASApp\STAR-SUBJECT07.csv.
char=07/22/2019 01:06 PM 2,116 STAR-SUBJECT07.csv filename=STAR-SUBJECT07.csv num=1 fname=STAR-SUBJECT07.csv
eof=0 studyID=. study=. Date=. readID=. _ERROR_=1 _N_=1
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.IN.
WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 0 observations and 7 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

Tom — Mon, 22 Jul 2019 20:07:37 GMT

Make sure that FNAME has the FULL name of the file. Your error message is saying you did not provide the path that the file lives in. If it is not in your source file list then add it.

FNAME='my directory name/' || filename;

All of the files have the same data in the same order right???

50 variables is NOTHING.

Copy the first line from one of the files and paste it into the editor and convert it into a LENGTH statement.

Replicate those that you might need to use for FORMAT or INFORMAT statement.

For the INPUT statement you just need to list the FIRST and LAST variable name with double hyphen to specify a positionally defined variable list.

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

Reeza — Mon, 22 Jul 2019 20:57:17 GMT

@kmardinian wrote:

Is there anyway that I can just ask SAS to spit out all the variables without listing their individual lengths and formats. I have over 50 variables in each of these files, so I was able to copy and paste them all into the input statement but writing out each length and format would be super tedious.

It is tedious but really the only way to ensure your data is read correctly. And honestly, it'll save you time to figure it out all now, rather than later when you realize that three variables were truncated so that's why your numbers are all off.

Re: Pulling files from an output folder without knowing the file names and merging them into a datas

kmardinian — Mon, 22 Jul 2019 21:18:54 GMT

Hi Tom,

I checked the filename in my first output dataset and they match exactly to the full name of each file.

I have created this macro variable for where the files are located. Am I not calling it correctly in my code because I then get this error.

%let path1=C:\Users\Kristina.Mardinian\Desktop\Reads\;
libname path1 "&path1";

%let dir=&path1;

55
56 data want ;
57 set in ;
58 fname=filename ;
59 infile "&dir/" filevar=fname dsd dlm='|' firstobs=2 truncover end=eof;
ERROR: A Physical file reference (i.e. "PHYSICAL FILE REFERENCE" ) or an aggregate file storage reference (i.e. AGGREGATE(MEMBER) )
reference cannot be used with the FILEVAR= option.
60 do while (not eof);
61
62 input studyID study Date readID ;
63
64 output;
65 end;
66 run;