topic Re: How to automate the name of an excel file when importing it. in SAS Programming

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

bradleyx — Thu, 27 Apr 2023 01:10:55 GMT

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?

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

whymath — Thu, 27 Apr 2023 02:11:36 GMT

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;

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

Kurt_Bremser — Thu, 27 Apr 2023 06:07:55 GMT

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."