How to automate the name of an excel file when importing it.

[bradleyx]

Good Day, I'm trying to automate importing an excel file from a folder based on the most recent time period.

For example,

proc import out= cars
datafile= "C:/mydata/SampleData_202304.xlsx"

dbms= xlsx replace;
sheet="Sheet1";
run;

It will import the file for April 2023 but for May 2023 I would have to manually change the file name. How can I automate that?

[whymath]

You can use the system date instead of writing it manual. Try this:

proc import out= cars
datafile= "C:/mydata/SampleData_%sysfunc(date(),yymmn6.).xlsx"
dbms= xlsx replace;
sheet="Sheet1";
run;

[Kurt_Bremser]

If you want the latest from a series of files:

data files;
rc = filename(dref,"C:/mydata");
did = dopen(dref);
if did
then do;
  do i = 1 to dnum(did);
    name = dread(did,i);
    if substr(name,1,11) = "SampleData_" then output;
  end;
  rc = dclose(did);
end;
rc = filename(dref);
keep name;
run;

proc sort data=files;
by descending name;
run;

data _null_;
set files;
call symputx('name',name);
stop;
run;

proc import out= cars
datafile= "C:/mydata/&name."