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?
%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.
%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.
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.));
Thanks. That's another approach. I promise to myself that one day I'll learn Pearl regular expressions in SAS 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.