topic Re: using proc import to import excel file but use the files partial name in New SAS User

using proc import to import excel file but use the files partial name

sas_student1 — Tue, 26 Apr 2022 17:57:39 GMT

Hello,

I have a question on how to import an excel file where the excel file name has an ID and date that is changed on a daily basis.

I get an excel file submitted to be the format of the naming convention of the file is that it has an ID (example 123 below) then the standard "excel_name" this doesn't change and then a date (e.g. 04252022 as below).

How can I read the file where if the file has the label "excel_name" regardless of the date and ID that it reads the file.

Thanks!

PROC IMPORT 
OUT= directory
DATAFILE= "C:\user\123_excel_name_04252022" 
            DBMS=XLSX REPLACE;
     GETNAMES=YES;
	 sheet='directory';
RUN;

Re: using proc import to import excel file but use the files partial name

Sajid01 — Tue, 26 Apr 2022 18:56:57 GMT

The following code will do. This will take the date on which the code is run.

If your run the code today, the date value will be 04262022 and the file name will be "c:\user\123_exel_name_04262022.xlsx". if you do not need the file extension then remove the .xlsx from the code. But I think you will need it as excel files come with an extension.

PROC IMPORT 
OUT= directory
DATAFILE= "C:\user\123_excel_name_%sysfunc(today(),mmddyyn8.).xlsx" 
            DBMS=XLSX REPLACE;
     GETNAMES=YES;
	 sheet='directory';
RUN;

Re: using proc import to import excel file but use the files partial name

Tom — Tue, 26 Apr 2022 19:38:02 GMT

You could use macro variables to build the filename.

So first set the macro variables.

%let id=123;
%let date=%sysfunc(today(),mmddyy8.);

Then use them to build the filename.

DATAFILE= "C:\user\&id._excel_name_&date..xlsx"

PS It is not a good idea to use date string in MDY or DMY order in filenames. The filenames will not sort in chronological order. If you use date strings in YMD order instead then they will sort properly. Plus you eliminate the confusion caused by values like 10122021. Is that Oct 12th or 10th of Dec?

Re: using proc import to import excel file but use the files partial name

Reeza — Tue, 26 Apr 2022 19:55:18 GMT

Are there multiple Excel files in the folder and you only want to read a specific one?
Is the file name always the day you run the code? Or is it always the most recent file?

Re: using proc import to import excel file but use the files partial name

sas_student1 — Tue, 26 Apr 2022 23:33:28 GMT

Good point there would end up being multiple files in one folder and I would want to get the most recent file that was saved. The way I get the file is that the sender adds a number and a date at the end. They have their own saving mechanism that they use. And this is something that I am trying to automate, so was thinking how to create an import that would be able to recognize the most recent file. Another item to note is that they would save the file at the end of day and then I wanted the code to pick it up the next day (I guess I can also have it run later in the evening so it doesn't have to wait the next day to run, but I allow the sender some time in case they decide to want to update the file after they have saved it.) I know its being complicated than it needs to be, but it got me to figure how "fancy" a sas code can one develop if one needed to.

Re: using proc import to import excel file but use the files partial name

sas_student1 — Tue, 26 Apr 2022 23:37:24 GMT

Thanks! This may work!. Though I wonder if I can add the %sysfunc(today(),mmddy8.) to the end of file name in the datafile step. That way I can have the one id macro. Would that not work as well?

I guess I can try it to see.

DATAFILE= "C:\user\&id._excel_name_%sysfunc(today(),mmddy8)..xlsx"

Re: using proc import to import excel file but use the files partial name

Reeza — Tue, 26 Apr 2022 23:44:23 GMT

Search the forum for how to import the most recent file in a folder. It's a question that's usually asked (and answered) at least once a month on here.

Re: using proc import to import excel file but use the files partial name

sas_student1 — Tue, 26 Apr 2022 23:45:52 GMT

will do thank you!

Re: using proc import to import excel file but use the files partial name

Sajid01 — Wed, 27 Apr 2022 02:03:07 GMT

Yes you can. Your code then will be.
PROC IMPORT
OUT= directory
DATAFILE= "C:\user\&id._excel_name_%sysfunc(today(),mmddyyn8.).xlsx"
DBMS=XLSX REPLACE;
GETNAMES=YES;
sheet='directory';
RUN;

Re: using proc import to import excel file but use the files partial name

Kurt_Bremser — Wed, 27 Apr 2022 06:50:04 GMT

<rant>Why do some ******, ****-******* ****** always come up with the stupid idea of using ANYTHING BUT a YMD date in filenames?</rant>

Anyway, here's some code that extracts the date from filenames, converts it to a SAS date value and gets the max of it:

%let indir=C:\user;

data _null_;
length dref $8 name $200;
rc = filename(dref,"&indir.");
did = dopen(dref);
if did
then do;
  do i = 1 to dnum(did);
    name = dread(did,i);
    if scan(name,2,"_") = "excel" and scan(name,3,"_") = "name" and scan(name,-1,".") = "xlsx"
    then do;
      date = input(scan(name,4,"_"),ddmmyy8.);
      if date > maxdate
      then do;
        maxdate = date;
        maxnum = scan(name,1,"_");
      end;
    end;
  end;
  if maxdate ne .
  then call symputx("infile",catx("_",maxnum,"excel","name",put(maxdate,ddmmyyn8.))!!".xlsx");
  else call symputx("infile","not found");
  rc = dclose(did);
end;
else call symputx("infile","directory not found");
rc = filename(dref);
run;

With a properly structured filename, where the date comes before any variable part and has a date in YMD order, it's one simple one-liner:

(UNIX example)

data _null_;
infile "ls &indir./excel_name_*_*.xlsx|tail -1" pipe;
input;
call symputx("infile",_infile_);
run;

Maxim 33: Intelligent Data Makes for Intelligent Programs.

Filenames are data, and properly structured filenames makes handling them a breeze.