topic Re: import excel files into sas from dates in data set in SAS Programming

import excel files into sas from dates in data set

Ronein — Sun, 21 Mar 2021 09:23:52 GMT

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

Re: import excel files into sas from dates in data set

ballardw — Sun, 21 Mar 2021 12:40:24 GMT

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.

Re: import excel files into sas from dates in data set

Kurt_Bremser — Sun, 21 Mar 2021 13:57:11 GMT

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.

Re: import excel files into sas from dates in data set

Tom — Sun, 21 Mar 2021 16:16:03 GMT

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;