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

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

caveman529 — Thu, 03 Apr 2014 07:30:12 GMT

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;

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

RW9 — Thu, 03 Apr 2014 09:05:41 GMT

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;

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

data_null__ — Thu, 03 Apr 2014 10:28:57 GMT

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)?

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

jakarman — Thu, 03 Apr 2014 11:38:47 GMT

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.

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

caveman529 — Thu, 03 Apr 2014 14:36:20 GMT

Yes. formatting is exactly the same.

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

ballardw — Thu, 03 Apr 2014 14:50:04 GMT

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.

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

data_null__ — Thu, 03 Apr 2014 16:22:43 GMT

See this thread

https://communities.sas.com/message/192902#192902