SAS macro-function %VARLIST

From PHUSE Wiki
Jump to: navigation, search


Introduction

%VARLIST is a user-written SAS macro-function to process lists of variables. The variables lists can be

  • specified as litterals
  • passed as macro-variables
  • retrieved from one or more datasets
  • combined using set operators (union, intersection, exclusion, exclusive OR)

The returned value of the function can consist of

  • variables names
  • variables attributes
  • source dataset (+ libname)
  • dataset alias
  • separators (conventional: space, comma, semi-colon; logical: and, or; almost any combination of characters)

In addition, standard or user-defined patterns can be applied in order generate SAS code around each variable in the returned list. This could be used to batch rename, carry-forward, build expressions around the individual variables and/or some of their attributes.

Code

/*----------------------------------------------------------------------------------------*
  Program name    : varlist.sas
  
  Author          : Jean-Michel Bodart (B&D Life Sciences)
  
  Version         : 1.0 - 03SEP2015
  
  Function        : retrieve, generate and/or manipulate lists of SAS variables,
                    existing or not in one or more SAS datasets.
					
  License         : MIT license: http://opensource.org/licenses/MIT

                    Copyright (c) 2015 Jean-Michel Bodart, Business & Decision Life Sciences

                    Permission is hereby granted, free of charge, to any person obtaining a copy 
					of this software and associated documentation files (the "Software"), to deal
					in the Software without restriction, including without limitation the rights 
					to use, copy, modify, merge, publish, distribute, sublicense, and/or sell 
					copies of the Software, and to permit persons to whom the Software is furnished 
					to do so, subject to the following conditions:

                    The above copyright notice and this permission notice shall be included 
					in all copies or substantial portions of the Software.

                    THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, 
					INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS 
					FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS 
					OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 
					WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, 
					OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS 
					IN THE SOFTWARE.


  Parameters:
     DATA         : (space-separated list of) dataset(s) to retrieve variables from.
                    default: 
                       #default#  : If parameter VAR=#default#, or contains any of the following
                                    special values (#num# #char# #all# #<i># #<i>-<j># /<pattern>/), 
                                    use dataset specified by &SYSLAST.
                                    Otherwise, variables are not retrieved from any dataset and
                                    only those variables explicitly specified in parameter VAR 
                                    are considered.
                    special values:
                       <empty>    : if parameter DATA has an empty value, an empty list of variables
                                    is returned.
                       <non-existing dataset>
                       or <invalid dataset name>
                                  : considered as a dataset with no variables
                       <dataset>:<alias>
                                  :if a dataset name is followed by a colon (:) and an alias, the defined alias
                                   can be used in the formatted results variable list (see paraemeter PATTERN)

                    dataset operators:
                       #union#    : (DEFAULT) consider variables that are at least in one of the specified datasets
                       #except#   : exclude variables that are present in the right-hand dataset(s)
                       #intersect#: consider only variables that are present in both left-hand and 
                                    right-hand datasets
                       #xor#      : consider only variables that are present in either left-hand or 
                                    right-hand datasets, not in both
                       #or#       : equivalent to #union#
                       #and#      : equivalent to #intersect#
                       #not#      : equivalent to #except#
                       <space>    : equivalent to #union#

     VAR          : (space-separated) list of variable names, to be matched against the contents of
                     datasets specified in DATA parameter) and variable operators.  
                    The complete list is first evaluated against the first dataset specified in DATA parameter,
                    providing a resulting list, before being evaluated again against the next dataset (if any).
                    The resulting lists corresponding to separate datasets will be combined sequentially
                    according to the specified dataset operators.
                    
                    Note: It is expected that all variable names respect the option VALIDVARNAME=V7.

                    Variables lists operators are evaluated sequentially, i.e. 
                    - the first operator processes the list of variables specified on its left
                      against the list of variables specified on its right (up to the next operator
                      or the end of the variables list is encontered)
                    - this resolves into a new list of variables on the left of the next operator (if any),
                      to be processed similarly by that operator against the list of variables specified on its right 
                      (up to the next operator or the end of the variables list is encontered)
                    - the final list of variables is obtained after the last operator processing

                    default: 
                       #default#  : i.e. consider all variables in the specified dataset

                    special values:
                       #all#      : consider all variables in the specified dataset
                       #num#      : consider all numerical variables in the specified dataset
                       #char#     : consider all character variables in the specified dataset
                       #<i>#      : consider variable number <i> in the specified dataset
                       #<i>-<j>#  : consider variable numbers between <i> and <j> (inclusive) 
                                    in the specified dataset 
                                    (where <i> and <j> are integer values >=0)
                                       e.g. use #1-5# to refer to the first 5 variables in the 
                                            specified dataset (or less if the dataset has less than 5 variables)
                                            use #5-1# to refer to the first 5 variables in reverse order
                       <var><i>-<j>: consider the list of variables named from <var><i> to <var><j>
                                    (where <var> is a variable root (not ending with a number) 
                                     and <i> and <j> are integer values >= 0, in ascending or descending order)
                                       e.g. use v8-v10 to refer to the sequence: v8 v9 v10
                                            use col008-col010 to refer to the sequence: col008 col009 col010
                                            use t100-t098 to refer to the sequence: t100 t099 t098
                                     note: the list of variables can be generated without any dataset specified,
                                           but if a dataset is specified only those variables found in the dataset
                                           will be kept
                       <varX>-<varY>
                                   : consider all variables from the specified dataset with a name that is between
                                     <varX> and <varY> in the alphabetical order (or reverse order) (case insensitive)
                                     Note: variables <varX> and <varY> do not have to exist in the specified dataset.
                       <varX>--<varY>
                                   : consider all variables with a position between <varX> and <varY> (inclusive)
                                     in the specified dataset
                                     Note: variables <varX> and <varY> have to exist in the specified dataset.
                       <root>:     : consider all variables with a name starting with <root> in the specified dataset
                       :<suffix>   : consider all variables with a name ending with <suffix> in the specified dataset
                       <root>:<suffix>   
                                   : consider all variables with a name both starting with <root> and ending with <suffix> 
                                     in the specified dataset
                       /<pattern>/: any variable matching the PERL regular expression <pattern>
                                    Note: <pattern> should not contain any blank character.
                                    e.g. /^trt\d\dn?$/ matches any variable name starting with "trt"
                                                       followed by two digits (from 00 to 99)
                                                       followed optionally by "n", and without any 
                                                       additional characters.  A case-insensitive 
                                                       match is applied.
                       <null>     : (e.g. parameter VAR is assigned the value of a macro-variable that resolves
                                     to a null string or blank(s) characters)
                                    results in returning an empty list of variables

                    variables operators:
                       #except#   : exclude all variables specified on the right hand of this operator
                                    from those specified on the left hand.  
                       #not#      : equivalent to #except#
                       #intersect#: consider only variables that are present both on the left-hand and on
                                    the right-hand of the operator
                       #and#      : equivalent to #intersect#
                       #xor#      : consider only variables that are present either on the left-hand or 
                                    on the right-hand of the operator, not on both sides

     PATTERN      : specifies a litteral expression used to "format" each variable name
                    in the final resulting list, where the following keywords have special meanings:
                      #var# will be replaced by the variable name 
                      #dsn# will be replaced by the (first) dataset name containing the variable #var#
                      #dsa# will be replaced by the dataset alias of the (first) dataset containing the variable #var#
                            if an alias was specified for that dataset (see parameter DATA), otherwise
                            by the dataset name itself (similar to #dsn#)
                      #vnum# will be replaced by #var# variable number from dataset #dsn# if defined, otherwise empty
                      #vlen# will be replaced by #var# variable length from dataset #dsn# if defined, otherwise empty
                      #vtyp# will be replaced by #var# variable type (C or N) from dataset #dsn# if defined, otherwise empty
                      #vfmt# will be replaced by #var# variable format from dataset #dsn# if defined, otherwise empty
                      #vfmtdef# will be replaced by #var# variable format from dataset #dsn# if defined, otherwise 
                                by default format ($#vlen#. for a character variable, Best12. for a numeric variable)
                      #vinfmt# will be replaced by #var# variable informat from dataset #dsn# if defined, otherwise empty
                      #vinfmtdef# will be replaced by #var# variable informat from dataset #dsn# if defined, otherwise 
                                by default format ($#vlen#. for a character variable, Best12. for a numeric variable)
                      #vlab# or #vlabel# will be replaced by #var# variable label from dataset #dsn# if defined, otherwise empty
                      #vlabsrc# or #vlabelsrc# will be replaced by #var# variable label from dataset #dsn# if defined, otherwise empty,
                                   with FIRST source dataset and variable appended as [<DATASET>.<VARIABLE>]
                            Caution: if a variable results from the COALESCE() of multiple variables from separate
                                     datasets, this will not be apparent from the resulting variable label.
                      #vlabq# or #vlabelq# will be replaced by #var# variable quoted label from dataset #dsn# if defined, otherwise " "
                      #vlabsrcq# or #vlabelsrcq# will be replaced by #var# variable quoted label from dataset #dsn# if defined, otherwise " ",
                                   with FIRST source dataset and variable appended as [<DATASET>.<VARIABLE>]
                      #vlenlabfmt# or #vlenlabelfmt# will be replaced by the sequence: 
                               <variable> [LENGTH=<length>] LABEL=<quoted label> [FORMAT=<format>] [INFORMAT=<informat>]
                            where parts between square brackets [] will only be present if the corresponding variable attribute
                            could be retrieved from the associated dataset (if specified in DATA parameter)
                            Note: This sequence is approprite to specify new variables in SQL syntax with the same attributes
                                  as an existing variable.
                      #vlenlabsrcfmt# or #vlenlabelsrcfmt# will be replaced by the sequence: 
                               <variable> [LENGTH=<length>] LABEL=<quoted label + [<DATASET>.<VARIABLE>]> [FORMAT=<format>] [INFORMAT=<informat>]
                            where parts between square brackets [] will only be present if the corresponding variable attribute
                            could be retrieved from the FIRST associated dataset (if specified in DATA parameter)
                            Note: This sequence is approprite to specify new variables in SQL syntax with the same attributes
                                  as an existing variable, when the the variable name (with its FIRST source dataset) needs 
                                  to be identifiable from its label.
                      #vareq-<w>-<x>[-<y>[-<z>]]# 
                            will be replaced with: 
                            (<w>.<variable>=<x>.<variable> [ and <w>.<variable>=<y>.<variable> [ and <w>.<variable>=<z>.<variable> ]])
                            (where parts between square brackets [] are optional
                                   <w>, <x>, <y>, <z> are single words corresponding to dataset names or aliases to dataset )
                            Note: This is meant to be used typically within the ON sql-expression used with SQL OUTER JOIN(s).
                      #coal-<w>-<x>[-<y>[-<z>]]# 
                            will be replaced with: 
                               COALESCE(<w>.<variable>, <x>.<variable>[, <y>.<variable> [, <z>.<variable> ]])
                               as <variable> [LENGTH=<length>] LABEL=<quoted label> [FORMAT=<format>] [INFORMAT=<informat>]
                            (where parts between square brackets [] are optional
                                   <w>, <x>, <y>, <z> are single words corresponding to dataset names or aliases to dataset )
                            Note: This is meant to be used typically within SQL SELECT expression used with SQL (inner/ outer/ left/ right) JOIN(s).
                      #autoCOAL# 
                            will be replaced with: 
                               COALESCE(<dsa1>.<variable>, <dsa2>.<variable>[, <dsa3>.<variable> [, ... ]]) 
                                  as <variable> [LENGTH=<length>] LABEL=<quoted label> [FORMAT=<format>] [INFORMAT=<informat>]
                               if <variable> was retrieved from multiple datasets combined with dataset operators #OR#, #UNION#, #AND", #INTERSECT#
                            or with:
                               <dsa1>.<variable>
                               if <variable> was retrieved from a single dataset
                                  (where <dsa1>..<dsa(n)> are the dataset aliases (or dataset names if aliases are not provided) 
                                                              of all those datasets from which <variable> was retrieved
                                         parts between square brackets [] will only be present if the corresponding variable attribute
                                                        could be retrieved from the associated dataset (if specified in DATA parameter)
                            Note: This is meant to be used typically within SQL SELECT expression used with SQL (inner/ outer/ left/ right) JOIN(s).
                      #autoCOALb# (for autoCOAL, bare)
                            will be replaced with: 
                               COALESCE(<dsa1>.<variable>, <dsa2>.<variable>[, <dsa3>.<variable> [, ... ]]) 
                               if <variable> was retrieved from multiple datasets combined with dataset operators #OR#, #UNION#, #AND", #INTERSECT#
                            or with:
                               <dsa1>.<variable>
                               if <variable> was retrieved from a single dataset
                                  (where <dsa1>..<dsa(n)> are the dataset aliases (or dataset names if aliases are not provided) 
                                                              of all those datasets from which <variable> was retrieved)
                            Note: This is meant to be used typically within SQL GROUP BY clause,
                                  especially when receiving the message: ERROR: Ambiguous reference, column <variable> is in more than one table..
                      #autoCOALsrc# 
                            will be replaced similarly to #autoCOAL# but with list of source variables between square brackets []
                            appended to the original variable label, 
                            i.e.:  LABEL=<quoted label + [coalesce: <dataset1>.<variable>, <dataset2>.<variable>[, <dataset3>.<variable> [, ... ]]]> 
                            Note: This is meant to be used typically within SQL SELECT expression used with SQL (inner/ outer/ left/ right) JOIN(s),
                                  when the exact source of each variable needs to be identifiable from its label.
                      #autoEQ# 
                            will be replaced with: 
                               (COALESCE(<dsa1>.<variable>, <dsa2>.<variable>[, <dsa3>.<variable> [, ... ]]) = <dsa(n)>.<variable>)
                                  if <variable> was retrieved from >2 datasets combined with dataset operators #OR#, #UNION#, #AND", #INTERSECT#
                                  where <dsa1>..<dsa(n)> are the dataset aliases of all those datasets from which <variable> was retrieved
                            or with:
                               (<dsa1>.<variable> = <dsa2>.<variable>) 
                                  if <variable> was retrieved from  exactly 2 datasets combined with dataset operators #OR#, #UNION#, #AND", #INTERSECT#

                      #comma#, #coma#, #c#                   : will be replaced by one comma (,) - alternative to %bquote(,)

                      #semicolon#, #semicol#, #scol#, #sc#   : will be replaced by one semicolon (;) - alternative to %bquote(;)

                      #autoFULLEQ#, #autoLEFTEQ#, #autoRIGHTEQ#, #autoINNEREQ# : when specified as the full contents of the pattern= parameter,
                            will be replaced by some code to be used as the contents of a SQL FROM clause respectively for one or more 
                            FULL JOIN(s), LEFT JOIN(s), RIGHT JOIN(s), INNER JOIN(s)
                            with the ON clause(s) corresponding to the equality of all variables in common between the right-hand dataset and the 
                            (previously joined) left-hand dataset(s) (equijoin).  If no variables are in common, the ON clause criterion 
                            will be specified as (1 EQ 1) i.e. an always true condition, which should result in something equivalent to 
                            a cartesian product or CROSS JOIN.
                            Alternatively, a similar FROM clause can be generated and appended to the list of variables returned by
                            the normal %VARLIST call (with the FROM keyword inserted after the list of variables) by specifying
                            similar keywords as part of the from= parameter.

                      Any other text will appear unchanged

                    default: #var#

     SEP          : separator between variables (formatted according to the specified PATTERN) in the resulting string,
                    where the following keywords have special meanings:
                     #comma#, #coma#, #c#                   : will be replaced by one comma (,)
                     #semicolon#, #semicol#, #scol#, #sc#   : will be replaced by one semicolon (;)
                     #space#, #s#                           : will be replaced by one space ( )
                     #commaspace#, #comaspace#, #cs#        : will be replaced by one comma followed by one space (, )
                     #semicolonspace#, #semicolons#, #semicolspace#, #semicols#, #scols#, #scs# 
                                                            : will be replaced by one semicolon followed by one space (; )

                     #and#                                  : will be replaced by " and " (without quotes)
                     #or#                                   : will be replaced by " or " (without quotes)
                    default: #space#

     FROM          : If specified, appends to the returned list of variables (meant to be inserted in a PROC SQL SELECT clause)
                     an SQL FROM clause [with the SQL keyword FROM included], based on dataset(s) in data= parameter,
                     including one or more SQL JOIN(s) between the specified datasets (if more than one).
                     In case of JOIN, this will be an EQUIJOIN based on variables in common between datasets (also taking into account the var= parameter),
                     JOIN type is according to parameter keywords (case-insensitive): 
                      #autoFULLEQ#  : FULL JOIN
                      #autoLEFTEQ#  : LEFT JOIN
                      #autoRIGHTEQ# : RIGHT JOIN
                      #autoINNEREQ# : INNER JOIN
                     Note: The corresponding ON clause(s) will be generated according to the variables in common between the (pairs of) datasets.
                     If no variables are in common the ON clause will specify the condition : 1 EQ 1 (always true) which in case of a FULL JOIN
                     should result in a JOIN equivalent to a cartesian product or CROSS JOIN.
                     It is also possible to generate a FROM clause (without the FROM keyword) from a separate call to %VARLIST, where the type
                     of join is specified as the full contnets of the pattern= parameter (#autoFULLEQ#, #autoLEFTEQ#, #autoRIGHTEQ# or #autoINNEREQ#).

   Additional parameters to be implemented:

     ORDER        : variables are ordered according to their position
                    - in VAR parameter, if specified litterally
                    - in the dataset where they are found first, according to dataset position in DATA parameter,
                      if specified using special values #default#, #all#, #num#, #char#, /<pattern>/

     UNIQUE       : If Y, prevents a variable name to appear in more than one PATTERN in the final resulting
                    string (although a single pattern could contain multiple occurrences of the variable name)

                    values: Y (Default), N



 *---------------------------------------------------------------------------------------*/

%macro varlist(data=#default#, var=#default#, pattern=#var#, sep=%str( ), unique=Y, order=data, pretext=, posttext=, from=);
   %local i j k l p varlist_o varlist varlist2 dslist ds_i var_i var luvar type ds dsid nvars prx prx2 prx3
          num char all lookupvars lookupvars2 selectvars oper dsoper alldsvars dsvar dsvarlist dsvarlist2
          patternin patternout dsn dsa refvarattr refauto sepout 
          vnum vlen vtyp vfmt vinfmt vlab vlabq vlenlabfmt vlenlabsrcfmt vfmtdef vinfmtdef
          dsnl dsal autocoal autocoalsrc autoeq item root suffix step first last
          join_type prev_datasets
          ;
   %if %symexist(debug)=0 %then %local debug;
   /*
   %let i=%index(%upcase(&data), #DEFAULT#);
   %let l=%length(&data);
   %if (%upcase(&debug)=Y) %then %put i=&i l=&l;
   %if (&i = 1) %then %let data=&syslast %substr(&data%str(  ), 10);
   %else %if &i >1 %then %let data=%substr(&data%str(  ), 1, %eval(&i-1))%str(&syslast)%substr(&data%str(  ), %eval(&i+10));
   */
   %let dslist=&data;   %*- store contents of DATA parameter -*;
   %let varlist_o=&var; %*- store contents of VAR parameter -*;

   %*- Initialize list of variables to be returned -*;
   %let varlist=;
   %let alldsvars=;
   %let dsvarlist=;

   %*- Convert keywords to lowercase in parameters PATTERN and SEP -*;
      %let prx=%sysfunc(prxparse(s/(#\S+#)/\L$1/i));
      %if (%upcase(&debug)=Y) %then %put Original PATTERN: &pattern;
      %let pattern=%qsysfunc(prxchange(&prx, -1, &pattern));
      %if (%upcase(&debug)=Y) %then %put Keywords converted to lowercase in parameter PATTERN: &pattern;
      %if (%upcase(&debug)=Y) %then %put Original SEP: &sep;
      %let sep=%qsysfunc(prxchange(&prx, -1, &sep));
      %if (%upcase(&debug)=Y) %then %put Keywords converted to lowercase in parameter SEP: &sep;
      %syscall prxfree(prx);

%IF   (%qupcase(&pattern)=#AUTOFULLEQ#) 
   or (%qupcase(&pattern)=#AUTOLEFTEQ#)
   or (%qupcase(&pattern)=#AUTORIGHTEQ#)
   or (%qupcase(&pattern)=#AUTOINNEREQ#)
%THEN %DO;
   %*- Specific processing of PATTERNs: #autoFULLEQ#, #autoLEFTEQ#, #autoRIGHTEQ#, #autoINNEREQ# -*;
   %let join_type=%sysfunc(tranwrd(%sysfunc(tranwrd(%qupcase(&pattern), #AUTO, %str( ))), EQ#, %str( )));
   %if (%upcase(&debug)=Y) %then %put join_type=&join_type;

   %let i=1;
   %let prev_datasets=;
   %do %while(%scan(&data, &i, %str( ))^=);
      %let dsn=%scan(&data, &i, %str( ));
      %if (%index(&dsn, #) EQ 0) %then %do;
         %if (&i>1) %then %do;
           %let varlist=&varlist &join_type JOIN;
         %end;
           %let varlist=&varlist %sysfunc(tranwrd(&dsn, :, %str( as )));
         %if (&i>1) %then %do;
           %let varlist2=%VARLIST(data=&prev_datasets #INTERSECT# &dsn, var=&varlist_o, sep=#and#, pattern=#autoEQ#);
           %if %length(&varlist2)>0 %then %do;
              %let varlist=&varlist ON &varlist2;
           %end; %else %do;
              %let varlist=&varlist ON (1 EQ 1);  %*- No variables in common, so try the equivalent of a Cartesian Product or CROSS JOIN -*;
           %end;
         %end;
         %let prev_datasets=&prev_datasets &dsn;
      %end;
      %let i=%eval(&i+1);
      %if (%upcase(&debug)=Y) %then %do;
         %put ## SPECIFIC PROCESSING STEP &i, dsn=&dsn ##;
         %put varlist=&varlist;
      %end;
   %end;


%END; %ELSE %DO;
   %*- General processing of all other cases -*;

   %if (%qupcase(&data)=%quote(#DEFAULT#)) %then %do;
      %*- Use dataset &syslast if VAR parameter contains #default#, #all#, #num#, #char#, #<i>#, #<i>-<j># or /<pattern>/ -*;
      %*let prx=%sysfunc(prxparse(/\s(#default#|#all#|#num#|#char#|\w+:|#\d+#|#\d+-\d+#|\/.*\/)\s/i));
      %let prx=%sysfunc(prxparse(/\s(#default#|#all#|#num#|#char#|\w+--?\w+|\w+:\w*|\w*:\w+|#\d+#|#\d+-\d+#|\/.*\/)\s/i));
      %if %sysfunc(prxmatch(&prx, %str( )%superq(var)%str( ))) %then %let dslist=&syslast;
      %syscall prxfree(prx);
   %end; 
   %if (%upcase(&debug)=Y) %then %put %str(Not)ice: (&sysmacroname): DATA evaluated as: &dslist. .;

   %*- loop across datasets -*;
   %let dsoper = #union#;
   %let ds_i=1;
   %do %while(%length(%scan(&dslist, &ds_i, %str( )))>0);
      %let ds=%scan(&dslist, &ds_i, %str( ));
      %if (%qsubstr(&ds.#, 1, 1)=%quote(#)) and (%qupcase(&ds)^=%quote(#DEFAULT#)) %then %do;
         %*- Not a dataset but a dataset operator -*; 
         %let dsoper=&ds;
         %if (%upcase(&debug)=Y) %then %put :: found dsoper = &dsoper ::;
      %end; %else %do;
         %if (%upcase(&debug)=Y) %then %put :: found ds = &ds ::;

         %*- Retrieve all, num and char variables in dataset -*;
         %LET all=;
         %LET num=;
         %LET char=;
         %let dsid=0;
         %let nvars=0;
         %if (%qupcase(&ds)^=%quote(#DEFAULT#)) %then %let dsid=%sysfunc(open(%scan(&ds, 1, :))); %* open current dataset (for read) *;
         %IF &dsid %THEN %DO; %* dataset opened successfully *;
               %LET nvars=%SYSFUNC(attrn(&dsid, nvars));  %* number of variables in current dataset *;
               %DO var_i=1 %TO &nvars; %* loop across all variables in current dataset *;
                  %LET var=%SYSFUNC(varname(&dsid, &var_i));
                  %LET type=%SYSFUNC(vartype(&dsid, &var_i));
                  %LET all=&all &var;                                    %* all variables in current dataset *;
                  %IF       &type = C %THEN %LET char=&char &var;        %* character variables in current dataset *;
                  %ELSE %IF &type = N %THEN %LET num=&num &var;          %* numerical variables in current dataset *;
                  %ELSE %PUT Unknown variable type: ds=&ds var=&var type=&type;
               %END;
               %LET rc=%sysfunc(close(&dsid));
         %END;
         %IF (&dsid>0) or (%qupcase(&ds)=%quote(#DEFAULT#)) %THEN %DO; 
             
               %let lookupvars = &varlist_o;

               %*- resolve patterns /<..>/ in list of variables to look up -*;
               %let i=1;
               %let lookupvars2=;
               %let prx=%sysfunc(prxparse(/^\/.*\/i?$/i));
               %do %while(%length(%scan(&lookupvars, &i, %str( )))>0);
                   %let luvar=%scan(&lookupvars, &i, %str( ));
                   %if %sysfunc(prxmatch(&prx, &luvar)) %then %do; 
                       %let prx2=%sysfunc(prxparse(&luvar));
                       %*- &luvar identified as a pattern -> look for matching variables in dataset -*;
                       %let matchvars=;
                       %let var_i=1;
                       %do %while(%length(%scan(&all, &var_i, %str( )))>0);
                          %LET var=%scan(&all, &var_i, %str( ));
                          %if %sysfunc(prxmatch(&prx2, %superq(var))) %then %let matchvars=&matchvars &var;
                          %let var_i=%eval(&var_i+1); %*- loop to next item in list &all -*;
                       %end;
                       %syscall prxfree(prx2);
                       %if (%upcase(&debug)=Y) and (%length(&matchvars)=0) %then %put Identified pattern: &luvar has no matching variables in &ds..;
                       %let lookupvars2=&lookupvars2 &matchvars;
                   %end; %else %do;
                       %let lookupvars2=&lookupvars2 &luvar;
                   %end;
                   %let i=%eval(&i+1);
               %end;
               %syscall prxfree(prx);
               %*- replace with updated lookup variables list -*;
               %let lookupvars=&lookupvars2;
               %let lookupvars2=;
               %if (%upcase(&debug)=Y) %then %do;
                  %put After resolving patterns /<..>/, lookup variables list = &lookupvars;
               %end;

               %*- resolve <root>: in list of variables to look up -*;
               %*- resolve :<suffix> in list of variables to look up -*;
               %*- resolve <root>:<suffix> in list of variables to look up -*;
               %let i=1;
               %let lookupvars2=;
               %let prx=%sysfunc(prxparse(/^(\w+:\w*|\w*:\w+)$/));
               %do %while(%length(%scan(&lookupvars, &i, %str( )))>0);
                   %let luvar=%scan(&lookupvars, &i, %str( ));
                   %if %sysfunc(prxmatch(&prx, &luvar)) %then %do; 
                       %let root=%qscan(%str( )&luvar%str( ), 1, %quote(:));
                       %let suffix=%qscan(%str( )&luvar%str( ), 2, %quote(:));
                       %let matchvars=;
                       %let var_i=1;
                       %do %while(%length(%scan(&all, &var_i, %str( )))>0);
                          %LET var=%qscan(&all, &var_i, %str( ));
                          %if (%sysfunc(index(%str( )%upcase(&var), %qupcase(&root)))=1) 
                          and (%sysfunc(index(%str( )%upcase(&var)%str( ), %qupcase(&suffix)))>=1) 
                             %then %let matchvars=&matchvars &var;
                          %let var_i=%eval(&var_i+1); %*- loop to next item in list &all -*;
                       %end;
                       %if (%upcase(&debug)=Y) and (%length(&matchvars)=0) %then %put Resolved &luvar (root=&root suffix=&suffix) as &matchvars in dataset &ds..;
                       %let lookupvars2=&lookupvars2 &matchvars;
                   %end; %else %do;
                       %let lookupvars2=&lookupvars2 &luvar;
                   %end;
                   %let i=%eval(&i+1);
               %end;
               %syscall prxfree(prx);
               %*- replace with updated lookup variables list -*;
               %let lookupvars=&lookupvars2;
               %let lookupvars2=;
               %if (%upcase(&debug)=Y) %then %do;
                  %put After resolving <root>:, :<suffix> and <root>:<suffix>, lookup variables list = &lookupvars;
               %end;

               %*- resolve <varX>-<varY> in list of variables to look up -*;
               %let i=1;
               %let lookupvars2=;
               %let prx=%sysfunc(prxparse(/^\w+-\w+$/));
               %do %while(%length(%scan(&lookupvars, &i, %str( )))>0);
                   %let luvar=%scan(&lookupvars, &i, %str( ));
                   %if %sysfunc(prxmatch(&prx, &luvar)) %then %do; 
                       %let first=%qupcase(%qscan(&luvar, 1, %quote(-)));
                       %let last=%qupcase(%qscan(&luvar, 2, %quote(-)));
                       %if (&first > &last) %then %do;
                          %let first=%qupcase(%qscan(&luvar, 2, %quote(-)));
                          %let last=%qupcase(%qscan(&luvar, 1, %quote(-)));
                       %end; 
                       %let matchvars=;
                       %let var_i=1;
                       %do %while(%length(%scan(&all, &var_i, %str( )))>0);
                          %LET var=%qscan(&all, &var_i, %str( ));
                          %if (&first <= %qupcase(&var)) 
                          and (%qupcase(&var) <= &last) 
                             %then %let matchvars=&matchvars &var;
                          %let var_i=%eval(&var_i+1); %*- loop to next item in list &all -*;
                       %end;
                       %if (%upcase(&debug)=Y) and (%length(&matchvars)=0) %then %put Resolved &luvar (first=&first last=&last) as &matchvars in dataset &ds..;
                       %let lookupvars2=&lookupvars2 &matchvars;
                   %end; %else %do;
                       %let lookupvars2=&lookupvars2 &luvar;
                   %end;
                   %let i=%eval(&i+1);
               %end;
               %syscall prxfree(prx);
               %*- replace with updated lookup variables list -*;
               %let lookupvars=&lookupvars2;
               %let lookupvars2=;
               %if (%upcase(&debug)=Y) %then %do;
                  %put After resolving <varX>-<varY>, lookup variables list = &lookupvars;
               %end;

               %*- resolve <varX>--<varY> in list of variables to look up -*;
               %let i=1;
               %let lookupvars2=;
               %let prx=%sysfunc(prxparse(/^\w+--\w+$/));
               %do %while(%length(%scan(&lookupvars, &i, %str( )))>0);
                   %let luvar=%scan(&lookupvars, &i, %str( ));
                   %if %sysfunc(prxmatch(&prx, &luvar)) %then %do; 
                       %let first=%sysfunc(index(%str( )%qupcase(&all)%str( ), %str( )%qupcase(%qscan(&luvar, 1, %quote(-)))%str( )));
                       %let last=%sysfunc(index(%str( )%qupcase(&all)%str( ), %str( )%qupcase(%qscan(&luvar, 2, %quote(-)))%str( )));
                       %if (&first > &last) %then %do;
                          %let temp=&first;
                          %let first=&last;
                          %let last=&temp;
                       %end; 
                       %let matchvars=;
                       %let var_i=1;
                       %if (&first >0) %then %do %while(%length(%scan(&all, &var_i, %str( )))>0);
                          %LET var=%qscan(&all, &var_i, %str( ));
                          %let p = %sysfunc(index(%str( )%qupcase(&all)%str( ), %str( )%qupcase(&var)%str( )));
                          %if (&first <= &p) and (&p <= &last) 
                             %then %let matchvars=&matchvars &var;
                          %let var_i=%eval(&var_i+1); %*- loop to next item in list &all -*;
                       %end;
                       %if (%upcase(&debug)=Y) and (%length(&matchvars)=0) %then %put Resolved &luvar (first=&first last=&last) as &matchvars in dataset &ds..;
                       %let lookupvars2=&lookupvars2 &matchvars;
                   %end; %else %do;
                       %let lookupvars2=&lookupvars2 &luvar;
                   %end;
                   %let i=%eval(&i+1);
               %end;
               %syscall prxfree(prx);
               %*- replace with updated lookup variables list -*;
               %let lookupvars=&lookupvars2;
               %let lookupvars2=;
               %if (%upcase(&debug)=Y) %then %do;
                  %put After resolving <varX>--<varY>, lookup variables list = &lookupvars;
               %end;

               %*- resolve remaining special names in list of variables to look up -*;
               %if (%qupcase(&varlist_o)=%quote(#DEFAULT#)) %then %let lookupvars=&all;

               %*- resolve #<i># and #<i>-<j># -*;
               %let prx=%sysfunc(prxparse(/\#(\d+|\d+-\d+)\#/i));
               %do %while(%sysfunc(prxmatch(&prx, %superq(lookupvars))) >0);
                   %let p=%sysfunc(prxmatch(&prx, %superq(lookupvars)));
                   %let item=%qscan(%qsubstr(%superq(lookupvars), &p), 1, %str( ));
                   %let lookupvars2=;
                   %let i=%scan(&item, 1, %bquote(-#));
                   %let j=%scan(&item, -1, %bquote(-#));
                   %if (%upcase(&debug)=Y) %then %put Resolving &item as variables numbered from &i to &j;
                   %if (&i <= &j) 
                       %then %let step=1;
                       %else %let step=-1;
                   %do k = &i %to &j %by &step;
                      %let lookupvars2=&lookupvars2 %scan(&all, &k, %str( ));
                   %end;
                   %if (&p>1) 
                       %then %let lookupvars=%qsubstr(%superq(lookupvars), 1, %eval(&p-1))%sysfunc(tranwrd(|%qsubstr(%superq(lookupvars), &p), |&item, &lookupvars2));
                       %else %let lookupvars=%sysfunc(tranwrd(|&lookupvars, |&item, &lookupvars2));
                  %if (%upcase(&debug)=Y) %then %do;
                     %put After resolving &item., lookup variables list = &lookupvars;
                  %end;
               %end;
               %syscall prxfree(prx);
               %*- resolve <var><i>-<j> -*;
               %let prx=%sysfunc(prxparse(/\w*\D\d+-\d+/));
               %let prx2=%sysfunc(prxparse(s/\w*\D(\d+-\d+)/\1/));
               %let prx3=%sysfunc(prxparse(s/(\w*\D)\d+-\d+/\1/));
               %do %while(%sysfunc(prxmatch(&prx, %superq(lookupvars))) >0);
                   %let p=%sysfunc(prxmatch(&prx, %superq(lookupvars)));
                   %let item=%qscan(%qsubstr(%superq(lookupvars), &p), 1, %str( ));
                   %let root=%qsysfunc(prxchange(&prx3, -1, &item));
                   %let suffix=%qsysfunc(prxchange(&prx2, -1, &item));
                   %let lookupvars2=;
                   %let i=%scan(&suffix, 1, %bquote(-));
                   %let j=%scan(&suffix, 2, %bquote(-));
                   %let l=%length(&i);
                   %if (%length(&j) > &l) %then %let l=%length(&j);
                   %if (%length(&i) > 1) and (%substr(&i, 1, 1)=0) or (%length(&j) > 1) and (%substr(&j, 1, 1)=0) %then %do;
                       %let fmt=Z&l..;
                       %if (%upcase(&debug)=Y) %then %put item=&item root=&root suffix=&suffix i=&i j=&j fmt=&fmt;
                       %if (%upcase(&debug)=Y) %then %put Resolving &item as variables numbered from &root.%sysfunc(putn(&i., &fmt.)) to &root.%sysfunc(putn(&j., &fmt.));
                       %if (&i <= &j) 
                           %then %let step=1;
                           %else %let step=-1;
                       %do k = &i %to &j %by &step;
                          %let lookupvars2=&lookupvars2 &root.%sysfunc(putn(&k., &fmt.));
                       %end;
                   %end; %else %do;
                       %if (%upcase(&debug)=Y) %then %put item=&item root=&root suffix=&suffix i=&i j=&j;
                       %if (%upcase(&debug)=Y) %then %put Resolving &item as variables numbered from &root.&i to &root.&j;
                       %if (&i <= &j) 
                           %then %let step=1;
                           %else %let step=-1;
                       %do k = &i %to &j %by &step;
                          %let lookupvars2=&lookupvars2 &root.&k.;
                       %end;
                   %end;
                   %if (%upcase(&debug)=Y) %then %put lookupvars2=&lookupvars2;
                   %if (&p>1) 
                       %then %let lookupvars=%qsubstr(%superq(lookupvars), 1, %eval(&p-1))%sysfunc(tranwrd(|%qsubstr(%superq(lookupvars), &p), |&item, &lookupvars2));
                       %else %let lookupvars=%sysfunc(tranwrd(|&lookupvars, |&item, &lookupvars2));
                  %if (%upcase(&debug)=Y) %then %do;
                     %put After resolving &item., lookup variables list = &lookupvars;
                  %end;
               %end;
               %syscall prxfree(prx);
               %syscall prxfree(prx2);
               %syscall prxfree(prx3);

               %let lookupvars = %sysfunc(tranwrd(&lookupvars, #all#, &all));
               %let lookupvars = %sysfunc(tranwrd(&lookupvars, #num#, &num));
               %let lookupvars = %sysfunc(tranwrd(&lookupvars, #char#, &char));
               %if (%upcase(&debug)=Y) %then %do;
                  %put After resolving remaining special names, lookup variables list = &lookupvars;
               %end;

               %*- Selecting variables from list that are found in current dataset (if they exist) -*;
               %if (%qupcase(&ds)^=%quote(#DEFAULT#)) %then %do;
                  %if (%upcase(&debug)=Y) %then %do;
                     %put Selecting variables from &ds (if they exist)...; 
                  %end;
                  %let lookupvars2=;
                  %let i=1;
                  %do %while(%length(%scan(&lookupvars, &i, %str( )))>0);
                      %let luvar=%scan(&lookupvars, &i, %str( ));
                      %if (%qsubstr(&luvar, 1, 1)=%quote(#)) %then %do;
                          %let lookupvars2=&lookupvars2 &luvar;
                      %end; %else %do;
                         %let j=%index(%str( )%upcase(&all)%str( ), %str( )%upcase(&luvar)%str( ));
                         %let l=%length(&luvar);
                         %if (&j > 0) %then %let lookupvars2=&lookupvars2.%substr(%str( )&all%str( ), &j, %eval(&l+2));
                      %end;
                      %let i=%eval(&i+1);
                  %end;
                  %if (%upcase(&debug)=Y) %then %do;
                     %put Updated variables list according to presence in dataset &ds = &lookupvars2;
                  %end;
                  %let lookupvars=&lookupvars2;
                  %let lookupvars2=;
               %end;

               %*- Process variables according to variable operators -*;
               %let oper=;
               %let leftvars=;
               %let rightvars=;
               %let i=1;
               %let done=0;
               %do %until(&done=1);
                   %let luvar=%scan(&lookupvars, &i, %str( ));
                   %*if (%upcase(&debug)=Y) %then %put i=&i oper=&oper luvar=&luvar leftvars=&leftvars rightvars=&rightvars;
                   %if (%substr(&luvar.#,1,1)=#) %then %do;
                       %if (%quote(&oper)=) %then %do;
                           %let oper=&luvar;
                       %end; %else %do;
                          %if (%upcase(&debug)=Y) and (%quote(&oper)^=) %then %do;
                             %put Processing Variables Operator: &oper. ; %put %str(   ) leftvars=&leftvars; %put %str(   ) rightvars=&rightvars;
                          %end;
                          %if (%quote(&oper)=) %then %do;
                               %*- end of varlist reach with no operator to process: nothing to do -*;
                           %end; %else %if (%qupcase(&oper)=%quote(#EXCEPT#)) or (%qupcase(&oper)=%quote(#NOT#)) %then %do;
                              %let selectvars=;
                              %let j=1;
                              %do %while(%length(%scan(&leftvars, &j, %str( )))>0);
                                  %let luvar=%scan(&leftvars, &j, %str( ));
                                  %if (%index(%str( )%upcase(&rightvars)%str( ), %str( )%upcase(&luvar)%str( )) = 0)
                                     %then %let selectvars=&selectvars &luvar;
                                  %let j=%eval(&j+1);
                              %end;
                              %let leftvars=&selectvars;
                              %let rightvars=;
                              %let selectvars=;
                              %if (%upcase(&debug)=Y) %then %do;
                                 %put Finished processing Variables Operator: &oper. ;  %put %str(   ) leftvars=&leftvars; %put %str(   ) rightvars=&rightvars;
                              %end;
                              %let oper=;
                           %end; %else %if (%qupcase(&oper)=%quote(#INTERSECT#)) or (%qupcase(&oper)=%quote(#AND#)) %then %do;
                              %let selectvars=;
                              %let j=1;
                              %do %while(%length(%scan(&leftvars, &j, %str( )))>0);
                                  %let luvar=%scan(&leftvars, &j, %str( ));
                                  %if (%index(%str( )%upcase(&rightvars)%str( ), %str( )%upcase(&luvar)%str( )) > 0)
                                     %then %let selectvars=&selectvars &luvar;
                                  %let j=%eval(&j+1);
                              %end;
                              %let leftvars=&selectvars;
                              %let rightvars=;
                              %let selectvars=;
                              %if (%upcase(&debug)=Y) %then %do;
                                 %put Finished processing Variables Operator: &oper. ; %put %str(   ) leftvars=&leftvars; %put %str(   ) rightvars=&rightvars;
                              %end;
                              %let oper=;
                           %end; %else %if (%qupcase(&oper)=%quote(#XOR#)) %then %do;
                              %let selectvars=;
                              %let j=1;
                              %do %while(%length(%scan(&leftvars &rightvars, &j, %str( )))>0);
                                  %let luvar=%scan(&leftvars &rightvars, &j, %str( ));
                                  %if ( (%index(%str( )%upcase(&leftvars)%str( ), %str( )%upcase(&luvar)%str( )) > 0)
                                       +(%index(%str( )%upcase(&rightvars)%str( ), %str( )%upcase(&luvar)%str( )) > 0)
                                       eq 1)
                                       and (%index(%str( )%upcase(&selectvars)%str( ), %str( )%upcase(&luvar)%str( )) = 0)
                                     %then %let selectvars=&selectvars &luvar;
                                  %let j=%eval(&j+1);
                              %end;
                              %let leftvars=&selectvars;
                              %let rightvars=;
                              %let selectvars=;
                              %if (%upcase(&debug)=Y) %then %do;
                                 %put Finished processing Variables Operator: &oper. ; %put %str(   ) leftvars=&leftvars; %put %str(   ) rightvars=&rightvars;
                              %end;
                              %let oper=;
                           %end; %else %if (%qupcase(&oper)=%quote(#OR#)) %then %do;
                              %let selectvars=;
                              %let j=1;
                              %do %while(%length(%scan(&leftvars &rightvars, &j, %str( )))>0);
                                  %let luvar=%scan(&leftvars &rightvars, &j, %str( ));
                                  %if (%index(%str( )%upcase(&selectvars)%str( ), %str( )%upcase(&luvar)%str( )) = 0)
                                     %then %let selectvars=&selectvars &luvar;
                                  %let j=%eval(&j+1);
                              %end;
                              %let leftvars=&selectvars;
                              %let rightvars=;
                              %let selectvars=;
                              %if (%upcase(&debug)=Y) %then %do;
                                 %put Finished processing Variables Operator: &oper. ; %put %str(   ) leftvars=&leftvars; %put %str(   ) rightvars=&rightvars;
                              %end;
                              %let oper=;
                           %end;
                           %else %do;
                              %PUT %STR(WAR)NING: (&sysmacroname): Invalid Variables Operator: &oper.;
                           %end;
                       %end;
                   %end; %else %do;
                       %if (%quote(&oper)=) %then %do;
                          %let leftvars=&leftvars &luvar;
                       %end; %else %do;
                          %let rightvars=&rightvars &luvar;
                       %end;
                   %end;
                   %if (%length(%scan(&lookupvars, &i, %str( )))=0) %then %let done=1;
                   %let i=%eval(&i+1);
               %end;
               %let lookupvars2=&leftvars;
               %if (%upcase(&debug)=Y) %then %do;
                  %put After processing variable operators, lookup variables list = &lookupvars2;
                  %put %str(  )leftvars=&leftvars;
                  %put %str(  )rightvars=&rightvars;
               %end;

               %*- Selecting variables from current dataset (if they exist) or from the VAR list (if not found in the dataset)-*;
               %if (%qupcase(&ds)=%quote(#DEFAULT#)) %then %do;
                  %*- Purely processing list ov variables and operators in VAR parameter,
                      with no dataset to lookup variables
                      so dsselectvars will not have any dataset information -*;
                  %let dsselectvars=&lookupvars2;
                  %if (%upcase(&debug)=Y) %then %do;
                     %put ==>> Selected dataset.variables (no dataset to look up) = &dsselectvars;
                  %end;
               %end; %else %do;
                  %*- Store variables with dataset information in dsselectvars -*;
                  %let dsselectvars=;
                  %let i=1;
                  %do %while(%length(%scan(&lookupvars2, &i, %str( )))>0);
                      %let luvar=%scan(&lookupvars2, &i, %str( ));
                      %let dsselectvars=&dsselectvars &ds..&luvar;
                      %let i=%eval(&i+1);
                  %end;
                  %if (%upcase(&debug)=Y) %then %do;
                     %put ==>> Selected dataset.variables from &ds = &dsselectvars;
                  %end;
               %end;
               %let selectvars=&lookupvars2;

         %END; %ELSE %DO;  /* End of "%IF (&dsid>0) or (%qupcase(&ds)=%quote(#DEFAULT#)) %THEN %DO" */

              %PUT %STR(WAR)NING: (&sysmacroname): Could not open &ds: ;
              %PUT %STR(         )%SYSFUNC(sysmsg());

         %END;

         %if (%upcase(&debug)=Y) %then %put :: done processing ds = &ds ::;

         %if (%upcase(&debug)=Y) %then %put :: processing dsoper = &dsoper ::;


         %if (%qupcase(&dsoper)=%quote(#OR#)) or (%qupcase(&dsoper)=%quote(#UNION#)) %then %do;
             %*- add selected variables from current dataset to the list of variables to be returned, if not yet present there -*;
               %let i=1;
               %do %while(%length(%scan(&selectvars, &i, %str( )))>0);
                   %let var=%scan(&selectvars, &i, %str( ));
                   %if (%index(%str( )%upcase(&varlist)%str( ), %str( )%upcase(&var)%str( )) = 0) %then %do;
                      %let varlist=&varlist &var;
                      %let dsvarlist=&dsvarlist &ds..&var;
                   %end; %else %do;
                      %*- keep track of all datasets containing the variable &var -*;
                      %let p=0;
                      %let dsvar=;
                      %let prx=%sysfunc(prxparse(/\S+\.&var /i));
                      %if (&prx > 0) %then %let p=%sysfunc(prxmatch(&prx, %str( )&dsvarlist%str( )));
                      %if (&p > 0) %then %let dsvar=%qscan(%qsubstr(%str( )&dsvarlist%str( ), &p), 1, %str( ));
                      %if (%upcase(&debug)=Y) %then %put var=&var previously found? prx=&prx p=&p dsvar=&dsvar;
                      %if (&prx > 0) %then %syscall prxfree(prx);
                      %if (%length(&dsvar)>0) %then %do;
                          %let newdsvar=%sysfunc(tranwrd(&dsvar, .%scan(&dsvar, -1, .), |&ds..%scan(&dsvar, -1, .)));
                          %let dsvarlist=%sysfunc(tranwrd(%str( )&dsvarlist%str( ), %str( )&dsvar%str( ), %str( )&newdsvar%str( )));
                          %if (%upcase(&debug)=Y) %then %do;
                              %put newdsvar=&newdsvar;
                              %put New dsvarlist=&dsvarlist;
                          %end;
                      %end;
                   %end;
                   %let i=%eval(&i+1);
               %end;

         %end; %else %if (%qupcase(&dsoper)=%quote(#NOT#)) or (%qupcase(&dsoper)=%quote(#EXCEPT#)) %then %do;
             %*- remove selected variables from current dataset from the list of variables to be returned (from previous dataset), if present there -*;
               %let varlist2=;
               %let dsvarlist2=;
               %let i=1;
               %do %while(%length(%scan(&varlist, &i, %str( )))>0);
                   %let var=%scan(&varlist, &i, %str( ));
                   %let dsvar=%scan(&dsvarlist, &i, %str( ));
                   %if (%index(%str( )%upcase(&selectvars)%str( ), %str( )%upcase(&var)%str( )) = 0) %then %do;
                      %let varlist2=&varlist2 &var;
                      %let dsvarlist2=&dsvarlist2 &dsvar;
                   %end;
                   %let i=%eval(&i+1);
               %end;
               %let varlist=&varlist2;
               %let varlist2=;
               %let dsvarlist=&dsvarlist2;
               %let dsvarlist2=;

         %end; %else %if (%qupcase(&dsoper)=%quote(#AND#)) or (%qupcase(&dsoper)=%quote(#INTERSECT#)) %then %do;
             %*- keep variables that are both selected from current dataset and from previous dataset -*;
               %let varlist2=;
               %let dsvarlist2=;
               %let i=1;
               %do %while(%length(%scan(&varlist, &i, %str( )))>0);
                   %let var=%scan(&varlist, &i, %str( ));
                   %let dsvar=%scan(&dsvarlist, &i, %str( ));
                   %if (%index(%str( )%upcase(&selectvars)%str( ), %str( )%upcase(&var)%str( )) > 0) %then %do;
                      %let varlist2=&varlist2 &var;
                      %let newdsvar=%sysfunc(tranwrd(&dsvar, .%scan(&dsvar, -1, .), |&ds..%scan(&dsvar, -1, .)));
                      %*let dsvarlist2=&dsvarlist2 &dsvar;
                      %let dsvarlist2=&dsvarlist2 &newdsvar;
                      %if (%upcase(&debug)=Y) %then %do;
                          %put newdsvar=&newdsvar;
                          %put New dsvarlist2=&dsvarlist2;
                      %end;
                   %end;
                   %let i=%eval(&i+1);
               %end;
               %let varlist=&varlist2;
               %let varlist2=;
               %let dsvarlist=&dsvarlist2;
               %let dsvarlist2=;

         %end; %else %if (%qupcase(&dsoper)=%quote(#XOR#)) %then %do;
             %*- keep variables that are either selected from current dataset or from previous dataset, but not from both -*;
               %let varlist2=;
               %let dsvarlist2=;
               %let i=1;
               %do %while(%length(%scan(&varlist &selectvars, &i, %str( )))>0);
                   %let var=%scan(&varlist &selectvars, &i, %str( ));
                   %let dsvar=%scan(&dsvarlist &dsselectvars, &i, %str( ));
                   %if ((%index(%str( )%upcase(&selectvars)%str( ), %str( )%upcase(&var)%str( )) > 0)
                       +(%index(%str( )%upcase(&varlist)%str( ), %str( )%upcase(&var)%str( )) > 0)
                       eq 1)
                      %then %do;
                          %let varlist2=&varlist2 &var;
                          %let dsvarlist2=&dsvarlist2 &dsvar;
                      %end;
                   %let i=%eval(&i+1);
               %end;
               %let varlist=&varlist2;
               %let varlist2=;
               %let dsvarlist=&dsvarlist2;
               %let dsvarlist2=;

         %end; %else %do;
             %PUT %STR(WAR)NING: (&sysmacroname): Invalid Dataset Operator: &dsoper.;

         %end;

         %*- if next dataset is not prceded by a dataset operator, consider <space> operator whichis equivalent to #UNION# -*;
         %let dsoper = #UNION#; 

      %end;

      %let ds_i=%eval(&ds_i+1); %*- loop: next dataset -*;
   %end;

   %if (%upcase(&debug)=Y) %then %put ==>> dsvarlist=&dsvarlist;

   %*- format list of variables according to PATTERN, separated by SEP -*;
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#semicolon#), %qsubstr(';', 2, 1)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#semicol#), %qsubstr(';', 2, 1)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#scol#), %qsubstr(';', 2, 1)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#sc#), %qsubstr(';', 2, 1)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#comma#), %qsubstr(',', 2, 1)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#coma#), %qsubstr(',', 2, 1))); %*- allow for misspelling -*;
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#c#), %qsubstr(',', 2, 1))); %*- allow for abbreviation -*;
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#space#), %qsubstr(' ', 2, 1)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#s#), %qsubstr(' ', 2, 1)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#commaspace#), %qsubstr(', ', 2, 2)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#comaspace#), %qsubstr(', ', 2, 2)));  %*- allow for misspelling -*;
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#cs#), %qsubstr(', ', 2, 2)));  %*- allow for abbreviation -*;
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#semicolonspace#), %qsubstr('; ', 2, 2)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#semicolons#), %qsubstr('; ', 2, 2)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#semicolspace#), %qsubstr('; ', 2, 2)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#semicols#), %qsubstr('; ', 2, 2)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#scols#), %qsubstr('; ', 2, 2)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#scs#), %qsubstr('; ', 2, 2)));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#and#), %str( and )));
   %let sep=%qsysfunc(tranwrd(&sep, %quote(#or#), %str( or )));
   %if (%upcase(&debug)=Y) %then %put sep = &sep;
   %let varlist2=;
   %let i=1;
   %let sepout=; %*- no separator before first item -*;
   %let patternin=&pattern;
   %if (%upcase(&debug)=Y) %then %put patternin = &patternin;
   %*- Expand patterns as follows
   #vareq-<w>-<x>[-<y>[-<z>]]# 
      as:
         (<w>.#var#=<x>.#var# [ and <w>.#var#=<y>.#var# [ and <w>.#var#=.#var# ]] )

   #coal-<w>-<x>[-<y>[-<z>]]# 
      as:
         COALESCE(<w>.#var#, <x>.#var#[, <y>.#var# [, <z>.#var# ]]) as #vlenlabfmt#
   -*;
   %let prx=%sysfunc(prxparse(%bquote(s/#vareq-(\w+)-(\w+)#/(\1.#var#=\2.#var#)/i)));
   %let patternin=%qsysfunc(prxchange(&prx, -1, &patternin));
   %syscall prxfree(prx);
   %if (%upcase(&debug)=Y) %then %put patternin = &patternin;
   %let prx=%sysfunc(prxparse(%bquote(s/#vareq-(\w+)-(\w+)-(\w+)#/(\1.#var#=\2.#var# and \1.#var#=\3.#var#)/i)));
   %let patternin=%qsysfunc(prxchange(&prx, -1, &patternin));
   %syscall prxfree(prx);
   %if (%upcase(&debug)=Y) %then %put patternin = &patternin;
   %let prx=%sysfunc(prxparse(%bquote(s/#vareq-(\w+)-(\w+)-(\w+)-(\w+)#/(\1.#var#=\2.#var# and \1.#var#=\3.#var# and \1.#var#=\4.#var#)/i)));
   %let patternin=%qsysfunc(prxchange(&prx, -1, &patternin));
   %syscall prxfree(prx);
   %if (%upcase(&debug)=Y) %then %put patternin = &patternin;

   %let prx=%sysfunc(prxparse(%bquote(s/#coal-(\w+)-(\w+)#/COALESCE(\1.#var#, \2.#var#) as #vlenlabfmt#/i)));
   %let patternin=%qsysfunc(prxchange(&prx, -1, &patternin));
   %syscall prxfree(prx);
   %if (%upcase(&debug)=Y) %then %put patternin = &patternin;
   %let prx=%sysfunc(prxparse(%bquote(s/#coal-(\w+)-(\w+)-(\w+)#/COALESCE(\1.#var#, \2.#var#, \3.#var#) as #vlenlabfmt#/i)));
   %let patternin=%qsysfunc(prxchange(&prx, -1, &patternin));
   %syscall prxfree(prx);
   %if (%upcase(&debug)=Y) %then %put patternin = &patternin;
   %let prx=%sysfunc(prxparse(%bquote(s/#coal-(\w+)-(\w+)-(\w+)-(\w+)#/COALESCE(\1.#var#, \2.#var#, \3.#var#, \4.#var#) as #vlenlabfmt#/i)));
   %let patternin=%qsysfunc(prxchange(&prx, -1, &patternin));
   %syscall prxfree(prx);
   %if (%upcase(&debug)=Y) %then %put patternin = &patternin;

   %*- check whether PATTERN contains any reference to a variable attribute -*;
   %let refvarattr=;
   %let prx=%sysfunc(prxparse(/(#vlen#|#vtyp#|#vfmt(def)?#|#vinfmt(def)?#|#vlab(el)?#|#vlab(el)?q#|#vlenlab(el)?fmt#|#vlenlab(el)?srcfmt#)/i));
   %if %sysfunc(prxmatch(&prx, %superq(patternin))) 
      %then %let refvarattr=Y;
      %else %let refvarattr=N;
   %syscall prxfree(prx);
   %if (%upcase(&debug)=Y) %then %put PATTERN has references to any variable attribute: &refvarattr..;

   %*- check whether PATTERN contains any reference to #autoEQ#, #autoCOAL# and/or #autoCOALsrc# -*;
   %let refauto=;
   %let prx=%sysfunc(prxparse(/(#autoEQ#|#autoCOALb?#|#autoCOALsrc#)/i));
   %if %sysfunc(prxmatch(&prx, %superq(patternin))) 
      %then %let refauto=Y;
      %else %let refauto=N;
   %syscall prxfree(prx);
   %if (%upcase(&debug)=Y) %then %put PATTERN has references to #autoEQ#, #autoCOAL# and/or #autoCOALsrc# : &refauto..;

   %do %while(%length(%scan(&varlist, &i, %str( )))>0);
       %let var=%scan(&varlist, &i, %str( ));
       %let dsvar=%scan(&dsvarlist, &i, %str( ));
       %let ds=%sysfunc(tranwrd(&dsvar%str( ), .&var%str( ), %str()));
       %let dsnl=;
       %let dsal=;
       %let dsnvarl=;
       %let autocoal=;
       %let autocoalb=;
       %let autoeq=;
       %if (%qsubstr(&ds%str( ), 1, 1) = %quote(#)) %then %do;
          %let dsn=;
          %let dsa=;
       %end; %else %do;
          %let dsn=%scan(%scan(&ds, 1, |), 1, :);
          %let dsa=%scan(%scan(&ds, 1, |), 2, :);
          %if (%quote(&dsa)=%quote()) %then %let dsa=%scan(&ds, -1, .);
          %let j=1;
          %let vnum=;
          %let vlen=;
          %let vtyp=;
          %let vfmt=;
          %let vinfmt=;
          %let vlab=;
          %let vlabsrc=;
          %let vlabq=" ";
          %let vlabsrcq=" ";
          %let vlenlabfmt=&var;
          %let vlenlabsrcfmt=&var;
          %do %while(%length(%scan(&ds, &j, |))>0);
             %let dsnl=&dsnl %scan(%scan(&ds, &j, |), 1, :);
             %if (&j>1) %then %let dsnvarl=&dsnvarl.%bquote(,);
             %let dsnvarl=&dsnvarl %scan(%scan(&ds, &j, |), 1, :).&var;

             %if (%length(&dsn)>0) and (&j=1) and (&refauto=Y) %then %do;
                %let dsid=%sysfunc(open(&dsn));
                %if &dsid %then %do;
                   %let vnum=%sysfunc(varnum(&dsid, &var));
                   %if &vnum %then %do;
                      %let vlen=%sysfunc(varlen(&dsid, &vnum));
                      %let vtyp=%sysfunc(vartype(&dsid, &vnum));
                      %let vfmt=%sysfunc(varfmt(&dsid, &vnum));
                      %let vinfmt=%sysfunc(varinfmt(&dsid, &vnum));
                      %let vlab=%qsysfunc(varlabel(&dsid, &vnum));
                      %let vlabsrc=&vlab [%upcase(&dsn..&var)];
                      %if (%length(&vlab)=0)
                         %then %let vlabq=" ";
                         %else %let vlabq=%sysfunc(quote(&vlab));
                      %let vlabsrcq=%sysfunc(quote(&vlabsrc));
                   %end;
                   %let rc=%sysfunc(close(&dsid));
                %end;
                %if (%length(&vlen)>0) %then %let vlenlabfmt=&vlenlabfmt length=&vlen;
                %let vlenlabfmt=&vlenlabfmt label=&vlabq;
                %if (%length(&vfmt)>0) %then %let vlenlabfmt=&vlenlabfmt format=&vfmt;
                %if (%length(&vinfmt)>0) %then %let vlenlabfmt=&vlenlabfmt informat=&vinfmt;
                %if (%length(&vlen)>0) %then %let vlenlabsrcfmt=&vlenlabsrcfmt length=&vlen;
                %let vlenlabsrcfmt=&vlenlabsrcfmt label=&vlabsrcq;
                %if (%length(&vfmt)>0) %then %let vlenlabsrcfmt=&vlenlabsrcfmt format=&vfmt;
                %if (%length(&vinfmt)>0) %then %let vlenlabsrcfmt=&vlenlabsrcfmt informat=&vinfmt;
             %end;

             %if (&refauto=Y) %then %do;
                %if (&j>1) %then %let autocoal=&autocoal.%quote(,);
                %if (&j>1) %then %let autoeq=&autoeq. %quote(=);
                %if (%qscan(%scan(&ds, &j, |), 2, :)=%quote()) %then %do;
                   %let dsal=&dsal %qscan(%scan(&ds, &j, |), 1, :);
                   %let autocoal=&autocoal %qscan(%qscan(%scan(&ds, &j, |), 1, :), -1, .).&var;
                   %let autoeq=&autoeq %qscan(%qscan(%scan(&ds, &j, |), 1, :), -1, .).&var;
                %end; %else %do;
                   %let dsal=&dsal %qscan(%scan(&ds, &j, |), 2, :);
                   %let autocoal=&autocoal %qscan(%scan(&ds, &j, |), 2, :).&var;
                   %let autoeq=&autoeq %qscan(%scan(&ds, &j, |), 2, :).&var;
                %end;
                %if (%upcase(&debug)=Y) %then %put (looping..) j=&j dsal=&dsal autocoal=&autocoal autoeq=&autoeq;
             %end;
             %let j=%eval(&j+1);
          %end;
          %if (&refauto=Y) %then %do;
             %*- auto-equal variables from all datasets that contain it -*;
             %if (&j >3) 
                %then %let autoeq=(coalesce(%sysfunc(tranwrd(&autocoal.|, %bquote(, )%scan(&autocoal.|, -1, %str( )), ))) = %scan(&autoeq, -1, %str( )));
                %else %if (&j =3) 
                          %then %let autoeq=(&autoeq);
                          %else %let autoeq=(&dsa..&var eq &dsa..&var);
             %*- auto-coalesce variables from all datasets that contain it -*;
             %if (&j >2) %then %do;
                %let autocoalb=coalesce(&autocoal);
                %let autocoalsrc=coalesce(&autocoal) as &var;
                %if (%length(&vlen)>0) %then %let autocoalsrc=&autocoalsrc length=&vlen;
                %*let autocoalsrc=&autocoalsrc %qsysfunc(quote(&vlab [%qupcase(&autocoal)]));
                %let autocoalsrc=&autocoalsrc %qsysfunc(quote(&vlab [coalesce: %qupcase(&dsnvarl)]));
                %if (%upcase(&debug)=Y) %then %put autocoalsrc=&autocoalsrc;
                %if (%length(&vfmt)>0) %then %let autocoalsrc=&autocoalsrc format=&vfmt;
                %if (%length(&vinfmt)>0) %then %let autocoalsrc=&autocoalsrc informat=&vinfmt;
                %if (%upcase(&debug)=Y) %then %put autocoalsrc=&autocoalsrc;
                %let autocoal=coalesce(&autocoal) as &vlenlabfmt;
             %end; %else %do;
                %let autocoalsrc=&dsa..&var as &vlenlabsrcfmt;
                %let autocoal=&dsa..&var;
                %let autocoalb=&autocoal;
             %end;
          %end;
       %end;
       %let patternout=&patternin;
       %if (%upcase(&debug)=Y) %then %put patternout=&patternout;
       %*- If PATTERN contains the sequence #dsn#.#var# and no dataset is associated to variable, 
           consider this sequence as if it consisted of #var# only to avoid a variable name with leading dot
           not preceded by a dataset name -*; 
       %if (%quote(&ds) = %quote(#default#))
          %then %let patternout=%qsysfunc(tranwrd(&patternout, #dsn#.#var#, #var#));
       %if (%upcase(&debug)=Y) %then %put patternout=&patternout;
       %let patternout=%qsysfunc(tranwrd(&patternout, #var#, &var));
       %if (%upcase(&debug)=Y) %then %put patternout=&patternout;
       %let patternout=%qsysfunc(tranwrd(&patternout, #dsn#, &dsn));
       %if (%upcase(&debug)=Y) %then %put patternout=&patternout;
       %let patternout=%qsysfunc(tranwrd(&patternout, #dsa#, &dsa));
       %if (%upcase(&debug)=Y) %then %put patternout=&patternout;
       %if (&refauto=Y) %then %do;
          %let patternout=%qsysfunc(tranwrd(&patternout, #autocoalb#, &autocoalb));
          %let patternout=%qsysfunc(tranwrd(&patternout, #autocoal#, &autocoal));
          %if (%upcase(&debug)=Y) %then %put patternout=&patternout;
          %let patternout=%qsysfunc(tranwrd(&patternout, #autocoalsrc#, &autocoalsrc));
          %if (%upcase(&debug)=Y) %then %put patternout=&patternout;
          %let patternout=%qsysfunc(tranwrd(&patternout, #autoeq#, &autoeq));
          %if (%upcase(&debug)=Y) %then %put patternout=&patternout;
       %end;
       %let vnum=;
       %let vlen=;
       %let vtyp=;
       %let vfmt=;
       %let vinfmt=;
       %let vlab=;
       %let vlabq=" ";
       %let vlabsrc=;
       %let vlabsrcq=" ";
       %*- if for a variable, a dataset is specified and PATTERN contains a keyword referencing one or more of 
           the variable attributes, attempt to retieve the variable attributes from the dataset -*;
       %if (%length(&dsn)>0) and (&refvarattr=Y) %then %do;
          %let dsid=%sysfunc(open(&dsn));
          %if &dsid %then %do;
             %let vnum=%sysfunc(varnum(&dsid, &var));
             %if &vnum %then %do;
                %let vlen=%sysfunc(varlen(&dsid, &vnum));
                %let vtyp=%sysfunc(vartype(&dsid, &vnum));
                %let vfmt=%sysfunc(varfmt(&dsid, &vnum));
                %let vinfmt=%sysfunc(varinfmt(&dsid, &vnum));
                %let vlab=%qsysfunc(varlabel(&dsid, &vnum));
                %let vlabsrc=&vlab [%upcase(&dsn..&var)];
                %let vlabsrcq=%sysfunc(quote(&vlabsrc));
                %if (%length(&vlab)=0)
                   %then %let vlabq=" ";
                   %else %let vlabq=%sysfunc(quote(&vlab));
                %if (%upcase(&debug)=Y) %then %put cnum=&vnum vlen=&vlen vtyp=&vtyp vfmt=&vfmt vinfmt=&vinfmt vlab=&vlab vlabq=&vlabq;
             %end;
             %let rc=%sysfunc(close(&dsid));
          %end;
       %end;
       %let vfmtdef=&vfmt;
       %let vinfmtdef=&vinfmt;
       %if (%length(&vfmt)=0) %then %do;
          %if (&vtyp=C) 
             %then %let vfmtdef=$&vlen..;
             %else %let vfmtdef=Best12.;
       %end;
       %if (%length(&vinfmt)=0) %then %do;
          %if (&vtyp=C) 
             %then %let vinfmtdef=$&vlen..;
             %else %let vinfmtdef=Best12.;
       %end;
       %let vlenlabfmt=&var;
       %if (%length(&vlen)>0) %then %let vlenlabfmt=&vlenlabfmt length=&vlen;
       %let vlenlabfmt=&vlenlabfmt label=&vlabq;
       %if (%length(&vfmt)>0) %then %let vlenlabfmt=&vlenlabfmt format=&vfmt;
       %if (%length(&vinfmt)>0) %then %let vlenlabfmt=&vlenlabfmt informat=&vinfmt;
       %if (%upcase(&debug)=Y) %then %put vlenlabfmt=&vlenlabfmt;
       %if (%length(&vlen)>0) %then %let vlenlabsrcfmt=&vlenlabsrcfmt length=&vlen;
       %let vlenlabsrcfmt=&vlenlabsrcfmt label=&vlabsrcq;
       %if (%length(&vfmt)>0) %then %let vlenlabsrcfmt=&vlenlabsrcfmt format=&vfmt;
       %if (%length(&vinfmt)>0) %then %let vlenlabsrcfmt=&vlenlabsrcfmt informat=&vinfmt;

       %let patternout= %qsysfunc(tranwrd(&patternout, #vnum#, &vnum));
       %if (%upcase(&debug)=Y) %then %put patternout{2}=&patternout;
       %let patternout= %qsysfunc(tranwrd(&patternout, #vlen#, &vlen));
       %if (%upcase(&debug)=Y) %then %put patternout{2}=&patternout;
       %let patternout= %qsysfunc(tranwrd(&patternout, #vtyp#, &vtyp));
       %if (%upcase(&debug)=Y) %then %put patternout{2}=&patternout;
       %let patternout= %qsysfunc(tranwrd(&patternout, #vfmt#, &vfmt));
       %let patternout= %qsysfunc(tranwrd(&patternout, #vfmtdef#, &vfmtdef));
       %if (%upcase(&debug)=Y) %then %put patternout{2}=&patternout;
       %let patternout= %qsysfunc(tranwrd(&patternout, #vinfmt#, &vinfmt));
       %let patternout= %qsysfunc(tranwrd(&patternout, #vinfmtdef#, &vinfmtdef));
       %if (%upcase(&debug)=Y) %then %put patternout{2}=&patternout;
       %let patternout=%qsysfunc(tranwrd(&patternout, #vlab#, &vlab));
       %let patternout=%qsysfunc(tranwrd(&patternout, #vlabel#, &vlab));
       %if (%upcase(&debug)=Y) %then %put patternout{2}=&patternout;
       %let patternout=%qsysfunc(tranwrd(&patternout, #vlabsrc#, &vlabsrc));
       %let patternout=%qsysfunc(tranwrd(&patternout, #vlabelsrc#, &vlabsrc));
       %if (%upcase(&debug)=Y) %then %put patternout{2}=&patternout;
       %let patternout=%qsysfunc(tranwrd(&patternout, #vlabq#, &vlabq));
       %let patternout=%qsysfunc(tranwrd(&patternout, #vlabelq#, &vlabq));
       %if (%upcase(&debug)=Y) %then %put patternout{2}=&patternout;
       %let patternout=%qsysfunc(tranwrd(&patternout, #vlabsrcq#, &vlabsrcq));
       %let patternout=%qsysfunc(tranwrd(&patternout, #vlabelsrcq#, &vlabsrcq));
       %if (%upcase(&debug)=Y) %then %put patternout{2}=&patternout;
       %let patternout=%qsysfunc(tranwrd(&patternout, #vlenlabfmt#, &vlenlabfmt));
       %let patternout=%qsysfunc(tranwrd(&patternout, #vlenlabelfmt#, &vlenlabfmt));
       %if (%upcase(&debug)=Y) %then %put patternout{2}=&patternout;
       %let patternout=%qsysfunc(tranwrd(&patternout, #vlenlabsrcfmt#, &vlenlabsrcfmt));
       %let patternout=%qsysfunc(tranwrd(&patternout, #vlenlabelsrcfmt#, &vlenlabsrcfmt));
       %if (%upcase(&debug)=Y) %then %put patternout{2}=&patternout;
       %*- resolve keywords for special punctuation symbols potentially used in PATTERNS -*;
       %let patternout=%qsysfunc(tranwrd(&patternout, %quote(#semicolon#), %qsubstr(';', 2, 1)));
       %let patternout=%qsysfunc(tranwrd(&patternout, %quote(#semicol#), %qsubstr(';', 2, 1)));
       %let patternout=%qsysfunc(tranwrd(&patternout, %quote(#scol#), %qsubstr(';', 2, 1)));
       %let patternout=%qsysfunc(tranwrd(&patternout, %quote(#sc#), %qsubstr(';', 2, 1)));
       %let patternout=%qsysfunc(tranwrd(&patternout, %quote(#comma#), %qsubstr(',', 2, 1)));
       %let patternout=%qsysfunc(tranwrd(&patternout, %quote(#coma#), %qsubstr(',', 2, 1))); %*- allow for misspelling -*;
       %let patternout=%qsysfunc(tranwrd(&patternout, %quote(#c#), %qsubstr(',', 2, 1))); %*- allow for abbreviation -*;

       %let varlist2=&varlist2.&sepout.&patternout;
       
       %let i=%eval(&i+1); %*- loop: next &i -*;
       %let sepout=&sep; %*- separator before following items -*;
   %end;
   %let varlist=&varlist2;
   %if (%upcase(&debug)=Y) %then %put varlist=&varlist;

   %if   (%qupcase(&from)=#AUTOFULLEQ#) 
      or (%qupcase(&from)=#AUTOLEFTEQ#)
      or (%qupcase(&from)=#AUTORIGHTEQ#)
      or (%qupcase(&from)=#AUTOINNEREQ#)
   %then %do;
      %*- Auto-generate a SQL FROM clause and JOIN (if multiple datasets), 
          and append it to the returned list of variables (meant to be used 
          in a PROC SQL SELECT clause):
          in case of JOIN, this will be an EQUIJOIN based on variables in common between datasets (also taking into account the var= parameter),
          JOIN type is according to parameter keyword: #autoFULLEQ# (FULL), #autoLEFTEQ# (LEFT), #autoRIGHTEQ# (RIGHT), #autoINNEREQ# (INNER) -*;
      %let join_type=%sysfunc(tranwrd(%sysfunc(tranwrd(%qupcase(&from), #AUTO, %str( ))), EQ#, %str( )));
      %if (%upcase(&debug)=Y) %then %put join_type=&join_type;

      %let i=1;
      %let prev_datasets=;
      %do %while(%scan(&data, &i, %str( ))^=);
         %let dsn=%scan(&data, &i, %str( ));
         %if (%index(&dsn, #) EQ 0) %then %do;
            %if (&i=1) %then %do;
              %let varlist=&varlist FROM;
            %end; %else %do;
              %let varlist=&varlist &join_type JOIN;
            %end;
              %let varlist=&varlist %sysfunc(tranwrd(&dsn, :, %str( as )));
            %if (&i>1) %then %do;
              %let varlist2=%VARLIST(data=&prev_datasets #INTERSECT# &dsn, var=&varlist_o, sep=#and#, pattern=#autoEQ#);
              %if %length(&varlist2)>0 %then %do;
                 %let varlist=&varlist ON &varlist2;
              %end; %else %do;
                 %let varlist=&varlist ON (1 EQ 1);  %*- No variables in common, so try the equivalent of a Cartesian Product or CROSS JOIN -*;
              %end;
            %end;
            %let prev_datasets=&prev_datasets &dsn;
         %end;
         %let i=%eval(&i+1);
         %if (%upcase(&debug)=Y) %then %do;
            %put ## SPECIFIC PROCESSING STEP &i, dsn=&dsn ##;
            %put varlist=&varlist;
         %end;
      %end;


   %end;

%END;

%*- (resolve to) list of variables to return -*;
%if %length(&varlist) %then %do;
%unquote(&pretext.&varlist&posttext.)
%end;
%mend;

Links

Usage in building SQL Joins

Paper

PhUSE 2015 (Paper CC05) Dynamic, Powerful, User-friendly SQL JOINS Made Easy with %VARLIST, a Generic SAS Macro Function

Presentation

Slides

Slides & notes: File:Phuse2015-CC05-notes.pdf

Top 10 uses of macro %varlist

Paper presented at PhUSE 2016 Conference (Paper CS09)

PhUSE 2016 (Paper CS09):

Other Usage Examples

%VARLIST Usage Examples

FAQ

%VARLIST FAQ

Author

Jean-Michel Bodart
Business & Decision Life Sciences
Rue Saint-Lambert 141
1200 Brussels
Belgium
http://www.businessdecision-lifesciences.com
--Jmbodart (talk) 10:47, 3 September 2015 (EDT)