Macro function that creates a vector, in fact a string, of variable names concatenated with a suffix.

160225_MCreateStringRange.sas

/*-----------------------------------------------------------------------------|
| Description : Macro function that creates a vector, in fact a string, of     |
|               variable names concatenated with a suffix (a date or a number, |
|               depending on the parameters given).                            |
| Assumptions : The last character of the "Collapse" parameter is useless in   |
|               the output string for the last created variable name.          |
| Parameters  : VariablePrefix  - Prefix of the variable of interest.          |
|               InitialSuffix   - Initial date of the period in format YYMM.   |
|                                 If you give a numeric character with less    |
|                                 than 4 digits, it will create a sequence of  |
|                                 numbers from this parameter.                 |
|               FinalSuffix     - Final date of the period in format YYMM.     |
|                                 The same as above for a string with less     |
|                                 than 4 digits.                               |
|               SuffixFormat    - Date format (Default: YYYYMM).               |
|                                 Some SAS useful formats are the following:   |
|                                   YYMMN4.   --> 1602                         |
|                                   YYMMD.    --> 2016-02                      |
|                                   YYMMDDN8. --> 20160224                     |
|                                   YYMMDDN6. --> 160224                       |
|                                   ESPDFMY.  --> feb16                        |
|               StringSeparator - String character separator (Default: _).     |
|               Collapse        - Collapse separator (Default: None).          |
|               SQLFieldFormat  - SQL Format.                                  |
|               SQLSummaryFun   - Creates a prefix for SQL code that tallies   |
|                                 summary values.                              |
| Output      : A list of variable names (with at least one variable) in a     |
|               string.                                                        |
|-----------------------------------------------------------------------------*/
/*  Examples:
%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 0,
                         FinalSuffix    = 10,
                         SuffixFormat   = 8.);
* Output: FOO_0 FOO_1 FOO_2 FOO_3 FOO_4 FOO_5 FOO_6 FOO_7 FOO_8 FOO_9 FOO_10;

%PUT %MCreateStringRange(VariablePrefix = ,
                         InitialSuffix  = 0,
                         FinalSuffix    = 10,
                         SuffixFormat   = 8.);
* Output: _0 _1 _2 _3 _4 _5 _6 _7 _8 _9 _10;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 1112,
                         FinalSuffix    = 1203);
* Output: FOO_201112 FOO_201201 FOO_201202 FOO_201203;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 0311,
                         FinalSuffix    = 0402,
                         SuffixFormat   = ESPDFMY.);
* Output: FOO_nov03 FOO_dic03 FOO_ene04 FOO_feb04;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 1112,
                         FinalSuffix    = 1203,
                         Collapse       = %STR(,));
* Output: FOO_201112, FOO_201201, FOO_201202, FOO_201203;

%PUT %MCreateStringRange(VariablePrefix  = FOO,
                         InitialSuffix   = 1112,
                         FinalSuffix     = 1203,
                         StringSeparator = ,
                         Collapse        = %STR(,));
* Output: FOO201112, FOO201201, FOO201202, FOO201203;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 1112,
                         FinalSuffix    = 1203,
                         Collapse       = %STR(,),
                         SuffixFormat   = MONYY.);
* Output: FOO_DEC11, FOO_JAN12, FOO_FEB12, FOO_MAR12;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 0311,
                         FinalSuffix    = 0402,
                         Collapse       = %STR(,),
                         SuffixFormat   = ESPDFMY.);
* Output: FOO_nov03, FOO_dic03, FOO_ene04, FOO_feb04;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 1112,
                         FinalSuffix    = 1201,
                         Collapse       = %STR(,),
                         SQLFieldFormat = COMMA32.0);
* Output: FOO_201112 FORMAT = COMMA32.0, FOO_201201 FORMAT = COMMA32.0;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 0,
                         FinalSuffix    = 1,
                         SuffixFormat   = Z3.,
                         Collapse       = %STR(,),
                         SQLFieldFormat = COMMA32.0);
* Output: FOO_000 FORMAT = COMMA32.0, FOO_001 FORMAT = COMMA32.0;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 1112,
                         FinalSuffix    = 1203,
                         SuffixFormat   = YYMMDDN8.);
* Output: FOO_20111231 FOO_20120131 FOO_20120229 FOO_20120331;

%PUT %MCreateStringRange(VariablePrefix = ,
                         InitialSuffix  = 1112,
                         FinalSuffix    = 1203,
                         SuffixFormat   = YYMMDDN8.);
* Output: _20111231 _20120131 _20120229 _20120331;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 1112,
                         FinalSuffix    = 1201,
                         SuffixFormat   = YYMMDDN8.,
                         Collapse       = %STR(,),
                         SQLSummaryFun  = MEAN);
* Output: MEAN(FOO_20111231) AS MEAN_FOO_20111231, MEAN(FOO_20120131) AS MEAN_FOO_20120131;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 1112,
                         FinalSuffix    = 1201,
                         SuffixFormat   = YYMMDDN8.,
                         Collapse       = %STR(,),
                         SQLFieldFormat = COMMA32.0,
                         SQLSummaryFun  = MEAN);
* Output: MEAN(FOO_20111231) AS MEAN_FOO_20111231 FORMAT = COMMA32.0, MEAN(FOO_20120131) AS MEAN_FOO_20120131 FORMAT = COMMA32.0;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 1112,
                         FinalSuffix    = 1201,
                         SuffixFormat   = ESPDFMY.,
                         Collapse       = %STR(,),
                         SQLFieldFormat = COMMA32.0,
                         SQLSummaryFun  = MEAN);
* Output: MEAN(FOO_dic11) AS MEAN_FOO_dic11 FORMAT = COMMA32.0, MEAN(FOO_ene12) AS MEAN_FOO_ene12 FORMAT = COMMA32.0;

%PUT %MCreateStringRange(VariablePrefix  = FOO,
                         InitialSuffix   = 1112,
                         FinalSuffix     = 1201,
                         SuffixFormat    = ESPDFMY.,
                         StringSeparator = ,
                         Collapse        = %STR(,),
                         SQLFieldFormat  = COMMA32.0,
                         SQLSummaryFun   = MEAN);
* Output: MEAN(FOOdic11) AS MEANFOOdic11 FORMAT = COMMA32.0, MEAN(FOOene12) AS MEANFOOene12 FORMAT = COMMA32.0;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 1,
                         FinalSuffix    = 2,
                         SuffixFormat   = Z3.,
                         Collapse       = %STR(,),
                         SQLSummaryFun  = MEAN);
* Output: MEAN(FOO_001) AS MEAN_FOO_001, MEAN(FOO_002) AS MEAN_FOO_002;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 0,
                         FinalSuffix    = 1,
                         SuffixFormat   = Z3.,
                         Collapse       = %STR(,),
                         SQLFieldFormat = COMMA32.0,
                         SQLSummaryFun  = MEAN);
* Output: MEAN(FOO_000) AS MEAN_FOO_000 FORMAT = COMMA32.0, MEAN(FOO_001) AS MEAN_FOO_001 FORMAT = COMMA32.0;

%PUT %MCreateStringRange(VariablePrefix = FOO,
                         InitialSuffix  = 0,
                         FinalSuffix    = 1,
                         SuffixFormat   = Z2.,
                         Collapse       = %STR(,),
                         SQLFieldFormat = COMMA32.0,
                         SQLSummaryFun  = MEAN);
* Output: MEAN(FOO_00) AS MEAN_FOO_00 FORMAT = COMMA32.0, MEAN(FOO_01) AS MEAN_FOO_01 FORMAT = COMMA32.0;
*/
/*-----------------------------------------------------------------------------|
| Date                Author                          Description              |
|------------------------------------------------------------------------------|
| February 25, 2016   Cesar R. Urteaga-Reyesvera      Creation.                |
|-----------------------------------------------------------------------------*/
%MACRO MCreateStringRange(VariablePrefix  = /* Prefix of each variable.      */,
                          InitialSuffix   = /* Initial date/value of the
                                               period/range.                 */,
                          FinalSuffix     = /* Final date/value of the
                                               period/range.                 */,
                          SuffixFormat    = YYMMN6. /* Date format.          */,
                          StringSeparator = _ /* String separator character. */,
                          Collapse        = /* Collapse character.           */,
                          SQLFieldFormat  = /* SAS format for SQL fields.    */,
                          SQLSummaryFun   = /* SQL's summary function.       */
                          );
  %LOCAL  li
          lInitialSuffix
          lFinalSuffix
          lInitialValue
          lNumberPeriods
          lSQLFieldFormat
          lSuffix
          lConcatenatedString;
  /* We set up the beginning and end of the specified range/period.           */
  %IF %LENGTH(&InitialSuffix) = 4 & %LENGTH(&FinalSuffix) = 4 %THEN
    %DO;
      /* In order to make some calculations later, we get the number
         representation of each date.                                         */
      %LET lInitialSuffix = %SYSFUNC(MDY(%SYSFUNC(SUBSTR(&InitialSuffix, 3, 2)),
                                    1, %SYSFUNC(SUBSTR(&InitialSuffix, 1, 2))));
      %LET lFinalSuffix   = %SYSFUNC(MDY(%SYSFUNC(SUBSTR(&FinalSuffix,   3, 2)),
                                    1, %SYSFUNC(SUBSTR(&FinalSuffix,   1, 2))));
      %LET lInitialValue = 1;
      /* It corresponds to the number of months between the initial and final
        dates.                                                                */
      %LET lNumberPeriods = %EVAL(%SYSFUNC(INTCK(MONTH, &lInitialSuffix,
                                                        &lFinalSuffix)) + 1);
    %END;
  %ELSE
    %DO;
      %LET lInitialValue  = &InitialSuffix;
      %LET lNumberPeriods = &FinalSuffix;
    %END;
  /* We define the field formats in order to use them in SQL statements.      */
  %IF &SQLFieldFormat ~= %THEN
    %LET lSQLFieldFormat = %STR( FORMAT = &SQLFieldFormat.);
  /* We create the final string with each month defined in the period.        */
  %LET lConcatenatedString = ;
  %DO li = &lInitialValue %TO &lNumberPeriods;
    %IF %LENGTH(&InitialSuffix) = 4 & %LENGTH(&FinalSuffix) = 4 %THEN
      %LET lSuffix = %SYSFUNC(PUTN(%SYSFUNC(INTNX(DAY, %SYSFUNC(INTNX(MONTH,
                                  &lInitialSuffix, &li)), -1)), &SuffixFormat));
    %ELSE
      %LET lSuffix = %SYSFUNC(PUTN(&li, &SuffixFormat));

    %IF &SQLSummaryFun = %THEN
      %LET lConcatenatedString = &lConcatenatedString. &VariablePrefix.&StringSeparator.&lSuffix&lSQLFieldFormat&Collapse;
    %ELSE
      %LET lConcatenatedString = &lConcatenatedString. &SQLSummaryFun%STR(%()&VariablePrefix.&StringSeparator.&lSuffix%STR(%)) AS &SQLSummaryFun.&StringSeparator.&VariablePrefix.&StringSeparator.&lSuffix&lSQLFieldFormat&Collapse;
  %END;
  /* Before we generate the string of variables, we remove the last character.*/
  %IF &Collapse = %THEN
    &lConcatenatedString;
  %ELSE
    /* Please, refer to the assumptions.                                      */
    %SUBSTR(&lConcatenatedString, 1, %LENGTH(&lConcatenatedString) - 1);
%MEND MCreateStringRange;