* Model_Do_Over.txt Example;

* Sunil Gupta;
* Gupta Programming;
* July 20, 2010;

libname mazlib 'M:\## Personal Folders\Sunil';

************************************************************************************************************************;
* Four Key Benefits;
************************************************************************************************************************;
* 1. Extended use of lists for repeated operations beyond traditional macro programming;
* 2. More concise code that is easier to write;
* 3. Lists are global in scope so no need to redefine array lists;
* 4. Rubust to process most any special characters "", (, ), /, ;, ., = and keywords;


************************************************************************************************************************;
* DO-OVER Usage Examples:
************************************************************************************************************************;

************************************************************************************************************************;
* A. Contents of list of values - all characters (can include imbedded blanks), all numeric, or lists of macro parameters;
************************************************************************************************************************;

* 1. Option to change default of blank to separate values;

* 2. Option to change default of '?' substitution text when applying macros;

************************************************************************************************************************;
* B. Three options to build lists;
************************************************************************************************************************;

* 1. Use %do_over values parameter for small number of values or numeric range with start and end values;
*    %array(mvarnm, values= D_M1-D_M5);

* 2. Use %array macro for average number of values or numeric range with start and end values;

* 3. Use %array macro with data set to permanently store and access large number of values or manage multiple lists with where data set option;

* May not be correct since arrays are global - Note that if %array macros are used then arrays must be recreated in subsequent data steps;

************************************************************************************************************************;
* C. Determine type of usage from 10 categories;
************************************************************************************************************************;

* 1. Bulk list before keywords such as rename, length, or set or variable assignment;
*     Outcome= rename A=PRE_A B=PRE_B;

*     Outcome= length PRE_A PRE_B PRE_C 8;
*     Code= length %DO_OVER(VALUES=A B C, PHRASE=pre_?) 8;

*     Outcome= A=0; 
*              B=0;
*     Code= %DO_OVER(xvarnm, PHRASE=?=0;);

* 2. Quoted list in SAS statement such as if with " ";

*     Outcome= if letter in ("A" "B");
*     Code= if letter in (%DO_OVER(VALUES=A B, PHRASE="?"));

* 3. Tracking merged data sets before keyword and with (in=);

*     Outcome= merge A(IN=INA) B(IN=INB);
*     Code= merge %DO_OVER(VALUES=A B, PHRASE=?(IN?)));

* 4. Complete statements within a SAS Procedure such as Proc Freq;

*     Outcome=;
*     proc freq; 
*      TABLE A / OUT=FREQSA;
*      TABLE B / OUT=FREQSB;

*     Code=;
*     proc freq; 
*      %DO_OVER(VALUES=A B, PHRASE=TABLE ? / OUT=FREQS?;);

* 5. Multiple statements without the need to create traditional macros;

*     Outcome=;
*     TITLE "PRINTOUT OF A";
*     PROC PRINT DATA = A;
*     TITLE "PRINTOUT OF B";
*     PROC PRINT DATA = B;

*     Code=;
*     %DO_OVER(VALUES=A B, PHRASE=TITLE "PRINTOUT OF ?";PROC PRINT DATA = ?;);

* 6. Macro Language for dynamic lists instead of hard coding values;

*     Outcome=;
*     %let new=PCT_A PCT_B;

*     Code=;
*     %let old=A B;
*     %let new=%do_over(values=&old, phrase=pct_?);

* 7. Inserting text such as else or commas between values;

*     Outcome=;
*     IF LETTER="A" THEN A=1;
*     ELSE IF LETTER="B" THEN B=1;

*     Code= %DO_OVER(VALUES=A B, PHRASE=if letter="?" then ?=1;, between=else);

*     %DO_OVER(dtvarnm mvarnm, PHRASE=if monthdt=?dtvarnm then ?mvarnm=1;, between=else);

* 8. Array index number;

*     Outcome= merge A(IN=IN1) B(IN=IN2);

*     Code= %DO_OVER(VALUES=A B, PHRASE=merge ?(IN=IN?_I_));

* 9. Processing two or more lists;

*     Outcome= rename A=C B=D;

*     Code=;
*     %ARRAY(AB, A B);
*     %ARRAY(CD, C D);
*     rename %DO_OVER(AB CD, PHRASE=?AB=?CD);

* 10.Calling macros with multiple lists of macro parameters;

*     Outcome=;
*     %DOIT(A, C);
*     %DOIT(B, D);

*     Code=;
*     %ARRAY(AB, A B);
*     %ARRAY(CD, C D);
*     %DO_OVER(AB CD, MACRO=DOIT);

************************************************************************************************************************;
* D. Debug usage of macros;
************************************************************************************************************************;

* 1. Set debug=Y to activate debugging statements;

* SAS Autocall;
 filename mspmac "M:\## Best Practices\SAS\Production SAS Code\Utility Macros";
 options mautosource sasautos=(sasautos mspmac) mprint;

* Alternative to include files;
* Need to delete UnCode file with each run;
 *filename mprint 'M:\## Personal Folders\Sunil\Model_Do_Over_UnCode.sas';
*filename mspmac 'M:\## Best Practices\SAS\Production SAS Code\Utility Macros';
%*include mspmac('array.sas');
%*include mspmac('do_over.sas');
%*include mspmac('numlist.sas');
*options mlogic mprint mfile symbolgen;


************************************************************************************************************************;

* Create temp macro to store code in mprint file;
%macro mrun;

dm log 'clear'; dm output 'clear';

* Array model;
* Called outside a data step;
%macro skip1;
%*ARRAY(arraypos,      /* array name to be used by do_array macro, may not appear in mprint file */
        array=,        /* same as arraypos except for being keyword paramater */  
        data=,         /* data set name containing list of values in the array, accepts where data set option */
        var=,          /* variable name containing list of values in the array */
                       /* multiple arrays are accepted if number of arrays and varaibles match */
        values=,       /* list of values in the array */
        delim=%STR( ), /* character used to separate values, default is blank */
        debug=N,       /* If Y, debugging statements are activated. */
        numlist=Y);    /* If Y, VALUES may be a number list, default is Y. */
%mend skip1;

/*
  Do Over 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(?)))
*/

* Do_Over Model;
* Called within a data step;
%macro skip2;
%DO_OVER(arraypos,      /* existing array name created within the same data step */
                        /* can accept multiple arrays for processing 2 sets of lists */
         array=,        /* same as arraypos except for being keyword parameter */
         values=,       /* alternative to using array if list is small or can use start and end numbers in range */
                        /* embedded blanks can be accepted with another deliminator such as '/' */
         delim=%STR( ), /* character used to separate values, default is blank */
         phrase=?,      /* exact text to create, accepts most any character including "", (, ), /, ;, ., = and keywords */
                        /* can also apply the array index value _i_ for incrementing numbers */
		                /* can also accept multiple arrays for processing 2 or more sets of lists */
         escape=?,      /* character substitution, default is "?" */
         between=,      /* text to insert between statements such as 'else' in if then else statements */
                        /* can also be used to insert text such as ',' between the list of values */
         macro=,        /* call macro name */ 
         keyword=       /* used with macro parameter when specifying more than one parameter */
  );                    /* not required if applied within SAS statement */
%mend skip2;

************************************************************************************************************************;

* Example A: Create Put statement containing three values;
* Result: put "This is a test: pre_A     pre_B     pre_C " ;
Data _null_;
 put "This is a test: 
 %DO_OVER(VALUES=A B C, PHRASE=pre_?)
 " ;
run;

************************************************************************************************************************;

* Example B: Create length and individual statements to create three variables;
* length pre_A pre_B pre_C 8;
* pre_A=3;
* pre_B=3;
* pre_C=3;

* Option 1 - %do_over values parameter - best for one time use short lists;

Data valueds;

 length %DO_OVER(VALUES=A B C, PHRASE=pre_?) 8;

 %DO_OVER(VALUES=A B C, PHRASE=pre_?=3;);
run;
proc contents data=valueds; run;
proc print data=valueds; run;


* Option 2 - %array macro - best for repeated use of short lists;

* List of values;
* Array macro call is global so array is avaialble in any subsequent data step;
%array(varnm, values=A B C, debug=Y);

Data arrayonly;

 * Assign length statement;
 length %DO_OVER(varnm, PHRASE=pre_?) 8;

 * Assign value of 3 to each variable;
 %DO_OVER(varnm, PHRASE=pre_?=3;)

run;
proc contents data=arrayonly; run;
proc print data=arrayonly; 
 var %DO_OVER(varnm, PHRASE=pre_?) ;
run;

* Option 3 - Data set with %array macro - best for shared or permanent access to short or long lists;

* Create data set containing values in array;

data araylst;
 length varnm2 $15;
 varnm2 = 'A'; output;
 varnm2 = 'B'; output;
 varnm2 = 'C'; output;
run;
proc print data=araylst; run;

 * Values from data set - must be called outside of data step to work with data set list;
 %array(array=varnm3, data=araylst, var=varnm2, debug=Y);

Data arrayds;

 * Assign length statement;
 length %DO_OVER(varnm3, PHRASE=pre_?) 8;

 * Assign value of 3 to each variable;
 %DO_OVER(varnm3, PHRASE=pre_?=3;)

run;

proc contents data=arrayds; run;
proc print data=arrayds; run;


************************************************************************************************************************;
* More examples;
************************************************************************************************************************;

* Starting and ending values;
%array(mvarnm, values=D_M1-D_M3);
Data arrayonly2;
 length %DO_OVER(mvarnm, PHRASE=pre_?) 8;
 %DO_OVER(mvarnm, PHRASE=pre_?=3;)
run;
proc contents data=arrayonly2; run;
proc print data=arrayonly2; 
 var %DO_OVER(mvarnm, PHRASE=pre_?) ;
run;


* Quoted list in SAS statement;
data test;
 length name $10;
 name = 'Bill'; output;
 name = 'Sue'; output;
 name = 'Tom'; output;
run;
Data myname;
 set test;
 if name in (%DO_OVER(VALUES=Bill Sam, PHRASE="?"));
run;
proc print data=myname; run;


%mend mrun;
%mrun;


* Array macro does not accept dates as starting and ending values;
%*array(dtvarnm, values= %str('01jan2010'd) - %str('01dec2010'd));

* Alternative method to create list of dates;
* Access dates from a data set;

* Create one record for each Monday in January;
* Set starting and ending dates in do loop;
* Restricted to fixed number of days to increment such as 1 or 7;
* Can not automatically select 1st or last day of month for example;
* May need to apply functions to set and keep first day of each month if needed;
data stack;
  format weekbeg date9.;
    do weekbeg='04jan2010'd to '25jan2010'd by 7;
       output;
    end;
run;

* Alternative to set to first of each month;
data stack2;
  format weekbeg date9.;
    do weekbeg='01jan2010'd to '31dec2010'd;
       if day(weekbeg)=1 then output;
    end;
run;

* Manual process to assign dates;
data stack3;
  format month $9.;
    do month='01jan2010', '01feb2010', '01mar2010';
       output;
    end;
run;


* Apply PROC SQl to create macro variable storing list of dates from data set;
* Store count of dates in another macro variable;
proc sql;
 select "'" !! trim(put(weekbeg, date9.)) !! "'d" into: janval separated by ' '
 from stack;
 select count(weekbeg) into: wcnt
 from stack;
quit;
%put &janval &wcnt;

* Call array macro to create array containing list of date flag variables;
%array(mvarnm, values= D_M1-D_M&wcnt);
%*array(mvarnm, values= D_M1-D_M4);

* Call array macro to create array containing list of dates from macro variable;
%*let janvalt = '04jan2010'd '11jan2010'd '18jan2010'd '25jan2010'd;
%array(dtvarnm, values= &janval);
%*array(dtvarnm, values= '04jan2010'd '11jan2010'd '18jan2010'd '25jan2010'd);

* Call do_over macro using both arrays;
data datetst;

 set mazlib._skeleton_;
 length %DO_OVER(mvarnm, PHRASE=?) 4;
 %DO_OVER(mvarnm, PHRASE=?=0;)

 %DO_OVER(dtvarnm mvarnm, PHRASE=if monthdt=?dtvarnm then ?mvarnm=1;, between=else)
run;
proc print data=datetst; run;


* Example from Mazda Dummy Variables.sas;
%macro advexam;

data dumvars;

 set mazlib._skeleton_;

 * Create arrays containing list of values;
 * Will need to recreate arrays is used in other data steps;
 %array(xvarnm, values= D_COMP_NEWGENERATION D_M_DAY_SALE_EVENT D_MAJORREFRESH D_MINORREFRESH);
 %array(yvarnm, values= D_NEWGENERATION D_NEWGENERATION_M2 D_NEWGENERATION_M3 D_NEWLAUNCH D_PR_BRD D_PR_DML);
 %array(mvarnm, values= D_M1-D_M12);
 %array(dtvarnm, values= '01jan2010'd '01feb2010'd '01mar2010'd '01apr2010'd '01may2010'd '01jun2010'd '01jul2010'd '01aug2010'd '01sep2010'd '01oct2010'd '01nov2010'd '01dec2010'd);
 %array(ndtvarnm, values= '01jan2011'd '01feb2011'd '01mar2011'd '01apr2011'd '01may2011'd '01jun2011'd '01jul2011'd '01aug2011'd '01sep2011'd '01oct2011'd '01nov2011'd '01dec2011'd);

 * Assign length statement;
 length %DO_OVER(xvarnm, PHRASE=?) 4;
 length %DO_OVER(yvarnm, PHRASE=?) 4;
 length %DO_OVER(mvarnm, PHRASE=?) 4;

 * Assign value of 0 instead of missing;
 %DO_OVER(xvarnm, PHRASE=?=0;)
 %DO_OVER(yvarnm, PHRASE=?=0;)
 %DO_OVER(mvarnm, PHRASE=?=0;)

 * assign to 1 for sept 2010;
 if monthdt = '01sep2010'd then D_M_DAY_SALE_EVENT = 1; else D_M_DAY_SALE_EVENT = 0;

 * assign to 1 for corresponding month;
 * if monthdt = '01jan2010'd then d_m1 = 1;
 %DO_OVER(dtvarnm mvarnm, PHRASE=if monthdt=?dtvarnm then ?mvarnm=1;, between=else)
 %DO_OVER(ndtvarnm mvarnm, PHRASE=if monthdt=?ndtvarnm then ?mvarnm=1;, between=else)

 * assign 1 to launch, major and minor refresh;
 if monthdt = '01may2010'd and model = 'M2' then D_NEWLAUNCH = 1;
 if monthdt = '01may2010'd and model = 'M2' then D_MAJORREFRESH = 1;

 if monthdt = '01may2010'd and model = 'M3' then D_MINORREFRESH = 1;

 if monthdt = '01mar2010'd and model = 'M6' then D_NEWLAUNCH = 1;
 if monthdt = '01mar2010'd and model = 'M6' then D_MAJORREFRESH = 1;

 * assign 1 to each brand and model;
 if monthdt = '01apr2010'd then do; D_PR_BRD = 1; D_PR_DML = 1; end;
 if monthdt = '01jul2010'd then do; D_PR_BRD = 1; D_PR_DML = 1; end;
 if monthdt = '01aug2010'd then do; D_PR_BRD = 1; D_PR_DML = 1; end;
 if monthdt = '01sep2010'd then do; D_PR_BRD = 1; D_PR_DML = 1; end;
 if monthdt = '01jan2011'd then D_PR_BRD = 1;
 if monthdt = '01mar2011'd then D_PR_BRD = 1;
 if monthdt = '01oct2010'd then D_PR_BRD = 1;
 if monthdt = '01nov2010'd then D_PR_BRD = 1;
 if monthdt = '01feb2011'd then D_PR_BRD = 1;

 * assign 1 to selected brand and model;
 if monthdt = '01sep2010'd and model = 'MX5' then D_PR_DML = 1;
 if monthdt = '01sep2010'd and model = 'M3' then D_PR_DML = 1;
 if monthdt = '01sep2010'd and model = 'RX8' then D_PR_DML = 1;
 if monthdt = '01oct2010'd and model = 'BT50' then D_PR_DML = 1;
 if monthdt = '01nov2010'd and model = 'M3' then D_PR_DML = 1;
 if monthdt = '01nov2010'd and model = 'RX8' then D_PR_DML = 1;
 if monthdt = '01jan2011'd and model = 'M3' then D_PR_DML = 1;
 if monthdt = '01jan2011'd and model = 'RX8' then D_PR_DML = 1;
 if monthdt = '01feb2011'd and model = 'RX8' then D_PR_DML = 1;
 if monthdt = '01feb2011'd and model = 'MX5' then D_PR_DML = 1;

 * keep only 2010 and 2011 records;
 where monthdt > '01dec2009'd;

run;

%mend advexam;