topic Re: Macro quoting function to mask (%) at SAS execution time in SAS Programming

Macro quoting function to mask (%) at SAS execution time

trb1 — Fri, 05 Jan 2018 17:50:29 GMT

With SAS 94M4, I have a macro parameter value that contains the trigger for the macro processor (%). I need this parameter value to be triggered at macro compile time, however at SAS execution time, I need a quoting function that does not attempt to resolve what appears as a macro reference.

At least that is how I understand the timing of things here. The SAS log is below, followed by the program. Any insights are appreciated along with how masking/unmasking and compile/execution timing for Macro quoting. I found Ian Whitlock's paper to be a great deal of help at:

http://www2.sas.com/proceedings/sugi28/011-28.pdf

 options mtrace mlogic mprint;
18   %let ds = WORK.mm_var;
19
20   %macro getargument(paramname=);
21      %* Get count for key values;
22      %* Get key values;
23
24      proc sql noprint;
25         select count(key) into :key_count
26         from &ds.;
27
28         select key into :key_values separated by "+"
29         from &ds.;
30      quit;
31
32      %put ===> key values are &key_values;
33      %* Test to see of the paramvalue is valid;
34
35      %if %sysfunc(indexw(&key_values, "&paramname", "+")) = 0 %then %do;
36         %put ===> The parameter "&paramname" is not valid.;
37         %put ===> Exiting the Macro.;
38         %goto exit;
39      %end;
40
41      %* Test to see if the paramname value is <blank>;
42
43      %if %sysevalf(%superq(paramname)=,boolean) %then %do;
44         %put ===>  The paramname value is "<blank>" existing MACRO;
45         %goto exit;
46      %end;
47
48      %* Test to see if the paramname value contains <blank> characters;
49      %* If true, replace <blank> with underscore (_);
50
51      %if %sysfunc(find(%bquote(&paramname),%str( ))) > 0 %then %do;
52
53         %let paramname1=%sysfunc(translate(%bquote(&paramname),%str(_),%str( )));
54            proc sql noprint;
55               select value into :val&paramname1
56               from &ds
57               where soundex(key) = soundex("&paramname");
58            quit;
59
60         %put ========================<>==========================;
61         %put ===> The Key is: &paramname and value is %unquote(%str(&&val&paramname1)) <===;
62
63         %goto exit;
64      %end;
65
66      proc sql noprint;
67          select value into :val
68          from &ds
69          where soundex(key) = soundex("&paramname");
70      quit;
71
72         %put ========================<>==========================;
73         %put ===> The Key is: &paramname and value is %trim(&val) <===;
74
75   %exit:
76   %mend;
77   run;
78   %getargument(paramname=trips up macro);
MLOGIC(GETARGUMENT):  Beginning execution.
MLOGIC(GETARGUMENT):  Parameter PARAMNAME has value trips up macro
MPRINT(GETARGUMENT):   proc sql noprint;
MPRINT(GETARGUMENT):   select count(key) into :key_count from WORK.mm_var;
MPRINT(GETARGUMENT):   select key into :key_values separated by "+" from WORK.mm_var;
MPRINT(GETARGUMENT):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


MLOGIC(GETARGUMENT):  %PUT ===> key values are &key_values
===> key values are kpi+EVAL1+benchmark+workdir+inputfile1+input file forsas+trips up macro
MLOGIC(GETARGUMENT):  %IF condition %sysfunc(indexw(&key_values, "&paramname", "+")) = 0 is FALSE
MLOGIC(GETARGUMENT):  %IF condition %sysevalf(%superq(paramname)=,boolean) is FALSE
MLOGIC(GETARGUMENT):  %IF condition %sysfunc(find(%bquote(&paramname),_ _)) > 0 is TRUE
MLOGIC(GETARGUMENT):  %LET (variable name is PARAMNAME1)
MPRINT(GETARGUMENT):   proc sql noprint;
MPRINT(GETARGUMENT):   select value into :valtrips_up_macro from WORK.mm_var where soundex(key) =
soundex("trips up macro");
MPRINT(GETARGUMENT):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


MLOGIC(GETARGUMENT):  %PUT ========================<>==========================
========================<>==========================
MLOGIC(GETARGUMENT):  %PUT ===> The Key is: &paramname and value is %unquote(_&&val&paramname1_)
      <===
WARNING: Apparent invocation of macro _SIGN not resolved.
WARNING: Apparent invocation of macro _SIGN not resolved.
===> The Key is: trips up macro and value is /home/sas/file_with_%_sign.sas

                                                                                          <===
MLOGIC(GETARGUMENT):  %GOTO EXIT (label resolves to EXIT).
MLOGIC(GETARGUMENT):  Ending execution.
79
80   run;

The program is:

options mprint mlogic mtrace;
DATA mm_var;
length key   $ 128
       value $ 1024;
INPUT KEY $ & @21 VALUE $;
DATALINES4;
kpi                 80
EVAL1               90
benchmark           70
workdir             /tmp/x
inputfile1          /tmp/x/input/file1.csv
input file forsas   /MM/RTemp/tmp.Fi8jheBOAu/input/5a4e0eaad8e0c3b8d782609f
trips up macro      /home/sas/file_with_%_sign.sas
;;;;
RUN;
proc print;run;
options mtrace mlogic mprint;
%let ds = WORK.mm_var;

%macro getargument(paramname=);
   %* Get count for key values;
   %* Get key values;

   proc sql noprint;
      select count(key) into :key_count
      from &ds.;

      select key into :key_values separated by "+"
      from &ds.;
   quit;

   %put ===> key values are &key_values;
   %* Test to see if the paramvalue is valid;

   %if %sysfunc(indexw(&key_values, "&paramname", "+")) = 0 %then %do;
      %put ===> The parameter "&paramname" is not valid.;
	  %put ===> Exiting the Macro.;
	  %goto exit;
   %end;
 
   %* Test to see if the paramname value contains <blank> characters;
   %* If true, replace <blank> with underscore (_);
    
   %if %sysfunc(find(%bquote(&paramname),%str( ))) > 0 %then %do;
    
      %let paramname1=%sysfunc(translate(%bquote(&paramname),%str(_),%str( ))); 
	     proc sql noprint;
	        select value into :val&paramname1
	        from &ds
	        where soundex(key) = soundex("&paramname");
	     quit;

	  %put ========================<>==========================;
      %put ===> The Key is: &paramname and value is %unquote(%str(&&val&paramname1)) <===;

	  %goto exit;
   %end;   

   proc sql noprint;
	   select value into :val
	   from &ds
	   where soundex(key) = soundex("&paramname");
   quit;

	  %put ========================<>==========================;
      %put ===> The Key is: &paramname and value is %trim(&val) <===;      

%exit:
%mend;
run;
%getargument(paramname=trips up macro);

run;

Re: Macro quoting function to mask (%) at SAS execution time

novinosrin — Fri, 05 Jan 2018 18:39:07 GMT

would %nrstr help instead of %str here:

%put ===> The Key is: &paramname and value is %unquote(%str(&&val&paramname1)) <===;/*existing version*/

%put ===> The Key is: &paramname and value is %unquote(%nrstr(&&val&paramname1)) <===;/*my opinion*/

Re: Macro quoting function to mask (%) at SAS execution time

snoopy369 — Fri, 05 Jan 2018 18:43:33 GMT

I think you are mostly running into trouble in the %put here; that's because you %unquote, which is inevitably going to cause an issue I think. Unquoting attempts to resolve the macro variable, which is a problem here clearly.

I prefer %superq for these scenarios:

   %if %sysfunc(find(%superq(paramname),%str( ))) > 0 %then %do;
    
      %let paramname1=%sysfunc(translate(%superq(paramname),%str(_),%str( ))); 
	     proc sql noprint;
	        select value into :val&paramname1 trimmed
	        from &ds
	        where soundex(key) = soundex("&paramname");
	     quit;

	  %put ========================<>==========================;
      %put ===> The Key is: &paramname and value is  %superq(val&paramname1) <===;

	  %goto exit;
   %end;

No unquoting needed, I don't think. I add TRIMMED option to PROC SQL so you don't get that big giant pile of excess spaces.

Re: Macro quoting function to mask (%) at SAS execution time

snoopy369 — Fri, 05 Jan 2018 18:42:51 GMT

%UNQUOTE will still attempt to resolve it, unfortunately.

Re: Macro quoting function to mask (%) at SAS execution time

novinosrin — Fri, 05 Jan 2018 18:43:24 GMT

hmm true. Thank you!

Re: Macro quoting function to mask (%) at SAS execution time

Tom — Fri, 05 Jan 2018 21:02:59 GMT

Your macro is working way to hard. No need to query the data twice to get the count, SQL will count for you. No need for SOUNDEX() since you already.eliminated user input that didn't match the key names. No need to make different macro variable names depending on whether or not key name has a space in it.

%macro getargument(paramname=);
%local key_count key_values mvar ;
proc sql noprint;
  select upcase(key)
    into :key_values separated by "+"
    from &ds.
  ;
%let key_count=&sqlobs;
quit;

%put ===> &=key_count &=key_values ;

%* Test to see if the paramvalue is valid;
%if %sysfunc(indexw(&key_values,%qupcase(&paramname),+)) = 0 %then %do;
  %put ===> The parameter "&paramname" is not valid.;
  %put ===> Exiting the Macro.;
  %goto exit;
%end;
 
%* Make Name of macro variable to hold value of parameter ;
%* Include paramname but replace <blank> with underscore (_);
%let mvar=val%sysfunc(translate(%superq(paramname),%str(_),%str( ))); 
%local &mvar ;

%* Get value ;
proc sql noprint;
  select value into :&mvar trimmed
    from &ds
    where upcase(key) = "%upcase(&paramname)"
  ;
quit;

%put ========================<>==========================;
%put ===> The Key is: "&paramname" and value is %qsysfunc(quote(%superq(&mvar))) <===;

%exit:
%mend;

Testing results

786   %getargument(paramname=trips up macro);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


===> KEY_COUNT=7 KEY_VALUES=KPI+EVAL1+BENCHMARK+WORKDIR+INPUTFILE1+INPUT FILE FORSAS+TRIPS UP MACRO
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


========================<>==========================
===> The Key is: "trips up macro" and value is "/home/sas/file_with_%_sign.sas" <===
787   %getargument(paramname=Notfound);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


===> KEY_COUNT=7 KEY_VALUES=KPI+EVAL1+BENCHMARK+WORKDIR+INPUTFILE1+INPUT FILE FORSAS+TRIPS UP MACRO
===> The parameter "Notfound" is not valid.
===> Exiting the Macro.

Re: Macro quoting function to mask (%) at SAS execution time

rbetancourt — Fri, 05 Jan 2018 21:40:30 GMT

Hired! When can you start

This is the best all around solution since it handles the problem with parameter values containing both Macro triggers (&, %). You are also correct that I am applying too many tests for conditions--where yours is the more generalized approach.

Thanks to all!