%MACRO ARRAY(arraypos, array=, data=, var=, values=, delim=%STR( ), debug=N, numlist=Y); /* last modified 8/4/2006 a.k.a. MACARRAY( ). 72nd col -->| Function: Define one or more Macro Arrays This macro creates one or more macro arrays, and stores in them character values from a SAS dataset or view, or an explicit list of values. A macro array is a list of macro variables sharing the same prefix and a numerical suffix. The suffix numbers run from 1 up to a highest number. The value of this highest number, or the length of the array, is stored in an additional macro variable with the same prefix, plus the letter “N”. The prefix is also referred to as the name of the macro array. For example, "AA1", "AA2", "AA3", etc., plus "AAN". All such variables are declared GLOBAL. Authors: Ted Clay, M.S. tclay@ashlandhome.net (541) 482-6435 David Katz, M.S. www.davidkatzconsulting.com "Please keep, use and pass on the ARRAY and DO_OVER macros with this authorship note. -Thanks " Full documentation with examples appears in SUGI Proceedings, 2006, "Tight Looping With Macro Arrays" by Ted Clay Please send improvements, fixes or comments to Ted Clay. Parameters: ARRAYPOS and ARRAY are equivalent parameters. One or the other, but not both, is required. ARRAYPOS is the only position parameter. = Identifier(s) for the macro array(s) to be defined. DATA = Dataset containing values to load into the array(s). Can be a view, and dataset options such as WHERE= are OK. VAR = Variable(s) containing values to put in list. If multiple array names are specified in ARRAYPOS or ARRAY then the same number of variables must be listed. VALUES = An explicit list of character strings to put in the list or lists. If present, VALUES are used rather than DATA and VAR. VALUES can be a numbered list, eg 1-10, a01-A20, a feature which can be turned of with NUMLIST=N. The VALUES can be used with one or more array names specified in the ARRAYPOS or ARRAY parameters. If more than one array name is given, the values are assigned to each array in turn. For example, if arrays AA and BB are being assigned values, the values are assigned to AA1, BB1, AA2, BB2, AA3, BB3, etc. Therefore the number of values must be a multiple of the number of arrays. DELIM = Character used to separate values in VALUES parameter. Blank is default. DEBUG = N/Y. Default=N. If Y, debugging statements are activated. NUMLIST = Y/N. Default=Y. If Y, VALUES may be a number list. REQUIRED OTHER MACRO: Requires NUMLIST if using numbered lists are used in the VALUES parameter. How the program works. When the VALUES parameter is used, it is parsed into individual words using the scan function. With the DATA parameter, each observation of data to be loaded into one or more macro arrays, _n_ determines the numeric suffix. Each one is declared GLOBAL using "call execute" which is acted upon by the SAS macro processor immediately. (Without this "global" setting, "Call symput" would by default put the new macro variables in the local symbol table, which would not be accessible outside this macro.) Because "call execute" only is handling macro statements, the following statement will normally appear on the SAS log: "NOTE: CALL EXECUTE routine executed successfully, but no SAS statements were generated." History 7/14/05 handle char variable value containing single quote 1/19/06 VALUES can be a a numbered list with dash, e.g. AA1-AA20 4/1/06 simplified process of making variables global. 4/12/06 allow VALUES= when creating more than one macro array. */ %LOCAL prefixes PREFIXN manum _VAR_N iter i J val VAR WHICH MINLENG PREFIX1 PREFIX2 PREFIX3 PREFIX4 PREFIX5 PREFIX6 PREFIX7 PREFIX8 PREFIX9 PREFIX10 PREFIX11 var1 var2 var3 var4 var5 var6 var7 var8 var9 var10 var11 ; %* Get array names from either the keyword or positional parameter; %if &ARRAY= %then %let PREFIXES=&ARRAYPOS; %else %let PREFIXES=&ARRAY; %* Parse the list of macro array names; %do MANUM = 1 %to 999; %let prefix&MANUM=%scan(&prefixes,&MAnum,' '); %if &&prefix&MANUM ne %then %DO; %let PREFIXN=&MAnum; %global &&prefix&MANUM..N; %* initialize length to zero; %let &&prefix&MANUM..N=0; %END; %else %goto out1; %end; %out1: %if &DEBUG=Y %then %put PREFIXN is &PREFIXN; %* Parse the VAR parameter; %let _VAR_N=0; %do MANUM = 1 %to 999; %let _var_&MANUM=%scan(&VAR,&MAnum,' '); %if %str(&&_var_&MANUM) ne %then %let _VAR_N=&MAnum; %else %goto out2; %end; %out2: %IF &PREFIXN=0 %THEN %PUT ERROR: No macro array names are given; %ELSE %IF %LENGTH(%STR(&DATA)) >0 and &_VAR_N=0 %THEN %PUT ERROR: DATA parameter is used but VAR parameter is blank; %ELSE %IF %LENGTH(%STR(&DATA)) >0 and &_VAR_N ne &PREFIXN %THEN %PUT ERROR: The number of variables in the VAR parameter is not equal to the number of arrays; %ELSE %DO; %*------------------------------------------------------; %* CASE 1: VALUES parameter is used %*------------------------------------------------------; %IF %LENGTH(%STR(&VALUES)) >0 %THEN %DO; %IF &NUMLIST=Y %then %DO; %* Check for numbered list of form xxx-xxx and expand it using the NUMLIST macro.; %IF (%INDEX(%quote(&VALUES),-) GT 0) and (%length(%SCAN(%quote(&VALUES),1,-))>0) and (%length(%SCAN(%quote(&VALUES),2,-))>0) and (%length(%SCAN(%quote(&VALUES),3,-))=0) %THEN %LET VALUES=%NUMLIST(&VALUES); %END; %LET MINLENG=99999; %DO J=1 %TO &PREFIXN; %DO ITER=1 %TO 9999; %LET WHICH=%EVAL((&ITER-1)*&PREFIXN +&J); %LET VAL=%SCAN(%STR(&VALUES),&WHICH,%STR(&DELIM)); %IF %QUOTE(&VAL) NE %THEN %DO; %GLOBAL &&&&PREFIX&J..&ITER; %LET &&&&PREFIX&J..&ITER=&VAL; %LET &&&&PREFIX&J..N=&ITER; %END; %ELSE %goto out3; %END; %out3: %IF &&&&&&PREFIX&J..N LT &MINLENG %THEN %LET MINLENG=&&&&&&PREFIX&J..N; %END; %if &PREFIXN >1 %THEN %DO J=1 %TO &PREFIXN; %IF &&&&&&PREFIX&J..N NE &MINLENG %THEN %PUT ERROR: Number of values must be a multiple of the number of arrays; %END; %END; %ELSE %DO; %*------------------------------------------------------; %* CASE 2: DATA and VAR parameters used %*------------------------------------------------------; %* Get values from one or more variables in a dataset or view; data _null_; set &DATA end = lastobs; %DO J=1 %to &PREFIXN; call execute('%GLOBAL '||"&&PREFIX&J.."||left(put(_n_,5.)) ); call symput(compress("&&prefix&J"||left(put(_n_,5.))), trim(left(&&_VAR_&J))); if lastobs then call symput(compress("&&prefix&J"||"N"), trim(left(put(_n_,5.)))); %END; run ; %* Write message to the log; %IF &DEBUG=Y %then %DO J=1 %to &PREFIXN; %PUT &&&&PREFIX&J..N is &&&&&&PREFIX&J..N; %END; %END; %END; %MEND; %MACRO DO_OVER(arraypos, array=, values=, delim=%STR( ), phrase=?, escape=?, between=, macro=, keyword=); /* Last modified: 8/4/2006 72nd col -->| Function: Loop over one or more arrays of macro variables substituting values into a phrase or macro. Authors: Ted Clay, M.S. Clay Software & Statistics tclay@ashlandhome.net (541) 482-6435 David Katz, M.S. www.davidkatzconsulting.com "Please keep, use and pass on the ARRAY and DO_OVER macros with this authorship note. -Thanks " Send any improvements, fixes or comments to Ted Clay. Full documentation with examples appears in "Tight Looping with Macro Arrays".SUGI Proceedings 2006, The keyword parameter was added after the SUGI article was written. REQUIRED OTHER MACROS: NUMLIST -- if using numbered lists in VALUES parameter. ARRAY -- if using macro arrays. Parameters: ARRAYPOS and ARRAY are equivalent parameters. One or the other, but not both, is required. ARRAYPOS is the only position parameter. = Identifier(s) for the macro array(s) to iterate over. Up to 9 array names are allowed. If multiple macro arrays are given, they must have the same length, that is, contain the same number of macro variables. VALUES = An explicit list of character strings to put in an internal macro array, VALUES may be a numbered lists of the form 3-15, 03-15, xx3-xx15, etc. DELIM = Character used to separate values in VALUES parameter. Blank is default. PHRASE = SAS code into which to substitute the values of the macro variable array, replacing the ESCAPE character with each value in turn. The default value of PHRASE is a single which is equivalent to simply the values of the macro variable array. The PHRASE parameter may contain semicolons and extend to multiple lines. NOTE: The text "?_I_", where ? is the ESCAPE character, will be replaced with the value of the index variable values, e.g. 1, 2, 3, etc. Note: Any portion of the PHRASE parameter enclosed in single quotes will not be scanned for the ESCAPE. So, use double quotes within the PHRASE parameter. If more than one array name is given in the ARRAY= or ARRAYPOS parameter, in the PHRASE parameter the ESCAPE character must be immediately followed by the name of one of the macro arrays, using the same case. ESCAPE = A single character to be replaced by macro array values. Default is "?". BETWEEN = code to generate between iterations of the main phrase or macro. The most frequent need for this is to place a comma between elements of an array, so the special argument COMMA is provided for programming convenience. BETWEEN=COMMA is equivalent to BETWEEN=%STR(,). MACRO = Name of an externally-defined macro to execute on each value of the array. It overrides the PHRASE parameter. The parameters of this macro may be a combination of positional or keyword parameters, but keyword parameters on the external macro require the use of the KEYWORD= parameter in DO_OVER. Normally, the macro would have only positional parameters and these would be defined in in the same order and meaning as the macro arrays specified in the ARRAY or ARRAYPOS parameter. For example, to execute the macro DOIT with one positional parameter, separately define %MACRO DOIT(STRING1); %MEND; and give the parameter MACRO=DOIT. The values of AAA1, AAA2, etc. would be substituted for STRING. MACRO=DOIT is equivalent to PHRASE=%NRQUOTE(%DOIT(?)). Note: Within an externally defined macro, the value of the macro index variable would be coded as "&I". This is comparable to "?_I_" within the PHRASE parameter. KEYWORD = Name(s) of keyword parameters used in the definition of the macro refered to in the MACRO= parameter. Optional. This parameter controls how DO_OVER passes macro array values to specific keyword parameters on the macro. This allows DO_OVER to execute a legacy or standard macro. The number of keywords listed in the KEYWORD= parameter must be less than or equal to the number of macro arrays listed in the ARRAYPOS or ARRAY parameter. Macro array names are matched with keywords proceeding from right to left. If there are fewer keywords than macro array names, the remaining array names are passed as positional parameters to the external macro. See Example 6. Rules: Exactly one of ARRAYPOS or ARRAY or VALUES is required. PHRASE or MACRO is required. MACRO overrides PHRASE. ESCAPE is used when PHRASE is used, but is ignored with MACRO. If ARRAY or ARRAYPOS have multiple array names, these must exist and have the same length. If used with externally defined MACRO, the macro must have positional parameters that correspond 1-for-1 with the array names. Alternatively, one can specify keywords which tell DO_OVER the names of keyword parameters of the external macro. Examples: Assume macro array AAA has been created with %ARRAY(AAA,VALUES=x y z) (1) %DO_OVER(AAA) generates: x y z; (2) %DO_OVER(AAA,phrase="?",between=comma) generates: "x","y","z" (3) %DO_OVER(AAA,phrase=if L="?" then ?=1;,between=else) generates: if L="x" then x=1; else if L="y" then y=1; else if L="z" then z=1; (4) %DO_OVER(AAA,macro=DOIT) generates: %DOIT(x) %DOIT(y) %DOIT(z) which assumes %DOIT has a single positional parameter. It is equivalent to: %DO_OVER(AAA,PHRASE=%NRSTR(%DOIT(?))) (5) %DO_OVER(AAA,phrase=?pct=?/tot*100; format ?pct 4.1;) generates: xpct=x/tot*100; format xpct 4.1; ypct=y/tot*100; format ypct 4.1; zpct=z/tot*100; format zpct 4.1; (6) %DO_OVER(aa bb cc,MACRO=doit,KEYWORD=borders columns) is equivalent to %DO_OVER(aa,bb,cc, PHRASE=%NRSTR(%doit(?aa,borders=?bb,columns=?cc))) Either example would generate the following internal do-loop: %DO I=1 %to &AAN; %doit(&&aa&I,borders=&&bb&I,columns=&&cc&I) %END; Because we are giving three macro array names, the macro DOIT must have three parameters. Since there are only two keyword parameters listed, the third parameter is assumed to be positional. Positional parameters always preceed keyword parameters in SAS macro definitions, so the first parameter a positional parameter, which is given the values of first macro array "aa". The second is keyword parameter "borders=" which is fed the values of the second array "bb". The third is a keyword parameter "columns=" which is fed the values of the third array "cc". History 7/15/05 changed %str(&VAL) to %quote(&VAL). 4/1/06 added KEYWORD parameter 4/9/06 declared "_Intrnl" array variables local to remove problems with nesting with VALUES=. 8/4/06 made lines 72 characters or less to be mainframe compatible */ %LOCAL _IntrnlN _Intrnl1 _Intrnl2 _Intrnl3 _Intrnl4 _Intrnl5 _Intrnl6 _Intrnl7 _Intrnl8 _Intrnl9 _Intrnl10 _Intrnl11 _Intrnl12 _Intrnl13 _Intrnl14 _Intrnl15 _Intrnl16 _Intrnl17 _Intrnl18 _Intrnl19 _Intrnl20 _Intrnl21 _Intrnl22 _Intrnl23 _Intrnl24 _Intrnl25 _Intrnl26 _Intrnl27 _Intrnl28 _Intrnl29 _Intrnl30 _Intrnl31 _Intrnl32 _Intrnl33 _Intrnl34 _Intrnl35 _Intrnl36 _Intrnl37 _Intrnl38 _Intrnl39 _Intrnl40 _Intrnl41 _Intrnl42 _Intrnl43 _Intrnl44 _Intrnl45 _Intrnl46 _Intrnl47 _Intrnl48 _Intrnl49 _Intrnl50 _Intrnl51 _Intrnl52 _Intrnl53 _Intrnl54 _Intrnl55 _Intrnl56 _Intrnl57 _Intrnl58 _Intrnl59 _Intrnl60 _Intrnl61 _Intrnl62 _Intrnl63 _Intrnl64 _Intrnl65 _Intrnl66 _Intrnl67 _Intrnl68 _Intrnl69 _Intrnl70 _Intrnl71 _Intrnl72 _Intrnl73 _Intrnl74 _Intrnl75 _Intrnl76 _Intrnl77 _Intrnl78 _Intrnl79 _Intrnl80 _Intrnl81 _Intrnl82 _Intrnl83 _Intrnl84 _Intrnl85 _Intrnl86 _Intrnl87 _Intrnl88 _Intrnl89 _Intrnl90 _Intrnl91 _Intrnl92 _Intrnl93 _Intrnl94 _Intrnl95 _Intrnl96 _Intrnl97 _Intrnl98 _Intrnl99 _Intrnl100 _KEYWRDN _KEYWRD1 _KEYWRD2 _KEYWRD3 _KEYWRD4 _KEYWRD5 _KEYWRD6 _KEYWRD7 _KEYWRD8 _KEYWRD9 _KWRDI ARRAYNOTFOUND CRC CURRPREFIX DELIMI DID FRC I ITER J KWRDINDEX MANUM PREFIXES PREFIXN PREFIX1 PREFIX2 PREFIX3 PREFIX4 PREFIX5 PREFIX6 PREFIX7 PREFIX8 PREFIX9 SOMETHINGTODO TP VAL VALUESGIVEN ; %let somethingtodo=Y; %* Get macro array name(s) from either keyword or positional parameter; %if %str(&arraypos) ne %then %let prefixes=&arraypos; %else %if %str(&array) ne %then %let prefixes=&array; %else %if %quote(&values) ne %then %let prefixes=_Intrnl; %else %let Somethingtodo=N; %if &somethingtodo=Y %then %do; %* Parse the macro array names; %let PREFIXN=0; %do MAnum = 1 %to 999; %let prefix&MANUM=%scan(&prefixes,&MAnum,' '); %if &&prefix&MAnum ne %then %let PREFIXN=&MAnum; %else %goto out1; %end; %out1: %* Parse the keywords; %let _KEYWRDN=0; %do _KWRDI = 1 %to 999; %let _KEYWRD&_KWRDI=%scan(&KEYWORD,&_KWRDI,' '); %if &&_KEYWRD&_KWRDI ne %then %let _KEYWRDN=&_KWRDI; %else %goto out2; %end; %out2: %* Load the VALUES into macro array 1 (only one is permitted); %if %length(%str(&VALUES)) >0 %then %let VALUESGIVEN=1; %else %let VALUESGIVEN=0; %if &VALUESGIVEN=1 %THEN %do; %* Check for numbered list of form xxx-xxx and expand it using NUMLIST macro.; %IF (%INDEX(%STR(&VALUES),-) GT 0) and (%SCAN(%str(&VALUES),2,-) NE ) and (%SCAN(%str(&VALUES),3,-) EQ ) %THEN %LET VALUES=%NUMLIST(&VALUES); %do iter=1 %TO 9999; %let val=%scan(%str(&VALUES),&iter,%str(&DELIM)); %if %quote(&VAL) ne %then %do; %let &PREFIX1&ITER=&VAL; %let &PREFIX1.N=&ITER; %end; %else %goto out3; %end; %out3: %end; %let ArrayNotFound=0; %do j=1 %to &PREFIXN; %*put prefix &j is &&prefix&j; %LET did=%sysfunc(open(sashelp.vmacro (where=(name eq "%upcase(&&PREFIX&J..N)")) )); %LET frc=%sysfunc(fetchobs(&did,1)); %LET crc=%sysfunc(close(&did)); %IF &FRC ne 0 %then %do; %PUT Macro Array with Prefix &&PREFIX&J does not exist; %let ArrayNotFound=1; %end; %end; %if &ArrayNotFound=0 %then %do; %if %quote(%upcase(&BETWEEN))=COMMA %then %let BETWEEN=%str(,); %if %length(%str(&MACRO)) ne 0 %then %do; %let TP = %nrstr(%&MACRO)(; %do J=1 %to &PREFIXN; %let currprefix=&&prefix&J; %IF &J>1 %then %let TP=&TP%str(,); %* Write out macro keywords followed by equals. If fewer keywords than macro arrays, assume parameter is positional and do not write keyword=; %let kwrdindex=%eval(&_KEYWRDN-&PREFIXN+&J); %IF &KWRDINDEX>0 %then %let TP=&TP&&_KEYWRD&KWRDINDEX=; %LET TP=&TP%nrstr(&&)&currprefix%nrstr(&I); %END; %let TP=&TP); %* close parenthesis on external macro call; %end; %else %do; %let TP=&PHRASE; %let TP = %qsysfunc(tranwrd(&TP,&ESCAPE._I_,%nrstr(&I.))); %let TP = %qsysfunc(tranwrd(&TP,&ESCAPE._i_,%nrstr(&I.))); %do J=1 %to &PREFIXN; %let currprefix=&&prefix&J; %LET TP = %qsysfunc(tranwrd(&TP,&ESCAPE&currprefix, %nrstr(&&)&currprefix%nrstr(&I..))); %if &PREFIXN=1 %then %let TP = %qsysfunc(tranwrd(&TP,&ESCAPE, %nrstr(&&)&currprefix%nrstr(&I..))); %end; %end; %* resolve TP (the translated phrase) and perform the looping; %do I=1 %to &&&prefix1.n; %if &I>1 and %length(%str(&between))>0 %then &BETWEEN; %unquote(&TP) %end; %end; %end; %MEND; %MACRO NUMLIST(listwithdash); /* 72nd col -->| Function: Generate the elements of a numbered list. For example, AA1-AA3 generates AA1 AA2 AA3 No prefix is necessary -- 1-3 generates 1 2 3. Author: Ted Clay, M.S. Clay Software & Statistics tclay@ashlandhome.net (541) 482-6435 "Please keep, use and share this macro with this authorship note." Parameter: ListWithDash -- text string containing a dash. The text before the dash, and the text after the dash, usually begin with a the same character string, called the stem. (The stem could be blank or null, as is the case of number-dash-number.) After the common stem must be two numbers. The first number must be less than the second number. Leading zeroes on the numbers are preserved. How it works: The listwithdash is parsed into _before and _after. _before and _after are compared equal up to the length of the "stem". What is after the "stem" is assigned to _From and _to, which must convert to numerics. Finally, the macro generates stem followed by all the numbers from _from through _to Examples: %numlist(3-6) generates 3 4 5 6. %numlist(1993-2004) generates 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004. %numlist(var8-var12) generates var8 var9 var10 var11 var12. %numlist(var08-var12) generates var08 var09 var10 var11 var12. */ %local _before _after _length1 _length2 minlength samepos _pos _from _to i; %let _before = %scan(%quote(&listwithdash),1,-); %let _after = %scan(%quote(&listwithdash),2,-); %let _length1 = %length(%quote(&_before)); %let _length2 = %length(%quote(&_after)); %let minlength=&_length1; %if &_length2 < &minlength %then %let minlength=&_length2; %*put before is &_before; %*put after is &_after; %*put minlength is &minlength; %* Stemlength should be just before the first number or the first unequal character; %let stemlength=0; %let foundit=0; %do _pos = 1 %to &minlength; %LET CHAR1=%upcase(%substr(%quote(&_before),&_pos,1)); %LET CHAR2=%upcase(%substr(%quote(&_after ),&_pos,1)); %if %index(1234567890,%QUOTE(&CHAR1)) GE 1 %then %let ISANUMBER=Y; %else %let isanumber=N; %if &foundit=0 and ( &isanumber=Y OR %quote(&CHAR1) NE %QUOTE(&CHAR2) ) %then %do; %let stemlength=%EVAL(&_pos-1); %*put after assignment stemlength is &stemlength; %let foundit=1; %end; %end; %if &stemlength=0 %then %let stem=; %else %let stem = %substr(&_before,1,&stemlength); %let _from=%substr(&_before,%eval(&stemlength+1)); %let _to =%substr(&_after, %eval(&stemlength+1)); %IF %verify(&_FROM,1234567890)>0 or %verify(&_TO ,1234567890)>0 %then %PUT ERROR in NUMLIST macro: Alphabetic prefixes are different; %else %if &_from <= &_to %then %do _III_=&_from %to &_to; %LET _XXX_=&_iii_; %do _JJJ_=%length(&_iii_) %to %eval(%length(&_from)-1); %let _XXX_=0&_XXX_; %end; %TRIM(&stem&_XXX_) %end; %else %PUT ERROR in NUMLIST macro: From "&_from" not <= To "&_to"; %MEND;