topic Re: Problem with using substring together with %sysfunc in SAS Programming

Problem with using substring together with %sysfunc

chris2377 — Wed, 18 Oct 2017 13:41:49 GMT

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?

Re: Problem with using substring together with %sysfunc

Astounding — Wed, 18 Oct 2017 13:52:00 GMT

%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.

Re: Problem with using substring together with %sysfunc

gamotte — Wed, 18 Oct 2017 13:56:06 GMT

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.));

Re: Problem with using substring together with %sysfunc

chris2377 — Wed, 18 Oct 2017 13:57:46 GMT

Thanks. I did both and it works now!

Re: Problem with using substring together with %sysfunc

chris2377 — Wed, 18 Oct 2017 14:19:27 GMT

@gamotte

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