DATA Step, Macro, Functions and more

How to list all text file in a directory and then feed into macro?

Reply
Regular Contributor
Posts: 161

How to list all text file in a directory and then feed into macro?

Never coded myself SAS macro before. 


I have a list of text files in a Linux directory with the name convention like below:

have_20090414.output.txt

I hope to extract the date first from all the text files within in that directory say /path/to/file/

and then feed the date just created into the dt macro variable below and repeat the process as below for all the files in that directory.  Could you please tell me how to best code the macro?

%let dt = 20111004;

DATA want.want_&dt;

LENGTH

date               8

key               $ 15

INFILE "d:\have&dt..output.txt"

LRECL=32767

ENCODING="WLATIN1"

DLM='7c'x

MISSOVER

DSD ;

INPUT

RUN;

proc sql;

create table want.xwant_&dt as

select distinct date, key

            from want.want_&dt;

quit;


I know that if the files in the directory are all SAS then it might be easier...

data _null_;

      set abccont end=last;

      by memname;

      i+1;

      call symputx('name'||trim(left(put(i,8.))),memname);

      if last then

            call symputx('count',i);

run;


%macro transform;

      %do i=1 %to &count;

            ???;

      %end;

%mend transform;

  %end;

%mend combdsets;

Super User
Super User
Posts: 7,392

Re: How to list all text file in a directory and then feed into macro?

As for getting the date from the filename, something like this should work:

filename dir_list pipe 'dir "c:\daten" /B /s /tw';

data temp;
      infile dir_list;
      attrib  buffer format=$2000. file_date format=date9.;
      input buffer $2000.;
      file_date=input(put(substr(buffer,6,8),$8.),yymmdd8.);
run;

The dataset temp will have filename and date variables.  However I am not sure on your second question though.  Do you want to put these dates into their own separate macro variables?  This doesn't seem like an efficient way of doing things, and if the are lots of files that would mean lots of macro variables.  Another way of doing it is to use the file and date list as before and let call execute generate the code from there:

data _null_;

set temp;

call execute('proc sql;

   create table want.xwant_'||put(file_date,date9.)||' as

   ...

        quit;');

run;

Respected Advisor
Posts: 3,777

Re: How to list all text file in a directory and then feed into macro?

Do you want to read the data from all the files in the directory?

If so do all the files in the directory have the same record layout?  That is can the fields all be read with the same INPUT statement(s)?

Regular Contributor
Posts: 161

Re: How to list all text file in a directory and then feed into macro?

Yes. formatting is exactly the same. 

Super User
Posts: 10,476

Re: How to list all text file in a directory and then feed into macro?

Reading the files can be done by using wildcards in a filename statement

filename readit "<system path>/*.txt";

The use the readit or whatever you use in the filename in the infile statement.

Respected Advisor
Posts: 3,777

Re: How to list all text file in a directory and then feed into macro?

Valued Guide
Posts: 3,208

Re: How to list all text file in a directory and then feed into macro?

Where is Tom? This is almost the same question as in: https://communities.sas.com/thread/56245

You could use pipe and "ls ---" commands using the X-cmd access but the SAS functions are probably nicer.  

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 6 replies
  • 2284 views
  • 6 likes
  • 5 in conversation