how to import excel files on a loop

[LilyVyhanek]

Hi Experts! 

I am trying to write a code that imports weekly excel files into one large table (this table should encapsulate all the weekly excel tables to create one large monthly table), the issue is that the days that they are reported are contantly changing so it can't be a hard code that can be repeated monthly. My solution to this was for the user to just input the dates that are given.  Here is what i have so far: 

%Let Date1 = 2023-8-2;
%Let Date2 = 2023-8-9;
%Let Date3 = 2023-8-16;
%Let Date4 = 2023-8-23;
%Let Date5 = 2023-8-30;

%Let FilePath2 = \\path\to\my\Data\SFC Funding Notification &Date1..xlsx;
LIBNAME ClMed2 xlsx "&FilePath2.";

proc contents data=ClMed2._all_ out=SheetNames(keep=memname) noprint;
run;
proc sort data=SheetNames;
by memname;
run;
data _null_;
set SheetNames end=last;
if last then call symputx('LastSheet', memname);
run;
data ClMed2_LastSheet;
set ClMed2."&LastSheet"n(firstobs=3);
run;

(The reason why the import is odd is because the name of the excel tabs is also constantly changing, but it always the last sheet so this was my attempt at only importing the last sheet on the excels )

What would be the best way to loop this so that the data table populates with the tables called "SFC Funding Notification 2023-8-2", "SFC Funding Notification 2023-8-9",  "SFC Funding Notification 2023-8-16", ... etc. 

[ballardw]

Once you have a library pointing to the spreadsheet then easiest would be to have another library define and copy of all of the sheets to the library.

I would set OPTIONs VALIDMEMNAME=COMPATIBLE VALIDVARNAME=V7;  before assigning that library to the spreadsheet so the non-standard names don't make more problems coding later. That can get around having to use those name literals for example.

Also you will not get those target names. They are too long. SAS data set names are limited to 32 characters and those names are 34 (or 35 with a 2 digit month). Which may be another problem because of the name truncation will yield duplicate names.

I would at least try this one time to see what you get.

libname mylocal "<path>\myfolder";
options validmemname=compatible validvarname=v7; 

LIBNAME ClMed2 xlsx "&FilePath2."; /* personally for the first trial there is no reason to use a macro variable*/
proc copy in=ClMed2 out=mylocal memtype=data;
run;

If this works then I would use a data step to write the code to a program file instead of trying to create multiple date variables and loop over them but get one working first.