import excel files into sas from dates in data set

[Ronein]

Hello

I have excel files called tDDMMYYYY .

for example: t14032021 ,t15032021 and so on

I want to import them into SAS from 7 days before yesterday until yesterday.

I created a data set that including a column date_char  with required potential excel files that need to import into SAS.

Some of the excel files might not exist.

What is the way to create a dynamic import into sas of these excel files

Thanks

%let lastDay_SAS=%sysfunc(intnx(day,%sysfunc(today()),-1,same));
%put &=lastDay_SAS;
%let minus7_SAS=%sysfunc(intnx(day,%sysfunc(today()),-8,same));
%put &=minus7_SAS;

data want_days(keep=date_char);
date=&minus7_SAS.;
date_char=put(date,DDMMYYn8.);
end_date=&lastDay_SAS.;
do while (date<=end_date);
output;
date=intnx('day', date, 1, 's');
date_char=put(date,DDMMYYn8.);
format date DDMMYYn8.;
end;
run;

Now need to import excel files :
t13032021
t14032021
t15032021
t16032021
t17032021
t18032021
t19032021
t20032021
It might happen that some of the excel files doesnt exist

[ballardw]

Create the Full path and name of the file.

Use the FEXIST function to determine if the file exists.
If it does not exist then don't add it to the data set.

Really, you want to

import excel files :t13032021t14032021t15032021t16032021t17032021t18032021t19032021t20032021

 I don't know how you make that string but you really need to consider what you did.

[Kurt_Bremser]

Suppose you have a macrio to import a single file, something like

data _null_;
do date = today() - 8 to today() - 1;
  datestr = put(date,ddmmyyn8.);
  if fileexist(cats("&path./t",datestr,".xlsx"))
  then call execute('%nrstr(%excel_import("!!(cats("&path./t",datestr,".xlsx"))!!'))');
end;
run;

Modify the CALL EXECUTE as needed.

BTW why do you use the unfavorable DMY date order? Dates in YMD order are much easier to handle.

[Tom]

Use FILEEXIST() function to test if the XLSX file exists.

Use iterative do loop when iterating over integers.

Use CALL EXECUTE() to generate code from data.

data want_days ;
  length date 8 member $32 fname $200;
  format date date9.;
  do date = today()-8 to today()-1 ;
    member = 't'||put(date,ddmmyyn8.);
    fname=cats("&path/",member,'.xlsx');
    if fileexist(fname) then do;
       output;
       call execute(catx(' '
              ,'proc import datafile=',quote(trim(fname))
              ,'dbms=xlsx'
              ,'out=',member,'replace'
              ,';run;'
       ));
    end;
  end;
run;