************************

TIP00408 - Macro to return an expanded list of variables based on a SAS dataset, by Ian Whitlock
  Handles lists with single and double hyphens, colons, _NUMERIC_, etc., and combinations

  Syntax:
    %macro wvarxpnd ( data = &syslast ,             /* sas data set name */
                             /* use _NULL_ to turn off data set checking */
                      vars = ,                      /* std sas notation  */
                      rcv = WESERR  /* add macro name to &RCV when error */
                    ) ;

  Examples:
    %PUT %WVARXPND (DATA=SASHELP.Class, VARS=_ALL_);
    Name Sex Age Height Weight
    %PUT %WVARXPND (DATA=SASHELP.Class, VARS=Name-CHARACTER-Weight);
    Name Sex
    %PUT %WVARXPND (DATA=SASHELP.Class, VARS=Age-NUMERIC-Weight);
    Age Height Weight
    %PUT %WVARXPND (DATA=SASHELP.Class, VARS=Sex--Height);
    Sex Age Height
    %PUT %WVARXPND (DATA=SASHELP.Class, VARS=A: );
    Age
    %PUT %WVARXPND (DATA=_NULL_, VARS=X1-X5);
    X1 X2 X3 X4 X5
************************

ACTUAL MACRO:

/* Return expanded list of variables based on a SAS data set         */
%macro wvarxpnd ( data = &syslast ,             /* sas data set name */
                         /* use _NULL_ to turn off data set checking */
                  vars = ,                      /* std sas notation  */
                  rcv = WESERR  /* add macro name to &RCV when error */
                ) ;

   /* ------------------------------------------------------------
      MODULE:     WVARXPND
      PURPOSE:    Return expanded list of vars when there are no
                  errors and an empty list when there are errors.
      CLASS:      Macro function
      USAGE:
                  %let exlist = %wvarxpnd ( data = w ,
                                            vars = x -- z a1 - a5 )
      PARAMETERS:   data=&syslast       Data set to use to do the
                                        expansion.  _NULL_ may be used
                                        to turn of checking when
                                        expanding something like
                                        X05 - X20

                  R vars                List to be expanded

                    RCV=WESERR          Variable for return code.  When
                                        error, WVARXPND is added to the
                                        variable.
      NOTES:        It is important to remember that there can be no
                    commas separating parts of the list specified
                    by VARS.
      SIDE EFFECTS: None.
      SYSTEMS:    All (tested WIN).
      HISTORY:    19dec1997 IW
      DOCUMENT:   Here.
      SUPPORT:    Ian Whitlock <ian dot whitlock at comcast dot net>
      ------------------------------------------------------------   */


   /* ---------------------------------------------------------
      helping macros in this file

         __wvxerr - reports errors and sets errflag
         __wvx1   - handles single variable name
         __wvxrtn - handles array (x1-x88)
         __wvx    - handles all -- types
         __wvxcln - handles colon notation

      other helping macros (also included here for non-Westat use)

         w_isint  - check for integer values
   --------------------------------------------------------- */

   %local
       rx              /* pointer from rxparse               */
       bignum          /* big number for rxchange            */
       word            /* temporary parsing unit             */
       type            /* method of expansion                */
       list            /* list being built from &vars        */
                       /* used and understood by all helpers */
       dsid            /* data set id from open              */
       rc              /* return code for helping macros     */
       errflag         /* 1 = error                          */
       i               /* looping index                      */
   ;

   %if %upcase(&rcv) = WESERR %then %global weserr ;
   %let errflag = 0 ;


   %if %sysevalf ( &sysver lt 6.12 ) %then
   %do ;
      %let &rcv = &&&rcv WVARXPND ;
      %put ERROR(WVARSPND): Not available for versions before 6.12 ;
      %put %str(                 )Halting for the above reason(s) ;
      %goto mexit ;
   %end ;



   /* ---------------------------------------------------------
      set up &vars for parsing
   --------------------------------------------------------- */

   %let bignum = 999;
   %let rx = %sysfunc ( rxparse (` $w* '-' $w* TO '-') );
   %syscall rxchange ( rx, bignum, vars ) ;
   %syscall rxfree ( rx ) ;
   %let vars = %qupcase(&vars) ;
   %let data = %upcase ( &data ) ;

   /* ---------------------------------------------------------
      open data set
   --------------------------------------------------------- */

   %if %quote(&data) = _NULL_ %then
   %do ;
      %let dsid = 0 ;
      %if    %index ( &vars , -- )
          or %index ( &vars , _ALL_ )
          or %index ( &vars , -ALL- )
          or %index ( &vars , _CHARACTER_ )
          or %index ( &vars , -CHARACTER- )
          or %index ( &vars , _NUMERIC_ )
          or %index ( &vars , -NUMERIC- )
          or %index ( &vars , : )
         %then %__wvxerr ( _NULL_ data set not allowed ) ;
   %end ;
   %else
   %do ;
      %let dsid = %sysfunc ( open ( &data ) ) ;
      %if &dsid = 0 %then
          %__wvxerr ( Could not open data set &data ) ;
   %end ;

   %if &errflag %then
   %do ;
      %let weserr = &weserr WVARXPND ;
      %__wvxerr ( Halting for reason given above )
      %goto mexit ;
   %end ;

   /* ---------------------------------------------------------
      parse &vars into words
   --------------------------------------------------------- */

   %let i = 1 ;
   %let word = %qscan ( &vars , &i , %str( ) ) ;
   /* word has form name | root1-rootN | name1-???-name2 | name: */
   %do %while ( &word ^= ) ;
      %let type =
         %sysfunc(compress(&word,%sysfunc(compress(&word,-:)))) ;
      %let type = %quote(&type) ;

      %if &type = %str() %then
      %do ;
         %if &word = _ALL_ %then
         %do ;
            %let type = %str(--) ;
            %let word = -ALL- ;
         %end ;
         %else
         %if &word = _NUMERIC_ %then
         %do ;
            %let type = %str(--) ;
            %let word = -NUMERIC- ;
         %end ;
         %else
         %if &word = _CHARACTER_ %then
         %do ;
            %let type = %str(--) ;
            %let word = -CHARACTER- ;
         %end ;
      %end ;

      %if &type = %str() %then
         %__wvx1 ( dsid = &dsid , items = &word ) ;
      %else
      %if &type = %str(-) %then
         %__wvxrtn ( dsid = &dsid , items = &word ) ;
      %else
      %if &type = %str(--) %then
      %do ;
         %if %index(&word, --) %then
            %__wvx( dsid = &dsid , items = &word ,
                    type = --, vartype = CN ) ;
         %else
         %if %index(&word, -ALL-) %then
            %__wvx( dsid = &dsid , items = &word ,
                    type = -ALL-, vartype = CN ) ;
         %else
         %if %index(%qupcase(&word), -CHARACTER-) %then
            %__wvx( dsid = &dsid , items = &word ,
                    type = -CHARACTER-, vartype = C ) ;
         %else
         %if %index(%qupcase(&word), -NUMERIC-) %then
            %__wvx( dsid = &dsid , items = &word ,
                    type = -NUMERIC-, vartype = N ) ;
         %else
            %__wvxerr ( Unexpected form &word ) ;
      %end ;
      %else
      %if &type = %str(:) %then
         %__wvxcln ( dsid = &dsid , items = &word ) ;
      %else
         %__wvxerr ( Unexpected form &word ) ;


      %if &errflag %then
      %do ;
         %__wvxerr ( Halting for reasons given above )
         %goto mexit ;
      %end ;

      %let i = %eval ( &i + 1 ) ;
      %let word = %qscan ( &vars , &i , %str( ) ) ;
   %end ;

   &list

%mexit:

   %if &dsid gt 0 %then
      %let rc = %sysfunc ( close ( &dsid ) ) ;

%mend  wvarxpnd ;

%macro __wvxerr ( msg ) ;
   %put ERROR(WVARXPND): &msg ;
   %let errflag = 1 ;
%mend  __wvxerr ;

%macro __wvx1 ( dsid = , items =  ) ;
   %if &dsid = 0 %then ;
   %else
   %if not %sysfunc ( varnum ( &dsid , &items ) ) %then
   %do ;
       %__wvxerr ( Variable &items not found on &data ) ;
   %end ;
   %let list = &list &items ;
%mend  __wvx1 ;

%macro __wvxrtn ( dsid = , items =  ) ;

   %local
      i             /* loop index                          */
      root          /* common root                         */
      len           /* length of smaller of &firsti &lasti */
      zi            /* corresponds to &i with leading 0's  */
      firstw        /* first word of &items                */
      lastw         /* second word of &items               */
      letter        /* letter in &firsti                   */
      firsti        /* number after &root                  */
      lasti         /* number after &root                  */
      byval         /* 1 or -1                             */
   ;
   %let firstw = %qscan ( &items , 1 , %str(-) ) ;
   %let lastw = %qscan ( &items , 2 , %str(-) ) ;
   %do i = 1 %to %length ( &firstw ) ;
      %let letter = %qsubstr ( &firstw , &i , 1 ) ;
      %if &letter = %qsubstr ( &lastw , &i , 1 ) %then
         %let root = &root&letter ;
      %else %goto endloop ;
   %end ;


%endloop:

   %let firsti = %substr ( &firstw , &i ) ;
   %let lasti = %substr ( &lastw , &i ) ;
   %if %w_isint ( &firsti ) and %w_isint ( &lasti ) %then
   %do ;
      %if &lasti ge &firsti %then
      %do ;
         %let len = %length (&firsti) ;
         %let byval = 1 ;
      %end ;
      %else
      %do ;
         %let len = %length (&lasti) ;
         %let byval = -1 ;
      %end ;

      %do i = &firsti %to &lasti %by &byval ;
         %if %length(&i) lt &len %then
            %let zi = %sysfunc ( putn ( &i , z , &len ) ) ;
         %else
            %let zi = &i ;

         %if &dsid = 0 %then ;
         %else
         %if not %sysfunc ( varnum ( &dsid , &root&zi ) ) %then
         %do ;
            %__wvxerr ( Variable &root&zi not found on &data ) ;
            %goto mexit ;
         %end ;

         %let list = &list &root&zi ;
      %end ;
   %end ;
   %else
      %__wvxerr ( &items does not have a common root ) ;

%mexit:
%mend  __wvxrtn ;

%macro __wvx ( dsid = ,
               items = ,
               type = ,   /* -- -ALL- -NUMERIC- -CHARACTER- */
               vartype =                          /* C N CN */
             ) ;

   %local first         /* first name in items          */
          last          /* last name in items           */
          i             /* loop index                   */
          firsti        /* first index value            */
          lasti         /* last index value             */
          name          /* variable name to add to list */
   ;

   %if %qsubstr(&items,1,1)=%str(-) %then
      %let items = %quote ( &items ) ;

   %let first = %qscan ( &items , 1 , %str(-) ) ;
   %if &first = %then
      %let firsti = 1 ;
   %else
      %let firsti = %sysfunc ( varnum ( &dsid , &first ) ) ;

   %if %qupcase(&type) = %str(--) %then
      %let last  = %qscan ( &items , 2 , %str(-) ) ;
   %else
      %let last  = %qscan ( &items , 3 , %str(-) ) ;

   %if &last = %then
      %let lasti = %sysfunc ( attrn ( &dsid , NVARS ) ) ;
   %else
      %let lasti  = %sysfunc ( varnum ( &dsid , &last ) ) ;

   %if &firsti and &lasti %then
   %do ;
      %do i = &firsti %to &lasti ;
         %let name = %sysfunc ( varname ( &dsid , &i ) ) ;
         %if %index ( &vartype , %sysfunc(vartype(&dsid,&i)) ) %then
            %let list = &list &name ;
      %end ;
   %end ;
   %else
      %__wvxerr ( &first or &last not found in &data ) ;

%mend  __wvx ;

%macro __wvxcln ( dsid = , items = ) ;
   %local i root name len flag ;
   %let len = %length ( &items ) - 1 ;
   %let root = %qsubstr ( &items , 1 , &len ) ;

   %let flag = 1 ;
   %do i = 1 %to %sysfunc ( attrn ( &dsid , NVARS ) ) ;
      %let name = %sysfunc ( varname ( &dsid , &i ) ) ;
      %if %length ( &name ) ge &len %then
      %do ;
         %if %qsubstr ( &name , 1 , &len ) = &root %then
         %do ;
            %let list = &list &name ;
            %let flag = 0 ;
         %end ;
      %end ;
   %end ;

   %if &flag %then
      %__wvxerr ( &root is not found as initial segment any variable ) 
;

%mend  __wvxcln ;

%macro W_IsInt ( val             /* value to test        */
               ) ;
/* W_IsInt is needed for %WVarXpnd */
/* Return 1 when &val is an integer and 0 otherwise      */

   /* ------------------------------------------------------------
      MODULE:     W_ISINT
      PURPOSE:    Decide if value is an integer.
      CLASS:      Macro utility
      USAGE:
                  %if not %w_isint ( &x ) %then
                  %do ;
                     %put X must be an integer ;
                     %goto mexit ;
                  %end ;

      PARAMETERS: R val    value to test
      NOTE:       Integers are of the form +, -, or null followed
                  by digits.  Thus 2e4 and 2.0 are not considered
                  integers by this macro.
      SIDE EFFECTS: None.
      SYSTEMS:    All (tested WIN).
      HISTORY:    28nov1997 IW
      DOCUMENT:   Here.
      SUPPORT:    Ian Whitlock <ian dot whitlock at comcast dot net>
      ------------------------------------------------------------   */

   %if %quote(&val) = %then 0 ;
   %else
   %if %sysfunc(verify(%substr(&val,1,1),+-0123456789)) %then 0 ;
   %else
   %if %length ( &val ) gt 1 %then
   %do ;
      %if %sysfunc(verify(%substr(&val,2),0123456789)) %then 0 ;
      %else 1 ;
   %end ;
   %else 1 ;

%mend  W_IsInt ;

solution # by Ian Whitlock - 

Here is a simple and useful solution to Mike's request for separator, prefix and suffix support.  I did not mess with the original macros.  
 
/* given space separated list, return modified separated list */
%macro wlistmod( list , sep = %str(,), prefix=, suffix= ) ;
   %*!!! ---------------------------------------------------------- !!!
         Prefix (and suffix) can be quoted quote marks of same kind. 
         If SEP, PREFIX, or SUFFIX contain macro quoted material then,
         the returned string will also contain that quoted material.
         The user is responsible for any necessary unquoting not done
         automatically by the system.
     !!! ---------------------------------------------------------- !!!
   ;
   %let list = %sysfunc(compbl(&list)) ;
   &prefix%sysfunc(tranwrd(&list,%str( ),&suffix&sep&prefix))&suffix
%mend  wlistmod ;
 
Here is a log illustrating possibilities.
12             %PUT %wlistmod( %WVARXPND (DATA=SASHELP.Class, VARS=_ALL_)
13                           , prefix = SS , suffix = 99 );
SSName99,SSSex99,SSAge99,SSHeight99,SSWeight99
14             %PUT %wlistmod( %WVARXPND (DATA=SASHELP.Class, VARS=Name-CHARACTER-Weight));
Name,Sex
15             %PUT %wlistmod(%WVARXPND (DATA=SASHELP.Class, VARS=Age-NUMERIC-Weight)
16                           , prefix = %str(%'), suffix=%Str(%'));
'Age','Height','Weight'
17             %PUT %wlistmod(%WVARXPND (DATA=SASHELP.Class, VARS=Sex--Height)
18                           , prefix = %str(%") , suffix = %str(%"));
"Sex","Age","Height"
19             %PUT %wlistmod(%WVARXPND (DATA=SASHELP.Class, VARS=A: )
20                           , prefix = __ , suffix = _group);
__Age_group
21             %PUT %wlistmod(%WVARXPND (DATA=_NULL_, VARS=X1-X5),sep=%str(<=));
X1<=X2<=X3<=X4<=X5
 
If you prefer a single macro call then WMODLIST could be used as follows.
 
%macro varxpnd ( data = &syslast , vars = , sep = %str(,), prefix= , suffix= ) ;
   %wlistmod( %wvarxpnd(data=&data, vars=&vars)   
             , sep=&sep, prefix=&prefix, suffix=&suffix) 
%mend  varxpnd ;
 
%put %varxpnd ( data = _null_ , vars = X1-X8 , prefix = __, suffix = _rev );
 
With log:
29 %macro varxpnd ( data = &syslast , vars = , sep = %str(,), prefix= , suffix= ) ;

30 %wlistmod( %wvarxpnd(data=&data, vars=&vars)

31 , sep=&sep, prefix=&prefix, suffix=&suffix)

32 %mend varxpnd ;

33 

34 %put %varxpnd ( data = _null_ , vars = X1-X8 , prefix = __, suffix = _rev );

__X1_rev,__X2_rev,__X3_rev,__X4_rev,__X5_rev,__X6_rev,__X7_rev,__X8_rev



/*** end of tip 00408 ***/