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;
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;
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)?
Yes. formatting is exactly the same.
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.
See this thread
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.