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

[Tom]

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.

[kmardinian]

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;

[Reeza]

The path needs to be part of the file name (fname variable), so you may need to go back a step and ensure that fname has the full filepath included as well as the file name.

[Tom]

---

@kmardinian wrote:

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;

---

Note that if you use the /B option on the Windows/DOS DIR command then it throws away the PATH and just gives the names for the files within the path.  Unless you also add the /S option, but then it will also return matching files from subdirectories instead of just the files in the directory you asked for.

C:\downloads>dir c:\downloads\*.rtf /b
test1.rtf
test2.rtf

C:\downloads>dir c:\downloads\*.rtf /b/s
c:\downloads\test1.rtf
c:\downloads\test2.rtf
c:\downloads\sas_scripts\test.rtf

In this LOG you have introduced a new error by trying to get INFILE to paste the path and filename (member name) together for you.

When you use the FILEVAR= option just put some dummy fileref after the INFILE keyword as it is not going to be used.  I like to use DUMMY to remind myself that it is just a dummy name because the syntax of the INFILE statement needs something in that place.

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

data want ;
   set file_list ;
   fname="&path1"||filename ;
   infile dummy filevar=fname .......

Note that you could put the two steps together.

data want ;
   infile "dir &path1/*.csv /b" pipe truncover ;
   input filename $256. ;
   filename="&path1"||filename;
   fname=filename;
   infile dummy filevar=fname dsd dlm=',' firstobs=2 end=eof;
   do while (not eof);
       input studyID :$20.  study :$20. date :$date. readid :$20. ;
       output;
   end;
run;

[kmardinian]

I feel like I'm super close, but SAS still gives me errors regarding the path. The file path is correct and yet I am still getting errors for it. I tried renaming the folder, assigning it a different location. But it doesn't seem to fix the issue.

57 data want ;
58 infile "&path1.*.csv /b" pipe truncover ;
59 input filename $256. ;
60 filename="&path1"||filename;
61 fname=filename;
62 infile dummy filevar=fname dsd dlm=',' firstobs=2 end=eof;
63 do while (not eof);
64 input studyID :$20 study: $20 date: $date readID :$20;

NOTE: The infile "C:\\Users\Kristina.Mardinian\Desktop\Reads\*.csv /b" is:

Unnamed Pipe Access Device,
PROCESS=C:\\Users\Kristina.Mardinian\Desktop\Reads\*.csv /b,
RECFM=V,LRECL=32767

Stderr output:
'C:\\Users\Kristina.Mardinian\Desktop\Reads' is not recognized as an internal or external command,
operable program or batch file.

NOTE: 0 records were read from the infile "C:\\Users\Kristina.Mardinian\Desktop\Reads\*.csv /b".

NOTE: The data set WORK.WANT has 0 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[Tom]

If you use the PIPE engine you need to give it a command to run.  A CSV file is not an executable command.

I think you meant to run the DIR command.

[kmardinian]

Hi Tom,

Are you referring to the below statement?

infile "&path1.*.csv /b" pipe truncover ;

Should it be this instead:

infile "dir &boxpath1.*.csv /b" pipe truncover ;

Because this also didn't seem to run and gave me a weird path name, which is why I tried removing the "dir"

[Tom]

What is &BOXPATH1 and how different is it than &PATH1 ?

[kmardinian]

Hi Tom, sorry I was playing around the code and changed the name at one point to boxpath. But when the code was ran, the path names were the same (path1). Should I be keeping the "dir" in the infile statement? 

I appreciate all your help on this!

[Reeza]

---

@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. 

[Tom]

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;

[ballardw]

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;

[ScottBass]

---

@kmardinian wrote:

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!

---

Two questions:

1) Are all the files the same format?  IOW does the same import process import the CSV?

2) You say "merge them into one dataset".  Do you mean merge, or concatenate?  I'm going to assume the latter.

Sample code:

* create 10 CSV files ***having the same format*** ;
%let pathname=%sysfunc(pathname(work));
%macro code;
   %do i=1 %to 10;
      %let i=%sysfunc(putn(&i,z2.));
      proc export data=sashelp.class dbms=csv outfile="&pathname\class&i..csv";
      run;
   %end;
%mend;
%code;

* check created file ;
data _null_;
   infile "&pathname\class01.csv";
   input;
   put _infile_;
run;

* build a directory list ;
%dirlist(dir=&pathname, filter=ext='csv')

* check dirlist ;
proc print data=dirlist;
run;

* read each file ;
%macro code;
%let fullname=%sysfunc(strip(&fullname));
data WORK.TEMP;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile "&fullname" delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2;
   informat Name $7. ;
   informat Sex $1. ;
   informat Age best32. ;
   informat Height best32. ;
   informat Weight best32. ;
   format Name $7. ;
   format Sex $1. ;
   format Age best12. ;
   format Height best12. ;
   format Weight best12. ;
input
            Name $
            Sex $
            Age
            Height
            Weight
;
if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
run;

proc append data=work.temp base=work.want;
run;
%mend;

* delete base table from previous runs ;
%kill(data=work.want)

* call code macro for every row in dirlist ;
%loop_control(control=dirlist)

* check final table ;
proc print data=want;
run;

Comments:

• @Tom 's suggestion of using filevar would perform better.  Still, I cranked out the sample code in 10 mins.
• I ran a single proc import of the CSV, then cut-and-pasted the output from the log and edited it.  So, that takes care of your 50 variables issue.

Even if I've misunderstood what you're wanting to do, perhaps you can modify some of this to suit your needs.

You also need to hit the doc for the infile statement.  Read up on the filevar option (and others).  Also review the examples:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000146932.htm

My macros are at https://github.com/scottbass/SAS/tree/master/Macro

Hope this helps...