Macro variable using scan by splitting a Variable.

[Santt0sh]

Hi All,

I am trying to create macro variables for the file names from a SAS data set.The dataset lists the File names from a Unix directory in order to create datasets by reading the files.
I have listed all the files from a directory, but the Sas dataset looks something like this.

Data Set file:

Filename
ABC.txt xyz.txt Def.txt wht.txt——————————————-
Tyua.txt egg.txt Dder.txt hiy.txt
——————————————-
Sret.txt Amy.txt vgu.txt uil.txt
———————————————

The Macro Variables are only created for the first observation and not for the other observations in the dataset.
The variables are only created for the first observation. Even after I have tried to loop on the count of the table.
I have tried taking the countW, Length of the macro variables.

Proc sql;
Select count(*) into :Recs from filenames:
Quit;

Proc sql;
Select filenames into: fnme from filenames;
Quit;

I have tried looping the Scan for both the counts mentioned above.

Please Suggest!

[Kurt_Bremser]

What do you want to do with the files?

[Santt0sh]

Hi Kurt,

Thank you for your quick response and apologies for the delayed response.

I need to create datasets by reading the files at the location.

I believe I can find the solutions from the below responses

[PaigeMiller]

Show us the desired output (desired macro variables). Show us your code.

[Santt0sh]

Hi Paige,
Thank you for your quick response and apologies for the delayed response.

I need to create datasets by reading the files at the location.

I believe I can find the solutions from the below responses

[Tom]

Your message has been scrabbled by the Forum editor.  If you want to insert SAS code use the Insert SAS Code icon to get a pop-up window where you can type/paste/edit your SAS code.  For other preformatted text, like data lines using the Insert Code icon instead.

You can ask PROC SQL to make one long macro variable using any delimiter your want.  If your actual filenames do not include any spaces you can use space as the delimiter.  Otherwise use some other character like vertical bar, which cannot be part of a filename.  There is no need to run the select twice to get the count, PROC SQL will count for you.

So if you have a dataset named FILENAMES with a variable named FILENAME then the code to create macro variables FILENAMES and RECS could look like this:

proc sql noprint;
select filename 
  into :filenames separated by '|'
  from filenames
;
%let recs=&sqlobs;
quit;

Which you could use with the %SCAN() function like this:

%do index=1 to &recs;
  %let next_file=%scan(&filenames,&index,|);
... code that does something with &NEXT_FILE ...
%end;

[Santt0sh]

Thank you for your response, I will try the below method and let you know.

[ErikLund_Jensen]

Hi @Santt0sh 

It is difficult to see the structure in your example data, but it seems there are more than one file name per record, so it looks like output from the LS command called without -L option. It requires a lot of coding to handle malformed input, so I think the first step is to make sure you have a data set with one full filename incl. directory in each observation. You can get that with the following code:

%let directory = /sasdata/prod/batchjobs/;

* Get list of filenames + file count in macrovar. Recs;
filename dirlist pipe "ls -l ""&directory""";
data filenames;
  infile dirlist lrecl=512 end=eof;
  length filename $512;
  input;

  * Output relevant lines and update counter;
  if _infile_ =: '-' then do;
    filename = catt("&directory",substr(_infile_,61));
    c + 1;
    output;
  end;

  * Store file count;
  if eof then call symputx('recs',c);
run;
%put &=recs;

Next step is to process the file list and get the desired macro variables. The solution suggested by @TOM works in two steps, first a macro variable is created containing a string of all filenames with separators, and then the individual filenames are pulled from the string in a loop. it works as long as the full list does not exceed 32767 characters in length, so with a average filename length of 50 bytes the string can hold 642 filenames.

This limit might not be a problem in your case, but my work it often gives problems, so I recommend a different approach, where each macro variable with a  single filename is pulled directly from the filenames-dataset without creating a list:

%macro fileloop;
  %do i = 1 %to &recs;

    * Get next filename into macro variable;
    data _null_;
      set filenames (obs=&i firstobs=&i);
      call symputx('next_file',filename);
    run;

    * Read file into SAS data set;
    data test_&i;
      infile "&next_file" lrecl=512 truncover;
      input record $char512.;
    run;
  %end;
%mend;
%fileloop;

Note: If you are processing many files, the log will be very big depending on what is done in each loop iteration, so I recommend to test the code with the loop %to-variable set to a small number, and then redirect the log to dummy as first step in the macro and back in a last step after the %end-statement, before you run the code on the total list of filenames.

[Tom]

If you want to use the filename to read a text file then skip the macro variable completely and just use the data set variable.

    * Read file into SAS data set;
    data test_&i;
      if _n_=1 then set filenames (obs=&i firstobs=&i keep=filename);
      infile nextfile filevar=filename lrecl=512 truncover;
      input record $char512.;
    run;

[ErikLund_Jensen]

@Tom 

You are right, of course, that's much smarter. My only (bad) excuse is that I made the example from a program that uses the filename more than once in the loop, because it also moves the file to a backup-directory after reading. 

[Santt0sh]

Hi,

Thank you for your response!!
I will try the below suggested and let you know.