topic Re: How to extract date from a file name in SAS Enterprise Guide

How to extract date from a file name

Nimish28 — Mon, 24 May 2021 19:38:16 GMT

Hi All,

I have multiple xlsx files (for example abc_20200101.xlsx ) , i have 38 files like this and i want to extract the dates from these file names into a new column.
I am already reading the files using macros but unable to find a logic that will fit in.
It would be really great if any could help me here.

Thank you in advance!

Re: How to extract date from a file name

ballardw — Mon, 24 May 2021 19:53:34 GMT

@Nimish28 wrote:
Hi All,

I have multiple xlsx files (for example abc_20200101.xlsx ) , i have 38 files like this and i want to extract the dates from these file names into a new column.
I am already reading the files using macros but unable to find a logic that will fit in.
It would be really great if any could help me here.

Thank you in advance!

Please show us the code you are using to read the files. That may contain enough information we can help.

You also need to tell us where you expect to store that "date". In the data set created when reading the file, in a different data set, in a macro variable, in a document (and what type) or someplace else.

Re: How to extract date from a file name

Nimish28 — Mon, 24 May 2021 20:44:12 GMT

I want that column in the dataset. We have to add the column in the dataset containing the files.
I might not be able to provide you the exact code right now but i can give you a brief how I am doing it,
I am pulling all the files names first from the files and then stored them in a macro and then used that macro in proc import to pull the files using call execute macro.

Hope that helps

Re: How to extract date from a file name

ballardw — Mon, 24 May 2021 21:18:55 GMT

Since you are using Proc import you will have to parse the bit of data you want out of the file name and then use a data step to bring that parsed value into the data set.

Something like:

%let mvar=folder\subfolder\abc_20200101.xlsx ;

data _null_;
   date = input(substr("&mvar.",index("&mvar.",".")-8,8),yymmdd10.);
   call sumputx('mdate',date);
run;

data want;
   set yourdatasetname;
   date = &mdate;
   format date yymmdd10.;
run;

Assumes that you have a variable that holds a single name of the file.

The data null step searches that "name" for the period before xlsx and pulls in the 8 characters of the date and uses an appropriate informat to read that text into a date value, creates another macro variable to use in a data step to add the variable to your data set. This assumes the "yourdataset" has been made, such as proc import import.

Caution since you are using Proc Import: You have to pick a name for the date variable that never appears in the columns of any the XLSX files. As it hasn't yet and never will appear in the future with any of the files.

Caution: If your file names ever have more than one period or use fewer or more digits for the date, or appear in any other order than YYYYMMDD the above code will not work and you will have to go back to the drawing board.

Re: How to extract date from a file name

Ksharp — Tue, 25 May 2021 12:32:14 GMT

%let path= c:\temp ;

data want;
 rc=filename('x',"&path");
 did=dopen('x');
 do i=1 to dnum(did);
   fname=dread(did,i);
   if prxmatch('/\d{8}\.xlsx/',fname) then do;
    want=scan(fname,-1,,'kd');output;
   end;
 end;
drop rc did i;
run;