text:
/*====================================================================
Program Name            : RunAll_ControlTable.sas
Purpose                 : Runs SAS programs asynchronously and
                          multi-threaded, honoring any job
                          dependencies.
SAS Version             : SAS 9.1.3
Input Data              : N/A
Output Data             : N/A

Macros Called           : None

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

Modification History    : Original Version

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

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

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

This program is a template program to create a Programs control table
with specified program dependencies, then calls the %RunAll macro to
run the programs in batch mode, honoring the specified dependencies.

This program uses SAS as the "scripting engine" to submit additional
SAS programs in batch mode.  This makes this approach portable to both
Windows and Unix.

This program honors any dependencies specified in the list of programs.
Programs dependent on the completion of other programs will not run
until the "parent" program(s) finish.  This is usually relevant to SDTM
or ADAM processing, although this program can also be used to
asynchronously run independent TFL programs.

Set the macro variable abort=Y to abort processing if any upstream
program ends in error.

Set the macro variable max_threads to limit concurrent threads to that
number.  In principle, there should always be a value for max_threads,
so always set it to to some value, i.e. a number high enough to get
maximum throughput. However, you should pay attention to any decreased
machine performance when running multiple SAS processes, and adjust
max_threads accordingly.

More threads is not necessarily better.  On my dual core laptop, I get
the best elapsed time with max_threads=2.  Use the elapsed time results
at the bottom of the log to derive the best value for max_threads for
your environment.

It is a good idea to set abort=Y and run with a small number of
programs until all parameters are correct and the process is debugged.
If SAS fails to initialize (usually due to bad configuration settings),
it is difficult to break out of the main SAS driver program. Setting
abort=Y and max_threads=1 should be sufficient to abort if the SAS
configuration parameters are incorrect.

If running programs from a single directory under a programming
environment (prod or qc), you can either define -log and -print options
in an (environment specific) SAS configuration file, as parameters to
the command string (&cmd), or in the programs dataset.

If running programs from multiple directories, or across programming
environments (eg. all of SDTM, ADAM, and TFLs, or from both prod and qc
environments), then the program paths and/or -log and -print options
must be specified in the programs dataset since these values are now
dynamic rather than static.

You can specify a list of programs via the -sysparm invocation
option to limit the list of programs to run.  The program names
specified on the sysparm option must exactly match the >basenames<
as specified in the datalines statement below
(eg. without path, with extension)

A run summary report is created at the end of the job run, indicating
the run status for each program.

To debug the code generation process, comment out the line
"%include code", then review the code via "fslist code".

To prevent warning messages in the log about unable to read
sasuser.profile, create a copy of your sasuser directory, and
configure the batch runs to use the copied sasuser directory and
the -rsasuser option.

The %dirlist macro
(https://github.com/scottbass/SAS/blob/master/Macro/dirlist.sas)
could also be useful to seed the Programs control dataset from a
directory of existing programs.

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

%let start_time=%sysfunc(datetime());

* define parameters for batch run ;
%let abort              = Y;
%let abort_codes        = 0 1;  * 0 to abort on warnings and errors, 0 1 to abort on errors only ;
%let max_threads        = 10;
%let fs                 = /;    * Windows = \, Unix = / ;
%let env                = prod; * prod or qc ;
%let base               = /proj/009/tak491_303;
%let autoexec           = &base.&fs.&env.&fs.autoexec.sas;
%let source             = &base.&fs.&env.&fs.programs;
%let log                = &base.&fs.&env.&fs.output&fs.logs;
%let print              = &base.&fs.&env.&fs.output&fs.reports;
%let summary_rpt_type   = html;  * html or rtf or txt.  I prefer HTML since MS Word keeps a lock on the RTF file. ;
                                 * Use txt on Unix or else html then ftp protocol in your browser (ftp://sbass@prnapp17//proj/009/tak491_303/...) ;
%let summary_rpt        = &log.&fs.RunAll.&summary_rpt_type;

%let sas                = %sysget(SAS_ROOT)/sas;
%let config             = ;
%let options            = -autoexec &autoexec;
%let command            = &sas;

* debugging statement ;
%put %quote(&command);

* define list of programs to run here ;
* define dependency groups using the group variable ;
* if running programs across environments specify the env variable ;
data programs;
   length group 8 env $4 program $200 source $200 log print $200;
   input group /* env */ program;
   source   = "&source";
   env      = "&env";
   log      = quote("&log");
   print    = quote("&print");
/* alternative approach
   log      = quote(catx("&fs","&base",env,"output","logs"));
   print    = quote(catx("&fs","&base",env,"output","reports"));
*/
   datalines;
0  ..\..\..\formats.sas
1  test1.sas
1  test2.sas
1  test3.sas
2  test4.sas
2  test5.sas
3  test_error.sas
3  test_warning.sas
4  test6.sas
4  test7.sas
4  test8.sas
4  test9.sas
;
run;

* compile RunAll macro ;
%include "&base.&fs.RunAll.sas" / nosource nosource2;
%RunAll(debug=N);

%let end_time=%sysfunc(datetime());
%put %str(Ela)psed Time=%sysfunc(round(&end_time - &start_time,1E-2)) seconds.;

options ls=&ls;


text:
/*====================================================================
Program Name            : dedup_string.sas
Purpose                 : Removes duplicate items from a string
SAS Version             : SAS 9.2
Input Data              : Data step string containing duplicate tokens
Output Data             : Data step string with duplicates removed

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 29AUG2011
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 _null_;
  oldstring="C A B B A G E 3 2 1 1 2 3";
  %dedup_string(invar=oldstring, outvar=newstring);
  %* dedup_string(invar=oldstring);
  put oldstring=;
  put newstring=;
run;

Dedups the input string oldstring, returning "C A B G E 3 2 1"

The first invocation creates a new variable, the second invocation
dedups the old variable in place.

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

data _null_;
  length oldstring newstring $200;
  oldstring="C|A|B|B|A|G|E|3|2|1|1|2|3";
  %* dedup_string(invar=oldstring, outvar=newstring, dlm=|);
  %dedup_string(invar=oldstring, dlm=|);
  put oldstring=;
  put newstring=;
run;

Same as above but using a different tokenization delimiter.

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

This macro must be called from within a data step.

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

%macro dedup_string
/*--------------------------------------------------------------------
Removes duplicate items from a string using hash objects
--------------------------------------------------------------------*/
(INVAR=        /* Input variable name (REQ).                        */
,OUTVAR=       /* Output variable name (Opt).                       */
,DLM=          /* Delimiter marking each token in the input string  */
               /* (Opt).  If not specified, a space will be used.   */
);

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

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

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

%* if OUTVAR was not specified set it to invar ;
%if (&outvar eq ) %then %let outvar=&invar;

%* if DLM was not specified set it to a space ;
%if (%superq(dlm) eq ) %then %let dlm=%str( );

length __word $200 __temp $32767;

%* if outvar ne invar define length of outvar via assignment statement ;
%* the data in outvar can never be longer than invar, only shorter ;
%if (&outvar ne &invar) %then %do;
  if 0 then &outvar=&invar;
%end;

%* need to use a temporary variable when updating in place ;
__temp=&invar;
call missing(&outvar);

%* process the input string, comparing current token with tokens already in output string ;
do __i = 1 by 1;
  __word=scan(__temp,__i,' ');
  if missing(__word) then leave;
  if indexw(upcase(&outvar),upcase(__word)) eq 0 then &outvar=catx(' ',&outvar,__word);
end;

drop __temp __word __i;

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

%mend;

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

repo_name: scottbass/SAS

repo_language: SAS

mime_type: text/plain


text:
/*=====================================================================
Program Name            : IsNum.sas
Purpose                 : Checks if alphanumeric (character) input is
                          valid numeric data.
                          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 IsNum
/*---------------------------------------------------------------------
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            : export_sas.sas
Purpose                 : Simple macro to copy a source dataset to a
                          target library with some additional error
                          checking.
SAS Version             : SAS 9.4
Input Data              : SAS dataset
Output Data             : SAS dataset in another library

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.

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

Modification History    : Original version

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

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

* cleanup from previous invocations ;
proc datasets lib=work nowarn nolist;
   delete class;
quit;

%export_sas(
   data=sashelp.class
)

or

* cleanup from previous invocations ;
proc datasets lib=work nowarn nolist;
   delete class;
quit;

%export_sas(
   data=sashelp.class
   ,outlib=work
)

Copies sashelp.class to work.class,
preserving any indexes and integrity constraints (in this case none).

The default OUTLIB parameter is work.

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

data class;
   set sashelp.class;
run;

* assumes sasuser.class does not already exist ;
%export_sas(
   data=class
   ,outlib=sasuser
)

* cleanup ;
proc delete data=sasuser.class;
run;

Copies work.class to sasuser.class using a one-level source dataset name.

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

%let options=%sysfunc(getoption(USER, keyword));
%put &=options;
options USER=sasuser;

* this will create sasuser.class because of the USER option ;
data class;
   set sashelp.class;
run;

* cleanup from previous invocations ;
proc datasets lib=work nowarn nolist;
   delete class;
quit;

%export_sas(
   data=class
   ,outlib=work
)

* cleanup ;
options user='';
proc delete data=sasuser.class;
run;

Creates sasuser.class due to the USER option and a one-level name,
then copies sasuser.class to work.class.

If a one-level input data set is specified,
the USER system option is checked, otherwise work is used.

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

data class;
   set sashelp.class;
run;

%export_sas(
   data=class
   ,outlib=work
)

No export (COPY) is done since the source dataset
already exists in the target library.
In other words, the source and target libraries are the same.

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

data work.myshoes;
   set sashelp.shoes;
   stop;  * empty dataset ;
run;

%export_sas(
   data=sashelp.shoes
   ,outlib=work
   ,outdata=myshoes
)

Copies sashelp.shoes to work.shoes using PROC COPY,
then renames work.shoes to work.myshoes.

If the target dataset (work.myshoes) already exists,
it will be overwritten.  The target dataset work.myshoes
will contain the complete set of sashelp.shoes data.

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

%export_sas(
   data=sashelp.class (where=(sex='F'))
   ,outlib=work
   ,outdata=females
)

Copies females from sashelp.class to work.class using PROC COPY,
then renames work.class to work.females.

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

Check that indexes and integrity constraints are preserved:

%let options=%sysfunc(getoption(dlcreatedir));
%put &=options;
options dlcreatedir;
libname temp "%sysfunc(pathname(work))\temp";
options &options;

data temp.class;
   set sashelp.class;
   where age between 12 and 16;
run;

proc datasets lib=temp nowarn nolist;
   modify class;
   index create name / unique;
   index create sex;
   ic create val_sex =
      check (where=(sex in ('M','F')))
      message = "Valid values for variable SEX are either 'M' or 'F'."
   ;
   ic create val_age =
      check (where=(age between 12 and 16))
      message = "An invalid AGE has been provided."
   ;
   run;
quit;

%export_sas(
   data=temp.class (where=(sex='M'))
   ,outlib=work
   ,outdata=males
)

* check that indexes and integrity constraints are preserved ;
proc contents data=work.males details;
run;

Copies males from temp.class to work.class,
then renames work.class to work.males,
preserving indexes and integrity constraints.

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

Error checking:

%export_sas(
   data=sashelp.doesnotexist
)

Fails, source dataset sashelp.doesnotexist does not exist.

%export_sas(
   data=sashelp.class
   ,outlib=work
   ,outdata=ClAsS
)

Fails, outdata name must be different than the source dataset name.

%export_sas(
   data=sashelp.class
   ,outlib=foo
)

Fails, the foo target library does not exist.
This error is not trapped by the macro but is gracefully handled by PROC COPY.

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

If the source library and target library are the same,
no export is done since the target dataset already exists.

However, if the OUTDATA parameter is specified,
and the OUTDATA dataset already exists in the target library,
that dataset will be overwritten.

If dataset options are specified, they are supported via PROC COPY's
OVERRIDE= option.  Usually the dataset option is a WHERE clause.
No testing has been done for other dataset options.

PROC COPY's CLONE option is used to copy various source dataset options
(bufsize, compress, reuse, outrep, encoding, pointobs) to the
target dataset.

PROC COPY's INDEX= and CONSTRAINT= options are used to copy any
source dataset indexes and integrity constraints to the target dataset.

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

%macro export_sas
/*---------------------------------------------------------------------
Simple macro to copy a source dataset to a target library
with some additional error checking.
---------------------------------------------------------------------*/
(DATA=         /* Dataset to export (REQ).                           */
               /* Data set options, such as a where clause, may be   */
               /* specified.                                         */
,OUTLIB=WORK   /* Output library (REQ).                              */
,OUTDATA=      /* Output dataset name (Opt).                         */
               /* If specified, the source dataset is copied         */
               /* to the target library with the source dataset      */
               /* name, then the target dataset is renamed           */
               /* to the OUTDATA name.                               */
);

%local macro parmerr;
%local _temp _pos _options _inlib _inds _outlib _outds _target;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(DATA,         _req=1,_words=1,_case=N)  /* words=1 allows ds options */
%parmv(OUTLIB,       _req=1,_words=0,_case=U)
%parmv(OUTDATA,      _req=0,_words=0,_case=U)

%if (&parmerr) %then %return;

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

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

%* parse the source dataset name ;
%let _inlib = %scan(%superq(_temp),1,.);
%let _inds  = %scan(%superq(_temp),2,.);

%* if one-level source dataset was specified, check the USER system option, otherwise use WORK ;
%if (&_inds eq ) %then %do;
   %let _inds = &_inlib;
   %let _inlib = %sysfunc(getoption(USER));
   %let _inlib = %sysfunc(compress(&_inlib,%str(%'%")));
   %if (&_inlib eq ) %then %let _inlib=WORK;
%end;
%let _inlib=%upcase(&_inlib);

%* if the OUTDATA parameter matches the source dataset name, abort with an ERROR ;
%if (%upcase(&outdata) eq %upcase(&_inds)) %then %do;
   %let syscc=8;
   %parmv(_msg=OUTDATA (&outdata) must be different than the input dataset (%upcase(&_inds)))
   %return;
%end;

%* create the target dataset name ;
%if (&outdata eq ) %then %do;
   %let _outlib = &outlib;
   %let _outds  = &_inds;
%end;
%else %do;
   %let _outlib = &outlib;
   %let _outds  = &outdata;
%end;
%let _target = %superq(_outlib).%superq(_outds);

%* if the input and output libraries are the same, abort with a WARNING ;
%if (%upcase(&_inlib) eq %upcase(&_outlib)) %then %do;
   %let syscc=4;
   %let _msg = %str(WAR)NING: Source library (%upcase(&_inlib)) and Target library (%upcase(&_outlib)) are the same.  No export was done.;
   %put &_msg;
   %return;
%end;

%* if the target dataset exists and will not be renamed, abort with a WARNING ;
%if (&outdata eq ) and (%sysfunc(exist(&_target,DATA)) or %sysfunc(exist(&_target,VIEW))) %then %do;
   %let syscc=4;
   %let _msg = %str(WAR)NING: Target dataset (&_target) already exists in the target library (&_outlib).  No export was done.;
   %put &_msg;
   %return;
%end;

%* copy the source dataset, including indexes and integrity constraints, to the output library ;
proc copy in=&_inlib out=&_outlib index=yes constraint=yes clone
   %if (%superq(_options) ne ) %then %do;
   override=%unquote(%superq(_options))
   %end;
   ;
   select &_inds;
run;

%* rename target dataset if requested ;
%if (&outdata ne ) %then %do;
   proc datasets lib=&_outlib nowarn nolist;
      delete &_outds;  %* no error message if &_outds does not exist ;
      change &_inds=&_outds;
   quit;
%end;

%mend;

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


text:
/*=====================================================================
Program Name            : justify.sas
Purpose                 : Creates a macro variable with text left,
                          center, and right justified within a given
                          width (usually the linesize).
                          (pure macro solution)
SAS Version             : SAS 9.1.3
Input Data              : N/A
Output Data             : N/A

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 05SEP2010
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:

options ls=80;

* basic tests ;
%let string=%justify(left=Left,center=Center,right=Right);
%put %length(&string) *&string*;

%let string=%justify(left=Left);
%put %length(&string) *&string*;

%let string=%justify(center=Center);
%put %length(&string) *%str(&string)*;

%let string=%justify(right=Right);
%put %length(&string) *&string*;

%let string=%justify(left=Left,right=Right);
%put %length(&string) *&string*;

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

* without trimming result ;
* all string lengths should be 80 ;
%let string=%justify(left=Left,center=Center,right=Right,trim=N);
%put %length(&string) *&string*;

%let string=%justify(left=Left,trim=N);
%put %length(&string) *&string*;

%let string=%justify(center=Center,trim=N);
%put %length(&string) *&string*;

%let string=%justify(right=Right,trim=N);
%put %length(&string) *&string*;

%let string=%justify(left=Left,right=Right,trim=N);
%put %length(&string) *&string*;

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

* more realistic use cases ;
options nonumber nodate ls=80;

%let string=%justify(left=Left,center=Center,right=Right);
%put &string;

title2 "&string";
footnote;
proc print data=sashelp.shoes (obs=10) noobs;
run;

   Creates the macro variable &string with the text
   "Left   Center   Right" justified as left/center/right respectively
   within the current linesize.

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

title;
footnote;

%let string=%justify(
   left=Prepared by: &sysuserid
   ,right=Date: %qleft(%qsysfunc(date(),weekdatx.))
   ,width=75
);
%put &string;

footnote "%unquote(&string)";
proc print data=sashelp.shoes (obs=10) noobs;
run;

   Creates the footnote with the text
   "Prepared by: <userid>   Date: <current date>"
   justified as left/right respectively within a line length of 75.

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

%let string=%justify(
   left=This is a long string that is left justified
   ,center=This is a long string that is centered
   ,right=This is a long string that is right justified
   ,width=80
);
%put &string;

   This will return an error string since the combined lengths of the
   input parameters is greater than the specified linesize.

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

data _null_;
   file print;
   string="%justify(left=Page 1,center=Acme Pharmaceuticals,right=&sysdate9)";
   put string;
run;

   Prints the string "Page 1   Acme Pharmaceuticals   <current date>"
   justified as left/center/right respectively within the current
   linesize.

   Of course, in a data step, there are probably easier ways of doing this.

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

This is a "pure macro" solution so it must be called as an rvalue, i.e.
on the right hand side of an equals sign or in the context of a
function call.

If the combined text is greater than the width specification
(default is the current linesize) then a warning is written to the
log and error text is returned.

Because of all the leading spaces, the returned value is returned QUOTED.
Depending on the context of the calling program, the returned value
may need to be unquoted.

Compare the results:

%put *%justify(center=Center)*;
%let string=%justify(center=Center);
%put *&string*;
%let string=%unquote(&string);
%put *&string*; * is now left justified due to the unquoted assignment statement ;
                * of course, in some cases that could be just what you want ;

%put *%unquote(%justify(center=Center))*;  * does not work ;

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

%macro justify
/*---------------------------------------------------------------------
Creates a macro variable of left/center/right justified text.
---------------------------------------------------------------------*/
(LEFT=         /* Left justified text (Opt.)                         */
,CENTER=       /* Center justified text (Opt.)                       */
,RIGHT=        /* Right justified text (Opt.)                        */
,WIDTH=        /* Explicit width (Opt).                              */
               /* If blank the current linesize option is used.      */
,TRIM=Y        /* Trim the returned string (Opt.)                    */
               /* If Y, the returned string is trimmed.              */
               /* If N, the returned string is padded with trailing  */
               /* spaces, with the returned string length equal to   */
               /* the specified linesize.                            */
);

%local macro parmerr string start_l start_c start_r;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(LEFT,         _req=0,_words=1,_case=N)
%parmv(CENTER,       _req=0,_words=1,_case=N)
%parmv(RIGHT,        _req=0,_words=1,_case=N)
%parmv(WIDTH,        _req=0,_words=0,_val=POSITIVE)
%parmv(TRIM,         _req=0,_words=0,_val=0 1)

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

%* if width is blank then use the current linesize option ;
%if (&width eq ) %then %let width=%sysfunc(getoption(linesize));

%* check if the combined length of the strings is greater than the width ;
%if (%eval(%length(&left&center&right) gt &width)) %then %do;
   %put %str(WAR)NING:  The combined lengths of the input parameters is greater than &width..;
   %let string=%str(*** ERROR IN &SYSMACRONAME PARAMETERS ***);
%end;
%else %do;
   %* unfortunately, SAS format modifiers do not work in the sysfunc/qsysfunc functions ;
   %* so, we have to roll our own justification algorithms ;
   %let start_l=0;
   %let start_c=0;
   %let start_r=0;

   %if %length(&left)   %then %let start_l=1;
   %if %length(&center) %then %let start_c=%eval((&width - %length(&center)) / 2);
   %if %length(&right)  %then %let start_r=%eval((&width - %length(&right)));

   %* put &start_l &start_c &start_r;  %* for debugging ;

   %* fill the buffer from left to right, always ensuring the buffer is as long as the width ;
   %* otherwise, qsubstr will return out of range errors ;
   %if (&start_l) %then %let string=%superq(left);
   %let string=%qsysfunc(putc(%superq(string),$char&width..));

   %if (&start_c) %then %let string=%qsubstr(%superq(string),1,&start_c)%superq(center);
   %let string=%qsysfunc(putc(%superq(string),$char&width..));

   %if (&start_r) %then %let string=%qsubstr(%superq(string),1,&start_r)%superq(right);
   %let string=%qsysfunc(putc(%superq(string),$char&width..));

   %* if trim was specified, trim the final string ;
   %if (&trim) %then %let string=%qtrim(%superq(string));
%end;

%* NOTE: because of all the leading spaces, the string is returned QUOTED ;
%* Depending on the context of the calling program, the returned value may need to be unquoted ;
&string

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

%mend;

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


text:
/*=====================================================================
Program Name            : execute_macro.sas
Purpose                 : Execute a macro if the macro exists.
                          If the macro does not exist do nothing.
SAS Version             : SAS 9.4
Input Data              : N/A
Output Data             : N/A

Macros Called           : None

Originally Written by   : Scott Bass
Date                    : 28SEP2015
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:

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

This macro replaces the default fcf_execute_macro that comes with AML,
and works around a bug in the SAS-supplied macro.

The SAS-supplied macro checks for the existance of a macro in
WORK.SASMACR and SOURCE.SASMACR only.  However, on Linux, esp. in EG
(and DI Studio?), compiled macros are saved to WORK.SASMAC1 instead of
WORK.SASMACR.

See http://support.sas.com/kb/36/360.html

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

%macro execute_macro(macroname);
   %let debug=0;

   %if (%superq(macroname) eq ) %then %return;
   %let fullname=&macroname;
   %let macroname=%scan(&macroname,1,%str(%());

   %* Is the macro pre-compiled in work.sasmacr (or work.sasmac1, work.sasmac2, etc) ;
   %if (%sysmacexist(&macroname)) %then %do;
      %put SYSMACEXIST: &macroname;
      %&fullname
      %return;
   %end;

   %* Is SASMSTORE active?  If so, is the macro pre-compiled there? ;
   %* Assume the SASMSTORE catalog name is sasmacr ;
   %let option_mstored   = %sysfunc(getoption(mstored));
   %let option_sasmstore = %sysfunc(getoption(sasmstore));
   %if (&debug) %then %put &=option_sasmstore &=option_mstored;
   %if ((&option_mstored eq MSTORED) and (%length(&option_sasmstore) gt 0)) %then %do;
      %if (%sysfunc(cexist(&option_sasmstore..sasmacr.&macroname..MACRO))) %then %do;
         %put SASMSTORE: %upcase(&option_sasmstore..&macroname..MACRO);
         %&fullname
         %return;
      %end;
   %end;

   %* Is it an autocall macro? ;
   %let rx1=%sysfunc(prxparse(/^\%str(%()(.*)\%str(%))$/));  %* remove leading and trailing parentheses ;
   %let rx2=%sysfunc(prxparse(/('.*?'|".*?"|\S+)/));  %* return single|double-quoted string, or non-space tokens ;
   %if (&debug) %then %put &=rx1 &=rx2;

   %* Get sasautos setting ;
   %let sasautos=%sysfunc(strip(%sysfunc(getoption(sasautos))));
   %if (&debug) %then %put &=sasautos;

   %* Remove leading and trailing parentheses if present ;
   %if (%sysfunc(prxmatch(&rx1,%superq(sasautos)))) %then %let sasautos=%sysfunc(prxposn(&rx1,1,%superq(sasautos)));
   %if (&debug) %then %put &=sasautos;

   %* Now parse the sasautos setting ;
   %let start=1;
   %let stop=%length(%superq(sasautos));
   %let position=0;
   %let length=0;
   %syscall prxnext(rx2, start, stop, sasautos, position, length);
   %if (&debug) %then %put &=start &=stop &=position &=length;

   %do %while (&position gt 0);
      %let found = %substr(%superq(sasautos), &position, &length);
      %if (&debug) %then %put &=found &=position &=length;

      %if (%superq(found) eq %str(,)) %then %goto skip;

      %* If a physical pathname then allocate a temporary fileref ;
      %* If a fileref then just use that one ;
      %if (%sysfunc(indexc(&found,%str(%"%')))) %then %do;
         %let fileref=________;
         %let rc=%sysfunc(filename(fileref,&found));
      %end;
      %else %do;
         %let fileref=&found;
      %end;

      %let dir_handle=%sysfunc(dopen(&fileref));
      %if (&dir_handle ne 0) %then %do;
         %let mem_handle=%sysfunc(mopen(&dir_handle,&macroname..sas,i));
         %if (&mem_handle ne 0) %then %do;
            %put SASAUTOS: %sysfunc(pathname(&fileref))/&macroname..sas;
            %&fullname
            %goto cleanup;
            %return;  %* should never execute but does not hurt ;
         %end;
         %let rc=%sysfunc(dclose(&dir_handle));
         %if (&fileref eq ________) %then %let rc=%sysfunc(filename(&fileref));
      %end;

      %* Next token ;
      %skip:
      %syscall prxnext(rx2, start, stop, sasautos, position, length);
   %end;

   %cleanup:
   %if (&mem_handle ne 0) %then %let rc=%sysfunc(fclose(&mem_handle));
   %if (&dir_handle ne 0) %then %let rc=%sysfunc(dclose(&dir_handle));
   %if (&fileref eq ________) %then %let rc=%sysfunc(filename(&fileref));

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

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


text:
/*=====================================================================
Program Name            : check_if_empty.sas
Purpose                 : Checks if the source dataset is empty.
SAS Version             : SAS 9.4
Input Data              : N/A
Output Data             : N/A

Macros Called           : parmv

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

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

Scott Bass (sas_l_739@yahoo.com.au)

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

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

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

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

Modification History    : Original version

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

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

* create test datasets ;
data empty not_empty;
   set sashelp.shoes (obs=30);
   output not_empty;
run;

* create test views ;
data not_empty_dsv / view=not_empty_dsv;
   set not_empty;
run;
data empty_dsv / view=empty_dsv;
   set empty;
run;

proc sql;
   create view not_empty_sql as
      select * from not_empty
   ;
   create view empty_sql as
      select * from empty
   ;
quit;

%put %check_if_empty(data=empty);         * should be 1 ;
%put %check_if_empty(data=not_empty);     * should be 0 ;

%put %check_if_empty(data=empty_dsv);     * should be 1 ;
%put %check_if_empty(data=not_empty_dsv); * should be 0 ;

%put %check_if_empty(data=empty_sql);     * should be 1 ;
%put %check_if_empty(data=not_empty_sql); * should be 0 ;

%put %check_if_empty(data=db_core.fsc_party_dim); * should be 0, works with Oracle tables too ;

%put %check_if_empty(data=not_empty(where=(region='XXX'))); * should be 1, no obs match where clause ;
%put %check_if_empty(data=doesnotexist);
%put %check_if_empty(data=doesnotexist(where=(region='XXX')));

data _null_;
   if %check_if_empty(data=empty) then
      put "Empty is empty";
   else
   if %check_if_empty(data=not_empty) then
      put "Not_empty is empty";
run;

%macro test;
   %let source=empty;
   %let empty=%check_if_empty(data=&source);
   %if &empty %then %put &source is empty.;

   %let source=not_empty;
   %let empty=%check_if_empty(data=&source);
   %if &empty %then %put &source is empty.;
%mend;
%test;

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

This macro is a "pure macro" and returns an rvalue.  It must be on the
right side of an assignment statement, or otherwise used where an
rvalue is appropriate, eg. %put %check_if_empty(data=foo).

Returns 1 if the dataset/view/table is empty, 0 if not empty.
So, the programming logic is
"if <empty> then <empty processing>" or
"if not <empty> then <not empty processing>".

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

%macro check_if_empty
/*---------------------------------------------------------------------
Checks if the source dataset is empty.
---------------------------------------------------------------------*/
(DATA=         /* Input dataset or view (REQ).                       */
);

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

%* check input parameters ;
%parmv(DATA,         _req=1,_words=1,_case=N)  /* words=1 allows data set options */

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

%let pos=%index(&data,%str(%());
%if (&pos eq 0) %then %do;
   %let dataset=&data;
   %let options=;
%end;
%else %do;
   %let dataset=%substr(&data,1,&pos-1);
   %let options=%substr(&data,&pos);
%end;

%* additional error checking ;
%* check if the dataset exists ;
%if not (%sysfunc(exist(&dataset,data)) or %sysfunc(exist(&dataset,view))) %then %do;
   %parmv(_msg=&dataset does not exist)
   %goto quit;
%end;

%let dsid=%sysfunc(open(&dataset &options,I));
%if (&dsid le 0) %then %do;
   %parmv(_msg=Unable to open &dataset for input)
   %goto quit;
%end;
%let rc=%sysfunc(fetch(&dsid));
%let dsid=%sysfunc(close(&dsid));
%eval(not (&rc eq 0))

%quit:

%mend;

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


text:
/*====================================================================
Program Name            : seplist.sas
Purpose                 : Emit a list of words separated by a delimiter.
SAS Version             : Unknown
Input Data              : N/A
Output Data             : N/A

Macros Called           : None

Originally Written by   : Richard Devenezia
Date                    : 02SEP1999
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.

Based on original work by Richard Devenezia.
http://www.devenezia.com/downloads/sas/macros/index.php?m=seplist
Used with permission.

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

Modification History    :

Programmer              : Scott Bass
Date                    : 24APR2006
Change/reason           : Copied from
                          http://www.devenezia.com/downloads/sas/macros/index.php?m=seplist
Program Version #       : 1.1

Programmer              : Scott Bass
Date                    : 07OCT2011
Change/reason           : Added %unquote(&prefix) and %unquote(&suffix)
                          to allow for incremented prefix or suffix
Program Version #       : 1.2

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

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

%put %seplist(Hello World);
* Returns Hello,World ;

%put %seplist(Hello World,nest=QQ);
* Returns "Hello","World" ;

%put %seplist(Hello   ^   World   ,nest=Q,indlm=^,trim=N);
* Returns 'Hello   ','   World' ;

%put %seplist(Hello   ^   World   ,nest=Q,indlm=^,trim=Y);
* Returns 'Hello','World' ;

%put %seplist(Hello   ^   World   ,nest=QQ,indlm=^,dlm=~,trim=Y);
* Returns "Hello"~"World" ;

%put %seplist(A B C,prefix=PREFIX_,suffix=_suffix);
* Returns PREFIX_A_suffix,PREFIX_B_suffix,PREFIX_C_suffix ;

%put %seplist(A B C,prefix=%str(%"PREFIX_),suffix=%str(_suffix%"));
* Returns "PREFIX_A_suffix","PREFIX_B_suffix","PREFIX_C_suffix" ;

%put %seplist(A B C,prefix=%nrstr(name&n=));
* Returns name1=A,name2=B,name3=C ;

%put %seplist(A B C,prefix=%nrstr(name&n=),suffix=%nrstr(_&n));
* Returns name1=A_1,name2=B_2,name3=C_3 ;

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

The NEST parameter is just a shortcut to set specifix
PREFIX and SUFFIX settings.  So, if the NEST parameter is specified,
it overrides the PREFIX and SUFFIX parameters.

If you want both PREFIX/SUFFIX and NESTing, do not specify NEST,
and "manually" specify the PREFIX and SUFFIX parameters.

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

%macro seplist
/*---------------------------------------------------------------------
Emit a list of words separated by a delimiter
---------------------------------------------------------------------*/
(ITEMS
,INDLM=%str( )
,DLM=%str(,)
,PREFIX=
,NEST=
,SUFFIX=
,TRIM=Y
);

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

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

%* Richard A. DeVenezia - 990902;
%*
%* emit a list of words separated by a delimiter
%*
%* items  - list of items, separated by indlm
%* indlm  - string that delimits each item of items
%*   dlm  - string that delimits list of items emitted
%* prefix - string to place before each item
%* nest   - Q (single quote ''),
%*          QQ (double quotes ""),
%*          P (parenthesis ()),
%*          C (curly braces {}),
%*          B (brackets [])
%* suffix - string to place after each item
%*
%* Note: nest is a convenience, and could be accomplished using
%*       prefix and suffix
%*;

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

%local macro parmerr emit nest prefix suffix item n;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(NEST,         _req=0,_words=0,_case=U,_val=Q QQ P C B)
%parmv(TRIM,         _req=0,_words=0,_case=U,_val=0 1)

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

%let emit=;

%let nest = %upcase (&nest);

%if (&nest = Q) %then %do;
   %let prefix = &prefix.%str(%');
   %let suffix = %str(%')&suffix;
%end;
%else
%if (&nest = QQ) %then %do;
   %let prefix = &prefix.%str(%");
   %let suffix = %str(%")&suffix;
%end;
%else
%if (&nest = P) %then %do;
   %let prefix = &prefix.%str(%();
   %let suffix = %str(%))&suffix;
%end;
%else
%if (&nest = C) %then %do;
   %let prefix = &prefix.%str({);
   %let suffix = %str(})&suffix;
%end;
%else
%if (&nest = B) %then %do;
   %let prefix = &prefix.%str([);
   %let suffix = %str(])&suffix;
%end;

%let n = 1;
%let item = %qscan (&items, &n, %quote(&indlm));

%do %while (%superq(item) ne );
   %if (&trim) %then %let item=%qleft(%qtrim(&item));

   %if (&n = 1) %then %do;
      %if (&nest eq ) %then
         %let emit = %unquote(&prefix.)&item.%unquote(&suffix);
      %else
         %let emit = &prefix.&item.&suffix;
   %end;
   %else %do;
      %if (&nest eq ) %then
         %let emit = &emit.&dlm.%unquote(&prefix.)&item.%unquote(&suffix);
      %else
         %let emit = &emit.&dlm.&prefix.&item.&suffix;
   %end;

   %let n = %eval (&n+1);
   %let item = %qscan (&items, &n, %quote(&indlm));
%end;

%unquote(&emit)

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

%mend;

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


text:
/*=====================================================================
Program Name            : transpose.sas
Purpose                 : Transpose the input dataset.
SAS Version             : SAS 9.1.3
Input Data              : Input  SAS dataset or view
Output Data             : Output SAS dataset

Macros Called           : parmv, loop, seplist, kill

Originally Written by   : Scott Bass
Date                    : 24MAY2010
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:

Note:  only a few common usage examples are listed here,
see the macro code for all available parameters.

%transpose(
   data=in,
   out=out,
   by=studyid usubjid visit,
   var=diabp height pulse sysbp temp weight,
   name=name,
   label=,
   col=measures
);

Transpose dataset in,
   creating dataset out,
   by studyid usubjid visit,
   for variables diabp height pulse sysbp temp weight,
   rename _name_ variable to name,
   drop _label_ variable,
   rename col1 to measures

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

%transpose(
   data=in,
   out=out,
   by=studyid usubjid visit,
   var=diabp height pulse sysbp temp weight,
   notsorted=Y
);

Transpose dataset in,
   creating dataset out,
   by studyid usubjid visit,
   for variables diabp height pulse sysbp temp weight,
   add the notsorted option to the by statement
   and do not sort the input dataset

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

%transpose(
   by=studyid usubjid visit,
   var=diabp height pulse sysbp temp weight,
);

Transpose the last dataset created (&syslast),
   overwriting it,
   by studyid usubjid visit,
   for variables diabp height pulse sysbp temp weight

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

By default, the input dataset is sorted by PROC SORT.  However, if this
dataset is already sorted, PROC SORT will just copy the input dataset
to the output dataset.

If the NOTSORTED parameter is specified, the input dataset is not
sorted, and the notsorted option is added to the BY statement.

If the NOSORT parameter is specified, the input dataset is not sorted.

It is unlikely that the BY variable(s) would be blank, but
PROC TRANSPOSE does allow it, see the documentation for details.
If blank, all observations in the source dataset are transposed.

It is unlikely that the VAR variable(s) would be blank, but
PROC TRANSPOSE does allow it, see the documentation for details.
If blank, all numeric variables in the source dataset are transposed.

If the NAME and/or LABEL parameters are blank, the _NAME_ and/or
_LABEL_ variables are dropped from the output dataset.  Otherwise
the PROC TRANSPOSE NAME and/or LABEL options are used.

If the COL parameter is specified, the output columns COL1 - COLn are
renamed according to the list of names specified in the COL parameter.
If there are more COL# columns in the output dataset than items
specified in the COL parameter, the additional columns are not renamed.
If there are more COL# items specified in the COL parameter than
COL# columns in the output dataset, a Warning message is generated.

Dataset options can be specified on the input or output datasets, but
best practice is to use the macro parameters where possible
(eg. name/label/col/where parameters instead of
drop/keep/rename/where dataset options).

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

%macro transpose
/*---------------------------------------------------------------------
Transpose the input dataset
---------------------------------------------------------------------*/
(
/* PROC TRANSPOSE Options */

 DATA=&syslast /* Input dataset (REQ).                               */
               /* Default is &syslast (last dataset created).        */
,OUT=&syslast  /* Output dataset (REQ).                              */
               /* Default is &syslast (last dataset created).        */
,BY=           /* By variables (Opt).                                */
               /* By variables for PROC TRANSPOSE.                   */
               /* If not specified then every observation in the     */
               /* input dataset is transposed.                       */
,VAR=          /* Var variables (Opt).                               */
               /* Var variables for PROC TRANSPOSE.                  */
               /* If not specified then every numeric variable in    */
               /* the input dataset is transposed.                   */
,PREFIX=       /* Transposed column prefix (Opt).                    */
               /* If blank, the PROC TRANSPOSE default COL is used.  */
,SORT=Y        /* Sort the input dataset? (Opt).                     */
               /* If 1, the input dataset is sorted before being     */
               /* transposed by PROC TRANSPOSE.                      */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
,NOTSORTED=N   /* Add the NOTSORTED option to the BY statement? (Opt)*/
               /* If Y, the SORT= parameter is set to N.             */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
,ID=           /* Variable whose formatted value names the output    */
               /* columns (Opt).  Only one of COL= or ID= parameters */
               /* can be specified (not both).                       */
,IDLABEL=      /* Variable whose formatted value labels the output   */
               /* columns (Opt).  Can only be used if the ID=        */
               /* parameter is also specified.                       */
,LET=          /* Allow duplicate values for an ID variable? (Opt).  */
               /* If specified, only the last occurence of the ID    */
               /* variable in the dataset or BY group is transposed. */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
,COPY=         /* Copy variables directly from the input to output   */
               /* dataset without transposing them.                  */

/* Input Dataset Options */

,WHERE=        /* Apply where clause to INPUT dataset (Opt).         */
               /* Do not specify the "WHERE=" portion of the clause. */
               /* Ex: var1 in (1,2,3) and var2="whatever"            */
,FORMAT=       /* Apply temporary format to input dataset variables  */
               /* (Opt).  Ex: var1 $mychrfmt. var2 mynumfmt.         */
,LBL=          /* Apply temporary labels to input dataset variables  */
               /* (Opt).  Ex: var1="Label 1" var2="Label 2"          */

/* Output Dataset options */

,COL=          /* Rename PROC TRANSPOSE COL# variable(s)? (Opt).     */
               /* Default value is blank (no rename).  Set to list   */
               /* of valid SAS variable name(s) to rename COL1 - COL#*/
               /* to new values.                                     */
,NAME=_NAME_   /* Rename PROC TRANSPOSE _NAME_ variable? (Opt).      */
               /* Default value is _NAME_ (keep variable w/o rename).*/
               /* Set to blank value to drop _NAME_ variable, set to */
               /* valid SAS variable name to rename to new value.    */
,LABEL=_LABEL_ /* Rename PROC TRANSPOSE _LABEL_ variable? (Opt).     */
               /* Default value is _LABEL_ (keep variable w/o rename)*/
               /* Set to blank value to drop _LABEL_ variable, set to*/
               /* valid SAS variable name to rename to new value.    */
);

%local macro parmerr drop rename options;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(DATA,         _req=1,_words=1,_case=N)  /* words=1 allows ds options */
%parmv(OUT,          _req=1,_words=1,_case=N)  /* words=1 allows ds options */
%parmv(BY,           _req=0,_words=1,_case=U)
%parmv(VAR,          _req=0,_words=1,_case=U)
%parmv(PREFIX,       _req=0,_words=0,_case=N)
%parmv(SORT,         _req=0,_words=0,_case=U,_val=0 1)
%parmv(NOTSORTED,    _req=0,_words=0,_case=U,_val=0 1)
%parmv(ID,           _req=0,_words=1,_case=U)
%parmv(IDLABEL,      _req=0,_words=1,_case=U)
%parmv(LET,          _req=0,_words=0,_case=U,_val=0 1)
%parmv(COPY,         _req=0,_words=1,_case=U)

%parmv(WHERE,        _req=0,_words=1,_case=N)
%parmv(FORMAT,       _req=0,_words=1,_case=U)
%parmv(LBL,          _req=0,_words=1,_case=N)

%parmv(NAME,         _req=0,_words=0,_case=U)
%parmv(LABEL,        _req=0,_words=0,_case=U)
%parmv(COL,          _req=0,_words=1,_case=U)

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

%* additional error checking ;
%* only one of COL or ID can be specified ;
%if (%superq(col) ne ) and (%superq(id) ne ) %then
   %parmv(_msg=Only one of COL or ID parameters can be specified - you cannot specify both);

%* IDLABEL requires that ID is also specified ;
%if (%superq(idlabel) ne ) %then
   %if (%superq(id) eq ) %then
      %parmv(_msg=The IDLABEL parameter requires that the ID parameter is also specified);

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

%* ensure SORT, NOTSORTED, and LET are boolean values ;
%let sort      = %eval(&sort eq 1);
%let notsorted = %eval(&notsorted eq 1);
%let let       = %eval(&let eq 1);

%* create macro to rename COL# columns ;
%macro _trans_rename;
   %local temp;
   %let temp=&prefix;
   %if (&temp eq ) %then %let temp=COL;
   &temp&__iter__=&word
%mend;

%* sort the input dataset ;
%* if NOTSORTED was specified do not sort ;

%* I used PROC SORT instead of an SQL view because, ;
%* if the input dataset is already sorted, it will ;
%* just be copied to the temporary dataset ;
%if (&notsorted) %then %let sort=0;
%if ((&sort) and (%superq(by) ne )) %then %do;
   proc sort data=&data out=_transpose_;
      by &by;
   run;
   %let data=_transpose_;  %* change value of &data parameter ;
%end;

%* Were output dataset options specified? ;
%let out_opts=0;
%let start=%sysfunc(findc(%superq(out),%str(%(),1));
%if (&start) %then %do;
   %let out_opts=1;
   %let options=%substr(%superq(out),&start+1);
   %let end=%sysfunc(findc(%superq(options),%str(%)),-9999));
   %let options=%substr(%superq(options),1,&end-1);  %* assume proper trailing paren ;
   %let out=%substr(%superq(out),1,&start-1);
%end;

%* Or, were output dataset options specified via macro parameters? ;
%if (&name&label&col ne ) %then %do;
   %let out_opts=1;
   %let drop=;
   %let rename=;
   %if (&name  eq ) %then %let drop=&drop _NAME_;
   %if (&label eq ) %then %let drop=&drop _LABEL_;
   %if (&col   ne ) %then %let rename=%loop(&col,mname=_trans_rename);
   %let drop=&drop; %* removes leading space ;
%end;

%* transpose the dataset ;
proc transpose data=&data out=&out
   %if (&out_opts) %then %do;
      (
      %if (%superq(drop) ne ) %then %do;
         drop=&drop
      %end;
      %if (%superq(rename) ne ) %then %do;
         rename=(&rename)
      %end;
      %if (%superq(options) ne ) %then %do;
         %unquote(%superq(options))
      %end;
      )
   %end;
   %if (%superq(name) ne %str( ) and %superq(name) ne _NAME_) %then %do;
      name=&name
   %end;
   %if (%superq(label) ne %str( ) and %superq(label) ne _LABEL_) %then %do;
      label=&label
   %end;
   %if (%superq(prefix) ne ) %then %do;
      prefix=&prefix
   %end;
   %if (&let) %then %do;
      let
   %end;
   ;
   %if (%superq(where) ne ) %then %do;
      where %unquote(&where);
   %end;
   %if (%superq(by) ne ) %then %do;
      by &by
      %if (&notsorted) %then %do;
         notsorted
      %end;
      ;
   %end;
   %if (%superq(var) ne ) %then %do;
      var &var;
   %end;
   %if (%superq(copy) ne ) %then %do;
      copy ©
   %end;
   %if (%superq(id) ne ) %then %do;
      id &id;
   %end;
   %if (%superq(idlabel) ne ) %then %do;
      idlabel &idlabel;
   %end;
   %if (%superq(format) ne ) %then %do;
      format %unquote(&format);
   %end;
   %if (%superq(lbl) ne ) %then %do;
      label %unquote(&lbl);
   %end;
run;

%* delete working dataset ;
%if (%sysfunc(exist(_transpose_))) %then %do;
   %kill(delete=_transpose_)
%end;

%quit:

%mend;

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


text:
/*=====================================================================
Program Name            : export.sas
Purpose                 : Replacement macro for PROC EXPORT
SAS Version             : SAS 9.4
Input Data              : N/A
Output Data             : N/A

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 14JAN2016
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

Note: This macro is superceded by %export_dlm.

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

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

%export(
   data=sashelp.class,
   file="C:\Temp\file.csv"
);

Creates C:\Temp\file.csv as a comma separated values file
   (default output file format).
Aborts if C:\Temp\file.csv already exists.

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

%export(
   data=sashelp.class,
   file="C:\Temp\file.csv",
   dbms=csv
);

Creates C:\Temp\file.csv as a comma separated values file
   (explicitly specified output file format).
Aborts if C:\Temp\file.csv already exists.

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

%export(
   data=sashelp.class,
   file="C:\Temp\file.csv",
   dbms=csv,
   replace=Y
);

Overwrites C:\Temp\file.csv if it already exists.

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

%export(
   data=sashelp.class,
   file="C:\Temp\file.tdf",
   dbms=tab,
   replace=Y
);

Creates C:\Temp\file.tdf as a tab delimited file.

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

%export(
   data=sashelp.class,
   file="C:\Temp\file.txt",
   dbms=dlm,
   replace=Y
);

Creates C:\Temp\file.txt as a space delimited file
   (probably not what you want).

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

%export(
   data=sashelp.class,
   file="C:\Temp\file.txt",
   dbms=dlm,
   replace=Y,
   delimiter=|
);

Creates C:\Temp\file.txt as a pipe delimited file.
You can use any sequence of characters (including hexadecimal constants)
as the field delimiter.

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

%export(
   data=sashelp.class,
   file="C:\Temp\file.txt",
   dbms=dlm,
   replace=Y,
   dlm=|
);

dlm can be used as an alias for delimiter.

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

data class;
   set sashelp.class;
   label
      name="Full Name"
      sex="Gender"
      height="How Tall?"
   ;
run;

%export(
   data=class,
   file="C:\Temp\file.txt",
   dbms=dlm,
   delimiter=|,
   replace=Y,
   label=Y
);

Creates C:\Temp\file.txt as a pipe delimited file
Uses the labels from the data set for the header column name

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

%export(
   data=sashelp.class,
   file="C:\Temp\file.txt"
   dbms=dlm,
   delimiter=|,
   replace=Y,
   header=N
);

Creates C:\Temp\file.txt as a pipe delimited file.
Does not write a header row.

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

filename temp temp;

%export(
   data=sashelp.class,
   file=temp
   dbms=dlm,
   delimiter=|
);

data _null_;
   infile temp;
   input;
   putlog _infile_;
run;

Writes sashelp.class to the fileref temp.

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

%export(
   data=sashelp.class,
   file="C:\Temp\file.txt
   dbms=dlm,
   delimiter=|,
   replace=Y,
   header=Y,
   lrecl=999999
);

Increases the output logical record length from the default of 32767.
Use if your combined output linesize overflows 32767 characters.

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

One of the main reasons for this macro is that PROC EXPORT puts
unwanted double-quotes around the header field names when the LABEL
option is used.

This macro also allows specifying an output logical record length.

This code is based on (i.e. virtually copied from):
https://communities.sas.com/t5/ODS-and-Base-Reporting/How-to-use-labels-in-proc-export-or-create-tab-dlm-file-using/m-p/76789#M8698

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

%macro export
/*---------------------------------------------------------------------
Replacement macro for PROC EXPORT.
---------------------------------------------------------------------*/
(DATA=         /* Dataset to export (REQ).                           */
               /* Data set options, such as a where clause, may be   */
               /* specified.                                         */
,FILE=         /* Output file (REQ).                                 */
               /* Either a properly quoted physical file (single or) */
               /* double quotes), or an already allocated fileref    */
               /* may be specified.                                  */
,DBMS=CSV      /* Output file type (REQ).                            */
               /* CSV, TAB, or DLM may be specified.                 */
               /* Default value is CSV.                              */
               /* Ignored if DELIMITER is specified.                 */
,DELIMITER=    /* Output field delimiter (Opt).                      */
               /* If specified, the DBMS parameter is ignored.       */
               /* If not specified:                                  */
               /*    If DBMS=CSV, DELIMITER=,                        */
               /*    If DBMS=TAB, DELIMITER="09"x (tab)              */
               /*    If DBMS=DLM, DELIMITER=" " (space)              */
,DLM=          /* Output field delimiter (Opt).                      */
               /* DLM is an alias for DELIMITER.                     */
               /* If both are specified then DELIMITER is used.      */
,REPLACE=N     /* Replace output file? (Opt).                        */
               /* Default value is NO.  Valid values are:            */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
,LABEL=N       /* Use data set labels instead of names? (Opt).       */
               /* Default value is NO.  Valid values are:            */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
               /* If the data set variable does not have a label     */
               /* then the variable name is used.                    */
,HEADER=Y      /* Output a header row? (Opt).                        */
               /* Default value is YES.  Valid values are:           */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE           */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
               /* If the data set variable does not have a label     */
               /* then the variable name is used.                    */
,LRECL=32767   /* Logical record length for output file (Opt).       */
               /* Default value is 32767 (32K)                       */
);

%local macro parmerr _dlm _type _exist;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(DATA,         _req=1,_words=1,_case=N)  /* words=1 allows ds options */
%parmv(FILE,         _req=1,_words=1,_case=N)
%parmv(DBMS,         _req=1,_words=0,_case=U,_val=CSV TAB DLM)
%parmv(DELIMITER,    _req=0,_words=0,_case=N)
%parmv(DLM,          _req=0,_words=0,_case=N)
%parmv(REPLACE,      _req=0,_words=0,_case=U,_val=0 1)
%parmv(LABEL,        _req=0,_words=0,_case=U,_val=0 1)
%parmv(HEADER,       _req=0,_words=0,_case=U,_val=0 1)
%parmv(LRECL,        _req=0,_words=0,_case=U,_val=POSITIVE)

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

%* additional error checking ;
%* if DELIMITER or DLM was specified then explicitly set the delimiter ;
%* and ignore the DBMS setting ;
%if (%superq(DELIMITER)%superq(DLM) ne ) %then %do;
   %let _dlm = %superq(DELIMITER);
   %if (%superq(_dlm) eq ) %then %let _dlm = %superq(DLM);
   %let _dlm = %sysfunc(compress(&_dlm,%str(%"%')));
   %let _dlm = "&_dlm";
%end;
%else %do;
   %if (&DBMS eq CSV) %then %let _dlm = %str(",");
   %else
   %if (&DBMS eq TAB) %then %let _dlm = %str("09"x);
   %else
   %if (&DBMS eq DLM) %then %let _dlm = %str(" ");
%end;

%* does the output file exist? ;
%let _exist=0;

%* quoted physical file ;
%if (%sysfunc(indexc(%superq(file),%str(%"%')))) %then %do;
   %let _type=FILE;
   %let _exist=%sysfunc(fileexist(%superq(file)));
%end;
%* fileref ;
%else %do;
   %let _type=FILEREF;
   %* is the fileref allocated? ;
   %if (%sysfunc(fileref(%superq(file))) gt 0) %then %do;
      %let msg=%str(NO)TE: The &file fileref is not allocated. No output created.;
      %put &msg;
      %goto quit;
   %end;
   %let _exist=%sysfunc(fexist(%superq(file)));
%end;

%* file exists but replace was not specified ;
%if (&_exist and not &replace) %then %do;
   %if (&_type eq FILE) %then %do;
      %let file=%sysfunc(compress(%superq(file),%str(%"%')));
      %let msg=%str(NO)TE: &file 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(NO)TE: %sysfunc(pathname(%superq(file))) already exists.;
      %let msg=&msg Specify REPLACE to overwrite.;
      %let msg=&msg No output created.;
   %end;
   %put &msg;
   %goto quit;
%end;

data _null_;
   file &file dsd dlm=&_dlm lrecl=&lrecl;
   set &data;
   %if (&header) %then %do;
   if _n_ eq 1 then link header;
   %end;
   put (_all_)(:);
return;

%if (&header) %then %do;
header:
   length _LABEL_ $128 _NAME_ $32;
   if _n_ eq 1 then do;
      do while(1);
      call vnext(_name_);
      if _name_ eq '_LABEL_' then leave;
      _label_ = ifc(&label,vlabelx(_name_),_name_);
      put _label_ @;
   end;
   put;
   end;
return;
%end;

run;
%quit:

%mend;

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