text:
/*=====================================================================
Program Name            : align_decimals.sas
Purpose                 : Aligns the decimal points of numeric or
                          character variables, optionally adding
                          additional bracketing characters
                          (usually parentheses).
SAS Version             : SAS 9.1.3
Input Data              : N/A
Output Data             : N/A

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 09OCT2010
Program Version #       : 1.0

=======================================================================

Scott Bass (sas_l_739@yahoo.com.au)

This code is licensed under the Unlicense license.
For more information, please refer to http://unlicense.org/UNLICENSE.

This is free and unencumbered software released into the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or
distribute this software, either in source code form or as a compiled
binary, for any purpose, commercial or non-commercial, and by any
means.

=======================================================================

Modification History    : Original version

=====================================================================*/

/*---------------------------------------------------------------------
Usage:

* create test dataset ;
data test;
   length num1 num2 8 chr1 chr2 $9 maxdec 8;
   input num1 num2 chr1 chr2 maxdec;
   datalines;
1              1           1           1              4
2.2            2.2         2.2         2.2            4
33.3           33.3        33.3        33.3           4
4.44           4.44        4.44        BAD            4
555.55         555.55      555.55      555.55         4
66.666         66.666      66.666      66.666         4
7777.77        7777.77     7777.77     7777.77        4
8888.888       8888.888    8888.888    BAD            4
9999.9999      9999.9999   9999.9999   9999.9999      4
;
run;

* align decimals for single numeric column ;
data aligned;
   set test;
   %align_decimals(var=num1,width=11,decpos=4);
run;
proc print;run;

* >>> creates output variable col_num1 as "(xxxx.xxxx)" (default) ;

=======================================================================

* align decimals for multiple numeric columns ;
data aligned;
   set test;
   %align_decimals(var=num1 num2,width=11,decpos=4);
run;
proc print;run;

* >>> creates output variables col_num1 col_num2 as "(xxxx.xxxx)" ;

=======================================================================

* align decimals for multiple character columns ;
data aligned;
   set test;
   %align_decimals(var=chr1 chr2,width=11,decpos=4,type=C);
run;
proc print;run;

* >>> create output variables col_chr1 col_chr2 as "(xxxx.xxxx)" ;

=======================================================================

* align decimals for multiple variables using multiple invocations ;
data aligned;
   set test;
   %align_decimals(var=num1 num2,width=13,decpos=4);
   %align_decimals(var=chr1 chr2,width=13,decpos=-4,type=C);
run;
proc print;run;

* >>> create output variables col_num1 col_num2 as "(xxxx.xxxx  )" ;
* >>> create output variables col_chr1 col_chr2 as "( xxxx.xxxx )" ;

=======================================================================

* align decimals using an input variable list ;
* if an input variable list is used, then out must be specified ;
* an output variable list (numeric suffix) can be used ;
data aligned;
   set test;
   %align_decimals(var=num1 -- num2,out=var1-var2,width=11,decpos=4);
run;
proc print;run;

* >>> create output variables var1 - var2 as "(xxxx.xxxx)" ;

=======================================================================

* specify explicit output variable names ;
* there must be a one to one match of input to output variable names ;
* based on position, although output variable lists are allowed ;
data aligned;
   set test;
   %align_decimals(var=chr1-chr2,out=var_a var_b,width=11,decpos=4,type=c);
run;
proc print;run;

* >>> create output variables var_a var_b as "(xxxx.xxxx)" ;

=======================================================================

* specify output variable prefix ;
data aligned;
   set test;
   %align_decimals(var=num1,width=11,decpos=4,prefix=myvar_);
run;
proc print;run;

* >>> create output variables myvar_num1 as "(xxxx.xxxx)" ;

=======================================================================

* specify numerically suffixed output variables ;
* if suffix=# then input variable name (num1, num2) are not part ;
* of the output variable name ;
* Note:  an output variable list might be just as easy, but with the below approach ;
* you do not need to know the actual number of variables in the input or output list ;
data aligned;
   set test;
   %align_decimals(var=num1-numeric-num2,width=11,decpos=4,prefix=somevar,suffix=#);
run;
proc print;run;

* >>> create output variables somevar1 somevar2 as "(xxxx.xxxx)" ;

=======================================================================

* right justify output (note increased width and negative decpos) ;
data aligned;
   set test;
   %align_decimals(var=num2,width=14,decpos=-4);
run;
proc print;run;

* >>> create output variable col_num2 as "(  xxxx.xxxx)" ;

=======================================================================

* same as above, using a maxdec variable in the source dataset ;
data aligned;
   set test;
   %align_decimals(var=num2,width=14,decpos=-maxdec);
run;
proc print;run;

* >>> create output variable col_num2 as "(  xxxx.xxxx)" ;

=======================================================================

* align decimals with additional bracketing characters ;
data aligned;
   set test;
   %align_decimals(var=num1 num2,width=14,decpos=5,lc={,rc=}*);
run;
proc print;run;

* >>> create output variables col_num1 col_num2 as "{ xxxx.xxxx }*" ;

=======================================================================

* align decimals with no additional bracketing characters ;
data aligned;
   format col_num1 col_num2;
   set test;
   %align_decimals(var=num1,width=18,decpos=4,lc=,rc=);
   %align_decimals(var=num2,width=18,decpos=-4,lc=,rc=);
   file print;
   put @1 "*" col_num1 $char18. +(-1) "#" @25 "*" col_num2 $char18. +(-1) "#";
run;

* >>> create output variables col_num1 col_num2 as ;
* "xxxx.xxxx     " (left justified) and ;
* "     xxxx.xxxx" (right justified) ;

=======================================================================

* error conditions ;
* left width too small ;
data aligned;
   set test;
   %align_decimals(var=num1,width=11,decpos=3);
run;
proc print;run;

=======================================================================

* right width too small ;
data aligned;
   set test;
   %align_decimals(var=num1,width=11,decpos=-3);
run;
proc print;run;

=======================================================================

* overall width too small ;
data aligned;
   set test;
   %align_decimals(var=num1,width=9,decpos=4);
run;
proc print;run;

=======================================================================

* different number of input and output variables ;
data aligned;
   set test;
   %align_decimals(var=num1-num2,out=col_num1,width=11,decpos=4);
run;
proc print;run;

-----------------------------------------------------------------------
Notes:

When aligning decimal points within a character output variable, the
two constants are the width of the output variable, and the position of
the decimal point.  By evaluating the length of the integer portion
(for left justification) and decimal portion (for right justification)
of the input number, pointers can be derived to determine the start
position of the numeric data within the output string.

In general, set the WIDTH parameter to the column width of your output
(usually a PROC REPORT column).  Set the DECPOS parameter to the
maximum length of either the integer portion (positive DECPOS) or
decimal portion (negative DECPOS) of the number.  Use a positive number
to left justify the output within the output WIDTH, and use a negative
number to right justify the output within the output WIDTH. Increase
the DECPOS parameter if you want additional spacing after or before
your bracketing characters (usually parentheses).

If the maximum decimal length has been pre-calculated (eg. via the
%max_decimals macro), and exists as a variable in the source dataset,
then that variable can be specified as the DECPOS parameter.

If the input variable(s) are a character representation of numeric
data, they are first validated as numeric data before being processed.
Invalid numeric data will be displayed as missing data.

Be aware that, as part of this validation process, the data step
error flag variable _ERROR_ is reset to 0.  Therefore, this macro
should be called early in the data step if you wish to trap later
downstream errors.

Because this macro uses data step arrays to process multiple input
variables, the VAR parameter must contain variables of a single data
type (numeric or character).  If both numeric and character data need
to be processed, invoke this macro twice.

By default this macro uses the LC ("left character" and RC ("right
character) parameters to bracket the aligned numeric data with left and
right parentheses.  Use the LC and RC parameters to change the
bracketing characters, including none at all.

The WIDTH parameter must be the TOTAL width of the output column,
including bracketing characters.  For example, if your maximum input
data is 4 characters integer and 4 characters decimal (eg. 1234.5678),
then your WIDTH should be a minimum of 11
(4 integers + 4 decimals + 1 (decimal point) + 2 (bracketing characters)).

If the numeric data cannot fit into the output column without
overwriting either of the bracketing characters, then a message is
written to the log and the output column is "blank" (bracketing
characters only).

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

%macro align_decimals
/*---------------------------------------------------------------------
Aligns the decimal points of numeric or character variables.
---------------------------------------------------------------------*/
(VAR=          /* Input variable(s) (REQ).                           */
,OUT=          /* Output variables (Opt).                            */
               /* If blank, the ouput variables are named as         */
               /* &PREFIX.&VAR1.&SUFFIX, &PREFIX.&VAR2.&SUFFIX, etc. */
,WIDTH=        /* Total output variable width (including any         */
               /* bracketing characters) (REQ).                      */
,DECPOS=       /* Decimal position within the total output variable  */
               /* width (REQ).                                       */
               /* Must be a positive or negative integer,            */
               /* or an existing variable in the source dataset that */
               /* is a positive or negative integer.                 */
,TYPE=N        /* Input variable(s) type (REQ).                      */
               /* Default is N.  Valid values are N or C.            */
               /* All input variables must be the same data type.    */
,PREFIX=col_   /* Output variable name prefix (Opt).                 */
               /* Default is col_.                                   */
               /* If missing then OUT= must be specified.            */
,SUFFIX=       /* Output variable name suffix (Opt).                 */
               /* If blank, the suffix is &VAR1, &VAR2, etc., where  */
               /* &VAR1, &VAR2, etc is each word in &VAR.            */
               /* Specify "#" to use a numeric counter suffix.       */
,LC=%str(%()   /* Left bracketing character (Opt).                   */
               /* Default is "(".                                    */
,RC=%str(%))   /* Right bracketing character (Opt).                  */
               /* Default is ")".                                    */
);

%local macro parmerr;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(VAR,          _req=1,_words=1,_case=N)
%parmv(WIDTH,        _req=1,_words=0,_case=N,_val=POSITIVE)
%parmv(DECPOS,       _req=1,_words=0,_case=N)
%parmv(TYPE,         _req=1,_words=0,_case=U,_val=N C)

%if (&parmerr) %then %goto quit;

%* additional error checking ;
%* if PREFIX and SUFFIX are blank then OUT must be specified ;
%* (otherwise the output variable name would equal the input variable name) ;
%if (%superq(prefix) eq ) and (%superq(suffix) eq ) %then %if (%superq(out) eq ) %then %do;
   %parmv(_msg=If PREFIX and SUFFIX are both blank then OUT must be specified)
   %goto quit;
%end;

%* if an input variable list is specified then OUT must be specified ;
%if (%index(%superq(var),%str(-_))) %then %if (%superq(out) eq ) %then %do;
   %parmv(_msg=If an input variable list is specified then OUT must be specified)
   %goto quit;
%end;

%* if OUT is blank then need to build the output variable list from VAR, PREFIX, and SUFFIX ;
%* if SUFFIX = # then use a numeric suffix ;
%if (%superq(suffix) eq %str(#)) %then
   %let num_suffix=1;
%else
   %let num_suffix=0;

%if (%superq(out) eq ) %then %do;
   %let i=1;
   %let word=%qscan(%superq(var),&i,%str( ));
   %do %while (%superq(word) ne );
      %if (&num_suffix) %then %do;
         %let word=%str();
         %let suffix=&i;
      %end;
      %let out=%superq(out)%superq(prefix)%superq(word)%superq(suffix)%str( );

      %let i=%eval(&i+1);
      %let word=%qscan(%superq(var),&i,%str( ));
   %end;
%end;
%let out=%unquote(&out);

%* since this macro supports multiple invocations within a single datastep, ;
%* we need to uniquely declare variable arrays or there will be a naming collision ;
%* rather than trying to keep track of macro invocations, ;
%* just create a (hopefully) unique random number ;
%let rand=%sysfunc(ranuni(0));
%let rand=%sysevalf(&rand*10000);
%let rand=%sysfunc(int(&rand));

%* now code the logic of this macro ;
array _var&rand._ {*} &var;
array _out&rand._ {*} $&width &out;

%* if the array dimensions are not the same, print an error and stop ;
if (dim(_out&rand._) ne dim(_var&rand._)) then do;
   putlog "ERR" "OR: The number of output variables does not equal the number of input variables.";
   stop;
end;

length _str&rand._ _num&rand._ $&width _width_ _decpos_ _intlen_ _declen_ _start_ _length_ 8;

_width_=&width;
_str&rand._="&lc"||putc("",cats("$",_width_-lengthn("&lc")-lengthn("&rc")))||"&rc";

do _i_=1 to dim(_var&rand._);
   %* if the input variable type is numeric, convert to character ;
   %* if the input variable type is character, validate as a number ;
   %if (&type eq N) %then %do;
      _num&rand._=put(_var&rand._{_i_},best32.-L);
   %end;
   %else %do;
      _num&rand._=put(input(_var&rand._{_i_},? best.),best.-L);
      if (_ERROR_) then do;
         putlog "ERR" "OR: " _var&rand._{_i_} "is not valid numeric data.  " _n_=;
         _ERROR_=0;  /* reset error flag */
      end;
      /* comment the above lines and uncomment the below line */
      /* if you do not want to validate the input character data as numeric */
      /* _num&rand._=strip(_var&rand._{_i_}); */
   %end;

   %* get the length of the integer and decimal portions ;
   _intlen_=lengthn(strip(scan(_num&rand._,1,".")));
   _declen_=lengthn(strip(scan(_num&rand._,2,".")));

   %* derive decimal position ;
   _decpos_=&decpos;

   if (_decpos_ lt 0) then
      _decpos_=_width_+(_decpos_)-lengthn("&rc");
   else
      _decpos_=_decpos_+1+lengthn("&lc");

   _out&rand._{_i_}=_str&rand._;
   _start_=_decpos_-_intlen_;
   _length_=length(_num&rand._);
   if (_start_ ge 1+lengthn("&lc")) and (_start_+_length_-1 le _width_-lengthn("&rc")) then
      substr(_out&rand._{_i_},_start_,_length_)=strip(_num&rand._);
   else
      putlog "ERR" "OR: " _num&rand._ "cannot fit within WIDTH=&width and DECPOS=&decpos..";

   drop _str&rand._ _num&rand._ _width_ _decpos_ _intlen_ _declen_ _start_ _length_ _i_;
end;

%* end of macro logic ;

%quit:

%mend;

/******* END OF FILE *******/


text:
/*=====================================================================
Program Name            : get_parameters.sas
Purpose                 : Get global and optional job-specific
                          parameters (macro variables) from a
                          specially structured metadata dataset
SAS Version             : SAS 9.1.3
Input Data              : METADATA= macro parameter
Output Data             : Various metadata parameters (macro variables)

Macros Called           : parmv, seplist, dump_mvars

Originally Written by   : Scott Bass
Date                    : 01FEB2007
Program Version #       : 1.0

=======================================================================

Scott Bass (sas_l_739@yahoo.com.au)

This code is licensed under the Unlicense license.
For more information, please refer to http://unlicense.org/UNLICENSE.

This is free and unencumbered software released into the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or
distribute this software, either in source code form or as a compiled
binary, for any purpose, commercial or non-commercial, and by any
means.

=======================================================================

Modification History    :

Programmer              : Scott Bass
Date                    : 11DEC2009
Change/reason           : Added support for multiple filters via
                          pipe(|) character plus metadata dataset
                          options (eg. where clause)
Program Version #       : 1.1

Programmer              : Scott Bass
Date                    : 15FEB2010
Change/reason           : Added clear= parameter
Program Version #       : 1.2

Programmer              : Scott Bass
Date                    : 17MAY2011
Change/reason           : Added resolve= and symboltable= parameters
Program Version #       : 1.4

=====================================================================*/

/*---------------------------------------------------------------------
Usage:

* CREATE SAMPLE METADATA ;

* delete all global macro variables ;
proc sql noprint;
   select name into :_mvars_ separated by " "
   from dictionary.macros
   where scope="GLOBAL" and name not like 'SYS%';
quit;
%symdel &_mvars_ _mvars_ / nowarn;
%put _global_;

* create sample metadata dataset ;
data
   work.parameters
   work.mymetadata
   ;
   length Filter $50 Required $1 Name $32 Value $200;
   infile datalines dlm="|";
   input Filter Required Name Value;
   datalines4;
GLOBAL   | Y   |  PARM1       | This is parm 1
GLOBAL   | N   |  PARM2       | Parm 2 references Parm 3 which does not exist yet --> &parm3
GLOBAL   | Y   |  PARM3       | Parm 3 contains embedded runtime Parm 1 --> &parm1
GLOBAL   | N   |  BASE        | /proj/001/b123456
GLOBAL   | Y   |  RUNTIME     | Embedded function style macro:  %sysfunc(datetime(),datetime.)
GLOBAL   | N   |  PROT        | 1234
GLOBAL   | Y   |  DOMAIN      | AE
GLOBAL   | N   |  MISSING1    | .
GLOBAL   | N   |  MISSING2    | .
SDTM     | Y   |  SDTM1       | This is SDTM parm 1
SDTM     | N   |  PARM2       | SDTM parm 2 overrides GLOBAL parm 2
LISTINGS | Y   |  LISTINGS1   | This is LISTINGS parm 1
AE       | Y   |  PARM1       | This is the AE domain and overrides GLOBAL Parm 1
LB       | Y   |  PARM1       | This is the LB domain and overrides GLOBAL Parm 1
JOB XXX  | N   |  JOB1        | This is JOB XXX parm 1
JOB XXX  | N   |  PARM1       | This is JOB XXX parm 1 and overrides GLOBAL and DOMAIN Parm 1
;;;;
run;

* NOTE:  For all these use cases, I have to explictly specify ;
* the _metadata parameter.  The default location for metadata ;
* is meta.parameters ;

-----------------------------------------------------------------------

* return global parameters ;
%get_parameters(_metadata=work.parameters)
%put _global_;  * previous macro variables are cleared out ;
%dump_mvars(&_parameters_)

=======================================================================

* return only non-missing global parameters ;
%get_parameters(_metadata=work.parameters,_missing=N)
%put _global_;  * previous macro variables are cleared out ;
%dump_mvars(&_parameters_)

=======================================================================

* return global and SDTM parameters ;
%get_parameters(SDTM,_metadata=work.parameters)
%put _global_;  * previous macro variables are cleared out ;
%dump_mvars(&_parameters_)

=======================================================================

* return global, LISTINGS, and DOMAIN-specific parameters ;
%get_parameters(LISTINGS^AE,_metadata=work.parameters)
%put _global_;  * previous macro variables are cleared out ;
%dump_mvars(&_parameters_)

%get_parameters(LISTINGS^LB,_metadata=work.parameters)
%put _global_;  * previous macro variables are cleared out ;
%dump_mvars(&_parameters_)

=======================================================================

* return global, LISTINGS, DOMAIN, and job-specific parameters ;
%let jobname=JoB xXx;  * spaces are allowed in filters, filter is case-insensitive ;
%get_parameters(LISTINGS^AE^&jobname,_metadata=work.parameters)
%symdel jobname;
%put _global_;  * previous macro variables are cleared out ;
%dump_mvars(&_parameters_)

%get_parameters(LISTINGS^LB^&jobname,_metadata=work.parameters)
%symdel jobname;
%put _global_;  * previous macro variables are cleared out ;
%dump_mvars(&_parameters_)

=======================================================================

* return GLOBAL, LISTINGS, and job-specific parameters ;
* where additional Filters Required="Y" and 1=1 and 2=2 ;
* from the work.mymetadata parameters dataset ;

* both macro invocations return the same results ;
%get_parameters(LISTINGS^Job XXX | Required="Y" | 1=1 | 2=2, _metadata=work.mymetadata)
%put _global_;  * previous macro variables are cleared out ;
%dump_mvars(&_parameters_)

%get_parameters(LISTINGS^Job XXX | Required="Y" and 1=1 and 2=2, _metadata=work.mymetadata)
%put _global_;  * previous macro variables are cleared out ;
%dump_mvars(&_parameters_)

=======================================================================

* return global parameters where NAME in (BASE, PROT, DOMAIN) ;
* either spaces or carets (^) can be used to delimit name= parameters ;
%get_parameters(_name=base prot^domain,_metadata=work.parameters)
%put _global_;  * previous macro variables are cleared out ;
%dump_mvars(&_parameters_)

=======================================================================

* return global parameters where NAME=BASE, but do not clear previous variables ;
%get_parameters(SDTM^LISTINGS^JOB XXX,_metadata=work.parameters); * sets all metadata macro variables ;
%let base=BLAH;  * pretend &base was set on a previous invocation ;
%put _global_;
%get_parameters(_name=base,clear=no,_metadata=work.parameters)  * &base is correct, but all other macro variables are untouched ;
%put _global_;
%dump_mvars(&_parameters_)

=======================================================================

* do not resolve embedded macro variables (GLOBAL --> PARM3 contains embedded macro variable reference);
%get_parameters(_resolve=N,_metadata=work.parameters)

* these were all set from the parameters dataset ;
* now that %get_parameters has completed, they all resolve correctly ;
%put parm1=&parm1;
%put parm2=&parm2;
%put parm3=&parm3;

=======================================================================

* but the embedded macro variable references still remain unresolved ;
%let parm1=foo;  * say this is set at runtime by a calling program ;
%put parm3=&parm3;  * now parm 3 has a new value, since &parm1 is still embedded in parm 3 ;

%symdel parm3 / nowarn;
%put parm2=&parm2;  * and so does parm 2 - this should give an unresolved macro variable warning ;

=======================================================================

* create macro variables in the most local symbol table ;
* valid values are G, L, and F ;
* Note:  L is allowed, but rather useless, since the macro variables created will be local ;
* to %get_parameters.  The macro variables will be out of scope (i.e. deleted) when %get_parameters ends ;
* see documentation for CALL SYMPUTX for details ;
%macro test;
   %local parm1 parm2 parm3;  %* try running this without declaring the macro variables first ;
   %get_parameters(_symboltable=F,_metadata=work.parameters)
   proc sql;
      select * from dictionary.macros where scope="&sysmacroname";
   quit;
   %dump_mvars(parm1 parm2 parm3)
%mend;
%test;
%dump_mvars(parm1 parm2 parm3);  * but not in the global symbol table ;

-----------------------------------------------------------------------
Notes:

I would prefer not to have to prefix all the macro parameters with an
underscore.  However, I cannot globalize any local macro variables,
which includes the parameters.  Prefixing the macro parameters with an
underscore reduces the likelihood of a local/global macro variable name
collision.

You must use a caret (^) as the delimiter between filter and name
tokens, since the filter can contain spaces.

You can specify multiple "levels" of filters by separating them with
the pipe (|) symbol.  The second and subsequent filters are added to
the initial filter using the AND operator.

The filter can be any string, but typically would be:
GLOBAL (optional), a grouping parameter encompassing several programs
(i.e. STDM, INTERVENTION, EXCEPTIONS, TABLES, LISTINGS, etc), and
job name (&jobname).  The filter must be synchronised with the filter
column in metadata.parameters.

The data in meta.parameters should be sorted from most general
(GLOBAL) to most specific (jobname).  If any variables are repeated,
the last one occurring has precedence.  So, you want a job-specific
parameter to override a global parameter.

meta.parameters can be a datastep view which reads from an external
file.  This is useful when reading a CSV external file, where Excel is
used to maintain the metadata (and saved as a CSV).

The filter is case-insensitive, but otherwise must be identical to the
filter string in metadata.parameters, eg. spaces between words are
significant.  Be especially aware that renaming the job will break
the linkage between the filter value in metadata.parameters and
&jobname.

If the Name column in the metadata dataset contains any macro variable
references (i.e. dynamically derived macro variable names), the calling
code needs to declare those variables via %global macrovariable before
calling this macro.

If you want embedded macro variables to remain unresolved in the
returned macro variables, set RESOLVE=N.  This allows you to embedded
macro variables in the metadata, which are set later during runtime.

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

%macro get_parameters
/*---------------------------------------------------------------------
Get the global and optional metadata parameters
---------------------------------------------------------------------*/
(_FLTR         /* Filter used to limit the data returned (Opt).      */
               /* If not set, only GLOBAL parameters will be         */
               /* returned.                                          */
,_NAME=        /* Additional filter on name(s) (Opt).                */
               /* If not set, all named items matching the filter    */
               /* criteria will be returned.                         */
,_WHERE=       /* Additional free form where clause (Opt).           */
               /* If set, it must specify a valid where clause for   */
               /* the metadata dataset (without the "where" keyword) */
,_METADATA=meta.parameters
               /* Metatdata parameters dataset (REQ).                */
,_CLEAR=Y      /* Clear previous macro variables? (REQ).             */
               /* Default value is YES.  Valid values are:           */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
,_RESOLVE=Y    /* Resolve embedded macro variable references (REQ).  */
               /* Default value is YES.  Valid values are:           */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
,_MISSING=Y    /* Set macro variables if value is missing? (REQ).    */
               /* If YES, macro variables are defined even if the    */
               /* value is missing.  If NO, only macro variables     */
               /* with non-missing data values are defined.          */
               /* Default value is YES.  Valid values are:           */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
,_SYMBOLTABLE=G
               /* Create macro variables in the specified macro      */
               /* variable symbol table (REQ).                       */
               /* Default value is G (Global).                       */
               /* Valid values are G (Global), L (most local symbol  */
               /* table), and F (if found, create in the most local  */
               /* symbol table in which it exists, else in the most  */
               /* local symbol table).                               */
);

%global _parameters_;

%local macro parmerr _num_levels _temp _serror;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(_FLTR,        _req=0,_words=1)
%parmv(_NAME,        _req=0,_words=1,_case=U)
%parmv(_METADATA,    _req=1,_words=0)
%parmv(_CLEAR,       _req=1,_words=0,_case=U,_val=0 1)
%parmv(_RESOLVE,     _req=1,_words=0,_case=U,_val=0 1)
%parmv(_MISSING,     _req=1,_words=0,_case=U,_val=0 1)
%parmv(_SYMBOLTABLE, _req=1,_words=0,_case=U,_val=G L F)

%if (&parmerr) %then %goto quit;

%* additional error checking ;

%* Does metadata parameters dataset exist? ;
%if ( (%sysfunc(exist(&_metadata,data)) ne 1) and
      (%sysfunc(exist(&_metadata,view)) ne 1) ) %then %do;
   %parmv(_msg=&_metadata dataset or view does not exist)
   %if (&parmerr) %then %goto quit;
%end;

%* parse the filter parameter ;
%* add end of string marker to properly process an empty filter string ;
%let _fltr=%str(&_fltr | ###);
%let _num_levels=1;
%let _temp=%scan(&_fltr,&_num_levels,%str(|));
%do %while (%quote(&_temp) ne %quote(###));
   %local _fltr&_num_levels;
   %let _fltr&_num_levels=&_temp;
   %let _num_levels=%eval(&_num_levels+1);
   %let _temp=%scan(&_fltr,&_num_levels,%str(|));
%end;
%let _num_levels=%eval(&_num_levels-1);

%* process the name parameter ;
%let _name = %upcase(%seplist(&_name,indlm=%str(^ ),nest=QQ));

%* if clear=yes, delete all possible macro variables ;
%if (&_clear) %then %do;
   * delete all previous macro variables ;
   %local _mvars_;
   %let _mvars_=1;
   proc sql noprint;
      select distinct name into :_mvars_ separated by " "
      from &_metadata
      where (^missing(name)) and (^notname(strip(name))) and (anydigit(strip(name)) ne 1)

      %do _i=1 %to &_num_levels;
         %if (&_i eq 1) %then
            and upcase(Filter) in (%seplist(%upcase(GLOBAL^&&_fltr&_i),indlm=%str(^),nest=QQ));
         %else
            and &&_fltr&_i;
      %end;

      %if (&_name ne ) %then
         and upcase(Name) in (&_name);

      %if (%superq(_where) ne ) %then
         and &_where;
     ;
   quit;
   %if (&_mvars_ ne ) %then %symdel &_mvars_ / nowarn;
%end;

%* now set the macro variables ;
%let _parameters_=;
%let _serror = %sysfunc(getoption(serror,keyword));
options noserror;
data _null_;
   set &_metadata end=eof;
   where

   %do _i=1 %to &_num_levels;
      %if (&_i eq 1) %then
         upcase(Filter) in (%seplist(%upcase(GLOBAL^&&_fltr&_i),indlm=%str(^),nest=QQ));
      %else
         and &&_fltr&_i;
   %end;

   %if (&_name ne ) %then
      and upcase(Name) in (&_name);

   %if (%superq(_where) ne ) %then
      and &_where;
   ;

   if (&_missing eq 0) then do;
      if missing(value) then do;
         link eof;
         return;
      end;
   end;

   %* Convert special characters from the Excel data ;
   %* Alt-Enter ("0A"x) and Paragraph Marks ("0D"x) become straight split characters ;
   %* Form Feed ("0B"x) become split characters + hanging indents ;
   %* Convert them to unresolved macro variable references, which get specified during later processing ;
   %* Convert "Smart quotes" to "normal" single and double quotes ;
%* rx1=prxparse('s/\x0A|x0D/&split./o');
%* rx2=prxparse('s/\x0B/&split.&hanging_indent/o');
   rx3=prxparse('s/\x{201C}|\x{201D}|\x{201E}/"/o');  %* "smart" double quotes ;
   rx4=prxparse("s/\x{2018}|\x{2019}/'/o");           %* "smart" single quotes ;

%* if prxmatch(rx1,value) then value=prxchange(rx1,-1,trim(value));
%* if prxmatch(rx2,value) then value=prxchange(rx2,-1,trim(value));
   if prxmatch(rx3,value) then value=prxchange(rx3,-1,trim(value));
   if prxmatch(rx4,value) then value=prxchange(rx4,-1,trim(value));

   drop rx1 rx2 rx3 rx4;

   if (&_resolve eq 1) then do;
      name  = resolve(name);
      value = resolve(value);
   end;

   length _parameters_ $32767;
   retain _parameters_;

   if (^missing(name)) and (^notname(strip(name))) and (anydigit(strip(name)) ne 1) then do;
      _parameters_=catx(" ",_parameters_,name);
      call symputx(name,trim(value),"&_symboltable");
   end;
   else do;
      putlog "USER NO" "TE: " name "is an invalid macro variable name and will not be created.";
   end;

   EOF:
   if (eof) then call symputx("_parameters_",_parameters_,"G");
run;
options &_serror;

%quit:

%mend;

/******* END OF FILE *******/

repo_name: scottbass/SAS

repo_language: SAS

mime_type: text/plain


text:
/*=====================================================================
Program Name            : optval.sas
Purpose                 : Returns value of option
SAS Version             : SAS 9.1.3
Input Data              : N/A
Output Data             : N/A

Macros Called           : None

Originally Written by   : Scott Bass
Date                    : 01MAY2007
Program Version #       : 1.0

=======================================================================

Scott Bass (sas_l_739@yahoo.com.au)

This code is licensed under the Unlicense license.
For more information, please refer to http://unlicense.org/UNLICENSE.

This is free and unencumbered software released into the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or
distribute this software, either in source code form or as a compiled
binary, for any purpose, commercial or non-commercial, and by any
means.

=======================================================================

Modification History    : Original version

=====================================================================*/

/*---------------------------------------------------------------------
Usage:

* print current options ;
%put %optval(missing) %optval(ls) %optval(ps);

* save values of current options, then change options ;
%let options = %optval(missing) %optval(ls) %optval(ps);
%put &options;

options missing="$" ls=70 ps=20;
%put %optval(missing) %optval(ls) %optval(ps);

* use new value of missing option ;
data _null_;
   x=.;
   put x=;
run;

* restore options to original values ;
options &options;
%put %optval(missing) %optval(ls) %optval(ps);

-----------------------------------------------------------------------
Notes:

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

%macro optval
/*---------------------------------------------------------------------
Returns value of option
---------------------------------------------------------------------*/
(OPTNAME       /* Option name (REQ).                                 */
,KEYWORD=N     /* Return value in a KEYWORD= format? (Opt)           */
               /* If Y, the KEYWORD parameter is passed to the       */
               /* getoption function.                                */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
);

%local macro parmerr;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(OPTNAME,      _req=1,_words=0,_case=N)
%parmv(KEYWORD,      _req=1,_words=0,_case=Y,_val=0 1)

%if (&parmerr) %then %goto quit;

%if (&keyword) %then %do;
   %sysfunc(getoption(&optname,keyword))
%end;
%else %do;
   %sysfunc(getoption(&optname))
%end;

%quit:
%* if (&parmerr) %then %abort;

%mend;

/******* END OF FILE *******/


text:
/*=====================================================================
Program Name            : libname_attr_sqlsvr.sas
Purpose                 : Retrieve libname attributes
                          (server name, database name, default schema)
                          from an ODBC libname allocated to a
                          SQL Server instance.
SAS Version             : SAS 9.3
Input Data              : N/A
Output Data             : N/A

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 22Jun2018
Program Version #       : 1.0

=======================================================================

Scott Bass (sas_l_739@yahoo.com.au)

This code is licensed under the Unlicense license.
For more information, please refer to http://unlicense.org/UNLICENSE.

This is free and unencumbered software released into the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or
distribute this software, either in source code form or as a compiled
binary, for any purpose, commercial or non-commercial, and by any
means.

=======================================================================

Modification History    : Original version

=====================================================================*/

/*---------------------------------------------------------------------
Usage:

* no additional options, esp. schema= ;

libname FOO ODBC
   NOPROMPT="Driver={SQL Server Native Client 10.0};
   Server=SVDCMHPRRLSQD01;
   Database=master;
   Trusted_Connection=yes;"
;

%libname_attr_sqlsvr(FOO);

* the macro prints these, but show that they are global macro variables ;
%put &=server &=dbname &=schema &=schema2;

* &schema is the schema as specified by the libname allocation ;
* &schema2 is the default schema for this connection to SQL Server ;

=======================================================================

* specify a schema ;
libname FOO ODBC
   NOPROMPT="Driver={SQL Server Native Client 10.0};
   Server=SVDCMHPRRLSQD01;
   Database=RLCS_dev;
   Trusted_Connection=yes;"
   bulkload=yes schema=content
;

libname BAR ODBC
   NOPROMPT="Driver={SQL Server Native Client 10.0};
   Server=SVDCMHPRRLSQD01;
   Database=master;
   Trusted_Connection=yes;"
;

%libname_attr_sqlsvr(FOO,prefix=yes);
%libname_attr_sqlsvr(BAR,prefix=yes);

%put &=foo_server &=foo_dbname &=foo_schema &=foo_schema2;
%put &=bar_server &=bar_dbname &=bar_schema &=foo_schema2;

=======================================================================

* demonstrate use in explicit pass-through ;
libname FOO ODBC
   NOPROMPT="Driver={SQL Server Native Client 10.0};
   Server=SVDCMHPRRLSQD01;
   Database=RLCS_dev;
   Trusted_Connection=yes;"
   bulkload=yes schema=content
;

libname BAR ODBC
   NOPROMPT="Driver={SQL Server Native Client 10.0};
   Server=SVDCMHDVSDWSQD1;
   Database=master;
   Trusted_Connection=yes;"
   schema=sys
;

%libname_attr_sqlsvr(FOO,prefix=yes);  * prefix=yes, so FOO_dbname, etc. ;
%libname_attr_sqlsvr(BAR,prefix=no);   * prefix=no, so dbname, schema, etc. ;

proc sql;
   connect using foo;
   select * from connection to foo (
      SELECT TOP 10 facility_identifier, facility_name, facility_type
      FROM &foo_dbname..&foo_schema..FACILITY
   );
   connect using bar;
   select * from connection to bar (
      SELECT name, database_id, collation_name
      FROM &dbname..&schema..databases
   );
quit;

-----------------------------------------------------------------------
Notes:

The main purpose of this macro is to retrieve metadata (macro variables)
about a given ODBC libname, knowing nothing more than the libname.

This is especially useful if the library is allocated in an external
program, or via the metadata engine.

The purpose of the metadata is to make explicit pass-through code
easier to maintain.  The idea is to allocate a library for every
server, database, and schema you need to access, even if the data could
be accessed via a single connection with two-,three-, or four-level
(i.e. linked server) names.

This approach gives you visibility of all the relevant tables in the
SAS libraries, makes it easy to write metadata-driven code (i.e.
using macro variables), and makes it easy to maintain your code if
server or database names change in the future.

This macro must be called outside data step or PROC context, i.e. in
"open code", but it generally only needs to be called once.

The the prefix=yes parameter is specified, the macro variables returned
are <libref>_server, <libref>_dbname, and <libref>_schema.
Otherwise server, dbname, and schema are returned.

Keep in mind this WOULD cause a code maintenance issue if the libnames
ever changed in the future.  If this is an issue, you can use this
approach:

%libname_attr_sqlsvr(FOO,prefix=no);
%let lib1_server=&server;%let lib1_dbname=&dbname;%let lib1_schema=&schema;

%libname_attr_sqlsvr(BAR,prefix=yes);
%let lib2_server=&server;%let lib2_dbname=&dbname;%let lib2_schema=&schema;

Since the macro variable name prefix is now static, it will not change
if the libname changes in the future.

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

%macro libname_attr_sqlsvr
/*---------------------------------------------------------------------
Retrieve libname attributes (server name, database name, default schema)
from an ODBC libname allocated to a SQL Server instance.
---------------------------------------------------------------------*/
(LIBREF        /* Libref for ODBC libname allocated to a SQL Server  */
               /* instance (REQ).                                    */
,PREFIX=N      /* Add the libref as a prefix to the returned macro   */
               /* variables? (Opt).                                  */
               /* Default value is NO.  Valid values are:            */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
);

%local macro parmerr dsid _engine _server _dbname _schema _schema2;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(LIBREF,       _req=1,_words=0,_case=U)
%parmv(PREFIX,       _req=0,_words=0,_case=U,_val=0 1)

%if (&parmerr) %then %goto quit;

%* additional error checking ;

%* is the libref allocated? ;
%if (%sysfunc(libref(&libref)) ne 0) %then %do;
   %parmv(_msg=The libref &libref is not allocated)
   %goto %quit;
%end;

%* is the libref an ODBC library? ;
%* open sashelp.vlibnam ;
%let dsid=%sysfunc(open(sashelp.vlibnam (where=(upcase(libname)="%upcase(&libref)"))));

%* was the open successful? ;
%if (&dsid le 0) %then %do;
   %parmv(_msg=Unable to open sashelp.vlibnam)
   %goto quit;
%end;

%* fetch the (only) row ;
%let rc=%sysfunc(fetchobs(&dsid,1));
%if (&rc ne 0) %then %do;
   %parmv(_msg=Unable to fetch row from sashelp.vlibnam)
   %goto %quit;
%end;

%* get the engine ;
%let _engine=%sysfunc(getvarc(&dsid,
             %sysfunc(varnum(&dsid,ENGINE))));
%if (&_engine ne ODBC) %then %do;
   %parmv(_msg=Libref &libref is not allocated with the ODBC engine)
   %goto %quit;
%end;

%* get the schema as set by the libname statement ;
%let _schema=%sysfunc(getvarc(&dsid,
             %sysfunc(varnum(&dsid,SYSVALUE))));

%* get the remaining attributes from SQL Server itself ;
proc sql noprint;
   option nolabel;
   connect using &libref;
   select server
         ,dbname
         ,schema2
   into   :_server trimmed
         ,:_dbname trimmed
         ,:_schema2 trimmed
   from   connection to &libref (
   SELECT @@servername  AS [server]
         ,DB_NAME()     AS [dbname]
         ,SCHEMA_NAME() AS [schema2]
   );
quit;

%if (&prefix eq 1) %then %do;
   %global &libref._server &libref._dbname &libref._schema &libref._schema2;
   %let &libref._server=&_server;
   %let &libref._dbname=&_dbname;
   %let &libref._schema=&_schema;
   %let &libref._schema2=&_schema2;

   %* cannot use the &=mvarname syntax ;
   %*put %upcase(&libref._server)=&&&libref._server %upcase(&libref._dbname)=&&&libref._dbname %upcase(&libref._schema)=&&&libref._schema %upcase(&libref._schema2)=&&&libref._schema2;
%end;
%else %do;
   %global server dbname schema schema2;
   %let server=&_server;
   %let dbname=&_dbname;
   %let schema=&_schema;
   %let schema2=&_schema2;

   %*put &=server &=dbname &=schema &=schema2;
%end;

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

%mend;

/******* END OF FILE *******/


text:
/*=====================================================================
Program Name            : export_csv.sas
Purpose                 : Wrapper macro for the %export_dlm macro
                          used to export a SAS dataset to a CSV file.
SAS Version             : SAS 9.4
Input Data              : SAS dataset
Output Data             : CSV flat file

Macros Called           : export_dlm, parmv

Originally Written by   : Scott Bass
Date                    : 04SEP2019
Program Version #       : 1.0

=======================================================================

Scott Bass (sas_l_739@yahoo.com.au)

This code is licensed under the Unlicense license.
For more information, please refer to http://unlicense.org/UNLICENSE.

This is free and unencumbered software released into the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or
distribute this software, either in source code form or as a compiled
binary, for any purpose, commercial or non-commercial, and by any
means.

=======================================================================

Modification History    : Original version

=====================================================================*/

/*---------------------------------------------------------------------
Usage:

See Usage in the %export_dlm macro header.

-----------------------------------------------------------------------
Notes:

See Notes in the %export_dlm macro header.

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

%macro export_csv
/*---------------------------------------------------------------------
Wrapper macro for the %export_dlm macro
used to export a SAS dataset to a CSV file.
---------------------------------------------------------------------*/
(DATA=         /* Dataset to export (REQ).                           */
               /* Data set options, such as a where clause, may be   */
               /* specified.                                         */
,PATH=         /* Output directory or file path (REQ).               */
               /* Either a properly quoted physical file             */
               /* (single or double quotes), or an already allocated */
               /* fileref may be specified.                          */
,REPLACE=N     /* Replace output file? (Opt).                        */
               /* Default value is NO.  Valid values are:            */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
,LABEL=N       /* Use data set labels instead of names? (Opt).       */
               /* Default value is NO.  Valid values are:            */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
               /* If the data set variable does not have a label     */
               /* then the variable name is used.                    */
,HEADER=Y      /* Output a header row? (Opt).                        */
               /* Default value is YES.  Valid values are:           */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
               /* If the data set variable does not have a label     */
               /* then the variable name is used.                    */
,LRECL=32767   /* Logical record length for output file (Opt).       */
               /* Default value is 32767 (32K)                       */
);

%local macro parmerr;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(DATA,         _req=1,_words=1,_case=N)  /* words=1 allows multiple datasets */
%parmv(PATH,         _req=1,_words=1,_case=N)
%parmv(REPLACE,      _req=0,_words=0,_case=U,_val=0 1)
%parmv(LABEL,        _req=0,_words=0,_case=U,_val=0 1)
%parmv(HEADER,       _req=0,_words=0,_case=U,_val=0 1)
%parmv(LRECL,        _req=0,_words=0,_case=U,_val=POSITIVE)

%if (&parmerr) %then %return;

%* call the %export_dlm macro with the correct parameters ;
%* all further error trapping is done by the %export_dlm macro ;
%export_dlm(
   data=%superq(data)
   ,path=%superq(path)
   ,dbms=csv
   ,replace=%superq(replace)
   ,label=%superq(label)
   ,header=%superq(header)
   ,lrecl=%superq(lrecl)
)

%mend;

/******* END OF FILE *******/


text:
/*=====================================================================
Program Name            : export_rldx.sas
Purpose                 : Wrapper macro to call various child macros
                          to export a SAS dataset or SQL Server table
                          to various target formats.
SAS Version             : SAS 9.4
Input Data              : SAS dataset or SQL Server table
Output Data             : Exported data in various formats

Macros Called           : parmv
                          export_dlm
                          export_dbms
                          export_csv
                          export_spss
                          export_stata
                          export_saphari

Originally Written by   : Scott Bass
Date                    : 04SEP2019
Program Version #       : 1.0

=======================================================================

Scott Bass (sas_l_739@yahoo.com.au)

This code is licensed under the Unlicense license.
For more information, please refer to http://unlicense.org/UNLICENSE.

This is free and unencumbered software released into the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or
distribute this software, either in source code form or as a compiled
binary, for any purpose, commercial or non-commercial, and by any
means.

=======================================================================

Modification History    : Original version

=====================================================================*/

/*---------------------------------------------------------------------
Usage:

%let path=\\sascs\linkage\RL_content_snapshots\Temp;  %* edit to suit your requirements ;

* create a dummy source dataset ;
options dlcreatedir;
libname sasout "%sysfunc(pathname(work))\temp";

* RLCS Server ;
%libname_sqlsvr(libref=DMT,server=SVDCMHPRRLSQD01,port=,database=RLCS_prod,schema=dmt)

* you may have to modify based on the current vwAP_FLAT_SAS* views available ;
data sasout.apdc_flat;
   set dmt.vwAP_FLAT_SAS_49;
   where year(episode_end_date)=2010;
   if _n_ > 1000 then stop;
run;

%export_rldx(
   data=sasout.apdc_flat
   ,prefix=apdc
   ,export_saphari=Y
)

Export sasout.apdc_flat to Saphari format,
which just creates a hoist formatted view.

=======================================================================

* the remaining examples assume you have created the test data ;
* in the first example. ;

%export_rldx(
   data=sasout.apdc_flat (where=(month(episode_end_date) in (1,2,3)))
   ,outlib=work
   ,outdata=apdc_subset
   ,export_sas=Y
)

Export sasout.apdc_flat to sasout.apdc_subset,
with episode_end_dates occurring in the first calendar quarter.

=======================================================================

%export_rldx(
   data=sasout.apdc_flat
   ,path="&path"
   ,replace=Y
   ,header=Y
   ,label=N
   ,export_csv=Y
)

Export sasout.apdc_flat to CSV file
\\sascs\linkage\RL_content_snapshots\Temp\apdc_flat.csv

=======================================================================

%export_rldx(
   data=sasout.apdc_flat
   ,path="&path"
   ,replace=Y
   ,label=N
   ,export_xlsx=Y
)

Export sasout.apdc_flat to XLSX file
\\sascs\linkage\RL_content_snapshots\Temp\apdc_flat.xlsx

=======================================================================

%export_rldx(
   data=sasout.apdc_flat (where=(month(episode_end_date) in (1,2,3)))
   ,path="&path\apdc_subset.sav"
   ,replace=Y
   ,label=N
   ,export_spss=Y
)

Export sasout.apdc_flat to SPSS file
\\sascs\linkage\RL_content_snapshots\Temp\apdc_subset.sav

=======================================================================

%export_rldx(
   data=sasout.apdc_flat (where=(month(episode_end_date) in (1,2,3)))
   ,path="&path\apdc_subset.dta"
   ,replace=Y
   ,label=N
   ,export_stata=Y
)

Export sasout.apdc_flat to STATA file
\\sascs\linkage\RL_content_snapshots\Temp\apdc_subset.dta

=======================================================================

* You may want to delete all files in the target path  ;
* and work.apdc_final before running this... ;

%export_rldx(
   data=sasout.apdc_flat
   ,path="&path"
   ,replace=Y
   ,header=Y
   ,label=N
   ,outlib=work
   ,outdata=apdc_final
   ,prefix=apdc
   ,export_saphari=Y
   ,export_sas=Y
   ,export_csv=Y
   ,export_xlsx=Y
   ,export_spss=Y
   ,export_stata=Y
)

Export sasout.apdc_flat to "the lot" (all specified formats).

In this scenario, you need to specify a directory only
for the PATH parameter, and allow the output files to
be automatically named.

-----------------------------------------------------------------------
Notes:

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

%macro export_rldx
/*---------------------------------------------------------------------
Wrapper macro to call various child macros to export a SAS dataset
or SQL Server table to various target formats.
---------------------------------------------------------------------*/
(DATA=         /* Dataset to export (REQ).                           */
               /* Data set options, such as a where clause, may be   */
               /* specified.                                         */

               /* ##### EXTERNAL FILES (DLM OR DBMS) #####           */

,PATH=         /* Output directory or file path (Opt).               */
               /* Either a properly quoted physical file             */
               /* (single or double quotes), or an already allocated */
               /* fileref may be specified.                          */
,REPLACE=N     /* Replace output file? (Opt).                        */
               /* Default value is NO.  Valid values are:            */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
,LABEL=N       /* Use data set labels instead of names? (Opt).       */
               /* Default value is NO.  Valid values are:            */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
               /* If the data set variable does not have a label     */
               /* then the variable name is used.                    */

               /* ##### CSV FILE ONLY #####                          */

,HEADER=Y      /* Output a header row? (Opt).                        */
               /* Default value is YES.  Valid values are:           */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
               /* If the data set variable does not have a label     */
               /* then the variable name is used.                    */
,LRECL=32767   /* Logical record length for output file (Opt).       */
               /* Default value is 32767 (32K)                       */

               /* ##### SAPHARI #####                                */

,PREFIX=       /* Output HOIST view name prefix (Opt).               */
               /* Usual dataset name prefixes are                    */
               /* APDC, EDDC, DTH, or CODURF, but any dataset name   */
               /* prefix can be specified.                           */

               /* ##### SAS DATASETS #####                           */

,OUTLIB=       /* Output library (Opt).                              */
,OUTDATA=      /* Output dataset name (Opt).                         */
               /* If specified, the source dataset is copied         */
               /* to the target library with the source dataset      */
               /* name, then the target dataset is renamed           */
               /* to the OUTDATA name.                               */

               /* ##### DESIRED OUTPUT FORMATS #####                 */

,EXPORT_SAPHARI=N
               /* Export to Saphari format? (Opt).                   */
               /* Default value is NO.  Valid values are:            */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
,EXPORT_SAS=N
               /* Export to SAS format? (Opt).                       */
               /* Same options as per EXPORT_SAPHARI                 */
,EXPORT_CSV=N
               /* Export to CSV format? (Opt).                       */
               /* Same options as per EXPORT_SAPHARI                 */
,EXPORT_XLSX=N
               /* Export to XLSX format? (Opt).                      */
               /* Same options as per EXPORT_SAPHARI                 */
,EXPORT_SPSS=N
               /* Export to SPSS format? (Opt).                      */
               /* Same options as per EXPORT_SAPHARI                 */
,EXPORT_STATA=N
               /* Export to STATA format? (Opt).                     */
               /* Same options as per EXPORT_SAPHARI                 */
);

%global syscc;
%local macro parmerr;
%local _temp _pos _options;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(DATA,            _req=1,_words=1,_case=N)  /* words=1 allows multiple datasets */
%parmv(PATH,            _req=0,_words=1,_case=N)
%parmv(REPLACE,         _req=0,_words=0,_case=U,_val=0 1)
%parmv(LABEL,           _req=0,_words=0,_case=U,_val=0 1)
%parmv(HEADER,          _req=0,_words=0,_case=U,_val=0 1)
%parmv(LRECL,           _req=0,_words=0,_case=U,_val=POSITIVE)

%parmv(OUTLIB,          _req=0,_words=0,_case=U)
%parmv(OUTDATA,         _req=0,_words=0,_case=U)

%parmv(EXPORT_SAPHARI,  _req=0,_words=0,_case=U,_val=0 1)
%parmv(EXPORT_SAS,      _req=0,_words=0,_case=U,_val=0 1)
%parmv(EXPORT_CSV,      _req=0,_words=0,_case=U,_val=0 1)
%parmv(EXPORT_XLSX,     _req=0,_words=0,_case=U,_val=0 1)
%parmv(EXPORT_SPSS,     _req=0,_words=0,_case=U,_val=0 1)
%parmv(EXPORT_STATA,    _req=0,_words=0,_case=U,_val=0 1)

%if (&parmerr) %then %return;

%* abort on upstream error (continue on upstream warning);
%if (&syscc gt 4) %then %do;
   %parmv(_msg=Export aborted due to upstream error.  Submit %nrstr(%let syscc=0;) to reset error)
   %return;
%end;

%* parse off dataset options (usually a where clause) ;
%let _temp = %superq(data);
%let _pos = %sysfunc(findc(%superq(data),%str(%()));
%if (&_pos) %then %do;
   %let _temp = %substr(%superq(data),1,&_pos-1);
   %let _options = %substr(%superq(data),&_pos);
%end;

%* does the source dataset exist? ;
%if not (%sysfunc(exist(%superq(_temp),DATA)) or %sysfunc(exist(%superq(_temp),VIEW))) %then %do;
   %let syscc=8;
   %parmv(_msg=Source dataset %superq(_temp) does not exist)
   %return;
%end;

%* process each selected option by calling the child macros ;

%if (&syscc gt 4) %then %do;
   %parmv(_msg=Export aborted due to upstream error.  Submit %nrstr(%let syscc=0;) to reset error)
   %return;
%end;

%if (&export_saphari eq 1) %then %do;
   %export_saphari(
      data=%superq(data)
      ,prefix=%superq(prefix)
   )
%end;

%if (&syscc gt 4) %then %do;
   %parmv(_msg=Export aborted due to upstream error.  Submit %nrstr(%let syscc=0;) to reset error)
   %return;
%end;

%if (&export_sas eq 1) %then %do;
   %export_sas(
      data=%superq(data)
      ,outlib=%superq(outlib)
      ,outdata=%superq(outdata)
   )
%end;

%if (&syscc gt 4) %then %do;
   %parmv(_msg=Export aborted due to upstream error.  Submit %nrstr(%let syscc=0;) to reset error)
   %return;
%end;

%if (&export_csv eq 1) %then %do;
   %export_csv(
      data=%superq(data)
      ,path=%superq(path)
      ,replace=%superq(replace)
      ,label=%superq(label)
      ,header=%superq(header)
      ,lrecl=%superq(lrecl)
   )
%end;

%if (&syscc gt 4) %then %do;
   %parmv(_msg=Export aborted due to upstream error.  Submit %nrstr(%let syscc=0;) to reset error)
   %return;
%end;

%if (&export_xlsx eq 1) %then %do;
   %export_xlsx(
      data=%superq(data)
      ,path=%superq(path)
      ,replace=%superq(replace)
      ,label=%superq(label)
   )
%end;

%if (&syscc gt 4) %then %do;
   %parmv(_msg=Export aborted due to upstream error.  Submit %nrstr(%let syscc=0;) to reset error)
   %return;
%end;

%if (&export_spss eq 1) %then %do;
   %export_spss(
      data=%superq(data)
      ,path=%superq(path)
      ,replace=%superq(replace)
      ,label=%superq(label)
   )
%end;

%if (&syscc gt 4) %then %do;
   %parmv(_msg=Export aborted due to upstream error.  Submit %nrstr(%let syscc=0;) to reset error)
   %return;
%end;

%if (&export_stata eq 1) %then %do;
   %export_stata(
      data=%superq(data)
      ,path=%superq(path)
      ,replace=%superq(replace)
      ,label=%superq(label)
   )
%end;

%mend;

/******* END OF FILE *******/


text:
/*=====================================================================
Program Name            : stp_batch_submit.sas
Purpose                 : Submit a SAS batch program from a Stored Process
SAS Version             : SAS 9.2
Input Data              : N/A
Output Data             : N/A

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 03MAY2012
Program Version #       : 1.0

=======================================================================

Scott Bass (sas_l_739@yahoo.com.au)

This code is licensed under the Unlicense license.
For more information, please refer to http://unlicense.org/UNLICENSE.

This is free and unencumbered software released into the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or
distribute this software, either in source code form or as a compiled
binary, for any purpose, commercial or non-commercial, and by any
means.

=======================================================================

Modification History    : Original version

=====================================================================*/

/*---------------------------------------------------------------------
Usage:

* simulate macro variables created from a stored process ;
* best practice is that the macro variables have the prefix "batch_" ;

* macro variables sent to the batch job ;
* these macro variables are set outside this macro, ;
* either by the STP prompts or in the STP SAS code. ;
%let batch_username     = &_username;
%let batch_jobname      = Your SAS Batch Job;
%let batch_config_file  = C:\Temp\batch_test.cfg;

* create a message dataset ;
* this dataset is used to display a confirmation message to the user ;
* this dataset is set outside this macro ;
data message;
   infile datalines truncover;
   input message $200.;
   keep message;
   datalines4;
Job &batch_jobname submitted at %sysfunc(strip(%sysfunc(datetime(),datetime18.))).
You will receive an email when the job finishes.
;;;;
run;

* call this macro ;
%stp_batch_submit(config=path_to_sas_config_file);

=======================================================================

* call this macro, overriding default values ;
%stp_batch_submit(
   message=my_message_dataset
   ,mvars=name like 'BATCH_' or name in ('FOO','BAR','BLAH')
   ,sas=path_to_sas_executable
   ,config=path_to_sas_config_file
   ,pgm=path_to_sas_program_file
   ,log=path_to_sas_log_file
   ,lst=path_to_sas_print_file
   ,options=any desired SAS invocation options
   ,timeout=300
);

=======================================================================

* As an alternative to a message dataset, you can also use an external file ;
%stp_batch_submit(
   messagefile=C:\Temp\message.html
   ,config=path_to_sas_config_file
);

-----------------------------------------------------------------------
Notes:

The message dataset must be created outside this macro.  If the message
dataset does not exist, a default message is displayed to the end user.

The message dataset must contain valid HTML.  You could create anything
in the message dataset, for example displaying a message box via
javascript, then javascript to close the new window.

Alternatively, you can create any HTML/javascript/etc. code and save it
in an external file, then use that file via the MESSAGEFILE parameter.
If both MESSAGE and MESSAGEFILE parameters are specified, the
MESSAGEFILE parameter has priority.

By default, all macro variables created by the stored process that
begin with "BATCH_" are propagated to the batch job.  The list of macro
variables sent to the batch program can be changed by setting the MVARS
parameter.

Hidden stored process prompts (macro variables) (usually prefixed with
"batch_") can be used to pass additional metadata to the batch job.

The macro variables are propagated to the batch program via the dataset
SAVE.PARAMETERS, where "SAVE" is the temporary directory created by the
stored process session.  This dataset name is hard coded and cannot be
changed unless you edit the macro code.

The SAVE library is a temporary library created by the stored process
server.  It is created under the stored process server work directory,
and is deleted once the stored process session times out.  The stored
process session timeout default value is 2 minutes, which is more than
enough time for the batch job to start and re-initialize the macro
variables.  However, this timeout can be changed via the TIMEOUT
parameter, although this would rarely if ever be needed.

The batch job must contain this code (or the functional equivalent):

-----------------------------------------------------------------------

* Allocate the SAVE library passed in via sysparm ;
* Additional parameters could be passed in via sysparm, delimited by ^ or | ;
libname save "%scan(&sysparm,1,%str(^|))";

* Set macro variables passed in to the batch job by the Stored Process ;
data _null_;
   set save.parameters;
   call symputx(name,value,"G");
run;

* The rest of the batch program goes here... ;
* Macro variable processing would be done exactly as in a stored process ;

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

%macro stp_batch_submit
/*---------------------------------------------------------------------
Submit a SAS batch program from a Stored Process
---------------------------------------------------------------------*/
(MESSAGE=work.message
               /* Message dataset to display a confirmation message  */
               /* to the end user (REQ).  The MESSAGE parameter must */
               /* define a syntactically correct dataset.  If the    */
               /* dataset does not exist, a default dataset is       */
               /* created.                                           */
,MESSAGEFILE=  /* Absolute path to an HTML file to display a         */
               /* confirmation message to the end user (Opt).        */
               /* If MESSAGEFILE is specified it will take priority  */
               /* over MESSAGE (which has a default value).          */
,MVARS=name like 'BATCH_%'
               /* Macro variables to propagate to the batch job (Opt)*/
               /* If blank, then all macro variables are propagated  */
               /* to the batch job.  This parameter should be        */
               /* specified as a where clause appropriate for the    */
               /* SQL dictionary table dictionary.macros.            */
,SAS=/opt/sas/Config/Lev1/SASApp/BatchServer/sasbatch.sh
               /* Absolute path to SAS executable (REQ).             */
,CONFIG=       /* Absolute path to SAS config file (Opt).            */
,PGM=          /* Absolute path to SAS program file (Opt).           */
,LOG=          /* Absolute path to SAS log file or directory (Opt).  */
,LST=          /* Absolute path to SAS print file or directory (Opt).*/
,OPTIONS=      /* Any desired SAS invocation options (Opt).          */
,TIMEOUT=120   /* Stored process server session timeout in seconds   */
               /* (REQ).                                             */
);

%local macro parmerr;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(SAS,          _req=1,_words=1,_case=N)
%parmv(TIMEOUT,      _req=1,_words=1,_case=N)

%if (&parmerr) %then %goto quit;

%* Additional error checking ;
%* If both CONFIG and PGM are blank, abort to prevent a DMS session ;
%* from being launched on the server ;
%if (%superq(config) eq ) and (%superq(pgm) eq ) %then %do;
   %parmv(_msg=%str(ERR)OR: Either CONFIG or PGM must be specified)
   %goto quit;
%end;

/*
Propagate any extra STP variables by prefixing them with "BATCH_"
*/
%let batch_username = &_username;
%let batch_program  = &_program;
%let batch_progname = %scan(&batch_program,-1,/);

/*
Create a Stored Process (STP) session, which allocates the temporary SAVE library.
This library is used to pass parameters between the STP and the batch job.
*/
%if (%sysfunc(libref(SAVE)) ne 0) %then %do;
   %let rc=%sysfunc(stpsrv_session(create,&timeout));
%end;

/*
Check that the SAVE library was allocated
*/
libname save list;

/*
This macro variable must be set AFTER the session is created.
*/
%let batch_save_library=%sysfunc(pathname(save));

/*
If the message dataset does not exist, create a default message dataset.
If a messagefile was specified, create a message dataset from that file.
Bummer you cannot use cards/datalines in a macro.
*/
%if (not %sysfunc(exist(&message)) or (%superq(messagefile) ne )) %then %do;
   %if (%superq(messagefile) eq ) %then %do;
      data &message;
         length message $200 buffer $32767;
         keep message;
         buffer='
| <html>
| <head>
| <script type="text/javascript">
| alert("Your &batch_progname has been submitted. You will receive an email when your job has finished.");
| window.close()
| </script>
| </head>
| <body>
| </body>
| </html>
';
         i=1;
         do while (scan(buffer,i,"|") ne "");
            message=left(scan(buffer,i,"|"));
            output;
            i+1;
         end;
      run;
   %end;
   %else %do;
      data &message;
         length message $32767;
         infile "&messagefile";
         input;
         message=_infile_;
      run;
   %end;
%end;

/*
Now copy the desired parameters (macro variables) to the SAVE library.
This dataset will be used by the batch job to recreate the macro variables.
*/
%if (%sysfunc(exist(save.parameters))) %then %lock(member=save.parameters,action=lock);
proc sql noprint;
   create table save.parameters as
      select
         name
         ,value
      from
         dictionary.macros
      where
         &mvars
   ;
quit;
%lock(member=save.parameters,action=clear)

/*
Setup the parameters for invoking SAS, then launch the SAS job.
You MUST pass in the path to the SAVE library location via sysparm.

My preference is to use a configuration file, rather than command line options,
with ALL other job configuration (eg. -sysin) done in the configuration file.
This gives the maintainer of the code ONE place to go to change the batch job options.
However, you can configure this however you wish.
*/

/*
NOTE:  Do NOT change the single/double quoting below, or you will likely break this!
*/
%let parms=;
%if (%superq(config)  ne ) %then %let parms=&parms -config '&config';
%if (%superq(pgm)     ne ) %then %let parms=&parms -sysin '&pgm';
%if (%superq(log)     ne ) %then %let parms=&parms -log '&log';
%if (%superq(lst)     ne ) %then %let parms=&parms -print '&lst';
%if (%superq(options) ne ) %then %let parms=&parms &options;

%let parms=&parms -sysparm '&batch_save_library';

/*
%* We need to specify a service user credentials for the spawned SAS job ;
%* For now just use my credentials until the service user is setup (including TeraProdAuth Domain) ;
*/
%let parms=&parms -metauser m49505 -metapass {sas002}5F4652380A480B7F1295D9FD49482080;

/*
Invoke the SAS batch job.
*/
%let options=%sysfunc(getoption(quotelenmax));
options noquotelenmax;
%if (&sysscp eq WIN) %then %do;
   systask command " ""&sas"" &parms " nowait;
%end;
%else %do;
   systask command "&sas &parms" nowait;
%end;
options &options;

/*
Resolve any embedded macro variables in the message dataset.
The options nomerror and noserror suppress warning messages in the log
for unresolved macro and unresolved macro variable references.
HTML code in particular can have tokens (eg. &_program=...) that look like macro variables
but are HTML tokens instead.
*/

%if (%sysfunc(fileref(_webout)) le 0) %then %do;

%let options=%sysfunc(getoption(merror,keyword)) %sysfunc(getoption(serror,keyword));
options nomerror noserror;
data work._message_ (compress=char);
   %* pick a variable not likely to be in the source dataset ;
   length ________ $32767;
   set &message;
   %* there should only be one variable, but this construct ;
   %* allows the variable name to be anything ;
   ________=cats(of _char_);
   ________=resolve(________);
run;
options &options;

/*
Now display the confirmation message to the user
*/
data _null_;
   file _webout;
   set work._message_;
   put ________;
run;

%end;

%quit:
%mend;

/******* END OF FILE *******/


text:
/*=====================================================================
Program Name            : sql_datetime.sas
Purpose                 : Converts a SAS datetime literal to a
                          SQL Server datetime literal.
SAS Version             : SAS 9.3
Input Data              : SAS datetime literal (macro variable)
Output Data             : SQL Server literal

Macros Called           : None

Originally Written by   : Scott Bass
Date                    : 18SEP2017
Program Version #       : 1.0

=======================================================================

Scott Bass (sas_l_739@yahoo.com.au)

This code is licensed under the Unlicense license.
For more information, please refer to http://unlicense.org/UNLICENSE.

This is free and unencumbered software released into the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or
distribute this software, either in source code form or as a compiled
binary, for any purpose, commercial or non-commercial, and by any
means.

=======================================================================

Modification History    : Original version

=====================================================================*/

/*---------------------------------------------------------------------
Usage:

* this creates DATE7:TIME literal ;
%let dt=%sysfunc(datetime(),datetime16.);
%put %sql_datetime(&dt);
%put %squote(%sql_datetime(&dt));

=======================================================================

* this creates DATE9:TIME literal (length of 19-40 all work) ;
%let dt=%sysfunc(datetime(),datetime19.);
%put %sql_datetime(&dt);
%put %squote(%sql_datetime(&dt));

=======================================================================

* DATE9:TIME literal with milliseconds ;
%let dt=%sysfunc(datetime(),datetime24.3);
%put %sql_datetime(&dt);
%put %squote(%sql_datetime(&dt));

=======================================================================

* this creates ISO8601 (YYYY-MM-DDTHH:MM:SS) literal ;
%let dt=%sysfunc(datetime(),e8601dt.);
%put %sql_datetime(&dt);
%put %squote(%sql_datetime(&dt));

=======================================================================

* ISO8601 literal with milliseconds ;
%let dt=%sysfunc(datetime(),e8601dt24.3);
%put %sql_datetime(&dt);
%put %squote(%sql_datetime(&dt));

=======================================================================

* More extensive test ;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
options msglevel=I;
options mprint mrecall;

* Change the below to suit your environment ;
* libname foo odbc NOPROMPT="Driver={SQL Server Native Client 10.0};Server=YOURSERVER;Database=YOURDATABASE;Trusted_Connection=yes;";

%macro test(dt);
proc sql;
   connect using foo;
   select * from connection to foo (
      DECLARE @dt DATETIME2(0);
      SELECT @dt=%squote(%sql_datetime(&dt));
      SELECT @dt as [DATETIME];
   );
%mend;
%test(%sysfunc(datetime(),datetime16.));
%test(%sysfunc(datetime(),datetime19.));
%test(%sysfunc(datetime(),datetime24.3));
%test(%sysfunc(datetime(),e8601dt.));
%test(%sysfunc(datetime(),e8601dt24.3));
%test(%sysfunc(datetime(),best.));  * error checking, invalid input, this will fail ;

-----------------------------------------------------------------------
Notes:

SQL Server (as configured at our site) accepts strings such as these as
datetime literals:

25DEC18 20:12:34        (DATE7 + space + 24-hour time)
25DEC18 20:12:34.567    (DATE7 + space + 24-hour time with milliseconds)
25DEC2018 20:12:34      (DATE9 + space + 24-hour time)
25DEC2018 20:12:34.567  (DATE9 + space + 24-hour time with milliseconds)
2018-12-25T20:12:34     (ISO8601 time format (e8601dt. SAS format))
2018-12-25T20:12:34.567 (ISO8601 time format (e8601dt. SAS format) with milliseconds)
2018-12-25 20:12:34     (ISO8601 time format with space instead of "T"
2018-12-25 20:12:34.567 (ISO8601 time format with space instead of "T" with milliseconds)

Milliseconds is just an example; it could be other orders of magnitude as well.

This macro accepts either a DATETIME or E8601DT. ***STRING*** literal
as input.

It does not accept the numeric datetime value - convert it to a string
first using an appropriate format.

This macro returns a string appropriate as a SQL Server datetime
literal in one of these formats:

DDMMMYY HH:MM:SS[.###]
DDMMMYYYY HH:MM:SS[.###]
YYYY-MM-DDTHH:MM:SS[.###]

If you want a datetime ***LITERAL*** that "works" as a literal in
both SAS and SQL Server, use a SAS datetime literal, and pass that
literal "as-is" to this macro.

For example:

%let dt=25DEC2018:12:34:56;

* In SAS:
data test;
   datetime="&dt"dt;
   format datetime datetime.;
run;

* In SQL Server:
proc sql;
   connect using foo;
   execute (
      insert into dbo.mytable (datetime) values (%squote(%sql_datetime(&dt)))
   ) by foo;
quit;

It would be nice if SAS also accepted an ISO8601 datetime string as a
datetime literal...but it doesn't.

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

%macro sql_datetime
/*---------------------------------------------------------------------
Converts a SAS datetime literal to a SQL Server datetime literal.
---------------------------------------------------------------------*/
(SAS_DATETIME  /* SAS datetime string in either DATETIME. or         */
               /* E8601DT. (ISO8601 datetime) format.                */
);

%local macro parmerr rx1 rx2 rc1 rc2 sql_datetime;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(SAS_DATETIME,  _req=1,_words=0,_case=U)
%if (&parmerr) %then %goto quit;

%* datetime format ;
%let rx1=%sysfunc(prxparse(/(\d{2})(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)(\d{2,4}):(\d{2}):(\d{2}):(\d{2})/io));

%* e8601dt format ;
%let rx2=%sysfunc(prxparse(/(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2})/o));

%* is it datetime or e8601dt format? ;
%let rc1=%sysfunc(prxmatch(&rx1,&sas_datetime));
%let rc2=%sysfunc(prxmatch(&rx2,&sas_datetime));

%syscall prxfree(rx1);
%syscall prxfree(rx2);

%if (&rc1) %then %do;
   %let sql_datetime=%sysfunc(prxchange(s/:/ /o,1,&sas_datetime)); %* convert first colon to space ;
%end;
%else
%if (&rc2) %then %do;
   %let sql_datetime=&sas_datetime;  %* return ISO8601 datetime string as is ;
%end;
%else %do;
   %put %str(ERR)OR: Input string must be a valid datetime in DATETIME or E8601DT format.;
   %let sql_datetime=*** Invalid Input String ***;
%end;

&sql_datetime

%quit:
%mend;

/******* END OF FILE *******/