Hello to every one!
I have a report in SAS that reads few .txt files that are distinguished from the date of the last refresh i.e
20220516_filenameabc
20220509_filenameabc
20220502_filenameabc
With the scripts below I was able to extract the latest refresh:
data filenames;
length fref $8 fname $200;
did = filename(fref,'path\Automated Extracts');
did = dopen(fref);
do i = 1 to dnum(did);
fname = dread(did,i);
output;
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;
data latest;
set filenames;
length ldate $8 ;
ldate = substr(fname, 1,8);
if substr (ldate ,1,1) ~= 2 then delete ;
drop fname ;
run;
proc sort data = latest;
by descending ldate ; run;
proc sort nodupkey data = latest;
by descending ldate ; run;
data latestd ;
set latest (OBS=1 FirstOBS=1) ; run ;
The part that I want to automate is this:
%LET INDATAFILE7 = 'path\Automated Extracts\20220516_filenameabc.txt';
Instead of manually updating here 20220516 to use a macro that reads this from the dataset created with the above script so I don't need to go every time that I run the report to check the latest file and manually change that part!
Any suggestion what I can do better here?
Thanks for your time and support!
You don't need a macro to read all text files in a folder. You can simply use an *.txt in the INFILE statement.
https://blogs.sas.com/content/sasdummy/2018/10/09/read-multiple-text-files/
Please use the search function, your issue has been asked and answers multiple times, so you will find something useful.
data _null_;
set work.latestd;
call symputx('INDATAFILE7','path\Automated Extracts\'||ldate||'_filenameabc.txt');
run;
If your files are named like <YYYYMMDD>_<something else>, this may work:
data _null_;
did = filename(fref,'path\Automated Extracts');
did = dopen(fref);
do i = 1 to dnum(did);
currentfile=dread(did,i);
if substr(currentfile,1,1)='2' and fname<currentfile then fname=currentfile;
end;
call symputx('INDATAFILE7','path\Automated Extracts\'||fname);
did = dclose(did);
did = filename(fref);
run;
Which should give you the filename beginning with "2" having the highest (latest) filename in your macro variable INDATAFILE7.
Just put the name of the most recent file into a macro variable.
proc sql noprint;
select fname into :fname trimmed
from filenames
order by fname desc
where fname like '202%'
;
quit;
Once you have the macro variable use it in your code that reads the file.
Make sure to use double quotes around the string so that the macro variable value is resolved.
data want;
infile "path/Automated Extracts/&fname" .... ;
...
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.