Problem with using substring together with %sysfunc

[chris2377]

Hi,

I have a macro that imports all Excel files in a given directory. The name of each Excel file contains information about year that I want to later use. However, in different files, the position of the year is different, e.g. datafile2016.xlsx, datafile2016_upd.xlsx etc.

I want to create a macro variable that extracts the year from each name of the file. My idea is to use nested substring and index functions:

year=substr(&name, x, 4), where x is the position of the "20" substring (first two digits of the year) that I find by using: index(&name, "20") and &name contains whole name of the file.

Since I'm doing this in the macro, I use %sysfunc with each function, which gives me the following code:

%let year=%sysfunc(%substr(&name, %sysfunc(inputn(%sysfunc(index(&name, "20")), 2.)), 4));

However, this produces the following error: ERROR: Function name missing in %SYSFUNC or %QSYSFUNC macro function reference.

What am I doing wrong?

[Astounding · Accepted solution]

%SYSFUNC is only need to permit macro language to utilize a non-macro function.  The first %SYSFUNC should be removed, since %SUBSTR is a macro function.

You may have other issues once this error is gone.  It's likely you need to remove the quotes around "20" since macro language would take that to be a four-character string.

[gamotte]

Hello,

You can extract the year with prxchange as follows :

%let a=datafile2016.xlsx;
%let b=%sysfunc(prxchange(s/.*(20\d{2}).*/$1/,-1,&a.));

[chris2377]

Thanks. I did both and it works now!

[chris2377]

@gamotte

Thanks. That's another approach. I promise to myself that one day I'll learn Pearl regular expressions in SAS 🙂