Issue with proc import

[marleeakerson]

Hello, 

So I am running a simple piece of code to import an excel file and sort it and then output it to a sas file. I am hoping that every day as I upload the new daily excel file I can run the code everyday and it will output a sas file for each day with that day's specific data. However, I have run the code the last few days, and every time I run it only imports data from the first day's excel file, even though I have changed the file name to import today's data. Here is the code: 

proc import datafile="data.xlsx"
out=forecast&sysdate. dbms=excel replace;
range="$E6:F";
getnames=yes;
/*MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
USEDATE=NO; */
run;

data in_capacity&sysdate.;
set forecast&sysdate.;
Capacity=Filled_Appointments/Total_Appointments_Avalable;
run;

proc sort data=in_capacity&sysdate.;
by Capacity;
run;

So I make sure that the file name everyday matches what is in the proc import code so that it imports new daily data, but it continues to only import data from the first day's file. 

Any ideas? 

Thank you! 

[PaigeMiller]

If you open SAS and leave it open for many days without shutting it down (he said redundantly), &SYSDATE does not change, it contains the date when you first opened SAS.

Instead of &SYSDATE in your code, you can use 

%sysfunc(putn(%sysfunc(today()),date7.))

[ballardw]

Please so the log from one of the runs where you are not getting the new data.

Copy the log starting at the Proc Import step through the end of the proc sort. Paste it into a code box opened on the forum with the </> icon to preserve formatting of the log. The log often has diagnostic characters and the main message windows here will reformat text so that the usefulness of the diagnostics goes away.

Caution with &sysdate macro variable. If your SAS session is on continuously it has the date the session started and may not have the desired date.

For example the SAS session on the machine I am working on has been on for a couple of days and from my log:

429  %put &sysdate.;
27MAY20

even though the day I post this is 29 May 2020.

[SASJedi]

The first problem I see with this code is that the Excel range is not clearly specified. A range specification is for a named range in the Excel workbook. If you truly need to pick up data from a cell range, make that area a named range in Excel before trying to access the data in SAS. For example, if your named range was called "Stats", this code would work:    

proc import datafile="data.xlsx"
   out=forecast&sysdate. dbms=excel replace;
   range="Stats";
   getnames=yes;
run;
data in_capacity&sysdate.;
   set forecast&sysdate.;
   Capacity=Filled_Appointments/Total_Appointments_Avalable;
run;
proc sort data=in_capacity&sysdate.;
  by Capacity;
run;

But you could skip all of the PROC IMPORT, DATA step and PROC SORT stuff, too. Using an XLSX LIBNAME statement would make this much simpler:

libname myxl XLSX "data.xlsx";
proc sql;
create table in_capacity&sysdate. as
   select *
         ,Filled_Appointments/Total_Appointments_Avalable as Capacity
      from myxl.Stats
       order by Capacity
;
quit;

This assumes a new Excel sheet is created each day containing only that day's data, and that the range is always named the same.