DATA Step, Macro, Functions and more

Problem with using substring together with %sysfunc

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Problem with using substring together with %sysfunc

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?


Accepted Solutions
Solution
‎10-18-2017 09:57 AM
Super User
Posts: 5,516

Re: Problem with using substring together with %sysfunc

Posted in reply to chris2377

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

View solution in original post


All Replies
Solution
‎10-18-2017 09:57 AM
Super User
Posts: 5,516

Re: Problem with using substring together with %sysfunc

Posted in reply to chris2377

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

Regular Contributor
Posts: 237

Re: Problem with using substring together with %sysfunc

Posted in reply to chris2377

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

Contributor
Posts: 60

Re: Problem with using substring together with %sysfunc

Thanks. I did both and it works now!
Contributor
Posts: 60

Re: Problem with using substring together with %sysfunc

@gamotte

 

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 95 views
  • 0 likes
  • 3 in conversation