topic Re: IFC Evaluation in SAS/MACRO of a String with Parentheses in SAS Programming

IFC Evaluation in SAS/MACRO of a String with Parentheses

Junyong — Mon, 06 Sep 2021 18:40:01 GMT

The following %example is supposed to return parenthesis no if there is no parenthesis in the argument and parentheses yes otherwise.

%macro example(argument);

%put %sysfunc(ifc(&argument=string without parenthesis,parenthesis no,parentheses yes));

%mend;

/*this works fine*/
%example(string without parenthesis)

/*this does not work*/
%example(string with (parentheses))

I also tried %example(%str(string with (parentheses))) for the last one, but it didn't work as well. How should I give the argument of parentheses correctly?

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

Junyong — Mon, 06 Sep 2021 18:59:08 GMT

It seems adding quotes works as follows.

%macro example(argument);

%put %sysfunc(ifc("&argument"="string without parenthesis",parenthesis no,parentheses yes));

%mend;

/*this works fine*/
%example(string without parenthesis)

/*this works as well*/
%example(string with (parentheses))

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

ChrisNZ — Mon, 06 Sep 2021 22:36:09 GMT

Yes that's a good workaround.

Be mindful that your code will fail if the string includes a double quote.

This is slightly better:

%macro example(argument);

%put %sysfunc(ifc( %sysfunc(quote(%superq(argument)))="string without parenthesis",parenthesis no,parentheses yes));

%mend;

%example(string without parenthesis)

%example(string %str(%")with (parentheses))

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

Quentin — Tue, 07 Sep 2021 00:11:01 GMT

Interesting. I would have hoped:

%example(string with %str(%(parentheses%)))

would work, but it doesn't:

7    %example(string with %str(%(parentheses%)))
ERROR: Required operator not found in expression: string with (parentheses)=string without parenthesis
ERROR: Argument 1 to function IFC referenced by the %SYSFUNC or %QSYSFUNC macro function is not a
       number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of
       %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.

Seems like no amount of macro quoting will keep ifc from unquoting it and seeing the parentheses as an instruction to do math. That's unfortunate.

With an old fashioned %IF statement, macro quoting is sufficient:

1    %macro example(argument);
2    %local return ;
3    %if &argument=string without parenthesis %then %let return=parenthesis no ;
4    %else %let return=parentheses yes;
5    %put &return ;
6    %mend;
7
8
9    /*this does not work*/
10   %example(string with (parentheses))
ERROR: Required operator not found in expression: &argument=string without parenthesis
ERROR: The macro EXAMPLE will stop executing.
11
12   /*this does work*/
13   %example(string with %str(%(parentheses%)))
parentheses yes

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

Junyong — Tue, 07 Sep 2021 16:18:34 GMT

May I ask one more? Sorry to bother you. The following code downloads an Excel file of multiple sheets and imports the sheets.

filename i temp;

proc http url="https://images.aqr.com/-/media/AQR/Documents/Insights/Data-Sets/Quality-Minus-Junk-Factors-Monthly.xlsx" out=i;
run;

%macro i(i);

proc import file=i dbms=xlsx replace out=i;
	range="&i$A19:%sysfunc(ifc(""&i""=""RF"",B,AD))0";
run;

data i;
	i=put("&i",$11.);
	set i;
	where date>.;
run;

data j;
	set %sysfunc(ifc("&i"="MKT",i,j i));
run;

%mend;

proc iml;
	if exist("j") then call delete("j");
quit;

%i(MKT)
%i(QMJ Factors)
%i(ME(t-1))
%i(RF)

The names of the sheets are, for example, MKT, QMJ Factors, ME(t-1), and RF. Because RF has only two columns, I adjusted range for proc import using %sysfunc(ifc(""&i""=""RF"",B,AD))—accordingly, I put double quotes "" inside single quotes ". Unfortunately, this adjustment caused an error for ME(t-1). Can't I use the double quotes with the parentheses together?

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

Tom — Tue, 07 Sep 2021 17:55:16 GMT

I don't understand why you are using two double quote characters? You only need to double up the quote characters when they are part of the value of the string being quoted.

Personally I would avoid using IFC(), it does not really add any extra functionality over normal IF/THEN/ELSE coding.

%macro i(i);
%local range dslist;

%if %sysfunc(quote(&i))="RF" %then %let range=B;
%else %let range=AD;
%let range=&i$A19:&range.0;

%if %sysfunc(quote(&i))="MKT" %then %let dslist=j i; 
%else %let dslist=i;

proc import file=i dbms=xlsx replace out=i;
  range="&range" ;
run;

data i;
  length i $11;
  i=symget('i');
  set i;
  where date>.;
run;

data j;
  set &dslist ;
run;

%mend;

%if %sysfunc(exist(j)) %then %do;
proc delete data=j;
run;
%end ;

options mprint;
%i(MKT)
%i(QMJ Factors)
%i(ME(t-1))
%i(RF)

PS: You are using the name i for many different things. The fileref to the downloaded spreadsheet. The parameter to the macro. The dataset name to create. The variable to create in the dataset. It would make the code clearer to using different names for each of those things.

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

ChrisNZ — Tue, 07 Sep 2021 22:54:02 GMT

Like @Tom I don't see why you add more quotes. The inner quotes are used before any code is compiled and disappear after the macro processor has parsed the text.

Notes about your code:

- Do you have a fetish with letter i ? 🙂 Use meaningful names to make it more legible

- i,j,k are normaly used for integers (this goes back FORTRAN requirements). Following norms helps legibility too.

- You build table J only to delete it as end?

- Deleting a table is normally done using proc dataset or proc delete

- Your code could be something like this:

%macro import(sheet);

proc import file=TMP dbms=xlsx replace out=IMPORTED ;
  range="&sheet$A19:%sysfunc(ifc("&sheet"="RF",B,AD))0";
run;

data TABLEJ;
  length SRC $11;
  set %sysfunc(ifc("&sheet"="MKT", ,TABLEJ )) IMPORTED ;
  where DATE > . ;
  if src=' ' then src="&sheet";
run;

%mend;

proc delete data=IMPORTED; run;

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

Junyong — Tue, 07 Sep 2021 23:01:34 GMT

I used the double-double quotes because I wanted double-quoted strings "&i" and "RF" inside the already double-quoted string passed for range.

proc import file=i dbms=xlsx replace out=i;
	range="&i$A19:%sysfunc(ifc(""&i""=""RF"",B,AD))0";
run;

I use ifc rather than %if/%then/%else to shorten—of course, the following code would be more explicit and cause no error.

proc import file=i dbms=xlsx replace out=i;
	%if &i=RF %then %do;
		range="&i$A19:B0";
	%end;
	%else %do;
		range="&i$A19:AD0";
	%end;
run;

However, I wondered why the macro string ME(t-1) is not resolved properly inside the double-double quotes, unlike inside the single-double quotes %sysfunc(ifc("&i"="MKT",i,j i));.

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

ChrisNZ — Tue, 07 Sep 2021 23:47:46 GMT

Not too sure why you say the resolution doesn't work for you. It works here:

 %let i=1; %put "%sysfunc(ifc(""&i""=""2"" ,a,b))"; %put "%sysfunc(ifc(""&i""=""1"" ,a,b))";

In any case, the double quotes are unneeded.

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

Quentin — Wed, 08 Sep 2021 00:01:07 GMT

I believe the Junyong is suprised that:

%let i=me(T-1);
%put "%sysfunc(ifc(""&i""=""2"" ,a,b))"; 
%put "%sysfunc(ifc(""&i""=""1"" ,a,b))";

Will error.

I think adding quote marks here is a bit of a hack, as an attempt to hide ifc from seeing the parentheses as a trigger to do math.

My guess is when you use ""&i"", the double-double-quotes lose their ability to hide the parentheses. Essentially "" loses its meaning as a quote mark to the tokenizer/macro processor.

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

Junyong — Wed, 08 Sep 2021 00:06:19 GMT

My initial understanding was that passing the five pieces—"&sheet$A19:%sysfunc(ifc(", &sheet, "=", RF, and ",B,AD))0"—for the range is impossible as it demands one single- or double-quoted string, but it seems it works with the following note 49-169.

1    filename tmp temp;
2
3    proc http
3  ! url="https://images.aqr.com/-/media/AQR/Documents/Insights/Data-Sets/Qualit
3  ! y-Minus-Junk-Factors-Monthly.xlsx" out=tmp;
4    run;

NOTE: PROCEDURE HTTP used (Total process time):
      real time           0.95 seconds
      cpu time            0.03 seconds

NOTE: 200 OK

5
6    %macro import(sheet);
7
8    proc import file=TMP dbms=xlsx replace out=IMPORTED ;
9      range="&sheet$A19:%sysfunc(ifc("&sheet"="RF",B,AD))0";
                                             ---
                                             49
NOTE 49-169: The meaning of an identifier after a quoted string might change in
             a future SAS release.  Inserting white space between a quoted
             string and the succeeding identifier is recommended.
10   run;
11
12   data TABLEJ;
13     length SRC $11;
14     set %sysfunc(ifc("&sheet"="MKT", ,TABLEJ )) IMPORTED ;
15     where DATE > . ;
16     if src=' ' then src="&sheet";
17   run;
18
19   %mend;
20
21   proc delete data=IMPORTED; run;

WARNING: File WORK.IMPORTED.DATA does not exist.
NOTE: PROCEDURE DELETE used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


22
23   %import(MKT)

NOTE:    Variable Name Change.  Global Ex USA -> Global_Ex_USA

NOTE:    Variable Name Change.  North America -> North_America

NOTE: The import data set has 1869 observations and 30 variables.
NOTE: WORK.IMPORTED data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.48 seconds
      cpu time            0.47 seconds



NOTE: There were 1141 observations read from the data set WORK.IMPORTED.
      WHERE DATE>.;
NOTE: The data set WORK.TABLEJ has 1141 observations and 31 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


24   %import(QMJ Factors)

NOTE:    Variable Name Change.  Global Ex USA -> Global_Ex_USA

NOTE:    Variable Name Change.  North America -> North_America

NOTE: The import data set has 1869 observations and 30 variables.
NOTE: WORK.IMPORTED data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.48 seconds
      cpu time            0.48 seconds



NOTE: There were 1141 observations read from the data set WORK.TABLEJ.
      WHERE DATE>.;
NOTE: There were 769 observations read from the data set WORK.IMPORTED.
      WHERE DATE>.;
NOTE: The data set WORK.TABLEJ has 1910 observations and 31 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


25   %import(ME(t-1))

NOTE:    Variable Name Change.  Global Ex USA -> Global_Ex_USA

NOTE:    Variable Name Change.  North America -> North_America

NOTE: The import data set has 1969 observations and 30 variables.
NOTE: WORK.IMPORTED data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.31 seconds
      cpu time            0.31 seconds



NOTE: There were 1910 observations read from the data set WORK.TABLEJ.
      WHERE DATE>.;
NOTE: There were 1141 observations read from the data set WORK.IMPORTED.
      WHERE DATE>.;
NOTE: The data set WORK.TABLEJ has 3051 observations and 31 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


26   %import(RF)

NOTE:    Variable Name Change.  Risk Free Rate -> Risk_Free_Rate

NOTE: The import data set has 1830 observations and 2 variables.
NOTE: WORK.IMPORTED data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.39 seconds
      cpu time            0.39 seconds



NOTE: There were 3051 observations read from the data set WORK.TABLEJ.
      WHERE DATE>.;
NOTE: There were 1141 observations read from the data set WORK.IMPORTED.
      WHERE DATE>.;
NOTE: The data set WORK.TABLEJ has 4192 observations and 32 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

Tom — Wed, 08 Sep 2021 02:00:46 GMT

So your short-cut is not only making your code harder to read and harder to understand it is also making it harder to get it to work.

Introducing %SYSFUNC() also opens you up to idiosyncrasies of that tool.

Plus you are moving the evaluation of the input parameter away from the top of the macro.

You are creating lines of code that are a mixture of macro code and SAS code. And you have to worry about extra quotes because you are inserting the macro logic into the middle a string variable instead of just setting the strings value and then using it where needed.

If you want more brevity try:

proc import file=i dbms=xlsx replace out=i;
  range=
%if "&i"="RF" %then "&i$A19:B0";
%else "&i$A19:AD0";
  ;
run;

Now the lines of macro logic are more clearly demarked from the lines of SAS code you are using the macro to create.