text:
/*====================================================================
Program Name            : guess_pk
Purpose                 : Guess the primary key of a SAS dataset or
                          RDBMS table.
SAS Version             : SAS 9.3
Input Data              : SAS dataset or RDBMS table
Output Data             : SAS output dataset

Macros Called           : parmv, seplist, varlist, dedup_mstring,
                          loop, loop_control, nobs, get_permutations

This macro is based on original ideas provided by Allan Bowe.

Originally Written by   : Scott Bass
Date                    : 18MAY2020
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:

%guess_pk(data=sashelp.class)

Guess the primary key for the sashelp.class dataset,
creating the _guess_pk_ output dataset (default value),
processing up to MAX_LEVEL=4 permutations (default value)
of the columns until a "hit" is found.

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

%guess_pk(data=sashelp.zipcode)

Guess the primary key for the sashelp.zipcode dataset,
creating the _guess_pk_ output dataset (default value),
processing up to MAX_LEVEL=4 permutations (default value)
of the columns until a "hit" is found.

Note that ZIP_CLASS, ALIAS_CITY, and ALIAS_CITYN are dropped as
potential primary key columns since they contain missing values.

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

%guess_pk(data=sashelp.cars,print=Y)

Guess the primary key for the sashelp.cars dataset,
creating the _guess_pk_ output dataset (default value),
processing up to MAX_LEVEL=4 permutations (default value)
of the columns until a "hit" is found.

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

%guess_pk(
   data=sashelp.cars,
   out=pk (
      where=(
         prxmatch('/\bmake\b/i',strip(key))
     and prxmatch('#\bmodel\b#i',strip(key))
      )
   ),
   level=3,
   print=Y
)
%guess_pk(
   data=sashelp.cars,
   out=pk (
      where=(
         findw(key,'make','','i')
     and findw(key,'model','','i')
      )
   ),
   min_level=3,
   max_level=4,
   print=Y
)

Let's say we disagree with the previous results,
believe that the primary key is probably either 3 or 4 columns,
believe that the primary key would likely contain Make and Model,
but we aren't sure what the other columns might be.

Guess the primary key for the sashelp.cars dataset,
creating the pk output dataset,
filtering the output dataset on key contains Make and Model (case-insensitive),
and processing depth level=3 and 3 to 4 of the column permutations.

From these results, we chose Make Model Drivetrain as a suitable
primary key.

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

%guess_pk(
   data=sashelp.cars,
   varlist=
        make model type
      | make model origin
      ~ make model drivetrain
      ^ make model type origin
      | make model type drivetrain
   ,print=Y
)

Following on from the above, we review the data in sashelp.cars,
and believe that the primary key could be one of the combinations
listed in the VARLIST parameter.

Guess the primary key for the sashelp.class dataset,
creating the _guess_pk_ output dataset (default value),
processing only the combination of columns listed in the
VARLIST parameter.

From these results, we chose Make Model Drivetrain as a suitable
primary key.

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

%guess_pk(data=sashelp.class,min_level=2,max_level=4,print=Y)

Guess the primary key for the sashelp.class dataset,
creating the _guess_pk_ output dataset (default value),
processing only depth level=2 to 4 permutations
of the columns until a "hit" is found.

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

%guess_pk(data=sashelp.class,min_level=2,max_level=4,stop=N,print=Y)

Guess the primary key for the sashelp.class dataset,
creating the _guess_pk_ output dataset (default value),
processing only depth level=2 to 4 permutations,
but process all depth levels, do not stop when a "hit" is found.

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

%guess_pk(data=sashelp.class,level=0,print=Y)

Guess the primary key for the sashelp.class dataset,
creating the _guess_pk_ output dataset (default value),
processing every possible permutation of columns (LEVEL=0)

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

data shoes;
   * correct the PDV order ;
   format Region Subsidiary Product Stores;
   set sashelp.shoes;
   * remove "duplicate" row ;
   if region='Western Europe'
  and subsidiary='Copenhagen'
  and product='Sport Shoe'
  and stores=1 then
      delete;
run;

%guess_pk(data=shoes,max_level=5,print=Y)
%guess_pk(data=shoes,max_level=5,drop=sales inventory returns,print=Y)
%guess_pk(data=shoes,level=0,keep=region product subsidiary stores,print=Y)

Guess the primary key for the (corrected) sashelp.shoes dataset,
creating the _guess_pk_ output dataset (default value),
processing permutations from depth level 1 to 5,
until a "hit" is found.

The first invocation returns Inventory,
since that fact (analysis) data is unique.
This is likely not what you want, since in a production environment
it could become non-unique at any time.

The second invocation returns Product Subsidiary.
While this does uniquely identifiy each row,
I would choose Region Subsidiary Product as the primary key.

The third invocation returns all possible primary keys
within the candidate columns, and confirms that
Region Subsidiary Product qualifies as a primary key.

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

%guess_pk(data=sashelp.class,keep=name age sex,print=Y)
%guess_pk(data=sashelp.class,drop=height weight,print=Y)
%guess_pk(data=sashelp.class,drop=height weight,keep=name age,print=Y)
%guess_pk(data=sashelp.class,drop=_numeric_,keep=name--sex,print=Y)

Guess the primary key for the sashelp.class dataset,
creating the _guess_pk_ output dataset (default value),
processing up to MAX_LEVEL=4 permutations (default value)
of the columns until a "hit" is found,
dropping or keeping columns from the source dataset as specified
by the DROP and KEEP parameters.

The normal SAS rules for keeping or dropping variables applies.
Variable lists can also be used.

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

%guess_pk(
   data=sashelp.shoes
  ,varlist=
      region subsidiary product
    | region subsidiary product stores
  ,print=Y
)

Note the "data bug" in sashelp.shoes.
Drilldown on region=Western Europe, subsidiary=Copenhagen, product=Sport Shoe

%guess_pk(
   data=sashelp.zipcode
  ,varlist=
      city state ^
      zip zip_class ^
      x y
  ,print=Y
)

Note that zip_class is dropped from the candidate column list
since it contains missing data.

%guess_pk(
   data=sashelp.cars
  ,varlist=
      make model type drivetrain | make | make model | make model type
  ,print=Y
)

Guess the primary key for the specified datasets,
creating the _guess_pk_ output dataset (default value),
considering only the variable list(s) provided by the calling code.

If the end user has a good idea of the potential combination of
columns that could form the primary key, providing this list can
significantly improve performance rather than using the brute force
method of processing all possible permutations.

However, this can result in false positives if one of the columns is
a primary key, since all additional combinations containing this column
would also be flagged as a primary key as well.

The VARLIST parameter does not support variable lists
(eg. name--sex, or var1-var20, or prefix:).
Each variable must be explicitly specified, and in the desired order
of the primary key.

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

FREQ Method:
Guess the primary key using PROC FREQ instead of the hash object.
These are most of the above use cases, except using the FREQ method.

The default method is to use the hash object to determine the
primary key.  Specifiy METHOD=freq if memory constraints prevent
the use of the hash object.

Typically you would not specify method=freq.  You would only specify
method=freq if your first attempt using the hash object aborted
due to out-of-memory error.

%guess_pk(method=freq,data=sashelp.class)
%guess_pk(method=freq,data=sashelp.zipcode)
%guess_pk(method=freq,data=sashelp.cars,print=Y)
%guess_pk(method=hash,data=sashelp.cars,print=Y)

%guess_pk(
   method=freq,
   data=sashelp.cars,
   out=pk (
      where=(
         prxmatch('/\bmake\b/i',strip(key))
     and prxmatch('#\bmodel\b#i',strip(key))
      )
   ),
   level=3,
   print=Y
)
%guess_pk(
   method=freq,
   data=sashelp.cars,
   out=pk (
      where=(
         findw(key,'make','','i')
     and findw(key,'model','','i')
      )
   ),
   min_level=3,
   max_level=4,
   print=Y
)
%guess_pk(
   method=freq,
   data=sashelp.cars,
   varlist=
        make model type
      | make model origin
      ~ make model type origin
      ^ make model type drivetrain
   ,print=Y
)

%guess_pk(method=freq,data=sashelp.class,min_level=2,max_level=4,print=Y)
%guess_pk(method=freq,data=sashelp.class,min_level=2,max_level=4,stop=N,print=Y)
%guess_pk(method=freq,data=sashelp.class,level=0,print=Y)

data shoes;
   * correct the PDV order ;
   format Region Subsidiary Product Stores;
   set sashelp.shoes;
   * remove "duplicate" row ;
   if region='Western Europe'
  and subsidiary='Copenhagen'
  and product='Sport Shoe'
  and stores=1 then
      delete;
run;

%guess_pk(method=freq,data=shoes,max_level=5,print=Y)
%guess_pk(method=freq,data=shoes,max_level=5,drop=sales inventory returns,print=Y)
%guess_pk(method=freq,data=shoes,level=0,keep=region product subsidiary stores,print=Y)

%guess_pk(method=freq,data=sashelp.class,keep=name age sex,print=Y)
%guess_pk(method=freq,data=sashelp.class,drop=height weight,print=Y)
%guess_pk(method=freq,data=sashelp.class,drop=height weight,keep=name age,print=Y)
%guess_pk(method=freq,data=sashelp.class,drop=_numeric_,keep=name--sex,print=Y)

%guess_pk(
   method=freq,
   data=sashelp.shoes
  ,varlist=
      region subsidiary product
    | region subsidiary product stores
  ,print=Y
)

%guess_pk(
  method=freq,
   data=sashelp.zipcode
  ,varlist=
      city state ^
      zip zip_class ^
      x y
  ,print=Y
)

%guess_pk(
   method=freq,
   data=sashelp.cars
  ,varlist=
      make model type drivetrain | make | make model | make model type
  ,print=Y
)

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

proc sql noprint;
   create table _columns_ as
   select name
   from dictionary.columns
   where libname='SASHELP' and memname='CARS'
   order by varnum;
quit;
%get_permutations(data=_columns_,var=name,level=0);

This is not a use case for %guess_pk per se, but rather an example
of the number of column permutations that can be generated from a
relatively small dataset.  The number of permutations increases
exponentially depending on the number of columns.
This can result in a SIGNIFCANT number of permutations.

Keep this in mind if you intend to use MAX_LEVEL=0 in %guess_pk.

I recommend you use VARLIST, KEEP, and/or DROP to limit the number
of column permutations if you have a very "wide" dataset.

But sure, if you have absolutely no idea which column or combination
of columns might possibly be the primary key, then brute force may be
the only option.  Depending on your data volume (number of rows)
this approach could take a very long time to return results.

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

Error Testing:

%guess_pk()

An input dataset or view (including RDBMS tables) must be specified.

%guess_pk(data=doesnotexist)

The input dataset or view must exist.

%guess_pk(data=sashelp.class,varlist=foo)
%guess_pk(data=sashelp.class,keep=foo)
%guess_pk(data=sashelp.class,drop=foo)

If VARLIST | KEEP | DROP is specified,
every variable specified must exist in the input dataset.

%guess_pk(data=sashelp.class,varlist=name,max_level=)

MAX_LEVEL is a required parameter.

%guess_pk(data=sashelp.class,varlist=name,max_level=-1)

MAX_LEVEL must be a NONNEGATIVE number.

%guess_pk(data=sashelp.class,varlist=name,out=)

OUT is a required parameter and must be a valid output dataset name.

%guess_pk(data=sashelp.class,varlist=name,method=foo)

Valid values for the METHOD parameter are HASH | FREQ (case-insensitive).

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

This macro creates working datasets with a naming convention
using leading and trailing underscores, i.e. _<dataset name>_.
If your session also uses this naming convention for your datasets
there is a slight chance of dataset name collision.

The default output dataset name is _<name of macro>_, i.e.
_guess_pk_.

I will use the term "dataset" to mean dataset, view, RDBMS table,
or RDBMS view.

For this macro, "primary key" is defined as the column or
combination of columns that uniquely identify a row in the dataset.
Therefore, when de-duping rows based on the primary key,
the count of each primary key will be 1, and the total row count
of the de-duped primary keys will equal the row count of the
source dataset.

The primary key is also defined as the minimum number of columns that
uniquely identified a row in the dataset.
For example, if "foo bar" and "foo bar blah baz" both uniquely identify a row,
then only "foo bar" will be returned, unless max_level=0 is specified.

However, once a "hit" is made, the remaining permutations at that
"level" will also be processed, in case of "ties".  For example, if
"foo bar", "bar blah", and "blah baz" all uniquely identify a row,
then they will all be returned for further investigation by the end
user in "breaking the tie" and deciding on the primary key.
But, "foo bar blah" or "foo bar blah baz" would not be returned,
since they are at a deeper level of column permutations.

Only columns that are NOT NULL will be considered for primary keys.
In other words, if the column contains any missing (NULL) values,
that column is removed from the list of columns to be considered as
contributing to the primary key.

If the VARLIST parameter is specified, the KEEP and/or DROP parameters
are ignored, and no column permutations are generated.
The VARLIST(s) are the column permutations that will be processed.

Multiple VARLISTs can be spefified, using either the pipe (|),
tilde (~), or caret (^) characters as delimiters between the individual
VARLISTs.  See examples above.

If both KEEP and DROP are specified, the DROP list will be applied
first, then the KEEP list will be applied to those results.
In practice, this usually results in the KEEP list taking precedence,
except when the DROP and KEEP lists overlap, such as
DROP=_numeric_, KEEP=name age sex.  In this case, only
name sex would be used.

This macro does not cater for space separated variables, i.e.
N'This variable contains spaces'.  If you have such a dataset or table,
use a view to map space separated variables into SAS-compliant variable
names.

This macro uses the hash object to test for the uniqueness of the key.
The default behaviour of a single valued hash object is to reject the
insertion of duplicate key values.  The return code from the attempted
insertion can be used to abort the processing of a key as soon as a
duplicate has been encountered.  This results in significant performance
gains, especially for large data volumes.

However, if you have very large data volumes, it is possible that the hash
object approach could result in out-of-memory error.  If that happens,
specify METHOD=freq.  This will use PROC FREQ instead of the hash
object to determine the primary key, at the expense of performance.

This macro uses the MD5 hash function to create the key from the
concatenation of the subject column(s).  This is done to create a
smaller memory footprint for the hash object or proc freq processing.

If METHOD=hash, reviewing the row counts read for each key will tell
you where the first duplicate occurred.  You can then review the
data around that row number to give you an idea of additional
columns to add to the proposed key.
Review the log output for the VARLIST examples above for more details.

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

%macro guess_pk
/*--------------------------------------------------------------------
Guess the primary key of a SAS dataset or RDBMS table.
--------------------------------------------------------------------*/
(DATA=         /* Input dataset (REQ).                              */
,OUT=work._guess_pk_
               /* Output dataset (REQ).                             */
               /* The default value is work._guess_pk_.             */
,VARLIST=      /* Space separated variable list (Opt).              */
               /* If specified, this variable list will be used     */
               /* instead of derived column permutations.           */
               /* Multiple variable lists can be specified,         */
               /* separated by |, ~, or ^.                          */
               /* All variables must exist in the DATA dataset.     */
,KEEP=         /* Space separated variable list (Opt).              */
               /* If specified, only these variables will be        */
               /* considered in deriving the column permutations.   */
               /* All variables must exist in the DATA dataset.     */
,DROP=         /* Space separated variable list (Opt).              */
               /* If specified, these variables will be dropped     */
               /* from consideration in deriving the columns        */
               /* permutations.                                     */
               /* All variables must exist in the DATA dataset.     */
,LEVEL=1       /* Desired depth level of permutations to generate   */
               /* (REQ).  Default value is 1.                       */
               /* If LEVEL=0, then all column permutations will     */
               /* be generated.  This can result in a significant   */
               /* number of permutations!                           */
,MIN_LEVEL=    /* Starting depth level of permutations to generate  */
               /* (Opt).                                            */
,MAX_LEVEL=4   /* Maximum depth level of permutations to generate   */
               /* (Opt).  Default value is 4.                       */
,STOP=Y        /* Stop processing the next level when a "hit" is    */
               /* found? (REQ).                                     */
               /* Default value is YES.  Valid values are:          */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE          */
               /* OFF N NO F FALSE and ON Y YES T TRUE              */
               /* (case insensitive) are acceptable aliases for     */
               /* 0 and 1 respectively.                             */
,METHOD=HASH   /* Method used to derive the primary key (REQ).      */
               /* Default value is HASH.                            */
               /* Valid values are HASH or FREQ.                    */
,PRINT=N       /* Print the OUT dataset to the Results window? (REQ)*/
               /* Default value is NO.  Valid values are:           */
               /* 0 1 OFF N NO F FALSE and ON Y YES T TRUE          */
               /* OFF N NO F FALSE and ON Y YES T TRUE              */
               /* (case insensitive) are acceptable aliases for     */
               /* 0 and 1 respectively.                             */
);

%local macro parmerr;
%local line columns i column dsid anobs nobs word num_items;
%local lev lev_count;
%global _found_;  %* this needs to be global ;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(DATA,         _req=1,_words=0,_case=N)
%parmv(OUT,          _req=1,_words=1,_case=N)  /* words=1 allows ds options */
%parmv(VARLIST,      _req=0,_words=1,_case=N)
%parmv(KEEP,         _req=0,_words=1,_case=N)
%parmv(DROP,         _req=0,_words=1,_case=N)
%parmv(LEVEL,        _req=1,_words=0,_case=N,_val=NONNEGATIVE)
%parmv(MIN_LEVEL,    _req=0,_words=0,_case=N,_val=POSITIVE)
%parmv(MAX_LEVEL,    _req=0,_words=0,_case=N,_val=POSITIVE)
%parmv(STOP,         _req=1,_words=0,_case=U,_val=0 1)
%parmv(METHOD,       _req=1,_words=0,_case=U,_val=HASH FREQ)
%parmv(PRINT,        _req=1,_words=0,_case=U,_val=0 1)

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

%let line=%sysfunc(repeat(=,71));

%* Additional error checking ;

%* If MIN_LEVEL or MAX_LEVEL are not specified set them to LEVEL ;
%if (&min_level eq ) %then %let min_level=&level;
%if (&max_level eq ) %then %let max_level=&level;

%* MIN_LEVEL must be less than or equal to MAX_LEVEL ;
%if (&min_level gt &max_level) %then %do;
   %parmv(_msg=MIN_LEVEL (&min_level) must be less than or equal to MAX_LEVEL (&max_level))
   %goto quit;
%end;

%* Does the dataset exist? ;
%if (%superq(data) ne ) %then %do;
   %if ^(%sysfunc(exist(&data,data)) or
         %sysfunc(exist(&data,view))) %then %do;
      %parmv(_msg=&data does not exist)
      %goto quit;
   %end;
%end;

%* The default column list is all the columns in the dataset ;
%let columns=%varlist(&data);

%* If VARLIST was specified, ignore KEEP or DROP. ;
%* Otherwise, if KEEP and/or DROP were specified, ;
%* build the variable list ;
%if (%superq(varlist) ne ) %then %do;
   %let drop=;
   %let keep=;

   %do i=1 %to %sysfunc(countw(&varlist,%str( |~^)));
      %let column=%scan(&varlist,&i,%str( |~^));
      %if (%sysfunc(findw(&columns,&column,' ',I)) eq 0) %then %do;
         %parmv(_msg=&column does not exist in &data)
         %goto quit;
      %end;
   %end;

   %* The column list is the de-duped list of columns in VARLIST ;
   %let columns=%sysfunc(translate(&varlist,%str(   ),%str(|~^)));
   %let columns=%dedup_mstring(&columns);
%end;
%if (%superq(varlist)%superq(drop)%superq(keep) ne ) %then %do;
   %* I could have used an "all macro" approach using %sysfunc, ;
   %* but I wanted to support variable lists, and submitting code ;
   %* is the most straightforward approach ;
   proc sql noprint;
      create view work._cols_ as
      select *
      from &data
      (
      %if (%superq(varlist) ne ) %then keep=&columns;
      %if (%superq(drop) ne ) %then drop=&drop;
      %if (%superq(keep) ne ) %then keep=&keep;
      )
      where 0=1;

      %* reset columns macro variable ;
      %let columns=;

      select name into :columns separated by ' '
      from dictionary.columns
      where libname='WORK' and memname='_COLS_'
      order by varnum;

      drop view work._cols_;
   quit;

   %if (%superq(columns) eq ) %then %do;
      %parmv(_msg=Unable to derive column list.  Ensure the DROP and/or KEEP variable list contains valid variables)
      %goto quit;
   %end;
%end;

%* End of additional error checking ;

%* get number of observations in the source dataset ;
%let nobs=-1;
%let dsid=%sysfunc(open(&data));
%if (&dsid gt 0) %then %do;
   %let anobs = %sysfunc(attrn(&dsid,anobs));
   %let nobs  = %eval(%sysfunc(attrn(&dsid,nobs)) - %sysfunc(attrn(&dsid,ndel)));
   %let dsid  = %sysfunc(close(&dsid));
%end;
%else %do;
   %parmv(_msg=Unable to open &data for input);
   %goto quit;
%end;

%* if the engine cannot determine the number of observations then use PROC SQL ;
%if ^(&anobs) %then %do;
   %* Get number of observations ;
   proc sql noprint;
      select count(0) format=32. into :nobs trimmed from &data;
   quit;
%end;

%if (&nobs le 0) %then %do;
   %parmv(_msg=Unable to determine the number of rows in &data);
   %goto quit;
%end;

%* drop any columns containing missing values ;
%macro missing;
%if (&__iter__ gt 1) %then ,;
/* max(missing(&word)) as &word */ /* do not use this, it performs poorly against RDBMS tables */
max(case when &word is null then 1 else 0 end) as &word
%mend;

* eliminate columns containing missing data ;
proc sql noprint;
   create table work._missing_ (compress=no) as
   select
   %loop(&columns,mname=missing)
   from &data;
quit;

proc transpose
   data=work._missing_
   out=work._columns_ (
      compress=no
      keep=_name_ col1
      rename=(_name_=name col1=missing)
      where=(missing=0)
   );
run;

%let num_items=%nobs(work._columns_);

%* Now that we have NUM_ITEMS, process for LEVEL=0 ;
%if (&level eq 0) %then %do;
   %let min_level=1;
   %let max_level=&num_items;
%end;

%* If MAX_LEVEL is greater than the NUM_ITEMS set it to NUM_ITEMS ;
%if (&max_level gt &num_items) %then %do;
   %put &line;
   %put %str(NO)TE: Specified MAX_LEVEL (&max_level) is greater than the number of items (&num_items).;
   %put %str(NO)TE: Resetting MAX_LEVEL from &level to &num_items..;
   %put &line;
   %let max_level=&num_items;
%end;

%* If MIN_LEVEL is greater than MAX_LEVEL set it to MAX_LEVEL ;
%if (&min_level gt &max_level) %then %do;
   %let min_level=&max_level;
%end;

%*** CREATE UTILITY MACROS *** ;

%macro varlist_keys;
* no need to derive permutations, the varlist(s) are the permutations. ;
data work._keys_ (compress=no);
   length lname name $32 key $500 buffer $500;
   %* hash to hold the non-missing columns ;
   declare hash h();
   h.defineKey('lname');
   h.defineData('name');
   h.defineDone();
   do until (eof);
      set work._columns_ end=eof;
      lname=lowcase(name);
      h.ref();
   end;
   varlist="%sysfunc(compbl(&varlist))";
   do i=1 to countw(varlist,'|~^');
      call missing(key);
      buffer=scan(varlist,i,'|~^');
      do j=1 to countw(buffer,' ');
         lname=lowcase(scan(buffer,j,' '));
         if (h.find() eq 0) then key=catx(' ',key,name);
      end;
      output;
   end;
   keep key;
   stop;
run;
%mend;

%macro hash_datastep(out);
* use a hash object to determine if this key is unique for the entire dataset ;
data &out;
   length key $500 _key_ $16 rowcnt nobs 8;
   nobs=&nobs;
   declare hash h();
   %loop_control(control=work._keys_,mname=hash_codegen)
   stop;
   keep key nobs rowcnt;
run;
%mend;

%macro hash_codegen;
%* trim trailing blanks ;
%let key=%trim(&key);
h=_new_ hash();
h.defineKey('_key_');
h.defineDone();
eof=0;
do until (eof);
   set &data end=eof;
   key="&key";
   _key_=md5(catx('|',%seplist(&key)));
   if (h.add() ^= 0) then leave;
end;
if (eof) then do;
   rowcnt=h.num_items;
   if (rowcnt=nobs) then do;
      output;
      %* process the rest of this level then end, unless a VARLIST was specified ;
      %if (%superq(varlist) eq ) %then %do; call symputx('_found_',1,'G'); %end;
   end;
end;
h.delete();
%mend;

%macro freq_datastep(out);
%* create skeleton dataset ;
data &out;
   length key $500 rowcnt nobs 8;
   call missing(of _all_);
   stop;
run;

%loop_control(control=work._keys_,mname=freq_codegen)

proc datasets lib=work nowarn nolist;
   delete _count_ _append_;
quit;
%mend;

%macro freq_codegen;
%* trim trailing blanks ;
%let key=%trim(&key);
data work._md5_ / view=work._md5_;
   length _key_ $16;
   set &data;
   _key_=md5(catx('|',%seplist(&key)));
   keep _key_;
run;
proc freq data=work._md5_ noprint;
   tables _key_ / out=work._count_ (drop=percent where=(count gt 1));
quit;
%if (%nobs(work._count_) eq 0) %then %do;
   %* process the rest of this level then end, unless a VARLIST was specified ;
   %if (%superq(varlist) eq ) %then %do; %let _found_=1; %end;

   data work._append_ (compress=no);
      length key $500 rowcnt nobs 8;
      key="&key";
      nobs=&nobs;
      rowcnt=&nobs;  %* not technically correct since it was not derived, but is still accurate ;
   run;

   proc append base=&out data=work._append_;
   run;
%end;
%mend;

%* this macro can run both HASH and FREQ methods ;
%macro runcode(type);
   %if (%superq(varlist) ne ) %then %do;
      %put &line;
      %put >>> Processing VARLIST=%sysfunc(compbl(&varlist)) <<<;
      %put &line;
      %varlist_keys
      %&type._datastep(&out)
   %end;
   %else
   %if (&level eq 0) %then %do;
      %put &line;
      %put >>> Processing All Levels <<<;
      %put &line;
      * get all permutations of the non-missing candidate columns ;
      %get_permutations(data=work._columns_,var=name,out=work._keys_ (compress=no keep=item0 rename=(item0=key)),level=0)
      %&type._datastep(&out)
   %end;
   %else
   %do;
      %let _found_=0;
      %let lev_count=%eval(&min_level-1);
      %do lev=&min_level %to &max_level;
         %put &line;
         %put >>> Processing Level=&lev <<<;
         %put &line;
         %let lev_count=%eval(&lev_count+1);
         * get the permutations of the non-missing candidate columns at this depth level ;
         %get_permutations(data=work._columns_,var=name,out=work._keys_ (compress=no keep=item0 rename=(item0=key)),level=&lev)
         %&type._datastep(work._primary_key_%sysfunc(putn(&lev,z2.))_ (compress=no))
         %if (&_found_ and &stop) %then %let lev=&max_level;
      %end;
      * concatenate each level into the final consolidated dataset ;
      data &out;
         set
         %do lev=&min_level %to &lev_count;
            work._primary_key_%sysfunc(putn(&lev,z2.))_
         %end;
         ;
      run;

      proc datasets lib=work nowarn nolist;
         delete _primary_key_:;
      quit;
   %end;
%mend;

%if (&method eq HASH) %then %do;
   %runcode(hash)
%end;
%else
%if (&method eq FREQ) %then %do;
   %runcode(freq)
%end;

%if (&print) %then %do;
   title "Primary Keys for &data";
   proc print data=&out;
   run;
   title;
%end;

%quit:

%symdel _found_ / nowarn;

%mend;

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


text:
%macro txt2rtf
/*----------------------------------------------------------------------
Convert text file to RTF file
----------------------------------------------------------------------*/
(in=   /* Filename (in quotes), or fileref, of the input text file.  */
,out=  /* Filename (in quotes), or fileref, of the RTF output file.  */
,font=Courier New   /* font name to use in RTF document */
,fs=8               /* font size to use in RTF document */
,orient=L           /* page orientation  L=landscape, P=portrait */
,magnif=100         /* Magnification in percent when opened for viewing */
,margins=       /* page margins in inches - top, bottom, left, right */
  0.9 0.4 1.0 1.0
,hanging = 1.5 /*Length of hanging indent for TOC in inches*/
,tabalign = Y  /*Insert a tab character after the table numbers in the TOC to align the text*/
,startRule = %str(scan(lowcase(string),1) in ('table' 'listing' 'figure' 'appendix')) /*Rule that determines when a table title starts*/
,endRuleSameLine = %str(scan(lowcase(string),-1) in ('set' 'population')) /*Rule that determines when a table title ends*/
,endRuleNextLine = %str(lowcase(string)=:'__________' or lowcase(string)=:'treatment:') /*Rule that determines when a table ended the line before*/

 /* Document Information Fields */
,author=   /* Windows default %USERNAME%. Unix default id command.     */
,title=    /* Title.    */
,subject=  /* Subject.  */
,keywords= /* Keywords. */
,toc=Y     /* Create TOC? */
);

/*----------------------------------------------------------------------
$Purpose: Creates RTF files from ASCII files
$System archet: UNIX
$Assumptions:
$Inputs: One or more ASCII files as specified by IN parameter.
$Outputs: RTF file as specified in out parameter.
$Called by:
$Calls to: parmv.sas
-----------------------------------------------------------------------
$Usage notes:
For information on RTF syntax and commands check out this webpage
 http://www.logictran.com/RTF/RTF114.htm

IN can be an aggregate file. Each new file will start a new page.

Does not protect against {\} characters in AUTHOR, TITLE, SUBJECT or
KEYWORDS parameters.

----------------------------------------------------------------------*/
%local macro parmerr m1 m2 m3 m4 i;

%parmv(orient,       _req=0,_val=L LANDSCAPE P PORTRAIT,_def=L)
%parmv(magnif,       _req=0,_val=positive)
%parmv(in,           _req=1,_words=1,_case=N)
%parmv(out,          _req=1,_words=1,_case=N)
%parmv(toc,          _req=0,_words=0,_case=N,_val=0 1)

%if (&parmerr) %then %goto quit;
%*----------------------------------------------------------------------
Get a value of the AUTHOR info tag
-----------------------------------------------------------------------;
%if ^%length(&author) %then %do;
  %if &sysscp=WIN %then
    %let author = %sysget(USERNAME);
  %else
    %let author = %sysget(USER);
%end;

%*----------------------------------------------------------------------
Use only first character of orientation.
-----------------------------------------------------------------------;
%let orient=%substr(&orient.L,1,1);

%*----------------------------------------------------------------------
RTF actually store FS in half a font. (Example: For 7.5 use 15 )
-----------------------------------------------------------------------;
%let fs=%sysevalf(2*&fs);

%do i = 1 %to 4;
  %let m&i=%sysevalf(1440*%scan(&margins,&i,%str( )));
  %let m&i=%sysfunc(int(&&m&i+0.5));
%end;

%let _err = ;

data _null_;
  format TOCText LastTOCText $32767. string $500. fnm $256.;
  file &out lrecl=1000 noprint;
  if _n_ eq 1 then do;
    put  "{\rtf1\ansi"                                               /* Start RTF document */
         "{\deff0\fonttbl{\f0\fmodern &font;}}"                      /* Font Table */
         "{\stylesheet{\s0\f0\fs&fs\sbasedon222\snext0 Normal;}"     /* To get all text in the right font */
         "{\s1\f0\fs&fs\fi-%sysevalf(&hanging*1440)\li%sysevalf(&hanging*1440)\sbasedon0\snext0 TOC 1;}}"   /* To get the hanging indent in TOC */
         "{\info"                                                    /* Document Information */
%if (%length(&title)) %then
         "{\title &title}"
;
%if (%length(&subject)) %then
         "{\subject &subject}"
;
         "{\author &author}"
         "{\keywords TEXT2RTF &keywords}"
         "}"
%if &orient ne L %then                                               /* Paper Size */
        "\paperh15840\paperw12240"
;
%else
         "\paperw15840\paperh12240\lndscpsxn"
;
         "\margt&m1\margb&m2\margl&m3\margr&m4 "                     /* Margins */
         "\dntblnsbdb"                                               /* Do not balance double byte characters */
         "\viewkind4\viewscale&magnif"                               /* View magnification */
         "\pard\plain \fs&fs\s1"                                     /* Set font and size for TOC*/
%if &toc %then
         "\field\fldedit{\*\fldinst {TOC \\f t \\h\\z \\* MERGEFORMAT}}{\fldrslt Press F9 for Table of Contents}" /* Insert a table of contents */
         "\par \pard\page\s0\fs&fs"                                  /* Set font and size for rest of document */
;
    ;
  end;
  else put "\par " @;
  if eof then put "\pard }";
  infile &in lrecl=200 truncover eov=eov end=eof filename=fnm;
  input string $char200. ;
*----------------------------------------------------------------------;
* If new file does not start with FORMFEED then insert page break ;
*----------------------------------------------------------------------;
  if eov and _n_ > 1 and string ^=: '0C'x then put '\page ' @;
*----------------------------------------------------------------------;
* Delete lines that start with ESC codes ;
*----------------------------------------------------------------------;
  if string =: '1B'x then return;
*----------------------------------------------------------------------;
* Protect special characters ;
*----------------------------------------------------------------------;
  string=tranwrd(string,'\','\\');
  string=tranwrd(string,'{','\{');
  string=tranwrd(string,'}','\}');
*----------------------------------------------------------------------;
* If a new page has started then start looking for a new title;
*----------------------------------------------------------------------;
  if index(string,'0C'x)>0 or _n_=1 or eov then do;
   if TOCEntry and (symget('_err')='') then do;
      call symputx('_err',"E"||"RROR: At least one table title did not encounter an end rule before a page break."
         ||'0D'x||"First occurrence in file "||strip(fnm)|| " in " || substr(TOCText,1,100));
   end;
   newpage = 1; retain newpage;
   TOCEntry=0; retain TOCEntry;
   TOCText=""; retain TOCText;
  end;

*----------------------------------------------------------------------;
*Mark the first table on any page as a new TOC entry if it is different
  from the TOC entry on the previous page;
*----------------------------------------------------------------------;
  if (&startRule) and newpage then do;
   TOCText = strip(string);
   TOCEntry=1;
   newpage = 0;
  end;
  else if TOCEntry then do;
   if (not (&endRuleNextLine)) and (notspace(string,1)>0) then TOCText = strip(TOCText)||" "||strip(string);
   if (&endRuleNextLine) or (&endRuleSameLine) then do;
*----------------------------------------------------------------------;
*Insert a tab character after the table number to align the titles in the hanging indent;
*----------------------------------------------------------------------;
      %if %sysfunc(indexw(1 TRUE Y YES,%upcase(&tabalign)))>0 %then %do;
         nLeft = anyspace(TOCText,1);
         if nLeft>0 then do;
            nLeft = notspace(TOCText,nLeft);
            if nLeft>0 then do;
               nLeft = anyspace(TOCText,nLeft);
               if nLeft>0 then do;
                  nRight = notspace(TOCText,nLeft);
                  if nRight>0 then
                     TOCText = strip(substr(TOCText,1,nLeft))||'09'x||strip(substr(TOCText,nRight));
               end;
            end;
         end;
      %end;
      if lastTOCText ne TOCText then string = "{\tc {\v {"||strip(TOCText)||"} \tcf116 }}"||trim(string);
      TOCEntry = 0;
      LastTOCText = TOCText;  retain LastTOCText;
   end;
  end;
*----------------------------------------------------------------------;
* Reset the EOV flag so it will be set to 1 at start of next file ;
*----------------------------------------------------------------------;
  eov=0;
*----------------------------------------------------------------------;
* Convert FORMFEED to \page command ;
*----------------------------------------------------------------------;
  string=tranwrd(string,'0C'x,'\page ');
*----------------------------------------------------------------------;
* Write the line to the output file ;
*----------------------------------------------------------------------;
  len=length(string);
  put string $varying. len;
run;
%put &_err;
%quit:
%mend;

repo_name: scottbass/SAS

repo_language: SAS

mime_type: text/plain


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

Macros Called           : export_dlm, parmv

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

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

Scott Bass (sas_l_739@yahoo.com.au)

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

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

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

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

Modification History    : Original version

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

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

See Usage in the %export_dlm macro header.

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

See Notes in the %export_dlm macro header.

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

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

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

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

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

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

%quit:

%mend;

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


text:
/*====================================================================
Program Name            : get_dups.sas
Purpose                 : Identifies duplicate records given a list
                          of variables
SAS Version             : SAS 9.3
Input Data              : SAS dataset or view
Output Data             : SAS dataset containing duplicate records

Macros Called           : parmv
                          seplist

Originally Written by   : Michael Raithel
Date                    : 11JUL2016
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 Michael Raithel.
Used with permission.

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

Modification History    :

Programmer              : Scott Bass
Date                    : 21JUL2016
Change/reason           : Modified from original work created by
                          Michael Raithel posted to LinkedIn and
                          to his blog.
                          http://michaelraithel.blogspot.com.au/2016/07/hack-312-identifying-duplicate-variable.html
Program Version #       : 1.1

+===================================================================*/

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

Note:  sashelp.cars has a few records with dups for make and model,
       so use this dataset as our test data.

Pretend you are trying to determine the keys for sashelp.cars,
i.e. the combination of variables that will uniquely identify the record.

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

%get_dups(data=sashelp.cars,varlist=make)

   Displays duplicate records for varlist=model
   using PROC MEANS (default)

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

%get_dups(data=sashelp.cars,varlist=make,proc=sql)

   Displays duplicate records for varlist=model
   using PROC SQL

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

%get_dups(data=sashelp.cars,varlist=make model)

   Displays duplicate records for varlist=make model
   using PROC MEANS (default)

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

%get_dups(data=sashelp.cars,varlist=make model,proc=sql)

   Displays duplicate records for varlist=make model
   using PROC SQL

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

%get_dups(data=sashelp.cars,varlist=make model drivetrain)

   Displays duplicate records for varlist=make model drivetrain
   using PROC MEANS (should be no duplicates)

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

%get_dups(data=sashelp.cars,varlist=make model drivetrain,proc=sql)

   Displays duplicate records for varlist=make model drivetrain
   using PROC SQL (should be no duplicates)

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

%get_dups(data=sashelp.cars,varlist=make model,method=class)

   Displays duplicate records for varlist=make model
   using the CLASS statement of PROC MEANS (default value)

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

%get_dups(data=sashelp.cars,varlist=make model,method=by)

   This will generate an error, since sashelp.cars is not pre-sorted
   by make model, the method=by, and sort=n is the default setting.

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

%get_dups(data=sashelp.cars,varlist=make model,method=by,sort=y)

   Displays duplicate records for varlist=make model
   using the BY statement of PROC MEANS,
   sorting the input dataset.

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

%get_dups(data=sashelp.cars,varlist=make model,print=y)

   Displays duplicate records for varlist=make model
   printing the duplicate records
   (default is to only create a duplicates dataset for post-processing
    in the calling program)

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

%get_dups(data=sashelp.cars,varlist=make model,print=y,proc=sql)

   Displays duplicate records for varlist=make model
   printing the duplicate records
   (default is to only create a duplicates dataset for post-processing
    in the calling program)

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

Error checking:

%get_dups(data=doesnotexist,varlist=make model)

%get_dups(data=sashelp.cars,varlist=make foo model)

%get_dups(data=sashelp.cars,varlist=make model,method=foo)

%get_dups(data=sashelp.cars,varlist=make model,proc=foo)

%get_dups(data=sashelp.cars(where=(make="BMW")),varlist=make model)

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

This macro is useful for determining the natural key(s) for a dataset
or table.  The natural key(s) will uniquely identify a record,
so no duplicates will be returned.

This macro will use either PROC MEANS or PROC SQL to determine whether
duplicate records exist for a given variable list.

PROC MEANS will usually (always?) perform better than PROC SQL,
since "under the covers" PROC SQL will sort the input dataset
by the variable list.

However, PROC MEANS collapses the data into a single record per
combination of variable list data, while PROC SQL will print all
records which have duplicates.

By default, this macro only produces a dataset (work.dups) containing
the duplicate records.  This dataset can then be post-processed by the
calling program.  Specify PRINT=Y to optionally print the duplicate
records to the default output destination.

PROC MEANS Only:

By default, the CLASS statement is used to group the variable list data.
However, when the CLASS statement is used, PROC MEANS will group the
data in memory.  On rare occasions, with very large datasets and
low cardinality of variable list data, this can result in an
out-of-memory exception.  In this scenario, specify METHOD=BY so that
PROC MEANS will use the BY statement to group the variable list data.

By default, when METHOD=BY is specified, it is assumed that the
input data is (either physically or logically) sorted by the varlist.
Specify SORT=Y for this macro to sort the input dataset to a
temporary dataset before calling PROC MEANS.

The SORT parameter is ignored unless METHOD=BY.

Also note that, if your data is already pre-sorted, BY processing
will usually give better performance than CLASS processing.

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

%macro get_dups
/*--------------------------------------------------------------------
Identifies duplicate records given a list of variables.
--------------------------------------------------------------------*/
(DATA=         /* Input dataset (REQ).                              */
               /* Either one- or two-level names are supported.     */
               /* Dataset options (i.e. WHERE=) are not supported.  */
,VARLIST=      /* Space-separated variable list (REQ).              */
               /* The variable list must exist in the input dataset.*/
,PROC=MEANS    /* Procedure used to identify duplicates (REQ).      */
               /* Default value is MEANS.                           */
               /* Valid values are MEANS or SQL.                    */
               /* MEANS performs better but SQL lists ALL records   */
               /* with duplicate values, not just a summary record. */
,METHOD=CLASS  /* Method used to group variable list data (REQ).    */
               /* Valid values are CLASS or BY.                     */
               /* Default value is CLASS.                           */
               /* Only used when PROC=MEANS.                        */
,SORT=N        /* Sort input dataset? (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 YES, the input dataset is sorted to a temporary*/
               /* dataset for use by PROC MEANS.                    */
               /* Only used when PROC=MEANS.                        */
,PRINT=N       /* Print duplicates dataset? (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 YES, the duplicates dataset is printed to the  */
               /* default output destination.                       */
);

%local macro parmerr dsid i src;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(DATA,          _req=1,_words=0,_case=U)
%parmv(VARLIST,       _req=1,_words=1,_case=U)
%parmv(PROC,          _req=1,_words=0,_case=U,_val=MEANS SQL)
%parmv(METHOD,        _req=1,_words=0,_case=U,_val=CLASS BY)
%parmv(SORT,          _req=0,_words=0,_case=U,_val=0 1)
%parmv(PRINT,         _req=0,_words=0,_case=U,_val=0 1)

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

%* additional error checking ;

%* were dataset options specified? ;
%if (%index(%superq(data),%str(%())) %then %do;
   %parmv(_msg=Dataset options are not allowed with this macro)
   %goto quit;
%end;

%* does the input dataset exist? ;
%if not (%sysfunc(exist(&DATA,data)) or %sysfunc(exist(&DATA,view))) %then %do;
   %parmv(_msg=The &DATA dataset does not exist)
   %goto quit;
%end;

%* are the variables in the input dataset? ;
%let dsid=%sysfunc(open(&DATA,I));  %* I should probably error check this, but... ;
%let i=1;
%let var=%scan(&VARLIST,&i,%str( ));
%do %while(&var ne );
   %if (%sysfunc(varnum(&dsid,&var)) le 0) %then %do;
      %parmv(_msg=The variable &var is not present in the &DATA dataset)
      %let dsid=%sysfunc(close(&dsid));
      %goto quit;
   %end;
   %let i=%eval(&i+1);
   %let var=%scan(&VARLIST,&i,%str( ));
%end;
%let dsid=%sysfunc(close(&dsid));

%if (&PROC eq SQL) %then %do;
   proc sql noprint;
      create table work.dups as
      select
         count(1) as count, *
      from
         &DATA
      group by
         %seplist(&varlist)
      having count > 1
   ;
%end;
%else
%if (&PROC eq MEANS) %then %do;
   %* does the data need to be sorted? ;
   %let src=&DATA;

   %if (&METHOD eq BY) and (&SORT) %then %do;
      libname workspde spde "%sysfunc(pathname(work))" temp=yes;
      proc sort data=&DATA out=workspde._sorted_;
         by &VARLIST;
      run;
      %let src=workspde._sorted_;
   %end;

   %* use PROC MEANS to create the duplicates dataset ;
   proc means data=&src nway noprint missing;
      &METHOD &VARLIST;
      output out=work.dups (
         drop=_type_
         rename=(_freq_=count)
         where=(count > 1)
      ) sum=;
   run;
%end;

%if (&PRINT) %then %do;
   title1 "Duplicate Values in the &DATA dataset";
   title2 "Duplicate Count for Variables: &VARLIST";
   proc print data=work.dups noobs label;
   %if (&proc eq MEANS) %then %do;
      var count &VARLIST;
   %end;
      label count = "Duplicate Count";
   run;
   title;
%end;

%quit:

%mend;

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


text:
/*=====================================================================
Program Name            : stp_seplist.sas
Purpose                 : Transform stored process (STP) multi-select
                          macro variable array into a separated list
                          output.
SAS Version             : SAS 9.2
Input Data              : Series of macro variables created by a STP.
Output Data             : Separated list output

Macros Called           : parmv, seplist

Originally Written by   : Scott Bass
Date                    : 24APR2012
Program Version #       : 1.0

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

Scott Bass (sas_l_739@yahoo.com.au)

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

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

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

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

Modification History    : Original version

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

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

* simulate macro variable output from a stored process ;
* this is the format from prompt type Text, Multiple Values ;

* multiple character values ;
%let chr0=3;
%let chr1=foo;
%let chr2=bar;
%let chr3=blah blah blah;
%let chr_count=3;

* multiple numeric values ;
%let num0=5;
%let num1=1;
%let num2=2;
%let num3=3;
%let num4=4;
%let num5=5;
%let num_count=5;

* single character values ;
* (SAS is really braindead in the way it implemented a single value macro "array" ;
%let single=braindead;
%let single_count=1;

%put %stp_seplist(chr);
%put %stp_seplist(chr,&chr_count);    * explicit setting of the count parameter ;
%put %stp_seplist(chr,2);             * if you only wanted the first two values ;
%put %stp_seplist(chr,indlm=%str( )); * note that "blah blah blah" got parsed into separate tokens ;
%put %stp_seplist(chr,dlm=|);
%put %stp_seplist(chr,indlm=,dlm=);   * this just concatenates all the words together ;
%put %stp_seplist(chr,indlm=%str( ),dlm=,nest=);   * this just concatenates and compresses all the words together ;
%put %stp_seplist(chr,nest=q);        * useful if your data contains macro special tokens (&, %, etc) ;
%put %stp_seplist(chr,upper=Y);

%put %stp_seplist(num);               * probably not what you want ;
%put %stp_seplist(num,nest=);         * for numbers you want to turn off the default quoting ;
%put %stp_seplist(num,&num_count,nest=);
%put %stp_seplist(num,2,nest=);       * if you only wanted the first two values ;

%put %stp_seplist(single);
%put SAS is %stp_seplist(single,nest=), I tell you!;

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

If you want to test this in a stored process itself, save this code as
a stored process:

options mprint;
%macro stp;
  %local where;
  %let where=%sysfunc(sum(&region_count,&product_count,&subsidiary_count));
  options noserror nomerror;
  data subset;
    set sashelp.shoes;
    %if (&where) %then %do;
      where 1
    %end;
    %if (&region_count) %then %do;
      and region in (%stp_seplist(region))
    %end;
    %if (&product_count) %then %do;
      and product in (%stp_seplist(product))
    %end;
    %if (&subsidiary_count) %then %do;
      and subsidiary in (%stp_seplist(subsidiary))
    %end;
    %if (&where) %then %do;
      ;
    %end;
  run;
  options serror merror;

  proc print;
  run;
%mend;
%stp

Create three multi-value text prompts on region, product, and
subsidiary, then run the STP to test the macro execution.

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

This macro is meant to be called within a stored process, to return the
macro variable array as a separated list.  Most likely, this separated
list will be used in a where clause.

I have not tested this macro with other prompt types.  It may work with
other types, but a separated list output may or may not be appropriate
to the other prompt types.

This macro is just a "wrapper macro" to the %seplist macro, converting
the macro array created by a STP into a format acceptable to %seplist.

This is a function style macro, and needs to be called in a
syntactically correct context where a separated list output is valid.

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

%macro stp_seplist
/*---------------------------------------------------------------------
Transform stored process (STP) multi-select macro variable array into a
separated list output.
---------------------------------------------------------------------*/
(MVAR          /* Macro variable name set by the STP (REQ).          */
,COUNT         /* Macro variable element count set by the STP (Opt). */
               /* If not set, a macro variable named <mvar>_count    */
               /* must exist.  This is the case with multi-value     */
               /* text prompts in a STP.                             */
,DLM=%str(,)   /* Output delimiter (Opt).                            */
               /* Default is a comma.                                */
               /* While not technically required, without an output  */
               /* delimiter, all the values are compressed together. */
,INDLM=%str(^) /* Input delimiter (Opt).                             */
               /* If your input data contains spaces, a blank INDLM  */
               /* parameter would cause each space delimited work to */
               /* be treated as a separate token.  In most cases, you*/
               /* will not need to change this value unless your     */
               /* input contains carets (^)                          */
,NEST=QQ       /* Nest each word in the separated list? (Opt).       */
               /* Default is QQ (double-quoting).  See the macro     */
               /* header of %seplist for more details.               */
,UPPER=0       /* Convert to uppercase? (Opt).                       */
               /* OFF N NO F FALSE and ON Y YES T TRUE               */
               /* (case insensitive) are acceptable aliases for      */
               /* 0 and 1 respectively.                              */
);

%local macro parmerr count string temp i;
%let macro = &sysmacroname;

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

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

%* if count was not explicitly specified, a macro variable <mvar>_count ;
%* should exist in the global environment.  if so, resolve it, otherwise assume 1 ;
%if (&count eq ) %then %do;
  %if (%symexist(&&mvar._count)) %then
    %let count=&&&mvar._count;
  %else
    %let count=1;
%end;

%* compensate for the absolute braindead way SAS implemented a single element macro "array" ;
%if (&count eq 1) %then %let &mvar.1=&&&mvar;

%let string=;
%let temp=;

%* Using %superq inside the loop generates the error: ;
%* ERROR: Maximum level of nesting of macro functions exceeded. ;
%* See http://support.sas.com/kb/00/449.html ;

%* If the source data contains tokens that can be interpretted as macro characters, ;
%* (for example Savings&Loan, %change), then set the options noserror nomerror ;
%* outside this macro.  I cannot set it here since this is a function style macro ;
%do i=1 %to &count;
  %if (&i gt 1) %then %let string=&string.&indlm;
  %let temp=&&&mvar&i;
  %if (&upper eq 1) %then %let temp=%upcase(&temp);
  %let string=&string.&temp;
%end;

%seplist(%superq(string),indlm=&indlm,dlm=&dlm,nest=&nest)

%quit:
%mend;


text:
/*====================================================================
Program Name            : kill.sas
Purpose                 : Deletes specified contents from a library
SAS Version             : SAS 9.1.3
Input Data              : N/A
Output Data             : N/A

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 15Feb2010
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                    : 30JAN2012
Change/reason           : Added data parameter.
Program Version #       : 1.1

Programmer              : Scott Bass
Date                    : 01AUG2016
Change/reason           : Changed save/delete/data parameters to not
                          change the case.  Table names are case
                          sensitive when working with RDBMS's.
Program Version #       : 1.2

+===================================================================*/

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

%kill;

   Deletes all datasets and views (default) from the WORK library (default)

%kill(
   lib=claims
);

   Deletes all datasets and views (default) from the claims library

%kill(
   lib=claims
   ,type=data
);

   Deletes all datasets (only) from the claims library

%kill(
   lib=claims
   ,type=data view catalog
);

   Deletes all contents (datasets, views, catalogs) from the claims library

%kill(
   lib=claims
   ,type=all
);

   Deletes all contents (datasets, views, catalogs) from the claims library
   (same as above)

%kill(
   lib=claims
   ,save=FOO BAR BLAH
);

   Deletes all contents from the claims library EXCEPT FOO, BAR, and BLAH
   datasets or views.  Since a SAS library can contain EITHER a dataset
   OR view for a given membername, both datasets and views are saved for
   a given membername.

%kill(
   lib=claims
   ,delete=FOO BAR BLAH
);

   Deletes ONLY FOO, BAR, and BLAH datasets or views from the claims library.

%kill(
   data=claims.foo
);

   Deletes the FOO dataset or view from the claims library.

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

The complete list of member types that can be specified to
PROC DATASETS are ACCESS ALL CATALOG DATA FDB MDDB PROGRAM VIEW.
For simplicity, this macro only accepts type=DATA VIEW CATALOG ALL.
A blank type= parameter is equivalent to DATA VIEW.

Only one of data=, delete=, or save= options can be specified.  If both
are specified an error message is returned.

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

%macro kill
/*--------------------------------------------------------------------
Deletes specified contents from a library
--------------------------------------------------------------------*/
(LIB=          /* Input library (Opt).  If not specified,           */
               /* the WORK library is used.                         */
,TYPE=DATA VIEW
               /* Member type affected (Opt).  If not specified,    */
               /* all datasets and views are deleted.               */
               /* Valid values are DATA, VIEW, CATALOG, ALL         */
,SAVE=         /* Member names to save from deletion (Opt).         */
               /* If not specified, all items are deleted.  If the  */
               /* member name does not exist in the specified       */
               /* library, no error occurs, but also no error       */
               /* message is displayed.                             */
,DELETE=       /* Member names to delete (Opt).                     */
               /* If not specified, all items are deleted.  If the  */
               /* member name does not exist in the specified       */
               /* library, no error occurs, but also no error       */
               /* message is displayed.                             */
,DATA=         /* Member name to delete (Opt).                      */
               /* Only a single name can be specified.              */
               /* If a one-level name is specified, the USER        */
               /* option is used for the library.  If the USER      */
               /* option is blank then WORK is used for the library.*/
);

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

%* check input parameters ;
%parmv(LIB,           _req=0,_words=0,_case=U)
%parmv(TYPE,          _req=0,_words=1,_case=U,_val=DATA VIEW CATALOG ALL)
%parmv(SAVE,          _req=0,_words=1,_case=N)
%parmv(DELETE,        _req=0,_words=1,_case=N)
%parmv(DATA,          _req=0,_words=1,_case=N)

%let check=%eval((&save ne ) + (&delete ne ) + (&data ne ));
%if (&check gt 1) %then
%parmv(_msg=Only one of DATA=, SAVE=, or DELETE= parameters may be specified)

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

%* If DATA was specified, parse into LIB and DELETE ;
%if (&data ne ) %then %do;
  %let delete=%scan(&data,2,.);  %* two level name ;
  %if (&delete eq ) %then
    %let delete=%scan(&data,1,.); %* one level name ;
  %else
    %let lib=%scan(&data,1,.);
%end;

%* if library was not specified, use USER= option, then use WORK ;
%if (&lib eq ) %then %let lib=%sysfunc(getoption(user));
%if (&lib eq ) %then %let lib=WORK;

%* if member type was not specified, use DATA VIEW ;
%if (&type eq ) %then %let type=DATA VIEW;

%* if save or delete were not specified, use KILL ;
%let kill=kill;
%if (&save ne ) or (&delete ne ) %then %let kill=;

%* check if library is readonly (assume WORK is not readonly!) ;
%let readonly=no;
%if (%upcase(&lib) ne WORK) %then %do;
   proc sql noprint;
      select readonly into :readonly separated by " "
      from dictionary.libnames
      where upcase(libname) = "%upcase(&lib)"
      ;
   quit;
%end;

%if (%index(&readonly,yes) eq 0) %then %do;
   proc datasets lib=&lib memtype=(&type) &kill nolist nowarn;
   %if (&save ne   ) %then %do;save &save;%end;
   %if (&delete ne ) %then %do;delete &delete;%end;
   quit;
%end;
%else %do;
   %put %str(NO)TE: Library &lib is readonly;
%end;

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

%mend;

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


text:
/*=====================================================================
Program Name            : nobs.sas
Purpose                 : Return the number of observations in a data
                          set.
SAS Version             : SAS 9.1.3
Input Data              : DATA= macro parameter
Output Data             : # of observations in the data set

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 01MAY2006
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                    : 27MAY2015
Change/reason           : Added format=32. to select statement
Program Version #       : 1.1

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

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

data work.foo;
   do x=1 to 25;
      output;
   end;
run;

data work.foo_view / view=work.foo_view;
   do x=1 to 14;
      output;
   end;
run;

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

* Assign the number of observations in &syslast ;
* (work.foo_view) to &nobs;
%nobs;
%put &nobs;

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

* Assign the number of observations in work.foo to &foo.
%let foo=%nobs(work.foo);
%put &foo;

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

* Assign the number of observations in work.foo to &foo. ;
* Also assign the global macro variable &mynobs ;
* (which in this case is a bit redundant). ;
%let foo=%nobs(work.foo, mvar=mynobs);
%put &foo;
%put &mynobs;

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

* Use %nobs as part of a logic condition in a data step ;
data _null_;
   if not (%nobs(work.foo)) then
      put "No observations";
   else
      put "%nobs(work.foo) observations.";
run;

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

* Invoke %nobs on a view, assigning the number of observations to &nobs ;
%nobs(work.foo_view);
%put &nobs;

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

* Invoke %nobs on a data set with a where condition, assigning the ;
* number of observations returned to &mymvar ;
%nobs(work.foo (where=(uniform(0) le .5)), mvar=mymvar);
%put &mymvar;

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

If %nobs can determine the number of observations from the data set
descriptor, %nobs will invoke an "all macro" syntax, returning the
number of observations as an RVALUE.  In this scenario, %nobs should
be called as part of an assignment statement or logic condition.

If %nobs cannot determine the number of observations from the data
set descriptor, or if a data set option (usually a where clause) is
specified, %nobs will invoke PROC SQL, and will return the number of
observations in a macro variable (default is &nobs, but can be set by
the calling code).

It is up to the end user to know the proper context for calling %nobs.
If there is any doubt, you could always do:

%nobs(work.foo());
%put &nobs;

but this will not perform as well as reading the number of observations
from the data set descriptor.

If you experience any error calling this macro, as a first course try
changing the context in which the macro is called.  For example, to
test:

%put %nobs(foo);
%nobs(foo);

One of these will *always* cause an error in the code execution phase
(i.e. after macro code resolution).

Returns:
   &nobs_rc = -1 if dataset does not exist
   &nobs_rc = -2 if dataset cannot be opened for input

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

%macro nobs
/*---------------------------------------------------------------------
Return the number of observations in a data file.
---------------------------------------------------------------------*/
(DATA          /* Name of data file (Opt).                           */
               /* If blank, &syslast is used                         */
,MVAR=         /* Name of returned macro variable (Opt).             */
               /* If non-blank then global mvar unconditionally set. */
               /* If blank then:                                     */
               /*    If "all macro" then no global macro var set.    */
               /*    If PROC SQL then global mvar &nobs set.         */
);

%local macro parmerr data mvar paren dataset options dsid anobs _nobs;
%let macro = &sysmacroname;

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

%global nobs_rc;
%let nobs_rc = 0;

%*---------------------------------------------------------------------
Reset global macro variable from any previous invocations
----------------------------------------------------------------------;
%if (&mvar ne ) %then %do;
   %global &mvar;
   %let &mvar = ;
%end;

%*---------------------------------------------------------------------
When data parameter not specified, use &syslast macro variable to get
last created data set.
----------------------------------------------------------------------;
%if (%superq(data) eq ) %then %let data = &syslast;

%*----------------------------------------------------------------------
Parse data parameter for data set options.  Note that the scan
function cannot be used since a WHERE clause attached to the DATA
parameter may contain a period.
-----------------------------------------------------------------------;
%let paren = %index(&data,%str(%() );
%if (&paren) %then %do;
   %let dataset = %substr(&data,1,&paren-1);
   %let options = %substr(&data,&paren);  /* not used but may as well save */
%end;
%else %do;
   %let dataset = &data;
%end;

%*---------------------------------------------------------------------
Does the dataset exist?
----------------------------------------------------------------------;
%if ^(%sysfunc(exist(&dataset,data)) or
      %sysfunc(exist(&dataset,view))) %then %do;
   %parmv(_msg=%str(ERR)OR: &dataset does not exist);
   %let nobs_rc = -1;
   %goto quit;
%end;

%*---------------------------------------------------------------------
Does the engine know the number of observations?
Or was a data set option specified?
----------------------------------------------------------------------;
%if (&paren) %then
   %let anobs = 0;
%else %do;
   %let dsid = %sysfunc(open(&dataset));
   %if (&dsid gt 0) %then %do;
      %let anobs = %sysfunc(attrn(&dsid,anobs));
      %let _nobs = %eval(%sysfunc(attrn(&dsid,nobs)) - %sysfunc(attrn(&dsid,ndel)));
      %let dsid  = %sysfunc(close(&dsid));
   %end;
   %else %do;
      %parmv(_msg=%str(ERR)OR: Unable to open &dataset for input);
      %let nobs_rc = -2;
      %goto quit;
   %end;
%end;

%if ^(&anobs) %then %do;

%*---------------------------------------------------------------------
Number of observations could not be determined, so use PROC SQL
If mvar is blank set it to "nobs"
----------------------------------------------------------------------;
   %if (&mvar eq ) %then %do;
      %let mvar = nobs;
      %global &mvar;
      %let &mvar = ;
   %end;
   proc sql noprint;
      select count(*) format=32. into :&mvar separated by " " from &data;
   quit;
%end;
%else %do;

%*---------------------------------------------------------------------
Return number of observations as an RVALUE
If mvar is specified then also return global macro variable
----------------------------------------------------------------------;
&_nobs

   %if (&mvar ne ) %then %do;
      %let &mvar = &_nobs;
   %end;
%end;

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

%mend;

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


text:
/*=====================================================================
Program Name            : export_saphari.sas
Purpose                 : Export SAS dataset or SQL Server table
                          to Saphari.
SAS Version             : SAS 9.4
Input Data              : SAS dataset or SQL Server table
Output Data             : SAS dataset(s) and view(s) required for use
                          by Saphari

Macros Called           : parmv, create_format, nobs

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:

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

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

* you may have to modify the based on the current vwAP_FLAT_SAS* views available ;
data sasout.apdc_flat;
   set dmt.vwAP_FLAT_SAS_49 (obs=1000);
run;

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

Create a hoist view for sasout_apdc_flat using flattened view
from RLCS Server.

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

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

* Saphari Prod Server ;
%libname_sqlsvr(libref=DMT,server=SVDCMHPRSDWSQD1,port=,database=RLDXHosp,schema=dmt)

* you may have to modify the based on the current vwAP_FLAT_SAS* views available ;
data sasout.apdc_flat;
   set dmt.vwAP_FLAT_SAS (obs=1000);
run;

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

Create a hoist view for sasout.apdc_flat using flattened view
from Saphari Prod Server.

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

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

* Saphari Prod Server ;
%libname_sqlsvr(libref=DMT,server=SVDCMHPRSDWSQD1,port=,database=RLDXHosp,schema=dmt)

* you may have to modify the based on the current vwAP_FLAT_SAS* views available ;
data sasout.apdc_flat;
   set dmt.vwAP_FLAT_SAS (obs=1000);
run;

%export_saphari(
   data=sasout.apdc_flat (where=(age_grouping_recode=13))
   ,prefix=apdc
)

Although this usage is rare, it is possible to create a hoist view
where the source dataset has a dataset option (in this case a where clause)

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

Error checking:

%export_saphari(
)

Fails, source dataset and hoist name prefix parameters are required.

%export_saphari(
   data=sasout.apdc_flat
)

Fails, hoist name prefix is required.

%export_saphari(
   data=sasout.DoesNotExist
   ,prefix=apdc
)

Fails, source dataset sasout.doesnotexist does not exist.

data work.apdc_flat;
   set sasout.apdc_flat;  * assumes you have run the above examples ;
run;

%export_saphari(
   data=work.apdc_flat
   ,prefix=apdc
)

Fails, work.apdc_flat exists, but must be in the sasout library.

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

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

%macro export_saphari
/*---------------------------------------------------------------------
Export SAS dataset or SQL Server table to Saphari.
---------------------------------------------------------------------*/
(DATA=         /* Dataset to export (REQ).                           */
               /* Data set options, such as a where clause, may be   */
               /* specified.                                         */
,PREFIX=       /* Output dataset name prefix (REQ).                  */
               /* Usual dataset name prefixes are                    */
               /* APDC, EDDC, DTH, or CODURF, but any dataset name   */
               /* prefix can be specified.                           */
);

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

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

%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=Source 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);

%* the source dataset must exist in the SASOUT library ;
%if (%upcase(&_inlib) ne SASOUT) %then %do;
   %let syscc=8;
   %parmv(_msg=The source dataset %superq(_temp) must exist in the SASOUT library)
   %return;
%end;

%***** NOTE: This code is no longer required, but I have commented it out rather than deleting it ***** ;
%macro comment;
%* create a format defining fiscal years from 2000 to 2030 ;
data cntlin;
   length startdate enddate 8 start end $16 label $40;
   do year=2000 to 2030;
      startdate=mdy(7,1,year);
      start    =put(startdate,best.-L);
      enddate  =mdy(6,30,year+1);
      end      =put(enddate,best.-L);
      label    =cats(&prefix,put(startdate,year2.),put(enddate,year2.));
      output;
   end;
   format startdate enddate date9.;
   keep startdate enddate start end label;
run;

%create_format(
   data=cntlin
   ,name=fiscalyear
   ,type=num_format
   ,start=start
   ,end=end
)

%* create a list of all possible output datasets ;
proc sql noprint;
   select label into :datasets separated by " " from cntlin;
quit;

%* create helper macros to build SAS syntax using the %loop macro ;
%macro dsnames;
sasout.&word
%mend;
%macro select;
when(&word) output sasout.&word;
%mend;
%macro dlt;  %* "delete" and "del" are reserved words ;
%if (%nobs(&word) eq 0) %then delete %scan(&word,2,.);;
%mend;

%* split input dataset into fiscalyear datasets ;
data %loop(&datasets,mname=dsnames);
   set &data;
   select(put(episode_end_date,fiscalyear.));
      %loop(&datasets,mname=select);
   end;
run;

%* delete any empty datasets ;
proc datasets lib=sasout nowarn nolist;
   %loop(&datasets,mname=dlt);
quit;
%mend comment;

%* an upstream requirement is that the input dataset is in the SASOUT library ;
%* create a view of that dataset with column names compatible with HOIST ;
%macro rename(source,target);
%do i=1 %to 50;
rename &source%sysfunc(putn(&i,z2.))=&target%eval(&i+1);
%end;
%mend;

data sasout.&prefix._hoist_names / view=sasout.&prefix._hoist_names;
   set &data;
   rename acute_flag                               = acuteflg;
   admdt= dhms(episode_start_date,0,0,episode_start_time);
   rename age_grouping_recode                      = Agegrp;
   rename age_recode_years                         = age_recode;
   rename ar_drg                                   = ardrg;
   rename ar_drg_version                           = ardrg_version;
   rename area_identifier                          = area_version_facility;
   rename birth_date                               = dob;
   rename block_num_p                              = procbl1;
/*   rename condition_onset_flagE1                   = coflag52;*/
/*   rename condition_onset_flagE2                   = coflag53;*/
   rename condition_onset_flag_p                   = coflag1;
   rename cost_weight_a                            = cost_wt_a;
   rename cost_weight_b                            = cost_wt_b;
   rename cost_weight_c                            = cost_wt_c;
   rename cost_weight_d                            = cost_wt_d;
   rename cost_weight_e                            = cost_wt_e;
   rename cost_weight_f                            = cost_wt_f;
   rename country_of_birth_sacc                    = COBSACC;
   rename days_in_psych_unit                       = psychday;
/*   rename diagnosis_codeE1                         = icd10d52;*/
/*   rename diagnosis_codeE2                         = icd10d53;*/
   rename diagnosis_code_p                         = icd10d1;
   rename dva_card_type                            = DVAType;
   rename emergency_status_recode                  = emergncy;
   rename episode_day_stay_los_recode              = dolos;
   rename episode_end_date                         = sepdate;
   rename episode_end_time                         = septime;
   rename episode_leave_days_total                 = leaveday;
   rename episode_length_of_stay                   = los;
   rename episode_of_care_type                     = csrvccat;
   rename episode_start_date                       = admdate;
   rename episode_start_time                       = admtime;
   rename facility_identifier_recode               = hoscode;
   rename facility_trans_from_recode               = trnsfrom;
   rename facility_trans_to_recode                 = tfrhosp;
   rename facility_type                            = hostype;
   rename financial_class                          = fin_class;
   rename financial_program                        = program;
   rename health_insurance_on_admit                = insstat;
   rename hours_in_icu                             = icuhours;
   rename hours_on_mech_ventilation                = hrsmechv;
   rename indigenous_status                        = abtsi;
   rename infant_start_weight                      = nweight;
   rename involuntary_days_in_psych                = invpsych;
   rename last_psych_admission_date                = psyyrlst;
   rename marital_status                           = marital;
   rename mdc                                      = mdcandrg;
   rename mode_of_separation_recode                = nsepmode;
   rename patient_postcode                         = pcode;
   rename payment_status_on_sep                    = payst_v4;
   rename peer_group                               = hcdbpeer;
   rename procedure_code_p                         = MBS_EP1;
   rename procedure_date_p                         = procdate1;
   rename procedure_location_p                     = procfl1;
   rename qualified_bed_days_recode                = qualday;
   rename readmitted_within_28_days                = readmit;
   rename recognised_ph_flag                       = rph_flag;
   rename referred_to_on_separation_recode         = refertoc;
   sepdt= dhms(episode_end_date,0,0,episode_end_time);
   rename source_of_referral_recode                = srcrefc;
   rename state_of_residence_recode                = state_of_residence;
   rename unit_type_on_admission                   = unittyp2;
   rename unqual_baby_bed_days                     = unqalday;

   %rename(block_num_,procbl);
   %rename(condition_onset_flag_,coflag);
   %rename(diagnosis_code_,icd10d);
   %rename(procedure_code_,MBS_EP);
   %rename(procedure_date_,procdate);
   %rename(procedure_location_,procfl);
run;

%mend;

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


text:
/*====================================================================
Program Name            : optsave.sas
Purpose                 : Saves current options settings to a SAS
                          dataset
SAS Version             : SAS 9.1.3
Input Data              : None
Output Data             : SAS options dataset

Macros Called           : parmv

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

%optsave;
   Saves current options settings to default dataset work.options

%optsave(out=sasuser.myoptions);
   Saves current options to dataset sasuser.myoptions

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

This is just a "wrapper macro" around PROC OPTSAVE.

PROC OPTSAVE allows both out= and key= options, but I don't like the
key= option, as it is difficult to remove the key from the sasuser
registry.  So I have only implemented the out= option.

No error checking is done, for example attempting to write output
dataset to readonly library.

Typically %optsave and %optload would be used at the beginning
and end of a program respectively, to save and restore the SAS
options to their original values.

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

%macro optsave
/*--------------------------------------------------------------------
Defines a hash object for later lookup
--------------------------------------------------------------------*/
(OUT=options   /* Output dataset to contain current options (REQ).  */
);

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

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

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

%* save current SAS system options, ;
%* see documentation for PROC OPTSAVE for more details ;
proc optsave out=&out;
run;

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

%mend;

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