text:
/*=====================================================================
Program Name            : getpassword.sas
Purpose                 : Gets a password from an external file
SAS Version             : SAS 9.3
Input Data              : External file containing a password on
                          line 1, optionally in column 1
Output Data             : Password as an r-value

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 02FEB2017
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 an external file containing a password on line 1, column 1.
Protect that file with operating system security as desired,
such that only trusted persons can read the file contents.

See http://passwordsgenerator.net, et. al. to generate a strong password.

%let mypassword=%getpassword(\\path\to\the\password\file);
%put ***&mypassword***;

Sets &mypassword to the returned password.

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

%put ***%getpassword(\\path\to\the\password\file)***;

Directly uses the returned password as a returned R-value.

Note: this construct prints a single space before the returned password,
i.e. *** mypassword***.  I believe this is due to the way the macro
tokenizer works.  If this leading space is significant to your
processing, either assign the value to a macro variable as above,
or see the example below.

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

%put ***%left(%getpassword(\\path\to\the\password\file))***;

Directly uses the returned password as a returned R-value.

This approach removes the leading space before the returned password.

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

* Error checking ;
%put ***%getpassword(\\does\not\exist)***;

* (edit the password file so the password is not on line 1) ;
%put ***%getpassword(\\path\to\the\password\file)***;

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

Technically this macro doesn't return a password per se, it merely
returns the text on line 1 in the supplied password file.  I've left
the macro name as %getpassword to indicate its intended purpose.

Leading and trailing spaces are stripped from the returned password.
Do not include leading or trailing spaces in the password stored in the
password file or you may get unintended results.

The password is returned as an R-value, so the macro must be called
in the correct context.

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

%macro getpassword
/*---------------------------------------------------------------------
Gets a password from an external file
---------------------------------------------------------------------*/
(FILENAME      /* External file containing the password on line 1    */
);

%local macro parmerr rc fileref password;
%let macro = &sysmacroname;

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

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

%* additional error checking ;

%* does the file exist? ;
%if (not %sysfunc(fileexist(&filename))) %then %do;
   %parmv(_msg=File &filename does not exist)
   %goto quit;
%end;

%* use a system generated fileref ;
%let fileref=;

%* this will create a system generated fileref, eg. #LN00001 ;
%* the fileref will increment each time the macro is called ;
%let rc=%sysfunc(filename(fileref,&filename));

%if (&rc eq 0) %then %do;
   %let fid = %sysfunc(fopen(&fileref));
   %let rc  = %sysfunc(fread(&fid));
   %let rc  = %sysfunc(fget(&fid,password));
   %let fid = %sysfunc(fclose(&fid));
   %let rc  = %sysfunc(filename(fileref));

   %if (%superq(password) ne ) %then %do;
&password
   %end;
   %else %do;
      %parmv(_msg=The password is blank.  The password must be on line 1 of the password file)
      %goto quit;
   %end;
%end;
%else %do;
   %parmv(_msg=Unable to open &filename)
   %goto quit;
%end;

%quit:

%mend;

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


text:
/*=====================================================================
Program Name            : dump_mvars.sas
Purpose                 : Dumps macro variables to the log.
SAS Version             : SAS 9.1.3
Input Data              : None
Output Data             : None

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 11MAY2011
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                    : 03NOV2011
Change/reason           : Added _ALL_, SCOPE=, and SORT= options
Program Version #       : 1.1

Programmer              : Scott Bass
Date                    : 19JUN2012
Change/reason           : Added support for mismatched quotes in the
                          dumped macro variable
Program Version #       : 1.2

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

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

%dump_mvars(
   sysdate9
   sysday
   sysmacroname
   sysscp
   sysuserid
   undefined
)

Dumps named macro variables to the log, in the order specified.

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

%let alpha=A;
%let omega=B;
%let zeta=C;

%dump_mvars(
   zeta
   omega
   alpha
   ,sort=yes
)

Dumps named macro variables to the log, sorted by name.

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

%dump_mvars(
   _all_
   ,scope=automatic
   ,sort=yes
)

Dumps ALL automatic macro variables to the log, sorted by name.

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

%dump_mvars(
   _all_
)

Dumps ALL macro variables to the log,
in the order returned by the dictionary table.

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

data _null_;
  a="Test of 'matched' quotes";
  b='Test of "matched" quotes';
  c="Test of 'mismatched quotes";
  d='Test of "mismatched quotes';
  call symputx("a", a, "G");
  call symputx("b", b, "G");
  call symputx("c", c, "G");
  call symputx("d", d, "G");
  call symputx("e", '&b', "G");
  call symputx("f", '&c', "G");
run;

* this works ;
%dump_mvars(
   a b c d e
)

* this fails ;
* it works for everything except an embedded macro variable reference to mismatched quotes ;
%dump_mvars(
   f
)

%symdel a b c d e f / nowarn;

Dump a macro variable with mismatched quotes.

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

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

%macro dump_mvars
/*---------------------------------------------------------------------
Dumps macro variables to the log.
---------------------------------------------------------------------*/
(
 MVARS         /* List of macro variables to dump to the log (REQ).  */
 ,SCOPE=       /* Macro variable scope (Opt).                        */
               /* If not specified, all macro scopes are printed.    */
               /* Valid values are GLOBAL, LOCAL, and AUTOMATIC      */
 ,SORT=        /* Print values sorted by macro variable name?        */
               /* If missing or NO, values are printed in the order  */
               /* specified in the macro call.  If YES, values are   */
               /* printed in sorted order based on macro variable    */
               /* name.                                              */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
);

%local macro parmerr mvar line i;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(MVARS,        _req=1,_words=1,_case=N)
%parmv(SCOPE,        _req=0,_words=1,_case=U,_val=GLOBAL LOCAL AUTOMATIC)
%parmv(SORT,         _req=0,_words=0,_case=U,_val=0 1)

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

%* if MVARS=_ALL_, print all macro variables ;
%* if SCOPE was specified, limit printing to specified scope ;
%* if SORT=YES, print macro variables in sorted order ;
%* In all three scenarios, we need to use the dictionary tables to reformat the MVARS list ;

%if ( (%upcase(&mvars) eq _ALL_) or (&scope ne ) or (&sort eq 1) ) %then %do;
  proc sql noprint;
    select
      name into :mvars separated by " "
    from
      dictionary.macros
    where

    %if (%upcase(&mvars) eq _ALL_) %then %do;
      1
    %end;
    %else %do;
      upcase(name) in (%upcase(%seplist(&mvars,nest=QQ)))
    %end;

    %if (&scope ne ) %then %do;
      and scope in (%upcase(%seplist(&scope,nest=QQ)))
    %end;

    %if (&sort eq 1) %then %do;
      order by name
    %end;
    ;
  quit;
%end;

%* now print the mvars list to the log ;
%let line=%sysfunc(repeat(=,%sysfunc(getoption(ls))-1));
%let line=%substr(&line,1,80);
%put &line;

%let i=1;
%let mvar=%scan(&mvars,&i);
%do %while (&mvar ne );
   %if %symexist(&mvar) %then %do;
      %let num1 = %sysfunc(countc(%superq(&mvar),%str(%')));
      %let num2 = %sysfunc(countc(%superq(&mvar),%str(%")));
      %if (%sysfunc(mod(&num1,2)) ne 0) %then %do;
         %put %sysfunc(putc(&mvar,$32.)) = %superq(&mvar);
      %end;
      %else
      %if (%sysfunc(mod(&num2,2)) ne 0) %then %do;
         %put %sysfunc(putc(&mvar,$32.)) = %superq(&mvar);
      %end;
      %else %do;
         %put %sysfunc(putc(&mvar,$32.)) = %unquote(%superq(&mvar));
      %end;
   %end;
   %else %do;
      %put %sysfunc(putc(&mvar,$32.)) = ***MACRO VARIABLE UNDEFINED***;
   %end;
   %let i=%eval(&i+1);
   %let mvar=%scan(&mvars,&i);
%end;

%put &line;

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

%mend;

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

repo_name: scottbass/SAS

repo_language: SAS

mime_type: text/plain


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

Macros Called           : export_dbms, 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_dbms macro header.

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

See Notes in the %export_dbms macro header.

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

%macro export_stata
/*---------------------------------------------------------------------
Wrapper macro for the %export_dbms macro
used to export a SAS dataset to a STATA 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.                    */
);

%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)

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

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

%quit:

%mend;

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


text:
/*=====================================================================
Program Name            : export_dbms.sas
Purpose                 : Wrapper macro for PROC EXPORT
                          used to export a SAS dataset to an external file.
SAS Version             : SAS 9.4
Input Data              : SAS dataset
Output Data             : External file

Macros Called           : 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.

Approach to deleting .bak files that are created by PROC COPY
is derived from original ideas by SAS Communities user "LaurieF".
https://communities.sas.com/t5/SAS-Programming/Clean-bak-files-from-PROC-EXPORT/m-p/362602/highlight/true#M85677

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

Modification History    : Original version

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

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

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

%export_dbms(
   data=sashelp.class
   ,path="&path"
)

Creates C:\Temp\class.xlsx as an XLSX file

Default output is an XLSX (Excel) file.
Default filename is <SAS dataset name>.xlsx
if only a directory path is specified.

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

%export_dbms(
   data=sashelp.class
   ,path="&path\file.xlsx"
);

Creates C:\Temp\file.xlsx as an XLSX file
   (explicitly specified output file name).
Aborts if C:\Temp\file.xlsx already exists.

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

%export_dbms(
   data=sashelp.class
   ,path="&path\file.xlsx"
   ,replace=Y
);

Creates C:\Temp\file.xlsx as an XLSX file,
replacing C:\Temp\file.xlsx if it already exists.

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

%export_dbms(
   data=sashelp.shoes
   ,path="&path\"
   ,replace=Y
   ,label=Y
)

Creates C:\Temp\shoes.xlsx as an XLSX file,
replacing C:\Temp\shoes.xlsx if it already exists,
using the column labels for the header row.

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

data work.vwStocks / view=work.vwStocks;
   * reorder columns, moving volume to the end ;
   format Stock Date Open High Low Volume;
   set sashelp.stocks;
   * drop Close and AdjClose ;
   drop Close AdjClose;
   * use a different format for the Date column ;
   format Date e8601da.;
   * add labels ;
   label
      Open="Opening Price"
      High="Highest Price"
      Low="Lowest Price"
      Volume="Total Daily Volume"
   ;
run;

%export_dbms(
   data=work.vwStocks
   ,path="&path\stocks.xlsx"
   ,replace=Y
   ,label=Y
)

Creates C:\Temp\stocks.xlsx as an XLSX file,
replacing C:\Temp\stocks.xlsx if it already exists,
using the column labels for the header row.

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

%export_dbms(
   data=sashelp.class
   ,path="&path"
   ,dbms=xls
   ,replace=Y
);

Creates C:\Temp\class.xls as an XLS file,
replacing C:\Temp\class.xls if it already exists.

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

%export_dbms(
   data=sashelp.class
   ,path="&path"
   ,dbms=spss
   ,replace=Y
);

Creates C:\Temp\class.sav as an SPSS file,
replacing C:\Temp\class.sav if it already exists.

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

%export_dbms(
   data=sashelp.class
   ,path="&path\"
   ,dbms=stata
   ,replace=Y
);

Creates C:\Temp\class.dta as a STATA file,
replacing C:\Temp\class.dta if it already exists.

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

%export_dbms(
   data=sashelp.shoes
   ,path="&path\labels.sav"
   ,dbms=spss
   ,replace=Y
   ,label=Y
);

Creates C:\Temp\labels.sav as an SPSS file,
replacing C:\Temp\labels.sav if it already exists,
using the column labels for the column names.

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

%export_dbms(
   data=sashelp.shoes
   ,path="&path\labels.dta"
   ,dbms=stata
   ,replace=Y
   ,label=Y
);

Creates C:\Temp\labels.dta as a STATA file,
replacing C:\Temp\labels.dta if it already exists,
using the column labels for the column names.

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

* test single level dataset name ;
data cars;
   set sashelp.cars;
run;

%export_dbms(
   data=cars (where=(make =: 'Mercedes' and type='Sports'))
   ,path="&path\MercedesBenzSportsCars.xlsx"
   ,replace=Y
);

Creates C:\Temp\MercedesBenzSportsCars.xlsx as an XLSX file,
replacing C:\Temp\MercedesBenzSportsCars.xlsx if it already exists,
containing Mercedes-Benz sports cars.

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

filename temp temp;

%export_dbms(
   data=sashelp.class
   ,path=temp
   ,replace=Y
);

Writes sashelp.class to the fileref temp as an XLSX file.

Use an unquoted path to specify a fileref.

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

Error checking:

%export_dbms(
   data=sashelp.DoesNotExist
   ,path="&path"
   ,replace=Y
);

Fails, SAS dataset sashelp.DoesNotExist does not exist.

%export_dbms(
   data=sashelp.class
   ,path="Z:\"
   ,replace=Y
);

Fails, drive letter Z: does not exist.

%export_dbms(
   data=sashelp.class
   ,path="C:\DoesNotExist"
   ,replace=Y
);

Fails, output directory does not exist.
The macro does not trap for this edge case error,
although the code fails as it should.

%export_dbms(
   data=sashelp.class
   ,path="C:\DoesNotExist\file.xlsx"
   ,replace=Y
);

Fails, output directory (part of file path) does not exist.

%export_dbms(
   data=sashelp.class
   ,path=FOO
   ,replace=Y
);

Fails, FOO fileref is not already allocated.

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

Physical output paths MUST be single or double quoted.
This is to indicate that it is a physical path and not a fileref.

If you specify a non-existent directory only, this macro does not
trap for that error, although the code will fail when it attempts
to create a file in the non-existent path.

For example:

%export_dbms(
   data=sashelp.class
   ,path="C:\DoesNotExist"
   ,replace=Y
);

If you specify a non-existent directory as part of the file path,
this macro will trap for that error.

For example:

%export_sbms(
   data=sashelp.class
   ,path="C:\DoesNotExist\file.xlsx"
   ,replace=Y
);

Note the different error messages from the two different invocations.

PROC EXPORT creates an annoying backup of an external file
if it already exists, even if the replace option is specified.

For example, if you run this code twice:

proc export data=sashelp.class outfile="&path\file.xlsx" dbms=replace;
run;

The file "&path\file.xlsx.bak" will be created.

The macro will delete that .bak file if it exists.

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

%macro export_dbms
/*---------------------------------------------------------------------
Wrapper macro for PROC EXPORT used to export a SAS dataset
to an external 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.                          */
,DBMS=XLSX     /* Output file type (REQ).                            */
               /* XLSX, XLS, SPSS, or STATA may be specified.        */
               /* Default value is XLSX.                             */
,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.                    */
);

%global syscc;
%local macro parmerr;
%local _rc _temp _pos _type _exist _fileref _is_dir _dir _lib _ds _dlm;
%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(DBMS,         _req=1,_words=0,_case=U,_val=XLSX XLS SPSS STATA)
%parmv(REPLACE,      _req=0,_words=0,_case=U,_val=0 1)
%parmv(LABEL,        _req=0,_words=0,_case=U,_val=0 1)

%if (&parmerr) %then %return;

%* additional error checking ;

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

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

%* If the path is a quoted physical name, check: ;
%*    1) Is it a directory?
%*    2) If not:
%*       A) Does the directory path for the file exist? (error);
%*       B) Does the file itself exist (not an error), ;
%*          but we need to know to propery process the REPLACE option. ;

%* initialize as not exists ;
%let _exist=0;

%* quoted physical file ;
%if (%sysfunc(findc(%superq(path),%str(%"%')))) %then %do;
   %let _type=FILE;
   %* remove quotes to derive output file path ;
   %let path=%sysfunc(compress(%superq(path),%str(%'%")));
%end;
%* fileref ;
%else %do;
   %let _type=FILEREF;
%end;

%if (&_type eq FILE) %then %do;
   %* create temporary fileref ;
   %let _fileref=;
   %let _rc=%sysfunc(filename(_fileref,"%superq(path)"));

   %* is the file a directory? ;
   %let _is_dir=%sysfunc(dopen(&_fileref));
   %let _msg=%sysfunc(sysmsg());
   %let _rc=%sysfunc(dclose(&_is_dir));
   %let _rc=%sysfunc(filename(_fileref));
   %*put &=_is_dir &=_msg;  %* for debugging, comment out in production ;

   %* if not a directory, assume it is a file and get the directory path ;
   %if (not &_is_dir) %then %do;
      %let _pos=%sysfunc(findc(%superq(path),\,-9999));
      %*put &=_pos;  %* for debugging, comment out in production ;
      %if (&_pos) %then %do;
         %let _dir=%substr(%superq(path),1,&_pos);
         %*put &=_dir;  %* for debugging, comment out in production ;

         %* does the directory exist? ;
         %let _fileref=;
         %let _rc=%sysfunc(filename(_fileref,"%superq(_dir)"));
         %let _is_dir=%sysfunc(dopen(&_fileref));
         %let _msg=%sysfunc(sysmsg());
         %let _rc=%sysfunc(dclose(&_is_dir));
         %let _rc=%sysfunc(filename(_fileref));
         %*put &=_is_dir &=_msg;  %* for debugging, comment out in production ;

         %if (not &_is_dir) %then %do;
            %let syscc=8;
            %parmv(_msg=The directory "%superq(_dir)" does not exist.  No output created)
            %return;
         %end;

         %* does the file exist? (which is not an error) ;
         %let _fileref=;
         %let _rc=%sysfunc(filename(_fileref,"%superq(path)"));
         %let _exist=%sysfunc(fexist(&_fileref));
         %let _msg=%sysfunc(sysmsg());
         %let _rc=%sysfunc(filename(_fileref));
         %*put &=_exist &=_msg;  %* for debugging, comment out in production ;
      %end;
   %end;

   %* it is a directory, so derive the output file name ;
   %else %do;
      %let _lib=%scan(%superq(_temp),1,.);
      %let _ds =%scan(%superq(_temp),2,.);
      %if (%superq(_ds) eq ) %then %let _ds=&_lib;
      %let path=%superq(path)\%superq(_ds);  %* this works whether the input path has a trailing \ or not ;
      %if (&dbms=XLSX) %then
         %let path=%superq(path).xlsx;
      %else
      %if (&dbms=XLS) %then
         %let path=%superq(path).xls;
      %else
      %if (&dbms=SPSS) %then
         %let path=%superq(path).sav;
      %else
      %if (&dbms=STATA) %then
         %let path=%superq(path).dta;

      %* does the file exist? (which is not an error) ;
      %let _fileref=;
      %let _rc=%sysfunc(filename(_fileref,"%superq(path)"));
      %let _exist=%sysfunc(fexist(&_fileref));
      %let _msg=%sysfunc(sysmsg());
      %let _rc=%sysfunc(filename(_fileref));
      %*put &=_exist &=_msg;  %* for debugging, comment out in production ;
   %end;
%end;

%* already allocated fileref ;
%else
%if (&_type eq FILEREF) %then %do;
   %* is the fileref allocated? ;
   %if (%sysfunc(fileref(%superq(path))) gt 0) %then %do;
      %let syscc=8;
      %parmv(_msg=The %superq(path) fileref is not allocated. No output created)
      %return;
   %end;
   %let _exist=%sysfunc(fexist(%superq(path)));
   %*put &=_exist;  %* for debugging, comment out in production ;
%end;

%* file exists but replace was not specified ;
%if (&_exist and not &replace) %then %do;
   %if (&_type eq FILE) %then %do;
      %let msg=%str(WAR)NING: "%superq(path)" already exists.;
      %let msg=&msg Specify REPLACE to overwrite.;
      %let msg=&msg No output created.;
   %end;
   %else
   %if (&_type eq FILEREF) %then %do;
      %let msg=%str(WAR)NING: "%sysfunc(pathname(%superq(path)))" already exists.;
      %let msg=&msg Specify REPLACE to overwrite.;
      %let msg=&msg No output created.;
   %end;
   %put &msg;  %* do not comment out this line ;
   %let syscc=4;
   %return;
%end;

%* add back the quotes to indicate that it is a physical filename ;
%if (&_type eq FILE) %then %do;
   %let path="%superq(path)";
%end;

%* export to external file ;
proc export data=&data outfile=&path dbms=&dbms replace
   %if (&label) %then %do;
   label
   %end;
   ;
   %if %sysfunc(cexist(work.formats)) %then %do;
   fmtlib=work.formats;
   %end;
run;

%* Delete PROC EXPORT annoying .bak file ;
%if (&_type eq FILE) %then %do;
   %let path=%sysfunc(compress(%superq(path),%str(%'%")));
   %if (%sysfunc(fileexist("&path..bak"))) %then %do;
      %let _rc=%sysfunc(filename(_fileref,"&path..bak"));
      %let _rc=%sysfunc(fdelete(&_fileref));
      %let _rc=%sysfunc(filename(_fileref));
   %end;
%end;

%mend;

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


text:
/*=====================================================================
Program Name            : randlist.sas
Purpose                 : Macro to create a random list of data
SAS Version             : SAS 8.2
Input Data              : DATA= macro parameter
Output Data             : OUT=  macro parameter

Macros Called           : parmv, varexist, nobs

Originally Written by   : Scott Bass
Date                    : 12JAN2005
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:

%randlist(data=ir&prot..lab)
   returns a random 50% of input dataset in macro variable &randlist.
   data returned is a list of subjid (default value of VAR parameter).

%randlist(data=ir&prot..lab, var=usubjid)
   returns a random 50% of input dataset in macro variable &randlist.
   data returned is a list of usubjid.

%randlist(data=ir&prot..lab, out=work.lab_randlist)
   returns a random 50% of input dataset in both macro variable
   &randlist and output dataset work.lab_randlist.  data returned would
   be a random 50% of subjids from the input dataset, and a random
   ordered list of these same subjids in &randlist.

%randlist(data=ir&prot..lab, out=work.lab_randlist, mvar=)
   returns a random 50% of input dataset in output dataset
   work.lab_randlist only (since the MVAR parameter is blank). data
   returned would be a random 50% of subjids from the input dataset.

%randlist(data=ir&prot..lab, pct=20)
   returns a random 20% of subjids from the input dataset in macro
   variable &randlist.

%randlist(data=ir&prot..lab, pct=20, min=10)
   returns a random 20% of subjids from the input dataset in macro
   variable &randlist, or a minimum of 10 items returned if 20% is less
   than 10 items.

%randlist(data=ir&prot..lab, pct=20, max=30)
   returns a random 20% of subjids from the input dataset in macro
   variable &randlist, or a maximum of 30 items returned if 20% is
   greater than 30 items.

%randlist(data=ir&prot..lab, pct=20, min=20, max=120)
   returns a random 20% of subjids from the input dataset in macro
   variable &randlist, with a minimum of 20 items and a maximum number
   of 120 items returned.

%randlist(
   data=ir&prot..lab (where=(subjid not like "S%")),
   out=work.randlist (keep=subjid visit)
)
   returns a random 50% of subjids from the input dataset, which is
   *first* subset by the input dataset where clause.

   returned data is in both the macro variable &randlist and the
   output dataset work.randlist.

   only the variables subjid and visit are kept in the output dataset.

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

This macro would be better if rewritten to use PROC SURVEYSELECT.

Input dataset must be specified and must exist.

VAR must be a variable in the input dataset.

By default, &randlist is always created.
Set MVAR to blank to suppress this.

The MVAR variable is returned as a comma-delimited list only (no parentheses).
This makes it easier to further parse the returned list if required.
If building an IN where clause, you need to add the parentheses, eg.

   where subjid in (&randlist);

   not

   where subjid in &randlist;

Character variables are returned as a single-quoted comma-delimited list,
eg. '10041017','10031023','10021037', etc.

and numeric variables are returned as a comma-delimited list,
eg. 17,3,22,7,4,53, etc.

An *output* WHERE clause defeats the whole purpose of this macro.
The &out parm is upper-cased in the macro.  An output WHERE clause will
have unpredictable results.

Note that, if you invoke this macro and create a macro variable &mvar
in a *non-default* macro variable name (i.e. not &randlist), then later
re-invoke the macro with &mvar set to missing, the *old* random item
list will still exist in that macro variable.  Since the syntax to not
create a macro variable is a blank &mvar, I don't know which old macro
variable to reset.  The best I can do is unconditionally delete the
*default* macro variable &randlist on each macro invocation. You can
use %symdel to delete an old macro variable before re-invoking the
macro.
---------------------------------------------------------------------*/

%macro randlist
/*---------------------------------------------------------------------
Create a random list of data, and create an output data set, a macro
variable list, or both.  Output can be limited by pct of total
&var (usually subjid), min # of subjids, max # of subjids, or a
combination of these parameters.
---------------------------------------------------------------------*/
(DATA=         /* Input dataset (REQ).                               */
               /* Input dataset options are allowed.                 */
,OUT=          /* Output dataset (Optional).                         */
               /* Output dataset options are allowed, such as KEEP=, */
               /* but don't use a WHERE= output dataset option.      */
               /* If missing no output dataset will be created.      */
,OUTRAND=0     /* Sort output dataset in random order? (Opt.)        */
               /* If no output dataset supplied then this option has */
               /* no effect.  Valid values are                       */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
,VAR=          /* Variable to subset on (REQ).                       */
,MVAR=randlist /* Output macro variable list (Optional).             */
               /* Default value is randlist.  If missing no macro    */
               /* variable list will be created.                     */
,PCT=50        /* percentage of input dataset desired in output.     */
               /* (Optional).  Default value is 50%.  If missing,    */
               /* 100% of the input dataset will be included in the  */
               /* output.  Valid values are 1 - 100.                 */
,MIN=          /* Minimum number of VAR desired in output (Opt.)     */
,MAX=          /* Maximum number of VAR desired in output (Opt.)     */
,SEED=0        /* Seed value for uniform function (REQ).             */
               /* Default value is 0.                                */
               /* Valid values are any non-negative integer.         */
);

%* delete the old default &mvar setting ;
%unquote(%nrstr(%symdel randlist / nowarn)); /* workaround a SAS bug */

%* basic error checking ;
%local macro parmerr options;
%let macro = &sysmacroname;

%* do not want to upcase the input data parm ;
%* or it will mess up any input where clause ;
%parmv(DATA,       _req=1,_words=1,_case=N) /* _words=1 allows input  ds options */
%parmv(OUT,        _req=0,_words=1,_case=U) /* _words=1 allows output ds options */
%parmv(OUTRAND,    _req=0,_words=0,_val=0 1)
%parmv(MVAR,       _req=0,_words=0,_case=U)
%parmv(VAR,        _req=1,_words=0,_case=U);
%parmv(PCT,        _req=0,_words=0,_case=U,_val=POSITIVE)
%parmv(MIN,        _req=0,_words=0,_case=U,_val=POSITIVE)
%parmv(MAX,        _req=0,_words=0,_case=U,_val=POSITIVE)
%parmv(SEED,       _req=1,_words=0,_case=U,_val=NONNEGATIVE)

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

%* first parse off any dataset options ;
%let randlist_index = %index(%bquote(&data),%str(%());
%if (&randlist_index) %then %do;
   %let options = %substr(%bquote(&data),&randlist_index);
   %let data    = %substr(%bquote(&data),1,&randlist_index-1);
%end;
%let data = %upcase(&data);

%* more error checking ;

%* does the input dataset exist? ;
%if not %sysfunc(exist(&data)) %then
   %parmv(_msg=Dataset &data does not exist);

%* is var in the input dataset? ;
%if not %varexist(&data,&var) %then
   %parmv(_msg=Variable &var does not exist in dataset &data);
%else
   %let vartype = %varexist(&data,&var,TYPE);

%* are both output dataset and mvar variable blank? ;
%if (%bquote(&out) = and &mvar = ) %then
   %parmv(_msg=Either an output dataset or macro variable must be specified);

%* output dataset cannot be named _temp_ ;
%let temp = %upcase(%bquote(%scan(&out,1,%str(%())));
%if (&temp = _TEMP_ or &temp = WORK._TEMP_) %then
   %parmv(_msg=Output dataset cannot be named _TEMP_.  Choose another output dataset name.);

%* is percent between 1 and 100? ;
%if not (1 <= &pct and &pct <= 100) %then
   %parmv(_msg=Percent must be an integer between 1 and 100);

%* is max greater than min? ;
%if (&min ^= and &max ^= )%then
   %if (&max <= &min) %then
      %parmv(_msg=Max (&max) must be greater than min (&min));

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

%* create a dataset of distinct &var, sorted in random order ;
proc sql noprint;
   create table _temp_ as
      select *, uniform(&seed) as sort
         from
            (select distinct &var
               from &data &options
            )
         order by sort
   ;
quit;

%* calculate percentage, min, and max ;
%let nobs=%nobs(_temp_);

%if (&pct = ) %then %let pct = 100;
%if (&min = ) %then %let min = 0;
%if (&max = ) %then %let max = &nobs;

%* convert pct to an observation count ;
%let pct = %sysfunc(round(&nobs * (&pct / 100)));

%* now calculate the desired item count given pct, min, and max settings ;
%let num = %sysfunc(min(%sysfunc(max(&min,&pct)),&max));

%* debugging statement ;
%put nobs=&nobs pct=&pct min=&min max=&max num=#

%* create random subset and/or item list ;
proc sql noprint;

   %* do you want an output dataset? ;
   %if (%bquote(&out) ^= ) %then %do;
      create table %bquote(&out) as
         select *
         %if (&outrand) %then %do;
            , uniform(&seed) as sort
         %end;
            from &data &options
            where &var in
               (select &var
                  from _temp_ (obs=&num)
               )
         %if (&outrand) %then %do;
            order by sort
         %end;
      ;
   %end;

   %* do you want an output macro variable? ;
   %* if you want a sorted, but still random, list then uncomment the "distinct" keyword ;
   %* but an unsorted list gives more confidence that the list is truly random ;
   %if (&mvar ^= ) %then %do;
      %global &mvar;

      %if (&vartype = C) %then %do;
         select /* distinct */ quote(strip(&var)) into :&mvar separated by ','
            from _temp_ (obs=&num)
         ;
      %end;
      %else %do;
         select /* distinct */ &var into :&mvar separated by ','
            from _temp_ (obs=&num)
         ;
      %end;
   %end;
quit;

%quit:
%mend;

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


text:
Dear &firstname,

Your SAS process with the Process ID &pid was locking SAS datasets which require updating during the Acme daily processing.

The file locks on these SAS datasets have been removed so that the Acme daily processing could complete successfully.

You should not have lost any work.

If your SAS session was an Enterprise Guide session, you should save your project, then restart Enterprise Guide.

If your SAS session was a Base SAS session, you should save all your open program editor sessions, then restart your Base SAS Session.

Your SAS session will be in an unstable state.  You should save your work and restart SAS as soon as possible.  Do not continue to use that SAS session.

If you have any questions, please contact someone in the Acme Support Team.

Regards,
Acme Support Team


text:
/*=====================================================================
Program Name            : fmtexist.sas
Purpose                 : Checks the format searchpath for the
                          existence of a format or informat.
SAS Version             : SAS 9.1.3
Input Data              : None
Output Data             : None

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 30APR2007
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:

%if (%fmtexist(gender)) %then %put EXISTS...;
%let num=%fmtexist(yesno, catalog=LIBRARY.MYFMTS);
%put %fmtexist($chr_fmt);
%put %fmtexist(#num_fmt);

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

Returns the number of formats/informats found as an RVALUE.
For example, formats and informats can have the same name in a
single catalog, or formats with the same name can exist across
multiple format catalogs, so it's possible that more than one entry
with the same name is found.

Returns the 4-level name of the entry(s) found in the global macro
variable FMTLIST.

To restrict the search to a particular type, specify:
   $ for character formats
   # for numeric formats

Otherwise the search will be for both types with the given format name.

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

%macro fmtexist
/*---------------------------------------------------------------------
Checks the format searchpath for the existence of a format or informat
---------------------------------------------------------------------*/
(FORMAT        /* Format or Informat to find (REQ).                  */
,CATALOG=      /* Format catalog to search (Opt).                    */
               /* If blank, uses current value of FMTSEARCH option   */
               /* to locate entry.                                   */
);

%global fmtlist;
%let fmtlist=;

%local macro parmerr found format where dsid rc;
%local libname memname objname fmtname fmttype;  %* call set macro variables ;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(FORMAT,       _req=1,_words=0)

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

%let found=0;

%if (%sysfunc(indexc(&format,$,#))) %then %do;
   %let format = %sysfunc(compress(&format,#));
   %let where=fmtname eq "%upcase(&format)";
%end;
%else
   %let where=objname eq "%upcase(&format)";

%if (&catalog ne ) %then %do;
   %let where=&where and libname eq "%upcase(%scan(&catalog,1,%str(.)))";
   %let where=&where and memname eq "%upcase(%scan(&catalog,2,%str(.)))";
%end;

%*put &where;

%let dsid=%sysfunc(open(sashelp.vformat (where=(&where)),i));
%if (&dsid) %then %do;
   %syscall set(dsid);
   %let rc=%sysfunc(fetch(&dsid));
   %do %while (&rc eq 0);
      %let found = %eval(&found+1);

      %if (%index(&fmtname,$)) %then %do;
         %if (&fmttype eq F) %then
            %let fmttype = FORMATC;
         %else
            %let fmttype = INFMTC;
      %end;
      %else %do;
         %if (&fmttype eq F) %then
            %let fmttype = FORMAT;
         %else
            %let fmttype = INFMT;
      %end;

      %let fmtlist = &fmtlist %sysfunc(catx(.,&libname,&memname,&fmtname,&fmttype));
      %let rc=%sysfunc(fetch(&dsid));

      %*put found=&found rc=&rc libname=&libname memname=&memname fmtname=&fmtname fmttype=&fmttype;
   %end;
%end;
%let dsid=%sysfunc(close(&dsid));

&found

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

%mend;

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


text:
/*=====================================================================
Program Name            : create_directory.sas
Purpose                 : Creates a directory using the dlcreatedir
                          option, without requiring the ALLOWXCMD
                          option to be active.
SAS Version             : SAS 9.3
Input Data              : N/A
Output Data             : N/A

Macros Called           : %parmv
                          %loop
                          %seplist

Originally Written by   : Scott Bass
Date                    : 11APR2017
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 must be a path from the perspective of the APPLICATION SERVER,
  not the local machine.  UNC paths are usually safest, and they must
  be accessible from and writeable by the application server ;
%let root=\\server\UNC\Path;

options nodlcreatedir;  * make sure it works even if the option is initially off ;

%create_directory(&root,dir1/Dir2/DIR3)
%create_directory(&root,dirA\DirB\DIRC)

%put %sysfunc(getoption(dlcreatedir));  * make sure option is restored by the macro ;

Create the directory path dir1/Dir2/DIR3 and dirA\DirB/DIRC.

Both invocations will work under both Windows and *nix.

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

This macro uses the dlcreatedir option available in SAS 9.3+ to create
the directory, or to silently succeed if the directory already exists.

Therefore, this macro does not require the ALLOWXCMD option to be
active, nor does it require any exit to the operating system
(X command, call systask, PIPE, etc)

However, the SAS Administrator can disable this functionality.
The below code will indicate whether the dlcreatedir functionality is
restricted or not:

* Lists which options you can restrict ;
proc options listrestrict;
run;

* Lists which options are restricted ;
proc options restrict;
run;

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

%macro create_directory
/*---------------------------------------------------------------------
Creates a directory using the dlcreatedir option, without requiring the
ALLOWXCMD option to be active.
---------------------------------------------------------------------*/
(ROOT          /* Root directory, under which the additional         */
               /* directory(ies) are created (REQ).                  */
,PATH          /* Directory path to create under the ROOT directory  */
               /* (REQ).                                             */
,DLM=%str(/\)  /* Directory path delimiter(s) (REQ).                 */
               /* Default is /\, so either the Windows or *nix       */
               /* path delimiter will be used to delimit the         */
               /* directories in the specified path.                 */
,LIBREF=dummy  /* Dummy libref to use (REQ).  Default is dummy.      */
);

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

%* check input parameters ;
%parmv(ROOT,         _req=1,_words=1,_case=N)  /* words allows directories containing spaces */
%parmv(PATH,         _req=1,_words=1,_case=N)
%parmv(DLM,          _req=1,_words=0,_case=N,_val=/\)  /* assume only delimiters allowed are / and \ */

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

%* This inner utility macro is used by the %loop macro to create a concatenated library path ;
%* Note: &concat must be global ;
%macro create_directory_concat;
%global concat;
%if (&__iter__ eq 1) %then
   %let concat=&root/&word;
%else
   %let concat=&concat/&word;
&concat|
%mend;

%* Capture the current dlcreatedir option ;
%let dlcreatedir=%sysfunc(getoption(dlcreatedir));
options dlcreatedir;

%* The %loop macro will create a delimited (|) path from the users PATH parameter ;
%* The %seplist macro will parse that path to create the correct syntax for a SAS concatenated library ;
libname &libref (%seplist(%loop(&path,dlm=&dlm,mname=create_directory_concat),indlm=|,nest=qq));
libname &libref clear;

options &dlcreatedir;
%symdel concat / nowarn;

%quit:

%mend;

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


text:
/*=====================================================================
Program Name            : parmv.sas
Purpose                 : Macro parameter validation utility.
                          Returns parmerr=1 and writes ERROR message
                          to log for parameters with invalid values.
SAS Version             : SAS 8.2
Input Data              : N/A
Output Data             : N/A

Macros Called           : None

Originally Written by   : Tom Hoffman
Date                    : 09SEP1996
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.

This is original work created by Tom Hoffman.
Placed into the public domain with permission.

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

Modification History    :

Programmer              : Tom Hoffman
Date                    : 16MAR1998
Change/reason           : Replaced QTRIM autocall macro with QSYSFUNC
                          and TRIM in order to avoid conflict with the
                          i command line macro.
Program Version #       : 1.1

Programmer              : Tom Hoffman
Date                    : 04OCT1999
Change/reason           : Added _val=NONNEGATIVE. Converted _val=0 1 to
                          map N NO F FALSE OFF --> 0 and Y YES T TRUE
                          ON --> 1. Added _varchk parameter to support
                          variables assumed to be defined before macro
                          invocation.
Program Version #       : 1.2

Programmer              : Tom Hoffman
Date                    : 12APR00
Change/reason           : Changed the word 'parameter' in the message
                          text to 'macro variable' when _varchk=1.
                          Fixed NONNEGATIVE option.
Program Version #       : 1.3

Programmer              : Tom Hoffman
Date                    : 10JUN01
Change/reason           : Added _DEF parameter. Returned S_MSG global
                          macro variable.
Program Version #       : 1.4

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

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

%macro test;

%local macro parmerr;
%let macro = TEST;

%parmv(INTERVAL,   _req=1,_words=1)
%parmv(IVAR,       _req=1)

%if (%length(&visit) > 7) %then
   %parmv(IVAR,    _msg=SAS name containing 7 or less characters)
;
%parmv(LZERO,      _req=1)
%parmv(UZERO,      _req=1)
%parmv(HIGH,       _req=1,_val=0 1)
%parmv(DAY,        _req=1)
%parmv(PRINT,      _req=1,_val=0 1)

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

%mend test;

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

=======================================================================
This code was developed by HOFFMAN CONSULTING as part of a FREEWARE
macro tool set. Its use is restricted to current and former clients of
HOFFMAN CONSULTING as well as other professional colleagues. Questions
and suggestions may be sent to TRHoffman@sprynet.com.

Note:  I have received permission from Tom Hoffman to use this macro.
Scott Bass
=======================================================================

The calling macro requires two local variables, PARMERR and MACRO,
where MACRO's value equals the name of the calling macro.

Invoke macro %parmv once for each macro parameter. After the last
invocation branch to the macro's end whenever PARMERR equals 1 (e.g.,
%if (&parmerr) %then %goto quit;).

Macro %parmv can be disabled (except for changing case) by setting the
global macro variable S_PARMV to 0.

Macros using the %parmv tool may not have any parameters in common
with parmv parameters.

Use the _MSG parameter to set parmerr to 1 and issue a message based on
validation criteria within the calling program.

Note that for efficiency reasons, parmv does not validate its own
parameters. Only code valid values for the _REQ, _WORDS, _CASE, and
_VARCHK parameters.

For macros that require 'many' non-parameter macro variables that may
not be defined by the programming environment, consider using the
CHKMVARS macro rather than setting _varchk=1. Both methods will work,
but note that DICTIONARY.MACROS is opened each time that parmv is
invoked with _varchk=1.
--------------------------------------------------------------------*/

%macro parmv
/*---------------------------------------------------------------------
Macro parameter validation utility. Returns parmerr=1 and writes
ERROR message to log for parameters with invalid values.
---------------------------------------------------------------------*/
(_PARM     /* Macro parameter name (REQ)                             */
,_VAL=     /* List of valid values or POSITIVE for any positive      */
           /* integer or NONNEGATIVE for any non-negative integer.   */
           /* When _val=0 1, OFF N NO F FALSE and ON Y YES T TRUE    */
           /* (case insensitive) are acceptable aliases for 0 and 1  */
           /* respectively.                                          */
,_REQ=0    /* Value required? 0=No, 1=Yes.                           */
,_WORDS=0  /* Multiple values allowed? 0=No ,1=Yes                   */
,_CASE=U   /* Convert case of parameter value & _val? U=upper,       */
           /* L=lower,N=no conversion.                               */
,_MSG=     /* When specified, set parmerr to 1 and writes _msg as the*/
           /* last error message.                                    */
,_VARCHK=0 /* 0=Assume that variable defined by _parm exists.        */
           /* 1=Check for existence - issue global statement if not  */
           /* defined (and _req=0).                                  */
,_DEF=     /* Default parameter value when not assigned by calling   */
           /* macro.                                                 */
);

%local _word _n _vl _pl _ml _error _parm_mv;
%global s_parmv s_msg;  /* in case not in global environment */

%*---------------------------------------------------------------------
Initialize error flags, and valid (vl) flag.
----------------------------------------------------------------------;
%if (&parmerr = ) %then %do;
   %let parmerr = 0;
   %let s_msg = ;
%end;
%let _error = 0;

%*---------------------------------------------------------------------
Support undefined values of the _PARM macro variable.
----------------------------------------------------------------------;
%if (&_varchk) %then %do;
   %let _parm_mv = macro variable;
   %if ^%symexist(&_parm) %then %do;
      %if (&_req) %then
         %local &_parm
      ;
      %else
         %global &_parm
      ; ;
   %end;
%end;
%else %let _parm_mv = parameter;

%*---------------------------------------------------------------------
Get lengths of _val, _msg, and _parm to use as numeric switches.
----------------------------------------------------------------------;
%let _vl = %length(&_val);
%let _ml = %length(&_msg);
%if %length(&&&_parm) %then
   %let _pl = %length(%qsysfunc(trim(&&&_parm)));
%else %if %length(&_def) %then %do;
   %let _pl = %length(&_def);
   %let &&_parm = &_def;
%end;
%else %let _pl = 0;

%*---------------------------------------------------------------------
When _MSG is not specified, change case of the parameter and valid
values conditional on the value of the _CASE parameter.
----------------------------------------------------------------------;
%if ^(&_ml) %then %do;
   %let _parm = %upcase(&_parm);
   %let _case = %upcase(&_case);

   %if (&_case = U) %then %do;
      %let &_parm = %qupcase(&&&_parm);
      %let _val = %qupcase(&_val);
   %end;
   %else %if (&_case = L) %then %do;
      %if (&_pl) %then %let &_parm = %qsysfunc(lowcase(&&&_parm));
      %if (&_vl) %then %let _val = %qsysfunc(lowcase(&_val));
   %end;
   %else %let _val = %quote(&_val);

%*---------------------------------------------------------------------
When _val=0 1, map supported aliases into 0 or 1.
----------------------------------------------------------------------;
   %if (&_val = 0 1) %then %do;
      %let _val=%quote(0 (or OFF NO N FALSE F) 1 (or ON YES Y TRUE T));
      %if %index(%str( OFF NO N FALSE F ),%str( &&&_parm )) %then
         %let &_parm = 0;
      %else %if %index(%str( ON YES Y TRUE T ),%str( &&&_parm )) %then
         %let &_parm = 1;
   %end;
%end;

%*---------------------------------------------------------------------
Bail out when no parameter validation is requested
----------------------------------------------------------------------;
%if (&s_parmv = 0) %then %goto quit;

%*---------------------------------------------------------------------
Error processing - parameter value not null

Error 1: Invalid value - not a positive integer
Error 2: Invalid value - not in valid list
Error 3: Single value only
Error 4: Value required.
Error 5: _MSG specified
----------------------------------------------------------------------;
%if (&_ml) %then %let _error = 5;

%*---------------------------------------------------------------------
Macro variable specified by _PARM is not null.
----------------------------------------------------------------------;
%else %if (&_pl) %then %do;

%*---------------------------------------------------------------------
Loop through possible list of words in the _PARM macro variable.
-----------------------------------------------------------------------;
   %if ((&_vl) | ^(&_words)) %then %do;
      %let _n = 1;
      %let _word = %qscan(&&&_parm,1,%str( ));
%*---------------------------------------------------------------------
Check against valid list for each word in macro parameter
----------------------------------------------------------------------;
      %do %while (%length(&_word));

%*---------------------------------------------------------------------
Positive integer check.
----------------------------------------------------------------------;
         %if (&_val = POSITIVE) %then %do;
            %if %sysfunc(verify(&_word,0123456789)) %then
               %let _error = 1;
            %else %if ^(&_word) %then %let _error = 1;
         %end;

%*---------------------------------------------------------------------
Non-negative integer check.
----------------------------------------------------------------------;
         %else %if (&_val = NONNEGATIVE) %then %do;
            %if %sysfunc(verify(&_word,0123456789)) %then
               %let _error = 1;
         %end;

%*---------------------------------------------------------------------
Check against valid list. Note blank padding.
----------------------------------------------------------------------;
         %else %if (&_vl) %then %do;
            %if ^%index(%str( &_val ),%str( &_word )) %then
               %let _error = 2;
         %end;

%*---------------------------------------------------------------------
Get next word from parameter value
-----------------------------------------------------------------------;
         %let _n = %eval(&_n + 1);
         %let _word = %qscan(&&&_parm,&_n,%str( ));
      %end; %* for each word in parameter value;

%*---------------------------------------------------------------------
Check for multiple _words. Set error flag if not allowed.
----------------------------------------------------------------------;
      %if (&_n ^= 2) & ^(&_words) %then %let _error = 3;

   %end; %* valid not null ;

%end; %* parameter value not null ;

%*---------------------------------------------------------------------
Error processing - Parameter value null

Error 4: Value required.
----------------------------------------------------------------------;
%else %if (&_req) %then %let _error = 4;

%*---------------------------------------------------------------------
Write error messages
----------------------------------------------------------------------;
%if (&_error) %then %do;
   %let parmerr = 1;
   %put %str( );
   %put ERROR: Macro %upcase(&macro) user error.;

   %if (&_error = 1) %then %do;
      %put ERROR: &&&_parm is not a valid value for the &_parm &_parm_mv..;
      %put ERROR: Only positive integers are allowed.;
      %let _vl = 0;
   %end;

   %else %if (&_error = 2) %then
      %put ERROR: &&&_parm is not a valid value for the &_parm &_parm_mv..;

   %else %if (&_error = 3) %then %do;
      %put ERROR: &&&_parm is not a valid value for the &_parm &_parm_mv..;
      %put ERROR: The &_parm &_parm_mv may not have multiple values.;
   %end;

   %else %if (&_error = 4) %then
      %put ERROR: A value for the &_parm &_parm_mv is required.;

   %else %if (&_error = 5) %then %do;
      %if (&_parm ^= ) %then
      %put ERROR: &&&_parm is not a valid value for the &_parm &_parm_mv..;
      %put ERROR: &_msg..;
   %end;

   %if (&_vl) %then
      %put ERROR: Allowable values are: &_val..;

   %if %length(&_msg) %then %let s_msg = &_msg;
   %else %let s_msg = Problem with %upcase(&macro) parameter values - see LOG for details.;

%end; %* errors ;

%quit:

%*---------------------------------------------------------------------
Unquote the the parameter value, unless it contains an ampersand or
percent sign.
----------------------------------------------------------------------;
%if ^%sysfunc(indexc(&&&_parm,%str(&%%))) %then
   %let &_parm = %unquote(&&&_parm);

%mend;

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


text:
/*=====================================================================
Program Name            : count_words.sas
Purpose                 : Returns the number of words delimited by a
                          delimiter or set of delimiters.
SAS Version             : SAS 9.1.3
Input Data              : None
Output Data             : None

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 16APR2011
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:

* Macro Context ;

* default delimiter is a space ;
%put %count_words(foo bar blah);

* specified delimiter ;
%put %count_words(foo ~  bar   ~ blah ~ blech,dlm=~);

* mixed delimiters in a string is ok, just specify multiple delimiters (a space is also allowed) ;
%put %count_words(foo ~  bar   # blah ^ blech xxx,dlm=%str( ~#^));

* should return zero ;
%let foo=;
%put %count_words(&foo);
%put %count_words();

* assign to another macro variable ;
%let foo=%count_words(x y z);
%put *%count_words(x y z)*;
%put *%eval(%count_words(x y z)+2)*;
%put *&foo*;

* Datastep Context ;
data source;
   length string $100;
   infile datalines dsd;
   input string & $char100.;
   datalines;
FOO BAR  BLAH
FOO~BAR ~ BLAH  ~   BLECH
FOO~BAR # BLAH  ^   BLECH XXX
FOO~ ~BAR~ ~BLECH
FOO~~BAR~   ~BLECH
FOO

;
run;

* word count is returned in the hard coded variable _word_count_ (macro default) ;
data word_count;
   set source;
   %count_words(string,dlm=%str( ~^#),context=datastep);
run;

proc print;
run;

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

Notes:

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

%macro count_words
/*---------------------------------------------------------------------
Returns the number of words delimited by a delimiter or set of
delimiters.
---------------------------------------------------------------------*/
(LIST          /* Input list (Opt).  If blank then zero is returned. */
,VAR=_word_count_
               /* Output variable name for datastep context (Opt).   */
               /* Only required when context=DATASTEP, otherwise it  */
               /* is ignored.                                        */
,DLM=%str( )   /* Delimiter (or delimiters) that define a "word"     */
               /* (REQ).  Default is a blank.                        */
,CONTEXT=MACRO /* Context in which this macro is called (REQ).       */
               /* Default is MACRO.                                  */
               /* Valid values are MACRO and DATASTEP.               */
);

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

%* check input parameters ;
%parmv(LIST,         _req=0,_words=1,_case=N)
%parmv(CONTEXT,      _req=1,_words=0,_case=U,_val=MACRO DATASTEP)

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

%* additional error checking ;
%* if context=DATASTEP then VAR is required ;
%if (&context eq DATASTEP) %then %do;
%parmv(VAR,          _req=1,_words=0,_case=N)
%end;

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

%if (&context eq MACRO) %then %do;
   %local _word_count;
   %let _word_count=1;
   %do %until (%qscan(%superq(list),&_word_count,&dlm) eq );
      %let _word_count=%eval(&_word_count+1);
   %end;
   %let _word_count=%eval((&_word_count-1)-(%superq(list) eq ));
%quote(&_word_count)
%end;
%else
%if (&context eq DATASTEP) %then %do;
   &var=1;
   do until(missing(scan(&list,&var,"&dlm")));
      &var+1;
   end;
   &var=((&var-1)-(missing(&list)));
%end;

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

%mend;

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


text:
/*=====================================================================
Program Name            : batch_submit.sas
Purpose                 : Submits the current SAS DMS editor session in
                          batch mode, routing the log and print files
                          to the correct output locations.

SAS Version             : SAS 9.1.3
Input Data              : N/A
Output Data             : N/A

Macros Called           : None

Originally Written by   : Scott Bass
Date                    : 08JUN2011
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:

%batch_submit()

Submits the current SAS DMS editor session in batch, using the default
config file name.

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

%batch_submit(,config=C:\Temp\myconfig.cfg)

Submits the current SAS DMS editor session in batch, using the specified
configuration file.  Best practice is to use an absolute path if you
specify a configuration file.

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

This macro should be used on Windows SAS DMS only.

This macro is meant to be assigned to your SAS DMS Application Toolbar.
To do so, follow these steps:

* Ensure a SAS DMS editor window is active
* RMB the Application Toolbar and choose Customize...
* Scroll to the bottom of the Customize Tools window
* Select Add Tool
* Enter the text:  submit '%batch_submit()'; for the command
* Enter desired text for Help Text and Tip Text
* Change the icon if desired (recommended)
* Use the arrows to position the new command where desired on the toolbar
* Click OK and Save to save the new tool.

Alternative to the above, you could also assign the macro to a
function or hot-key setting.

You should create a configuration file specifying desired options,
such as -LOG, -PRINT, -AUTOEXEC, etc.

By default the expected configuration filename is config_batch.cfg,
but this can be changed via the macro CONFIG parameter.

The configuration file should set ALL other desired SAS options, eg.
-AUTOEXEC, -LOG, -PRINT, -RSASUSER, -ICON, -NOSPLASH, -AWSTITLE, etc.

However, if the configuration file cannot be found (either it wasn't
created, has the wrong name, or you're submitting a batch program from
a non-standard location, such as c:\temp), then the program will be
submitted in batch, with the log and print files written to the
directory containing the SAS program.

If the input filename is blank, it will be set to
%sysget(SAS_EXECFILEPATH) which, for a SAVED editor session under
SAS Windows DMS, is the full path to the editor session file.

This macro will first save out your current editor session to disk
before invoking the saved file in batch.

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

%macro batch_submit
/*---------------------------------------------------------------------
Submits the current SAS DMS editor session in batch mode.
---------------------------------------------------------------------*/
(FILE          /* File to submit (Opt).                              */
                      /* If blank, the file will be set to                  */
                      /* %sysget(SAS_EXECFILEPATH)                          */
,CONFIG=       /* Default name of config file (Opt).                 */
                      /* If blank, it is set to config_batch.cfg.           */
                      /* If specified, a full path to the desired config    */
                      /* file should be specified.                          */
);

%* compile sub-macro ;
%macro dump_mvars(mvars);

%* dump list of macro variables to the log ;
%let line=%sysfunc(repeat(=,%sysfunc(getoption(ls))-1));
%let line=%substr(&line,1,80);
%put &line;

%let i=1;
%let mvar=%scan(&mvars,&i);
%do %while (&mvar ne );
    %put %sysfunc(putc(&mvar,$12.)) = %unquote(&&&mvar);
    %let i=%eval(&i+1);
    %let mvar=%scan(&mvars,&i);
%end;

%put &line;
%mend;

%* begin main macro ;
%local sas pos path basename log lst;

%* path to SAS command ;
%let sas=D:\Program Files\SASHome\SASFoundation\9.3\sas.exe;

%* if input file was not specified, set to SAS_EXECFILEPATH environment variable ;
%if (&file eq ) %then %let file=%sysget(SAS_EXECFILEPATH);

%* search backwards for the string "\prod\" (case insensitive) ;
%let pos=%sysfunc(find(&file,\prod\,i,-9999));
%if (&pos) %then %let path=%substr(&file,1,&pos)prod\;

%* search backwards for the string "\test\" (case insensitive) ;
%let pos=%sysfunc(find(&file,\qc\,i,-9999));
%if (&pos) %then %let path=%substr(&file,1,&pos)qc\;

%* if path is not found then the program is likely in a non-standard location ;
%* set the path to the directory containing the current program ;
%if (&path eq ) %then %do;
    %let pos=%sysfunc(find(&file,\,i,-9999));
    %if (&pos) %then %let path=%substr(&file,1,&pos);
    %let basename=%scan(%substr(&file,&pos+1),-2,.);
    %let log=&path.&basename..log;
    %let lst=&path.&basename..lst;
%end;

%* if config is missing set to &path.config_batch.cfg ;
%* otherwise assume an absolute path was specified ;
%if (&config eq ) %then %let config=&path.config_batch.cfg;

%* make sure the current editor session is saved to disk ;
dm "file";

%* if the config file is found use it, otherwise just submit without a config file ;
%let quotelenmax=%sysfunc(getoption(quotelenmax,keyword));
options noquotelenmax;
%if (%sysfunc(fileexist(&config)) and (%length(&log.&lst) eq 0)) %then %do;
    %dump_mvars(sas file path config)
    systask command " ""&sas"" -config ""&config"" -sysin ""&file"" " nowait;
%end;
%else %do;
    %dump_mvars(sas file path log lst)
    systask command " ""&sas"" -sysin ""&file"" -log ""&log"" -print ""&lst"" -sasinitialfolder ""&path"" " nowait;
%end;
options &quotelenmax;

%mend;

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


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

Macros Called           : export_dbms, 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_dbms macro header.

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

See Notes in the %export_dbms macro header.

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

%macro export_xlsx
/*---------------------------------------------------------------------
Wrapper macro for the %export_dbms macro
used to export a SAS dataset to an XLSX 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.                    */
);

%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)

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

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

%quit:

%mend;

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


text:
/*=====================================================================
Program Name            : IsNumD.sas
Purpose                 : Checks if alphanumeric (character) input is
                          valid numeric data (data step).
                          Also sets additional flag variables for
                          IsInt, IsFloat, IsNonNeg, IsPos, etc.
SAS Version             : SAS 9.3
Input Data              : Character data in a data step
Output Data             : Flag variables:
                          _IsNum
                          _IsNum2
                          _IsInt
                          _IsFloat
                          _IsNonNeg
                          _IsPos

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 16FEB2016
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:

data have;
   length char $32;
   input char $char32.;
   datalines;

.
._
.A
.Z
-1
0
1
1.1
 123456789012.123456789012
-123456789012.123456789012
 1234567890123.1234567890123
-1234567890123.1234567890123
 12345678901234.12345678901234
-12345678901234.12345678901234
 123456789012345.123456789012345
-123456789012345.123456789012345
 1234567890123456.1234567890123456
-1234567890123456.1234567890123456
 1.1
 -123
 -123.45
 --123.45
A
 B
123 456
123-456
~!@#$%^&*()_+=
;
run;

data want;
   set have;
   %IsNum(char);
run;

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

_IsNum:    Returns 1 if character input is valid numeric input,
           excluding blank, missing, or special missing values.

_IsNum2:   Returns 1 if character input is valid numeric input,
           including blank, missing, or special missing values.

_IsInt:    Returns 1 if character input is valid numeric input,
           and is also integer input.

_IsFloat:  Returns 1 if character input is valid numeric input,
           and is also floating point input.

_IsNonNeg: Returns 1 if character input is valid numeric input,
           and is also non-negative input (>= 0).

_IsPos:    Returns 1 if character input is valid numeric input,
           and is also positive input (> 0).

Note that the check for IsInt and IsFloat collapses for large numbers,
where precision is lost and the difference between
num and int(num) becomes 0 [num-int(num)=0].

On my machine, this happens around 1E15, or 15 significant digits.

This code must be called within a data step.  It creates the above
flag variables; it is up to the calling program to process those
flag variables as desired, including dropping those flag variables
from the output dataset if desired.

To easily drop these flag variables, the statement:
drop _Is:;
can be used, assuming no other variables use this prefix.

The input dataset should not have these variable names or data
collisions will result.

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

%macro IsNumD
/*---------------------------------------------------------------------
Checks if alphanumeric (character) input is valid numeric data.
---------------------------------------------------------------------*/
(CHAR          /* Character variable to check (REQ).                 */
);

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

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

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

__num__ = input(&char,?? best32.);
if (not missing(__num__)) then do;
   IsNum    = 1;
   IsNum2   = 1;
   IsInt    = (int(__num__) = __num__);
   IsFloat  = (abs(__num__ - int(__num__)) gt 0);
   IsNotNeg = (__num__ ge 0);
   IsPos    = (__num__ gt 0);
end;
else do;
   IsNum    = 0;
   IsNum2   = (strip(&char) in ('','.') or (.A le __num__ le .Z) or (__num__ = ._));
   IsInt    = 0;
   IsFloat  = 0;
   IsNotNeg = 0;
   IsPos    = 0;
end;
drop __num__;

%quit:

%mend;

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


text:
/*=====================================================================
Program Name            : useridToEmail.sas
Purpose                 : Maps a userid to the corresponding email address
SAS Version             : SAS 9.2
Input Data              : LAN Userid
                          Data step view created by %QueryActiveDirectory
Output Data             : Email address (default is &email macro variable)

Macros Called           : parmv, queryActiveDirectory

Originally Written by   : Scott Bass
Date                    : 06JUL2012
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:

* return email address of logged in LAN user (&sysuserid) ;
%useridToEmail;
%put &email;

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

* return email address of a specific user, into a named macro variable ;
%useridToEmail(
  userid=sbass
  ,mvar=emailaddress
);
%put &emailaddress;

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

This is really just a "wrapper" macro around the %QueryActiveDirectory
macro, passing in the proper parameters to return the email address.
All the "heavy lifting" is done by %QueryActiveDirectory.

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

%macro useridToEmail
/*---------------------------------------------------------------------
Maps a userid to the corresponding email address
---------------------------------------------------------------------*/
(USERID=&sysuserid
               /* Userid to lookup in Active Directory (REQ).        */
,MVAR=email    /* Macro variable in which to return the email        */
               /* address (REQ).                                     */
,DEBUG=        /* Echo debugging information in the SAS log? (Opt).  */
               /* Valid values are:                                  */
               /*    Blank = no debugging                            */
               /*    I     = connection information only             */
               /*    F     = full debugging (on a large query this   */
               /*          =    can fill the SAS DMS log)            */
);

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

%* check input parameters ;
%parmv(USERID,       _req=1,_words=0,_case=N)
%parmv(MVAR,         _req=1,_words=0,_case=U)
%parmv(DEBUG,        _req=0,_words=0,_case=U,_val=I F)

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

%* clear macro variable from any previous invocations ;
%symdel &mvar / nowarn;
%global &mvar;

%* call the QueryActiveDirectory macro ;
%QueryActiveDirectory(
  filter=(&(sAMAccountname=&userid)(objectclass=user))
  ,attrs=mail
  ,debug=&debug
)

%* set the macro variable ;
data _null_;
  set &syslast;
  call symputx("&mvar",value);
  stop;
run;

%if (&qad_rc ne 0) %then %do;
  %parmv(_msg=Error calling the QueryActiveDirectory macro)
  %goto quit;
%end;

%quit:
%mend;

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


text:
/*=====================================================================
Program Name            : reduce_pixel.sas
Purpose                 : Macro to reduce a map based on the pixel size
                          of the output.  Creates a map dataset that has
                          reduced size and also removes single- and
                          dual-point polygons.
SAS Version             : SAS V9
Input Data              : Map Dataset
Output Data             : Reduced Map Dataset

Macros Called           : parmv

Originally Written by   : SAS Institute
Date                    : JAN 2008
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                    : 24SEP2013
Change/reason           : Modified macro originally written by
                          SAS Institute.
Program Version #       : 1.1

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

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

libname mapsspde spde "%sysfunc(pathname(work))" temp=yes;

proc copy in=mapssas out=mapsspde;
   select world china us;
run;

%macro map(data=, id=, x=, y=, title=);
   %reduce_pixel(inmap=&data, outmap=mapsspde.mymap, id=&id, xpix=&x, ypix=&y);
   title  "&title Map with &x x &y reduction";
   title2 "%trim(%left(%nobs(&syslast))) obs";
   proc gmap data=mapsspde.mymap map=mapsspde.mymap;
      id &id;
      choro %scan(&id,1,%str( )) / nolegend statistic=first;
   run;
   quit;
%mend;

%map(data=mapsspde.world, id=cont id, x=1920, y=1080, title=World);
%map(data=mapsspde.world, id=cont id, x=1600, y=1200, title=World);
%map(data=mapsspde.world, id=cont id, x= 800, y= 600, title=World);
%map(data=mapsspde.world, id=cont id, x= 400, y= 300, title=World);

%map(data=mapsspde.china, id=id,      x=1920, y=1080, title=China);
%map(data=mapsspde.china, id=id,      x=1600, y=1200, title=China);
%map(data=mapsspde.china, id=id,      x= 800, y= 600, title=China);
%map(data=mapsspde.china, id=id,      x= 400, y= 300, title=China);

%map(data=mapsspde.us,    id=state,   x=1920, y=1080, title=US);
%map(data=mapsspde.us,    id=state,   x=1600, y=1200, title=US);
%map(data=mapsspde.us,    id=state,   x= 800, y= 600, title=US);
%map(data=mapsspde.us,    id=state,   x= 400, y= 300, title=US);

proc copy in=mapsgfk out=mapsspde;
   select world china us;
run;

* <repeat above code > ;

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

This is a modified copy of the reduce_pixels macro available for download
from http://support.sas.com/rnd/datavisualization/mapsonline/html/tools.html.
The macro has been renamed from %reduce to %reduce_pixel.

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

%macro reduce_pixel
/*--------------------------------------------------------------------
Macro to reduce a map based on the pixel size of the output.
--------------------------------------------------------------------*/
(INMAP=        /* Input map dataset name (REQ).                     */
,OUTMAP=       /* Output map dataset name (REQ).                    */
,ID=           /* Space separated list of ID values for the map     */
               /* regions (REQ).                                    */
,XPIX=         /* Number of horizonal pixels (REQ).                 */
,YPIX=         /* Number of vertical pixels (REQ).                  */
,MULT=1        /* Multiplier (REQ).  Increases the number of data   */
               /* points in the output dataset.  For example,       */
               /* MULT=2 to double to data points, MULT=3 to triple */
               /* the data points, etc.  ("Double" and "Triple" are */
               /* illustrative only, and not an exact amount.)      */
);

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

%* check input parameters ;
%parmv(INMAP,         _req=1,_words=0,_case=U)
%parmv(OUTMAP,        _req=1,_words=0,_case=U)
%parmv(ID,            _req=1,_words=1,_case=U)
%parmv(XPIX,          _req=1,_words=0,_val=POSITIVE)
%parmv(YPIX,          _req=1,_words=0,_val=POSITIVE)
%parmv(MULT,          _req=1,_words=0,_val=POSITIVE)

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

/* Get the X/Y extents of the map data */
proc summary data=&inmap;
   var x y;
   output out=t min(x)=xmin max(x)=xmax min(y)=ymin max(y)=ymax;
run;

/*
Calculate the ratio for the output pixels "pixscale" and the map data
"mapscale".  This is simply Y/X, but it will determine which ordinate
(X or Y) will constrain the map data.  For instance, a map like the
United Kingdom will be constrained in the Y before the X, whereas a map
of Russia will be constrained in the X before the Y.
*/
data _null_;
   set t;
   xpix=&xpix;
   ypix=&ypix;
   mult=&mult;

   pixscale=ypix/xpix;
   mapscale=(ymax-ymin)/(xmax-xmin);

/*
Set a macro variable to the range that is going to be the constraint
for the map (X or Y).  The formula is maprange/pixelrange for the
particular ordinate values.
*/

   if (pixscale > mapscale) then
      range=(xmax-xmin)/xpix;
   else
      range=(ymax-ymin)/ypix;
   range=range/mult;
   call symputx("range",put(range,f12.7-L));
   stop;
run;

/*
Call GREDUCE and use the range calculated above as the E1 value, which
is the necessary distance between points for a DENSITY value of 1. All
points which are either 0 DENSITY (defined by intersection of polygons)
or 1 DENSITY (defined as being farther apart than the E1 value) will be
acceptable for our output.
*/
proc greduce data=&inmap out=&outmap e1=⦥
   id &id;
run;

/*
Make sure the data is sorted so that we can use BY-group processing to
remove extraneous points and no-area polygons (1- and 2-point polygons)
*/
proc sort data=&outmap;
   by &id segment;
run;

/*
Remove extra data from the map:
   1.  Any DENSITY value greater than 1.
   2.  Any polygons that contain only 1 or 2 points.
*/
data &outmap;
   retain first;
   set &outmap;
   by &id segment;
   where density le 1;
   if first.segment then first=_n_;
   if last.segment and _n_-first < 2 then delete;
   drop first;
run;

/*
A second pass is necessary to remove the second point of the 2-point
polygons.
*/
data &outmap;
   * GREDUCE does not preserve PDV order, which is annoying.  ;
   * Use the input dataset to re-order the PDV. ;
   if 0 then set &inmap;
   set &outmap;
   by &id segment;
   if first.segment and last.segment then delete;
run;

%quit:

%mend;

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