text:
/*=====================================================================
Program Name            : excel2sas.sas
Purpose                 : Read an Excel workbook into a SAS dataset(s)
                          using PROC IMPORT
SAS Version             : SAS 9.1.3
Input Data              : None
Output Data             : None

Macros Called           : parmv, seplist, subset_data, loop, kill

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

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

Scott Bass (sas_l_739@yahoo.com.au)

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

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

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

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

Modification History    :

Programmer              : Scott Bass
Date                    : 07MAY2009
Change/reason           : Changed to use scan function to work with Excel
                          worksheets named like SheetName$_
Program Version #       : 1.1

Programmer              : Scott Bass
Date                    : 19APR2011
Change/reason           : Added processing for reading Excel files on UNIX
Program Version #       : 1.2

Programmer              : Scott Bass
Date                    : 04NOV2016
Change/reason           : Changed default engine to EXCEL2007 on Windows
                          (should probably add an ENGINE parameter)
Program Version #       : 1.3

Programmer              : Scott Bass
Date                    : 07JUL2017
Change/reason           : Changed SELECT and EXCLUDE parameters to use
                          Perl Regular Expression syntax rather than
                          SQL syntax (i.e. like FOO%)
Program Version #       : 1.3

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

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

%let file = C:\Path_to_Excel_Workbook.xls;

%excel2sas(file=&file)
   Import entire workbook,
      saving output datasets to work,
      naming output datasets based on worksheet names

%excel2sas(file=&file,select=Table 1^Sheet 3^List of Notes)
   Import worksheets named Table 1, Sheet 3, and List of Notes
      saving output datasets to work,
      naming output datasets based on worksheet names

%excel2sas(file=&file,exclude=Instructions^Issue Log
   Import all worksheets except Instructions and Issue Log
      saving output datasets to work,
      naming output datasets based on worksheet names

%excel2sas(file=&file,select=(^Table.*),lib=mylib)
   Import worksheets whose names begin with "Table",
      saving output datasets to mylib,

%excel2sas(file=&file,select=Table 1^Table 2^Table3,out=mytable1^mytable2^mytable3)
   Import worksheets named Table 1, Table 2, and Table 3,
      saving output datasets to work
      naming output datasets as mytable1, mytable2, and mytable3

%excel2sas(file=&file,select=Sheet1,range=C5:J50)
   Import "Sheet1" worksheet,
      range C2:J50,
      saving output datasets to work,
      naming output datasets as Sheet1

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

The main purpose of this macro is to read all worksheets from an Excel
workbook without knowing the worksheet names.  The macro will attempt
to convert the worksheet names into SAS-compliant dataset names if
required.  The macro also allows selection or exclusion of specific
worksheet names (whose names must be known, of course).  If incorrect
worksheet names are specified, the macro will attempt to import the
remaining worksheets without generating an error.

However, dynamically determining the worksheet names is currently only
supported on Windows (via the libname EXCEL engine).

If running this macro on UNIX, there is no (current) way for SAS to
dynamically get the sheet names from the Excel workbook.  Therefore,
you must specify the desired sheet names in the macro call, and there
is no error checking as to whether the specified sheet names are correct.

Since worksheet names can contain spaces, use a caret (^) as the
delimeter between sheet names for the SHEETS= parameter.  For consistency,
the same delimiter is used for the OUT= parameter.  SAS Name Literals
(i.e. data 'Sheet A B C'n;...) are not supported by this macro.

This macro may return incorrect results if the source workbook contains
sheet names with a dollar sign ($) in the worksheet name.

Use Perl Regular Expression (PRX) syntax to search for multiple worksheets.
Enclose the PRX within parentheses to indicate that it is a PRX instead
of a worksheet name.

Examples:
(^Table):  Worksheet names beginning with "Table"
(Notes$):  Worksheet names ending with "Notes"
(^A):      Worksheet names beginning with "A"
(^FOO$|^BAR$|^BLAH$):  Worksheet names that are exactly FOO or BAR or BLAH

By default a PRX will match any part of the worksheet name,
i.e. FOO will match FOO, FOOBAR, BARFOO, WHOOPDEFOO, etc.

Use metacharacters such as ^ (beginning of line) or $ (end of line)
to anchor the text.

See the Perl Regular Expression documentation for more details.

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

%macro excel2sas
/*---------------------------------------------------------------------
Read an Excel workbook into a SAS dataset(s)
---------------------------------------------------------------------*/
(              /* ===== PROC IMPORT / LIBNAME EXCEL OPTIONS =====    */

 FILE=         /* Excel workbook to import (REQ).                    */
,LIB=WORK      /* Output dataset library (REQ).                      */
               /* Default is WORK.                                   */
,OUT=          /* Output dataset name(s) (Opt).                      */
               /* If blank, output dataset name is based on          */
               /* worksheet name.                                    */
               /* If the number of output dataset names equals the   */
               /* number of sheet names, there is a one-to-one       */
               /* correspondence between the sheet names and output  */
               /* dataset names.                                     */
               /* If the output dataset names does not match the     */
               /* number of sheet names, or if SHEETS is blank, then */
               /* the FIRST output dataset name is used as a prefix  */
               /* for the output dataset names.                      */
,GETNAMES=NO   /* Set column names based on first row of data in     */
               /* the imported range? (Opt).                         */
               /* Default is NO.  Valid values are YES and NO.       */
,RANGE=        /* Specific range of cells to import from the         */
               /* worksheet (Opt).                                   */
               /* If blank, entire worksheet is imported.            */
,SELECT=       /* Specific worksheets to import from workbook (Opt). */
               /* If blank, all worksheets are imported.             */
               /* Multiple worksheets must be separated with a caret */
               /* (^) since worksheet names can contain spaces.      */
               /* Wildcards can be used for worksheets, see          */
               /* usage notes.                                       */
,EXCLUDE=      /* Specific worksheets to exclude from import (Opt).  */
               /* If blank, no worksheets are excluded.              */
               /* Multiple worksheets must be separated with a caret */
               /* (^) since worksheet names can contain spaces.      */
               /* Wildcards can be used for worksheets, see          */
               /* usage notes.                                       */
,MIXED=YES     /* Specifies whether to convert numeric data values   */
               /* into character data values for a column with mixed */
               /* data types.                                        */
               /* Default is YES.  If YES, mixed data will be        */
               /* imported as text.  If NO, mixed data will be       */
               /* imported as numeric if the majority of data is     */
               /* numeric (based on TypeGuessRows registry setting). */
               /* If blank, PROC IMPORT default is used.             */
,SCANTEXT=YES  /* Scan text data for longest data length, and use    */
               /* this length for the SAS column width? (Opt).       */
               /* Default is YES.                                    */
               /* If blank, PROC IMPORT default is used.             */
,SCANTIME=YES  /* Scan columns for DATETIME data, and automatically  */
               /* determine the TIME datatime if no DATE or DATETIME */
               /* data is found? (Opt).
               /* Default is YES.                                    */
               /* If blank, PROC IMPORT default is used.             */
,USEDATE=NO    /* Use DATE. format for DATETIME. data? (Opt).        */
               /* Default is NO.                                     */
               /* If blank, PROC IMPORT default is used.             */
,TEXTSIZE=32767
               /* Specifies the field length that is allowed for     */
               /* importing Memo fields (Opt).                       */
               /* Default is 32767.                                  */
               /* If blank, PROC IMPORT default is used.             */
,DBSASLABEL=NONE
               /* Set SAS labels to data source column names? (Opt). */
               /* Valid values are COMPAT or NONE.                   */
               /* Default is NONE.                                   */
               /* If blank, PROC IMPORT default is used.             */

               /* ===== POST-PROCESSING OPTIONS =====                */

,WHERE=        /* Where clause (Opt).                                */
,IF=           /* Subsetting If clause (Opt).                        */
,FIRSTOBS=     /* First observation (Opt).                           */
,LASTOBS=      /* Last  observation (Opt).                           */
,OBS=          /* Range of observations (Opt).                       */
,KEEP=         /* Variable list to keep (Opt).                       */
,DROP=         /* Variable list to drop (Opt).                       */
,RENAME=       /* Variable list to rename (Opt).                     */
);

%local macro parmerr os engine sheets
       outdsn sheet startrow startcol endrow endcol
       word _flag
;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(FILE,         _req=1,_words=1,_case=N)
%parmv(LIB,          _req=1,_words=0,_case=U)
%parmv(GETNAMES,     _req=0,_words=0,_case=U,_val=YES NO)
%parmv(RANGE,        _req=0,_words=0,_case=U)
%parmv(SELECT,       _req=0,_words=1,_case=N)
%parmv(EXCLUDE,      _req=0,_words=1,_case=N)
%parmv(MIXED,        _req=0,_words=0,_case=U,_val=YES NO)
%parmv(SCANTEXT,     _req=0,_words=0,_case=U,_val=YES NO)
%parmv(SCANTIME,     _req=0,_words=0,_case=U,_val=YES NO)
%parmv(USEDATE,      _req=0,_words=0,_case=U,_val=YES NO)
%parmv(TEXTSIZE,     _req=0,_words=0,_case=U,_val=POSITIVE)
%parmv(DBSASLABEL,   _req=0,_words=0,_case=U,_val=NONE COMPAT)
%parmv(FIRSTOBS,     _req=0,_words=0,_case=U,_val=POSITIVE)
%parmv(LASTOBS,      _req=0,_words=0,_case=U,_val=POSITIVE)

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

%*---------------------------------------------------------------------
Get the operating system and set the desired import engine
----------------------------------------------------------------------;
%if (%upcase(%scan(&sysscp,1)) eq WIN) %then %do;
   %let os=WIN;
   %let engine=EXCEL2007;
   %* let engine=XLSX;  %* testing ;
%end;
%else
%if (%upcase(%scan(&sysscp,1)) eq HP) %then %do;
   %let os=UNIX;
   %let engine=XLS;
%end;
%else
%if (%upcase(%scan(&sysscp,1)) eq LIN) %then %do;
   %let os=UNIX;
   %let engine=XLS;
%end;

%*---------------------------------------------------------------------
Windows processing...
----------------------------------------------------------------------;
%if (&os eq WIN) %then %do;

   %*---------------------------------------------------------------------
   If both SELECT and OUT were specified, we need to maintain the
   specified parameter order in order to maintain the one-to-one
   correspondence between the specified worksheets and the output dataset
   names. However, the dictionary tables will return the actual worksheet
   names in alphabetical order.

   If SELECT, EXCLUDE, and/or OUT were specified, save the worksheet names
   into a working dataset for later processing.
   ----------------------------------------------------------------------;
   %if (%superq(select) ne ) %then %do;
      %if (not %index(%superq(select),%str(%())) %then %do;
         data _select_;
            length sort 8 memname $32;
            do memname=%seplist(%superq(select),indlm=^,nest=QQ);
               sort+1;
               output;
            end;
         run;
      %end;
   %end;

   %if (%superq(exclude) ne ) %then %do;
      %if (not %index(%superq(exclude),%str(%())) %then %do;
         data _exclude_;
            length sort 8 memname $32;
            do memname=%seplist(%superq(exclude),indlm=^,nest=QQ);
               sort+1;
               output;
            end;
         run;
      %end;
   %end;

   %* no need to check for PRX wildcard on OUT parameter ;
   %if (%superq(out) ne ) %then %do;
      data _out_;
         length sort 8 memname $32;
         do memname=%seplist(%superq(select),indlm=^,nest=QQ);
            sort+1;
            output;
         end;
      run;
   %end;

   %*---------------------------------------------------------------------
   Use the ODBC engine to get list of worksheets in the Excel workbook
   This seems to be quicker than the libname excel engine.
   ----------------------------------------------------------------------;
   /* libname _XCEL_ odbc noprompt="dsn=Excel Files; dbq=&file"; */

   %* issue libname statement using excel engine ;
   libname _XCEL_ excel "&file" access=READONLY;

   /* libname _XCEL_ xlsx "&file" access=READONLY; */

   %*---------------------------------------------------------------------
   Get a list of all sheet names.
   Filter out any print areas, etc.
   ----------------------------------------------------------------------;
   proc sql noprint;
      * create list of all worksheets in the workbook ;
      create table _worksheets_all_ as
         select distinct
            scan(compress(memname,'''"'),1,"$") as memname length=32
         from
            dictionary.tables
         where
            upcase(libname) = "_XCEL_"
            and
            upcase(compress(memname,'''"')) like "%$%"
      ;

      * now filter the list ;
      %let _flag=0;
      create table _worksheets_ as
         select
            w.memname
         from
            _worksheets_all_ w

         %* an inner join will select those items in the _select_ table ;
         %if (%superq(select) ne ) %then %do;
            %if (not %index(%superq(select),%str(%())) %then %do;
               inner join
                  _select_ s
               on
                  upcase(w.memname)=upcase(s.memname)
            %end;
            %else %do;
               %sysfunc(ifc(&_flag=0,where,and)) prxmatch("/&select/io",strip(w.memname))
               %let _flag=1;
            %end;
         %end;

         %* use a left join and appropriate where clause to implement exclude functionality ;
         %if (%superq(exclude) ne ) %then %do;
            %if (not %index(%superq(exclude),%str(%())) %then %do;
               left join
                  _exclude_ e
               on
                  upcase(w.memname)=upcase(e.memname)
               where
                  e.memname is null
            %end;
            %else %do;
               %sysfunc(ifc(&_flag=0,where,and)) not prxmatch("/&exclude/io",strip(w.memname))
            %end;
         %end;

         %* keep the worksheets in the sorted order as specified in SELECT ;
         %if (%superq(select) ne ) %then %do;
            %if (not %index(%superq(select),%str(%())) %then %do;
               order by
                  s.sort
            %end;
         %end;
      ;

      * create the SHEETS macro variable ;
      select memname into :sheets separated by "^" from _worksheets_
      ;

      %if (&sqlobs eq 0) %then %let sheets=;
   quit;

   libname _XCEL_ clear;
%end;

%*---------------------------------------------------------------------
UNIX processing...
----------------------------------------------------------------------;
%else
%if (&os eq UNIX) %then %do;
   %* additional error checking ;
   %* On UNIX SELECT is a required parameter ;
   %parmv(SELECT,_req=1,_words=1,_case=N)

   %if (&parmerr) %then %do;
      %parmv(_msg=SELECT is a required parameter on UNIX)
      %goto quit;
   %end;

   %let sheets=&select;
%end;

%put;
%put sheets=&sheets;
%put;

%* now import all the desired worksheets via PROC IMPORT ;
%macro import;

   %*---------------------------------------------------------------------
   If an output dataset was specified, use it.
   If no output dataset was specified, translate the worksheet name into
   an acceptable output dataset name.
   ----------------------------------------------------------------------;
   %let outdsn=%qscan(%superq(out),&__iter__,^);

   %*---------------------------------------------------------------------
   If outdsn is blank, either OUT was not specified,
   or there is not a one-to-one correspondence with the SHEETS list.
   In either case, convert the sheet name to a valid SAS dataset name
   and append the LIB parameter
   ----------------------------------------------------------------------;

   %*---------------------------------------------------------------------
   WORK AROUND BUG IN PROC IMPORT on Unix, where PROC IMPORT sporadically
   creates WORK.WORK datasets, even though this does not match the OUT=
   PROC IMPORT parameter.  The workaround is to convert two-level names
   to one-level names, i.e. WORK.my_dataset to my_dataset.
   ----------------------------------------------------------------------;
   %if (&os eq UNIX) and (%upcase(&LIB) eq WORK) %then %let lib=;

   %if (&outdsn eq ) %then %do;
      %let outdsn=%sysfunc(translate(&word,%str(______),%str( ,#!-%%)));
   %end;
   %if (&lib ne ) %then %let outdsn=&lib..&outdsn;

   %* make sure output dataset name is not too long ;
   %let outdsn = %substr(%str(&outdsn                                   ),1,32);

   %let sheet=%left(%trim(&word));

   %* do not specify both SHEET= and RANGE= with PROC IMPORT ;
   %* It generates the warning "WARNING: SHEET name will be ignored if conflict occurs with RANGE name specified." ;
   %* Instead, concatenate the range onto the sheet name ;
   %if (&range ne ) and (&engine ne XLS) %then %do;
      %let sheet=&sheet$⦥
   %end;

   %* if range was specified and engine=XLS, parse to create STARTROW/COL & ENDROW/COL ;
   %* assume range was specified like A1:Z20 or A1 : Z20 ;
   %* no other error checking is done ;
   %if (&engine eq XLS) and (&range ne ) %then %do;
      %let rx=%sysfunc(prxparse(/([a-zA-Z]+)(\d+)\s*:\s*([a-zA-Z]+)(\d+)/o));
      %let rc=%sysfunc(prxmatch(&rx,&range));
      %let startcol=%sysfunc(prxposn(&rx,1,&range));
      %let startrow=%sysfunc(prxposn(&rx,2,&range));
      %let endcol  =%sysfunc(prxposn(&rx,3,&range));
      %let endrow  =%sysfunc(prxposn(&rx,4,&range));

      %syscall prxfree(rx);
   %end;

   %* if outdsn name is invalid prefix with an underscore ;
   %let rx = %sysfunc(prxparse(/[a-zA-Z_]/));
   %let rc = %sysfunc(prxmatch(&rx,%substr(&outdsn,1,1)));
   %syscall prxfree(rx);

   %if (&rc eq 0) %then %let outdsn = _&outdsn;

   proc import
      datafile="&file"
      out=&outdsn
      dbms=&engine replace;
      sheet="&sheet";
      %if (&startcol ne ) and (&engine eq XLS) %then %do;
         startcol=&startcol;
      %end;
      %if (&startrow ne ) and (&engine eq XLS) %then %do;
         startrow=&startrow;
      %end;
      %if (&endcol   ne ) and (&engine eq XLS) %then %do;
         endcol=&endcol;
      %end;
      %if (&endrow   ne ) and (&engine eq XLS) %then %do;
         endrow=&endrow;
      %end;
      %if (&getnames ne ) %then %do;
         getnames=&getnames;
      %end;
      %if (&mixed ne ) %then %do;
         mixed=&mixed;
      %end;
      %if (&scantext ne ) and (&engine ne XLS) %then %do;
         scantext=&scantext;
      %end;
      %if (&scantime ne )  and (&engine ne XLS) %then %do;
         scantime=&scantime;
      %end;
      %if (&usedate ne )  and (&engine ne XLS) %then %do;
         usedate=&usedate;
      %end;
      %if (&textsize ne ) %then %do;
         textsize=&textsize;
      %end;
      %if (&dbsaslabel ne ) %then %do;
         dbsaslabel=&dbsaslabel;
      %end;
   run;

   %* if errors, the likely cause is the user has the workbook open in Excel ;
   %if (&syserr ne 0) %then %do;
      %put;
      %put >>> Make sure &file is not already open in Excel <<<;
      %put;
   %end;

   %* were any post-processing options specified? ;
   %if (%length(%superq(where)%superq(if)&firstobs.&lastobs.&obs.%superq(keep)%superq(drop)%superq(rename)) gt 0) %then %do;  %* if any options were specified ;

      %subset_data(
         data=&syslast,
         out=&syslast,
         where=%str(&where),
         if=%str(&if),
         firstobs=&firstobs,
         lastobs=&lastobs,
         obs=%str(&obs),
         keep=%str(&keep),
         drop=%str(&drop),
         rename=%str(&rename)
      )
   %end;
%mend;

%if (%superq(sheets) ne ) %then %loop(%superq(sheets),dlm=^,mname=import);

%kill(delete=_select_ _exclude_ _out_ _worksheets_all_ _worksheets_)

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

%mend;

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


text:
/*=====================================================================
Program Name            : get_data_attr.sas
Purpose                 : Function style macro to return a dataset
                          attribute.
SAS Version             : SAS 9.1.3
Input Data              : N/A
Output Data             : N/A

Macros Called           : parmv

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

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

Scott Bass (sas_l_739@yahoo.com.au)

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

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

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

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

Modification History    : Original version

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

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

* create test dataset ;
data class (label="My Dataset Label");
   set sashelp.class;
run;

* introduce a time delay between creation and modification date ;
data _null_;
   rc=sleep(10);
run;

* now modify the dataset ;
data class;
   modify class;
   replace;
run;

%put %get_data_attr(class,label);
%put %sysfunc(putn(%get_data_attr(class,crdte),datetime.));
%put %sysfunc(putn(%get_data_attr(class,modte),datetime.));

%let crdte=%get_data_attr(class,crdte);
%put &crdte;
%put %sysfunc(datepart(&crdte),date.);
%put %sysfunc(timepart(&crdte),time.);

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

I have used positional rather than keyword parameters since positional
parameters are much more natural and easier to remember for this
function-style macro.

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

%macro get_data_attr
/*---------------------------------------------------------------------
Function style macro to return a dataset attribute.
---------------------------------------------------------------------*/
(DATA          /* Input dataset or view (REQ).                       */
,ATTR          /* Desired dataset attribute (REQ).                   */
               /* Either numeric or character attributes can be      */
               /* specified.  See code for valid attributes.         */
);

%local macro parmerr dsname attr_type return;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(DATA,         _req=1,_words=1,_case=N)
%parmv(ATTR,         _req=1,_words=0,_case=U,_val=

/* Numeric Attributes */
ALTERPW
ANOBS
ANY
VAROBS
ARAND
RANDOM
ARWU
AUDIT
AUDIT_DATA
AUDIT_BEFORE
AUDIT_ERROR
CRDTE
ICONST
INDEX
ISINDEX
ISSUBSET
LRECL
LRID
MAXGEN
MAXRC
MODTE
NDEL
NEXTGEN
NLOBS
NLOBSF
NOBS
NVARS
PW
RADIX
READPW
TAPE
WHSTMT
WRITEPW

/* Character Attributes */
CHARSET
ENCRYPT
ENGINE
LABEL
LIB
MEM
MODE
MTYPE
SORTEDBY
SORTLVL
SORTSEQ
TYPE
)

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

%* parse off any dataset options ;
%let dsname=%scan(%superq(data),1,%str(%());

%* additional error checking ;
%* does the dataset exist? ;
%if (^%sysfunc(exist(&dsname,data)) and ^%sysfunc(exist(&dsname,view))) %then %do;
   %parmv(_msg=&dsname does not exist)
   %goto quit;
%end;

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

%* but open the dataset with all specified options (esp. where clause) ;
%let dsid=%sysfunc(open(%superq(data)));

%* was the open successful? ;
%if (&dsid le 0) %then %do;
   %parmv(_msg=Unable to open %superq(data) dataset or view)
   %goto quit;
%end;

%* retrieve the desired attribute ;
%if (%sysfunc(indexw(
ALTERPW
ANOBS
ANY
VAROBS
ARAND
RANDOM
ARWU
AUDIT
AUDIT_DATA
AUDIT_BEFORE
AUDIT_ERROR
CRDTE
ICONST
INDEX
ISINDEX
ISSUBSET
LRECL
LRID
MAXGEN
MAXRC
MODTE
NDEL
NEXTGEN
NLOBS
NLOBSF
NOBS
NVARS
PW
RADIX
READPW
TAPE
WHSTMT
WRITEPW
,&attr
))) %then %let attr_type=N;
%else
%if (%sysfunc(indexw(
CHARSET
ENCRYPT
ENGINE
LABEL
LIB
MEM
MODE
MTYPE
SORTEDBY
SORTLVL
SORTSEQ
TYPE
,&attr
))) %then %let attr_type=C;

%let return=%sysfunc(attr&attr_type(&dsid,&attr));
%let dsid=%sysfunc(close(&dsid));

%unquote(&return)

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

%mend;

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

repo_name: scottbass/SAS

repo_language: SAS

mime_type: text/plain


text:
/*=====================================================================
Program Name            : dirlist.sas
Purpose                 : Creates a SAS dataset containing a directory
                          list of external files.
SAS Version             : SAS 9.3
Input Data              : N/A
Output Data             : N/A

Macros Called           : parmv

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

%dirlist(dir=C:\Windows\System32)

List files and directories in C:\Windows\System32,
creating the default dataset work.dirlist.

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

%dirlist(dir=C:\Windows\System32, data=sasuser.directory)

List files and directories in C:\Windows\System32,
creating the sasuser.directory dataset.

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

%dirlist(dir=C:\Windows\System32, type=f)

List files (only) in C:\Windows\System32,
creating the default dataset.

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

%dirlist(dir=C:\Windows\System32, type=d)

List directories (only) in C:\Windows\System32,
creating the default dataset.

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

%dirlist(dir=C:\Windows\System32, filter=basename=:'a' and ext='exe')

Filter output, saving only .exe files beginning with 'a'.

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

%dirlist(dir=C:\Windows\System32, filter=prxmatch("/^.*S$/i",strip(basename)))

Filter output, saving only directories and files whose basename ends in 'S'
(case-insensitive).

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

%dirlist(dir=C:\Windows\System32, type=f, filter=prxmatch("/^.*S$/i",strip(basename)))

Filter output, saving only files whose basename ends in 's'
(case-insensitive).

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

%dirlist(dir=C:\Does\Not\Exist)

Error checking: path does not exist.

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

%dirlist(dir=C:\Windows\explorer.exe)

Error checking: path exists but is not a directory.

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

This macro is most useful when ALLOWXCMD=N and o/s system commands are
disabled.

This macro does not currently support directory recursion.

The FILTER parameter must be a syntactically correct SAS if statement.

The TYPE parameter is just a specialized implementation of a FILTER.

Note that SAS does not return datetime information for a directory,
even though Windows explorer and the dir command do.

This macro was developed under Windows.
To see the file options SAS returns for your o/s, run this code,
then modify this macro as required:

   data _null_;
      length optname optvalue $200;
      fname="C:\Windows\explorer.exe"; * replace as required ;
      rc=filename('myfile',fname);
      fid=fopen('myfile');
      do i=1 to foptnum(fid);
         optname=foptname(fid,i);
         optvalue=finfo(fid,optname);
         put optname= optvalue=;
      end;
      rc=fclose(fid);
      rc=filename('myfile','');
   run;

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

%macro dirlist
/*---------------------------------------------------------------------
Creates a SAS dataset containing a directory list of external files.
---------------------------------------------------------------------*/
(DIR=          /* Directory path to list (REQ).                      */
,DATA=work.dirlist
               /* Name of output dataset (REQ).                      */
               /* Default value is work.dirlist.                     */
,TYPE=         /* Type of files to list (Opt).                       */
               /* Valid values are F or D (case-insensitive)         */
,FILTER=       /* Filter to apply to the directory list (Opt).       */
               /* If not specified, all files and directories are    */
               /* returned.                                          */
);

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

%* check input parameters ;
%parmv(DIR,          _req=1,_words=1,_case=N)
%parmv(DATA,         _req=1,_words=0,_case=N)
%parmv(TYPE,         _req=0,_words=0,_case=U,_val=F D)

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

%* additional error checking ;

%* does the directory exist? ;
%if not (%sysfunc(fileexist(&dir))) %then %do;
   %parmv(_msg=%str(ERR)OR: &dir does not exist)
   %goto quit;
%end;

%* no further error checking is done ;
%* the check for whether the path is a directory is done in the main code block ;
%* no checking is done on the syntax of the filter parameter ;

%* build the filter ;
%let temp=1;
%if (%superq(type) ne )   %then %let temp=%str(&temp and type="&type");
%if (%superq(filter) ne ) %then %let temp=%str(&temp and &filter);

data &data;
   length fullname $260 pathname $200 filename $100 basename $100 ext $10;
   length type $1 recfm $3 lrecl filesize createtime lastmodified 8;
   format filesize comma32. createtime lastmodified datetime21.;

   rc=filename('_dir_',"&dir",'','encoding="utf-8"');
   did=dopen('_dir_');
   if (did lt 1) then do;
      put "ERR" "OR: Unable to open &dir as a directory.";
      stop;
   end;

   do i=1 to dnum(did);
      call missing(of fullname pathname filename basename ext type recfm lrecl filesize createtime lastmodified);

      * name attributes ;
      fullname = catx('\',"&dir",dread(did,i));
      pos      = findc(fullname,'\',-999);
      pathname = substr(fullname,1,pos-1);
      filename = substr(fullname,pos+1);

      * extended file attributes ;
      fid=mopen(did,filename);
      type=ifc(fid > 0,'F','D');
      if (type='F') then do;
         basename       = scan(filename,-2,'.');
         ext            = scan(filename,-1,'.');
         %* processing if the file had no extension ;
         if (missing(basename)) then do;
            basename    = ext;
            call missing(ext);
         end;
         recfm          = finfo(fid,'RECFM');
         lrecl          = input(finfo(fid,'LRECL'),32.);
         filesize       = input(finfo(fid,'File Size (bytes)'),32.);
         createtime     = input(finfo(fid,'Create Time'),anydtdtm.);  %* anydtdtm to cope with regional settings ;
         lastmodified   = input(finfo(fid,'Last Modified'),anydtdtm.);
      end;
      else do;
         basename       = filename;
      end;
      fid=fclose(fid);
      if &temp then output;
   end;
   did=dclose(did);
   rc=filename('_dir_','');
   keep fullname pathname filename basename ext type recfm lrecl filesize createtime lastmodified;
run;

%quit:

%mend;

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


text:
/*=====================================================================
Program Name            : date_impute.sas
Purpose                 : Impute partial dates
SAS Version             : SAS 9.1.3
Input Data              : N/A
Output Data             : N/A

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 28JUL2010
Program Version #       : 1.0

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

Scott Bass (sas_l_739@yahoo.com.au)

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

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

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

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

Modification History    : Original version

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

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

data input_mm_format;
   length date 8 y m d $4;
   format date date9.;
   input  date yymmdd8. y m d;
   datalines;
20100728 2010  07    28
.        2010  07    UK
.        2010  UNK   28
.        2010  UK    UNK
.        UNK   07    28
.        UNK   07    UNK
.        UNK   UNK   UNK
;
run;

data output_mm_format;
   set input_mm_format;

   %date_impute(
      in_date=date
      ,in_y=y
      ,in_m=m
      ,in_d=d
      ,out_date=date_imputed
      ,out_flag=date_imputed_flag
      ,imp_y=year(date())
      ,imp_m=1
      ,imp_d=1
      ,month_fmt=mm
   );

   length date_imputed_flagc $3;
   date_imputed_flagc=ifc(date_imputed_flag,"Yes","No");
   format date_imputed is8601da.;
run;

Impute dates
When the input date variable DATE is missing
Where Y, M, and D are input character variables for year, month, and day
And month format is MM (01, 02, etc.)
Creating the output variable date_imputed
And also creating the output variable date_imputed_flag (SAS boolean value)
Using the rule "year(date())" for the imputed Year value
Using the rule "1" for the imputed Month value (this is the default)
Using the rule "1" for the imputed Day value (this is the default)
Derive the date_imputed_flagc variable in a post-processing step
And override the default output date format of date9 with is8601da.;

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

data output_mm_format;
   set input_mm_format;

   * simulate an end date value in the source data ;
   end_date="01FEB2222"d;
*  end_date="01NOV2123"d;  * to test ifn logic ;

   %date_impute(
      in_date=
      ,in_y=y
      ,in_m=m
      ,in_d=d
      ,out_date=date_imputed
      ,out_flag=date_imputed_flag
      ,imp_y=.
      ,imp_m=ifn(month(end_date) le 8,1,9)
      ,imp_d=15
      ,month_fmt=mm
   );

   * if the date was imputed, implement these additional rules ;
   * if year was unknown, date cannot be imputed (remains missing) ;
   * if month and day were both unknown, set to end of year ;
   * if day was unknown, set to end of month ;
   if date_imputed_flag then do;
      if (not missing(date_imputed)) then do;
         if (__M_error and __D_error) then
            date_imputed=intnx("year",date_imputed,0,"E");
         else
         if (__D_error) then
            date_imputed=intnx("month",date_imputed,0,"E");
      end;
   end;
run;

Unconditionally impute dates (in_date parameter is missing)
Where Y, M, and D are input character variables for year, month, and day
And month format is MM (01, 02, etc.)
Creating the output variable date_imputed
And also creating the output variable date_imputed_flag (SAS boolean value)
Using the rule "." (missing) for the imputed Year value (this is the default)
Using the rule "ifn(month(end_date le 6),1,7)" for the imputed Month value
Using the rule "15" for the imputed Day value.
And post-processing the results to implement additional, more complex
   imputation rules.

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

data input_mon_format;
   length date 8 y m d $4;
   input  date yymmdd8. y m d;
   datalines;
20100728 2010  JUL   28
.        2010  JUL   UK
.        2010  UNK   28
.        2010  UK    UNK
.        UNK   JUL   28
.        UNK   JUL   UNK
.        UNK   UNK   UNK
;
run;

data output_mon_format;
   set input_mon_format;

   %date_impute(
      in_date=date
      ,in_y=y
      ,in_m=m
      ,in_d=d
      ,out_date=date_imputed
      ,out_flag=date_imputed_flag
      ,imp_y=year(date())
      ,month_fmt=mon
   );

run;

Same output as first example, using input data where the format of the
Month variable is mon (Jan, FEB, Mar, etc.) instead of mm (01, 02, etc.)
Default values imp_m=1 and imp_d=1 were used.

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

This macro must be called within a data step.

This macro resets the automatic data step error flag _error_.
Therefore, you should call this macro:
   1) Near the top of your data step, or
   2) In a dedicated data step where only date imputations are calculated.
Otherwise, this macro's resetting of the data step error flag variable
could mask other errors created by code outside this macro.

SAS's automatic putting of _all_ data step variables when _error_=1
takes place at the end of the data step (when "run;" is encountered)
so you are "safe" if you call this macro near the top of the data step.
Downstream code after this macro may still set the _error_ variable.

Code fragments can be used for the imputation rules as long as they
resolve to a syntactically correct assigment statement.
For example, from the above test cases:

   if missing(__Y) then __Y=year(date());
   if missing(__Y) then __Y=year(end_date);
   if missing(__M) then __M=ifn(month(end_date le 8),1,9);

If your required imputation rules cannot be specified per the above
requirements, you can still call this macro, then implement your own
imputation rules in post-processing using the derived variables
__Y, __M, __D.

OUT_DATE, IMP_Y, IMP_M, and IMP_D are all optional parameters.
However, if any of these parameters are blank, no imputation is done
other than creating the __Y, __M, and __D variables for further
post-processing after this macro.

By default no date imputation is done if the Year input variable
is missing (since the default value for IMP_Y=.).

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

%macro date_impute
/*---------------------------------------------------------------------
Impute partial dates
---------------------------------------------------------------------*/
(IN_DATE=      /* Input date variable (Opt).                         */
               /* If specified, dates are imputed only if the date   */
               /* variable is not missing.  This variable is often a */
               /* SAS date variable, but could also be a date        */
               /* character string, where the same logic above still */
               /* applies.                                           */
,IN_Y=         /* Input Year variable (REQ).                         */
               /* Should be a character variable,                    */
               /* use "put(year,best.-L)" to convert.                */
,IN_M=         /* Input Month variable (REQ).                        */
               /* Should be a character variable,                    */
               /* use "put(month,best.-L)" to convert.               */
,IN_D=         /* Input Day variable (REQ).                          */
               /* Should be a character variable,                    */
               /* use "put(day,best.-L)" to convert.                 */
,OUT_DATE=     /* Output imputed date variable (Opt).                */
               /* No format is applied, apply format in calling code.*/
,OUT_FLAG=     /* Output imputed date flag variable (Opt).           */
               /* If specified, the flag variable is created         */
               /* indicating if the date was imputed.                */
,IMP_Y=.       /* Imputation rule for Year variable (Opt).           */
               /* The default value is "." (missing) which means by  */
               /* default no date imputation is done if the IN_Y     */
               /* value is not a valid numeric value.                */
,IMP_M=1       /* Imputation rule for Month variable (Opt).          */
,IMP_D=1       /* Imputation rule for Day variable (Opt).            */
,MONTH_FMT=MM  /* Input Month variable format (REQ).                 */
               /* Valid values are MM (01, 02, etc.) or MON          */
               /* (Jan, Feb, etc.).  Default is MM.                  */
);

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

%* check input parameters ;
%_parmv(IN_DATE,     _req=0,_words=0,_case=U)
%_parmv(IN_Y,        _req=1,_words=0,_case=U)
%_parmv(IN_M,        _req=1,_words=0,_case=U)
%_parmv(IN_D,        _req=1,_words=0,_case=U)
%_parmv(OUT_DATE,    _req=0,_words=0,_case=N)
%_parmv(OUT_FLAG,    _req=0,_words=0,_case=N)
%_parmv(IMP_Y,       _req=0,_words=1,_case=N)
%_parmv(IMP_M,       _req=0,_words=1,_case=N)
%_parmv(IMP_D,       _req=0,_words=1,_case=N)
%_parmv(MONTH_FMT,   _req=1,_words=0,_case=U,_val=MM MON)

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

%* check if OUT_DATE, IMP_Y, IMP_M, or IMP_D are blank ;
%let no_impute=%eval((%superq(out_date) eq ) or (%superq(imp_y) eq ) or (%superq(imp_m) eq ) or (%superq(imp_d) eq ));

* create working variables ;
attrib __Y        length=8  label="Derived Year Value";
attrib __M        length=8  label="Derived Month Value";
attrib __D        length=8  label="Derived Day Value";

attrib __Y_error  length=8  label="Derived Year Error Flag";
attrib __M_error  length=8  label="Derived Month Error Flag";
attrib __D_error  length=8  label="Derived Day Error Flag";

* derive working variables ;
__Y=input(&in_y, ? best.);
__Y_error=_error_;
_error_=0;

%* if MONTH_FORMAT is MON, use the MONYY. informat and a dummy year value to derive the month number ;
%if (&month_fmt eq MON) %then %do;
   __M=input(cats(&in_m,"9999"), ? monyy.);
   __M_error=_error_;
   if not missing(__M) then __M=month(__M);
   _error_=0;
%end;
%else %do;
   __M=input(&in_m, ? best.);
   __M_error=_error_;
   _error_=0;
%end;

__D=input(&in_d, ? best.);
__D_error=_error_;
_error_=0;

%* if imputation flag variable is desired, derive it now before applying imputation rules ;
%if (&out_flag ne ) %then %do;
   * derive imputation flag variable ;
   &out_flag=(nmiss(__Y,__M,__D) ne 0);
%end;

%* if no imputation is desired, bail out ;
%if (&no_impute) %then %goto quit;

* apply partial date imputation rules ;

%* if IN_DATE is specified, only impute dates if IN_DATE is missing ;
%* otherwise unconditionally impute dates ;
%if (&in_date ne ) %then %do;
if missing(&in_date) then do;
%end;
   if missing(__Y) then __Y=&imp_y;
   if missing(__M) then __M=&imp_m;
   if missing(__D) then __D=&imp_d;

   * derive imputed date ;
   if (nmiss(__M,__D,__Y) eq 0) then &out_date=mdy(__M,__D,__Y);
   format &out_date date9.;
%if (&in_date ne ) %then %do;
end;
%end;

%quit:

%mend;

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


text:
%** V0000 *****************************************************************************************;
%** Program   : log2pdf.sas                                                                      **;
%** On LAN    : No                                                                               **;
%** On Server : Yes                                                                              **;
%** Date      : 17AUG2012                                                                        **;
%** Author    : Michael Dixon                                                                    **;
%** Owner     : BCA                                                                              **;
%** Purpose   : Convert SAS Log file into a PDF file including simple Syntax Highlighting        **;
%**                                                                                **;
%** Parms     : LOGFILE   - Required, full path and filename of SAS log file to convert          **;
%**           : PDFFILE   - Required, full path and filename of PDF file to create               **;
%** ---------------------------------------------------------------------------------------------**;
%** History                                                                                **;
%**                                                                                **;
%** Version   :                                                                                **;
%** Date      :                                                                                **;
%** Author    :                                                                                **;
%** Reason    :                                                                                **;
%**                                                                                **;
%**                                                                                **;
%**************************************************************************************************;
%macro log2pdf(LOGFILE= /* Full path and filename of log file */
              ,PDFFILE= /* Full path and filename of PDF to create */
              );

  filename inlog "&LOGFILE";
  data mylog;
    infile inlog truncover;
    length line $ 1024;
    label line="";
    input;
    line=_infile_;
    * Start of Page Special Character ;
    if _N_=1 or substr(line,1,1)="" or line=" " then delete;
  run;
  filename inlog clear;

  title "%SCAN(&LOGFILE,-1,%str(\))";
  footnote '';

  options linesize=132 nonumber nodate;

  ods pdf uniform file="&PDFFILE" style=minimal notoc;

  proc report data=MYLOG nowd style(REPORT)={rules=NONE} style(REPORT)={cellpadding=0};
    column line;
    define line / display;
    compute line;
      if substr(line,1,6)="ERROR:"   then call define(_col_,"style","style={background=red}");
      else
      if substr(line,1,8)="WARNING:" then call define(_col_,"style","style={foreground=green}");
      else
      if substr(line,1,5)="NOTE:"    then call define(_col_,"style","style={foreground=blue}");
    endcomp;
  run;

  ods pdf close;
%mend;


text:
/*====================================================================
Program Name            : optload.sas
Purpose                 : Loads SAS options for an options dataset
SAS Version             : SAS 9.1.3
Input Data              : SAS options dataset
Output Data             : None

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:

%optload;
   Restores options settings from the default work.options dataset.

%optload(data=sasuser.myoptions);
   Restores options settings from the sasuser.myoptions dataset.

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

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

PROC OPTLOAD 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.

An error check is done to check if the data= dataset exists, but no
further error checking is done, for example feeding an improperly
formatted dataset to PROC OPTLOAD.

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 optload
/*--------------------------------------------------------------------
Loads SAS options for an options dataset
--------------------------------------------------------------------*/
(DATA=options  /* Source dataset to contain desired options (REQ).  */
);

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

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

%* check if the dataset exists ;
%if (^%sysfunc(exist(&data))) %then
%parmv(_msg=&data dataset does not exist);

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

%* restore SAS system options from the options dataset,
%* see documentation for PROC OPTLOAD for more details ;
proc optload data=&data;
run;

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

%mend;

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


text:
%macro format_text(left,center,right,length=80);
   %* initialize buffer with text that will not be in the data ;
   %* (technically that does not matter but it is still best practice) ;
   %let buffer=%str(~~~);

   %* left ;
   %let buffer=&buffer%left(&left);

   %* pad buffer with spaces so downstream %substr() calls will not fail ;
   %let buffer=%str(&buffer                                                                        );
   %*put @&buffer@;

   %* center ;
   %let pos=%eval((&length - %length(&center))/2);
   %*put &=pos;
   %let buffer=%qsubstr(&buffer,1,&pos+2)&center;
   %let buffer=%str(&buffer                                                                        );
   %*put #&buffer#;

   %* right ;
   %let pos=%eval(&length - %length(&right));
   %*put &=pos;
   %let buffer=%qsubstr(&buffer,1,&pos+2)&right;
   %let buffer=%str(&buffer                                                                        );
   %*put $&buffer$;

   %* trim ;
   %let buffer=%qsubstr(&buffer,4,&length);

   %* return the buffer ;
%trim(&buffer)
%mend;


text:
/*====================================================================
Program Name            : hash_define.sas
Purpose                 : Defines a hash object for later lookup.
SAS Version             : SAS 9.1.3
Input Data              : SAS input dataset
Output Data             : None

Macros Called           : parmv, seplist

Originally Written by   : Scott Bass
Date                    : 12MAY2010
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                    : 22JUL2011
Change/reason           : Added support for dataset options for hash
                          object since they are now supported in
                          SAS 9.2
Program Version #       : 1.1

Programmer              : Scott Bass
Date                    : 15AUG2011
Change/reason           : Added multidata option.
Program Version #       : 1.2

Programmer              : Scott Bass
Date                    : 04FEB2016
Change/reason           : Added hashname option.
Program Version #       : 1.3

Programmer              : Scott Bass
Date                    : 30MAY2016
Change/reason           : Added duplicate option.
Program Version #       : 1.4

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

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

data source;
   length key1 key2 svar1 svar2 $1;
   input  key1 key2 svar1 svar2;
   datalines;
E  F  1  2
A  B  3  4
C  D  5  6
;
run;

data lookup;
   length key1 key2 lvar3 lvar4 dontwant $1;
   input  key1 key2 lvar3 lvar4 dontwant;
   datalines;
C  D  7  8  X
E  F  9  0  X
;
run;

* "Standard" lookup ;
data joined;
   * set PDV order (optional) ;
   if 0 then set source;

   * initialize &_hashnum_ to zero then declare hash objects ;
   %let _hashnum_=0;
   %hash_define(data=lookup, keys=key1 key2, vars=lvar3);
   %hash_define(data=lookup, keys=key1,      vars=lvar4);  * multiple hashes can be declared ;

   set source;
   %hash_lookup;

   if _rc_h1 ne 0 then put "Lookup failed for hash #1 " key1= key2=;
   if _rc_h2 ne 0 then put "Lookup failed for hash #2 " key1= key2=;
   if sum(of _rc_h:) ne 0 then put "Some lookup failed, generic error processing";

   if (_n_ = 3) then do;
      %hash_lookup(hashnum=2);
      put key1= key2= lvar4=;
   end;

*  drop _rc_h:;  * optional ;
run;

proc print;
run;

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

* "Standard" lookup with renames ;
data joined;
   * initialize &_hashnum_ to zero then declare hash objects ;
   %let _hashnum_=0;
   %hash_define(data=lookup, keys=key1 key2, vars=lvar3 lvar4, rename=key1=foo key2=bar lvar3=blah lvar4=blech);

   set source (keep=key1 key2 rename=(key1=foo key2=bar));
   %hash_lookup;
*  drop _rc_h:;  * optional ;
run;

proc print;
run;

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

* Multidata lookup - better source/lookup example datasets ;
data lookup;
  set
    sashelp.class
    sashelp.class
    sashelp.class
  ;
run;

proc sort;
  by name;
run;

* build dummy date ranges ;
data lookup;
  set lookup;
  date=(_n_-1)*3;
  format date date7.;
run;

* Multidata lookup with single %hash_lookup macro call ;
data joined;
  * set PDV order (optional) ;
  if 0 then set lookup;

  * initialize &_hashnum_ to zero then declare hash objects ;
  %let _hashnum_=0;
  %hash_define(data=lookup, keys=name, vars=_all_, multidata=Y)

  set sashelp.class (keep=name);
  %hash_lookup(hashnum=1, lookup="01JAN60"d le date le "15JAN60"d, link=derive, return=Y)

  * some dummy derivations for illustration only ;
  derive:
    height=height*1000;
    weight=age*1000;
    new=date;
    format new yymmddd.;
  return;

*  drop _rc_h:;  * optional ;
run;

proc print;
run;

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

* Multidata lookup with multiple %hash_lookup macro calls ;
* (for code generation illustration only - normally different lookup datasets would be used) ;
data joined;
  * set PDV order (optional) ;
  if 0 then set lookup;

  * initialize &_hashnum_ to zero then declare hash objects ;
  %let _hashnum_=0;
  %hash_define(data=lookup, keys=name, vars=_all_, multidata=Y)
  %hash_define(data=lookup, keys=name, vars=_all_, multidata=Y)

  set sashelp.class (keep=name);
  %hash_lookup(hashnum=1, lookup="01JAN60"d le date le "15JAN60"d, link=derive, return=N)
  %hash_lookup(hashnum=2, lookup=age le 12,                        link=derive, return=Y)

  * some dummy derivations for illustration only ;
  derive:
    height=height*1000;
    weight=age*1000;
    new=date;
    format new yymmddd.;
  return;

*  drop _rc_h:;  * optional ;
run;

proc print;
run;

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

* Hash names used instead of hash numbers ;
data joined;
  * set PDV order (optional) ;
  if 0 then set lookup;

  %let _hashnum_=0;
  %hash_define(hashname=foo, data=lookup, keys=name, vars=_all_)

  * This next invocation does not specify an explicit name, ;
  * so its name is the default name _h2 ;
  %hash_define(              data=lookup, keys=name, vars=_all_)

  %hash_define(hashname=_bar,data=lookup, keys=name, vars=_all_)

  * At this point, &_hashname_=foo | _h2 | _bar ;

  set sashelp.class (keep=name);

  * even though a hash name was used, the default hash_lookup ;
  * will process all hash objects ;
  %hash_lookup;

  * or you can process by number ;
  %hash_lookup(hashnum=1)
  %hash_lookup(hashnum=2)
  %hash_lookup(hashnum=3)

  * or you can process by name (in any order) ;
  %hash_lookup(hashname=_bar)
  %hash_lookup(hashname=foo)

  if _rc_foo ne 0 then put "Lookup failed for hash foo " key1= key2=;
  if _rc_bar ne 0 then put "Lookup failed for hash bar " key1= key2=;

*  drop _rc_:;  * optional ;
run;

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

Set &_hashnum_ = 0 before calling this macro for the first time.
&_hashnum_ is incremented each time this macro is called.

(Technically you do not have to set _hashnum_ in the calling program,
 but it will get created on the first %hash_define invocation,
 and incremented each time %hash_define is called.
 This would be a problem in a development environment like EG or DMS,
 but would not be an issue in batch processing.
 Do yourself a favor and just set _hashnum_=0 in the calling program.)

You do not need to explicitly set &_hashname_.
It is set to blank when &_hashnum_=1, and appended to each time
%hash_define is called.

Neither the source nor the lookup datasets need to be sorted.

Each time the find() command executes (%hash_lookup), the current
key values in the PDV are used to lookup the corresponding record in
the lookup dataset(s).  If found, the satellite variables are assigned
in the PDV.

The default hash object name is _h1, _h2, ..., _h<n>, where "n"
is the number of times %hash_define was called.

If the HASHNAME parameter is specified, that name is used for the
hash object, and for the return code set by %hash_lookup.

The default return code variable is _rc_h1, _rc_h2,..., _rc_n<n>.
If the HASHNAME parameter was specified in %hash_define, the
return code variable is named _rc_<hashname>.  If the hashname
contains a leading underscore, it is removed from the return
code variable.

For example, if the HASHNAME parameter = _myhash,
the generated code will be
_rc_myhash = _myhash.find(), instead of
_rc__myhash = _myhash.find().

The return code indicates whether the lookup was successful,
and is similar to IN= dataset option on a merge.

If the VARS parameter is not specified, then no additional variables
are joined with the master dataset.  Typically this is used when the
only thing you are interested in is the existence of the key variables
in the lookup dataset.  This is similar to a data step merge with no
additional variables other than the keys, and processing the IN=
dataset option.  Check the value _rc_h# = 0 for successful key lookup.

If the VARS parameter is set to _ALL_, then the ALL parameter is
specified in the hash object declaration, and ALL variables from the
lookup dataset are joined with the master dataset.

If the RENAME parameter is specified, it must be specified as
physical_name1=virtual_name1 physical_name2 = virtual_name2, etc.
Spaces are allowed between the equals sign and the oldname/newname
variable pairs.

If the RENAME parameter is specified, the KEYS and VARS parameters must
be specified from the perspective of the virtual names, i.e. the
renamed variables.

By default, the first key in the load dataset is added to the hash
object, and all other keys are ignored.
If REPLACE=R, then the last key in the load dataset is added to the hash object.
If REPLACE=E, then duplicate keys in the load dataset will generate an error.

If MULTIDATA=Y, then multiple values of the keys are permitted in the
lookup hash object.  Otherwise, only the first occurence of each key
value will be added to the lookup hash object.

If MULTIDATA=Y, you would usually pair this with a LOOKUP=<subsetting
criteria> %hash_lookup macro call.  See usage example above for details.

The hash lookup is a lookup/join, not a merge.  It does NOT merge the
additional observations from the lookup dataset. The resulting number
of observations will match the number of observations in the source
dataset.

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

%macro hash_define
/*--------------------------------------------------------------------
Defines a hash object for later lookup
--------------------------------------------------------------------*/
(DATA=         /* Lookup dataset (Opt).                             */
               /* If specified, the lookup dataset loads the hash   */
               /* object.  If not specified, an empty hash object   */
               /* is created.                                       */
,KEYS=         /* Lookup keys (REQ).                                */
,VARS=         /* Lookup satellite variables (Opt).                 */
               /* Default is no additional variables from the lookup*/
               /* dataset.  Specify _ALL_ to return all variables   */
               /* from the lookup dataset.                          */
,RENAME=       /* Rename lookup keys or satellite variables (Opt).  */
               /* If specified, then specify as oldname1=newname1   */
               /* oldname2=newname2 etc.                            */
,WHERE=        /* Where clause to apply to the lookup dataset(Opt). */
,KEEP=         /* Additional keep variables (Opt).                  */
               /* KEYS= and VARS= variables are automatically kept, */
               /* specify KEEP= if you have variables needed for a  */
               /* WHERE= clause that are neither key nor satellite  */
               /* variables.                                        */
,ORDERED=N     /* Store lookup table in sorted order? (REQ).        */
               /* Valid values are N=none, A=ascending, D=descending*/
               /* (case insensitive).                               */
               /* Default=none.                                     */
,MULTIDATA=N   /* Multiple key values allowed in the lookup hash    */
               /* object? (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.                             */
,REPLACE=      /* Replace keys that have already been loaded into   */
               /* the hash object? (Opt).                           */
               /* Default value is <blank>, the first key is loaded */
               /* and all other keys are ignored.                   */
               /* If REPLACE=R | REPLACE, duplicate keys overwrite  */
               /* previous keys, i.e. the last key is loaded.       */
               /* If REPLACE=E | ERROR, duplicate keys will         */
               /* generate an error.                                */
,HASHNAME=     /* Explicit hash object name (Opt.)                  */
               /* If not specified, the default name                */
               /* _h<hashnum> is used.                              */
);

%local macro parmerr keep rx hn;
%let macro = &sysmacroname;

%* check input parameters ;
%parmv(DATA,         _req=0,_words=0,_case=N)
%parmv(KEYS,         _req=1,_words=1,_case=N)
%parmv(VARS,         _req=0,_words=1,_case=N)
%parmv(RENAME,       _req=0,_words=1,_case=N)
%parmv(WHERE,        _req=0,_words=1,_case=N)
%parmv(KEEP,         _req=0,_words=1,_case=N)
%parmv(ORDERED,      _req=0,_words=0,_val=A D N)
%parmv(MULTIDATA,    _req=1,_words=0,_case=U,_val=0 1)
%parmv(REPLACE,      _req=0,_words=0,_case=U,_val=R REPLACE E ERROR)
%parmv(HASHNAME,     _req=0,_words=0,_case=N)

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

%* we need to transform the keys and vars variables expressed as ;
%* the physical variable names into the renamed variables ;
%* keys and vars must match the actual variable names ;
%if (%upcase(&vars) ne _ALL_) %then %let keep=&keys &vars &keep;

%* remove spaces around equals sign ;
%let rx=%sysfunc(prxparse(s/\s*=\s*/=/));
%let rename=%sysfunc(prxchange(&rx,-1,&rename));
%syscall prxfree(rx);

%* convert the keep variables to the physical names ;
%macro _convert_;
  %let rx=%sysfunc(prxparse(s/%scan(&word,2,=)/%scan(&word,1,=)/i));
  %let keep=%sysfunc(prxchange(&rx,-1,&keep));
  %syscall prxfree(rx);
%mend;
%loop(%superq(rename),mname=_convert_)

%* declare _hashnum_ as a global variable for use in %hash_lookup macro ;
%if (^%symexist(_hashnum_)) %then %do;
  %global _hashnum_;
  %let _hashnum_=0;
%end;

%* declare _hashname_ as a global variable for use in %hash_lookup macro ;
%if (^%symexist(_hashname_)) %then %do;
  %global _hashname_;
  %let _hashname_= ;
%end;

%* increment _hashnum_ ;
%let _hashnum_ = %eval(&_hashnum_+1);

%* if this is the first iteration set _hashname_ to blank ;
%if (&_hashnum_ eq 1) %then %let _hashname_ = ;

%* if an explicit hash name was specified use it, ;
%* otherwise set the default hash name of _h<hashnum> ;
%if (&hashname eq ) %then
   %let hn=_h&_hashnum_;
%else
   %let hn=&hashname;

%* append the hashname to &_hashname_ with a pipe (|) delimiter ;
%if (&_hashnum_ gt 1) %then %let _hashname_ = &_hashname_ |;
%let _hashname_ = &_hashname_ &hn;

%* build dataset options ;
%local options;

%if (%superq(keep)%superq(rename)%superq(where) ne ) %then
  %let options=%str(%();
%if (%superq(keep)    ne ) %then
  %let options=%superq(options) keep=%superq(keep);
%if (%superq(rename)  ne ) %then
  %let options=%superq(options) rename=(%superq(rename));
%if (%superq(where)   ne ) %then
  %let options=%superq(options) where=(%superq(where));
%if (%superq(options) ne ) %then
  %let options=%superq(options) %str(%));
%let options=%unquote(&options);

%* declare hash object(s) on first iteration of the datastep ;
if (_n_ = 1) then do;
  %if (&data ne ) %then %do;
  %* set the variable attributes, but do not read any records ;
  if 0 then set &data &options;
  %end;

  %* declare the hash object ;
  declare hash &hn (
    %if (&data ne ) %then %do;
    dataset: "&data &options" ,
    %end;
    hashexp: 16
    , ordered: "&ordered"
    %if (&replace eq R or &replace eq REPLACE) %then %do;
    , duplicate: "R"
    %end;
    %else
    %if (&replace eq E or &replace eq ERROR) %then %do;
    , duplicate: "E"
    %end;
    %if (&multidata) %then %do;
    , multidata: "Y"
    %end;
  );

  %* define keys and satellite variables ;
  &hn..defineKey(%seplist(&keys,nest=QQ));

  %if (%upcase(&vars) eq _ALL_) %then %do;
    &hn..defineData(ALL: "YES");
  %end;
  %else
  %if (&vars ne ) %then %do;
    &hn..defineData(%seplist(&vars,nest=QQ));
  %end;

  %* end hash declaration ;
  &hn..defineDone();
end;

%* explicitly set hash variables to missing so they are not ;
%* retained across lookups if a lookup fails ;
%if (&vars ne ) %then %do;
   call missing(of &vars);
%end;

%quit:

%mend;

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


text:
/*=====================================================================
Program Name            : create_datetime_range.sas
Purpose                 : Create min and max datetime values for a
                          given period
SAS Version             : SAS 9.1.3
Input Data              : N/A
Output Data             : N/A

Macros Called           : parmv

Originally Written by   : Scott Bass
Date                    : 23OCT2007
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_datetime_range;
   Creates &dt_beg and &dt_end macro variables with period of DAY
   based on the value of the DATETIME() function

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

%create_datetime_range(,MONTH);
   Creates &dt_beg and &dt_end macro variables with period of MONTH
   based on the value of the DATETIME() function

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

%create_datetime_range(25DEC2007);
   Creates &dt_beg and &dt_end macro variables with period of DAY
   based on the value of 25DEC2007.  Uses the anydtdtm informat
   to convert the date to a datetime value.

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

%create_datetime_range(20071020);
   Creates &dt_beg and &dt_end macro variables with period of DAY based
   on the value of 20071020 (20OCT2007).  Uses the anydtdtm informat to
   convert the date to a datetime value.

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

%create_datetime_range(01JAN1960:12:34:56);
   Creates &dt_beg and &dt_end macro variables with period of DAY based
   on the value of 01JAN1960:12:34:56.  Uses the anydtdtm informat to
   convert the date to a datetime value.

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

%let datetime=%sysfunc(datetime());
%create_datetime_range(&datetime)
   This will NOT WORK.  The datetime parameter must be a datetime
   *string*, not datetime *value*.

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

%let datetime=%sysfunc(datetime(),datetime20.);
%create_datetime_range(&datetime)
   But this *would* work.

If the data is specified as YYYYMM, you need to suffix that with a day,
i.e. 01, since 200709 is ambiguous, and would be interpretted as
20JUL2009.

For example:

data month_key;
   month_key = "200709";
run;

* create a macro variable ;
proc sql noprint;
   select month_key into :month_key separated by " " from month_key;
quit;

* now create datetime ranges ;
%create_datetime_range(&month_key.01);

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

I could have also specified an informat parameter, but felt that the
anydtdtm informat meets all our needs.  This will cover any date /
datetime string of the format DATE, DATETIME, DDMMYY, JULIAN, MMDDYY,
MONYY, TIME, YYMMDD, or YYQ.

Note that you CANNOT specify an actual datetime value, since the
anydtdtm informat is hard coded.  IOW, if you have a macro variable
containing a date or datetime value, you'll first need to use a
date(time) format to convert it to a string of the above form.

If any error occurs, the macro variables will be unset.  This ensures
a warning in the calling program, which will call attention to
fixing the problem.

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

%macro create_datetime_range
/*---------------------------------------------------------------------
Create min and max datetime values for a given time period
---------------------------------------------------------------------*/
(DATETIME      /* Input datetime (Opt).                              */
               /* If not specified, DATETIME() function is used.     */
,PERIOD        /* Datetime period (Opt).                             */
               /* If not specified, DAY is used.                     */
               /* Valid values are D DAY M MONTH and are case-       */
               /* insensitive.                                       */
);

%* unset macro variables from previous macro invocations ;
%symdel dt_beg dt_end / nowarn;

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

%* set default period if it was not specified ;
%if (&period eq ) %then %let period = D;

%* check input parameters ;
%parmv(PERIOD,       _req=1,_words=0,_case=U,_val=D DAY M MONTH)

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

%global dt_beg dt_end;

data _null_;
   if "&datetime" = "" then
      datetime = datetime();
   else
      datetime = input("&datetime",anydtdtm.);

   select (substr("&period",1,1));
      when("D") period = "dtday";
      when("M") period = "dtmonth";
   end;

   dt_beg = put(intnx(period,datetime,0,"B"),datetime20.-L);
   dt_end = put(intnx(period,datetime,0,"E"),datetime20.-L);

   call symput("dt_beg",trim(dt_beg));
   call symput("dt_end",trim(dt_end));
   call symput("_error_",put(_error_,best.-L));
run;

%if (&_error_ ne 0) %then %symdel dt_beg dt_end / nowarn;

%quit:

%mend;

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


text:
options ls=max;  * so the SAS log lines do not wrap ;
options mprint;  * print macro generated code ;

* create source data ;
data source (index=(acct));
  length acct parent $3 amount 8;
  input acct parent amount;
  datalines;
A1  .   10
A2  A1  20
A3  A2  30
A4  A2  20
A5  A1  10
A6  A5  10
A7  .   10
A8  A7  20
A9  A7  30
A10 A9  20
;
run;

%macro code;
* option 1: index key lookup to derive top-level parent ;

* use recursion to get the top level parent ;
/* Note: This is my first version...I prefer the second approach
data option1;
  set source;
  temp=acct;
  do while (1);
    if missing(parent) then do;
      parent=acct;
      acct=temp;
      leave;
    end;
    acct=parent;
    set source (drop=amount) key=acct / unique;
  end;
  _error_=0;
  drop temp;
run;
*/

data option1;
  set source;
  _acct=acct;
  _parent=parent;
  do while (not missing(parent));
    acct=parent;
    set source (drop=amount) key=acct / unique;
  end;
  toplevel=acct;
  acct=_acct;
  parent=_parent;
  _error_=0;
  drop _:;
run;

proc summary data=option1 nway noprint;
  class toplevel;
  var amount;
  output out=sum1 sum=;
run;

* option 2: but, even better is to derive the hierarchy ;
* there are many ways to do this, this is just one example ;
data option2;
  set source;
  length hier $200;  * buffer variable, could be 32K long max ;
  _acct=acct;
  _parent=parent;
  do while (not missing(parent));
    hier=catx("|",acct,hier);
    acct=parent;
    set source (drop=amount) key=acct / unique;
  end;
  hier=catx("|",acct,hier);
  acct=_acct;
  parent=_parent;
  _error_=0;
  drop _:;
run;

* I could "overallocate" an array below (create say 20 levels) and "clean up" (delete columns) later. ;
* Instead, process the dataset to get the max levels ;
%global max_hier;
%let max_hier=;
proc sql noprint;
  select max(countw(hier,"|")) into :max_hier separated by " " from option2;
quit;
%put &=max_hier;

* create a dimension dataset for the accounts ;
* Note: Lev3 (the lowest level) is the same as the accts ;
data dim_acct;
  length Lev1-Lev&max_hier $3;
  set option2;
  array lev{*} Lev:;
  do i=1 to dim(lev);
    lev{i}=scan(hier,i,"|");
    if missing(lev{i}) then lev{i}=lev{i-1}; * mimic coalesce function here instead of the view ;
  end;
  keep Lev: acct;
run;

* create a view joining the dimension and fact table ;
* use proc contents for dynamic code generation ;
proc contents data=dim_acct out=contents (keep=name varnum where=(name like 'Lev%')) noprint;
run;

* the %seplist utility macro generates a separated list suitable for SQL ;
* for this join, we are using acct as the key ;
proc sql noprint;
  select name into :levs separated by " " from contents order by varnum;
  create view joined as
    select
      %seplist(&levs,prefix=d.),
      f.acct,  /* this is redundant, I could drop this and just use the lowest level */
      f.amount
    from
      dim_acct d
    inner join
      option2 f
    on
      d.acct=f.acct
  ;
quit;

* now generate a summary for each level in the hierarchy ;
proc summary data=joined noprint;
  class lev:;
  var amount;
  types (lev:) ();
  output out=sum2 sum=;
run;
%mend;
%code;

* now, say new hierarchies are added ;
* these hierarchies do not have to be contiguous, ;
* they only need to be unique, and unambigously map ;
* to a parent acct ;
data source (index=(acct));
  length acct parent $3 amount 8;
  input acct parent amount;
  datalines;
A1  .   10
A2  A1  20
A3  A2  30
A4  A2  20
A5  A1  10
A6  A5  10
A7  .   10
A8  A7  20
A9  A7  30
A10 A9  20
A11 A4  5
A12 A9  5
A13 A11 5
A14 A12 5
;
run;

* just run the above code again, using this new source ;
* no code changes are required ;
%code;

* finally, create a dimensional model ;

* create a dimension for the account locations ;
* apologies to India...I don't know suburb names, but love their food! ;
data dim_acct_names (index=(acct name_id));
  length Name_Id 8 Acct $3 Acct_Name $100;
  infile datalines dsd dlm="|";
  input Acct Acct_Name;
  Name_Id+1;
  datalines;
A1  | IBM Australia
A2  | New South Wales
A3  | Newcastle
A4  | Sydney
A5  | Victoria
A6  | Melbourne
A7  | HP India
A8  | Chennai
A9  | New Delhi
A10 | Roganjosh
A11 | CBD
A12 | Vindaloo
A13 | Pitt St. Branch
A14 | Korma
;
run;

* create a dimension dataset for the account hierarchy ;
* for the hierarchy, we are really interested in the name, not the acct id ;
* I have decided to use a snowflake schema instead of star schema ;
* and a data step view instead of sql view ;
data dim_acct (index=(lev_id));
  length Lev_Id 8 Lev1-Lev&max_hier 8;
  set option2;
  Lev_Id+1;
  _acct=acct;
  array lev{*} Lev1-Lev&max_hier;
  do i=1 to dim(lev);
    acct=scan(hier,i,"|");
    set dim_acct_names (keep=acct name_id) key=acct / unique;
    lev{i}=name_id;
    if missing(lev{i}) then lev{i}=lev{i-1}; * mimic coalesce function here instead of the view ;
  end;
  acct=_acct;
  keep Lev: Acct;
  _error_=0;
run;

* view joining account hierarchy and account names ;
data v_dim_acct/view=v_dim_acct;
  length Lev_Id 8 Name1-Name&max_hier $100;
  set dim_acct;
  array lev{*} Lev1-Lev&max_hier;
  array nam{*} Name1-Name&max_hier;
  do i=1 to dim(lev);
    name_id=lev{i};
    set dim_acct_names (keep=name_id acct_name) key=name_id / unique;
    nam{i}=acct_name;
    if missing(nam{i}) then nam{i}=nam{i-1}; * mimic coalesce function here instead of the view ;
  end;
  keep Lev_Id Name:;
  drop Name_Id;
  _error_=0;
run;

* create the fact table and join in the keys ;
* I will show a hash object join approach and an SQL approach ;
data fact_stores;
  length Lev_Id amount 8;
  set option2 (keep=acct amount);
  %let _hashnum_=0;
  %hash_define(data=dim_acct,       keys=acct, vars=Lev_Id);
  %hash_lookup;
  drop _h: acct;
run;

* via SQL ;
proc sql;
  create table fact_stores as
  select
    t2.Lev_Id
    ,t1.Amount
  from
    option2 t1
  left join
    dim_acct t2
  on
    t1.acct=t2.acct
  ;
quit;

* create a view for reporting (proc summary) ;
* use proc contents for dynamic code generation ;
proc contents data=v_dim_acct out=contents (keep=name varnum where=(name like 'Name%')) noprint;
run;

* the %seplist utility macro generates a separated list suitable for SQL ;
* for this join, we are using acct as the key ;
proc sql noprint;
  select name into :levs separated by " " from contents order by varnum;
  create view v_fact_stores as
    select
      %seplist(&levs,prefix=l.)
      ,f.amount
    from
      fact_stores f
    left join
      v_dim_acct l
    on
      f.lev_id=l.lev_id
  ;
quit;

proc summary data=v_fact_stores noprint;
  class Name:;
  var amount;
  types (Name:) ();
  output out=sum3 sum=;
run;


text:
#include <windows.h>
#include "Globals.h"
#include "MainWnd.h"
#include "Resource.h"

/* Global instance handle */
HINSTANCE g_hInstance = NULL;

/* Our application entry point */
int PASCAL WinMain(HINSTANCE hInstance, HINSTANCE hPrevInstance, LPSTR lpCmdLine, int nCmdShow)
{
  HWND hWnd;
  HACCEL hAccelerators;
  MSG msg;

  /* Assign global HINSTANCE */
  g_hInstance = hInstance;

  /* Register our main window class, or error */
  if (!hPrevInstance && !RegisterMainWindowClass())
  {
    MessageBox(NULL, "Error registering main window class.", "Error", MB_ICONHAND | MB_OK);
    return 0;
  }

  /* Create our main window, or error */
  if (!(hWnd = CreateMainWindow()))
  {
    MessageBox(NULL, "Error creating main window.", "Error", MB_ICONHAND | MB_OK);
    return 0;
  }

  /* Load accelerators */
  hAccelerators = LoadAccelerators(hInstance, MAKEINTRESOURCE(IDR_ACCELERATOR));

  /* Show main window and force a paint */
  ShowWindow(hWnd, nCmdShow);
  UpdateWindow(hWnd);

  /* Main message loop */
  while (GetMessage(&msg, NULL, 0, 0) > 0)
  {
    if (!TranslateAccelerator(hWnd, hAccelerators, &msg))
    {
      TranslateMessage(&msg);
      DispatchMessage(&msg);
    }
  }

  return (int)msg.wParam;
}

repo_language: C

mime_type: text/x-c


text:
40
projectIdent
0
VpeMain
1
WRect
0
0
10270
9969
2
MProject
3
MCommand
0
4
MCommand
0
1
5
WFileName
12
Win16App.tgt
6
WVList
1
7
VComponent
8
WRect
0
0
1871
8365
0
0
9
WFileName
12
Win16App.tgt
0
12
7


text:
//{{NO_DEPENDENCIES}}
// App Studio generated include file.
// Used by RESOURCE.RC
//
#define IDI_APPICON                     100
#define IDR_MAINMENU                    101
#define IDR_ACCELERATOR                 102
#define IDD_ABOUTDIALOG                 103
#define ID_FILE_EXIT                    200
#define ID_HELP_ABOUT                   300
#define IDC_STATIC                      -1

// Next default values for new objects
//
#ifdef APSTUDIO_INVOKED
#ifndef APSTUDIO_READONLY_SYMBOLS

#define _APS_NEXT_RESOURCE_VALUE        104
#define _APS_NEXT_COMMAND_VALUE         301
#define _APS_NEXT_CONTROL_VALUE         1000
#define _APS_NEXT_SYMED_VALUE           101
#endif
#endif


text:
#ifndef GLOBALS_H
#define GLOBALS_H

#include <windows.h>

/* Global instance handle */
extern HINSTANCE g_hInstance;

#endif


text:
# Microsoft Visual C++ generated build script - Do not modify

PROJ = WIN16APP
DEBUG = 0
PROGTYPE = 0
CALLER =
ARGS =
DLLS =
D_RCDEFINES = /d_DEBUG
R_RCDEFINES = /dNDEBUG
ORIGIN = MSVC
ORIGIN_VER = 1.00
PROJPATH = UNKNOWN
USEMFC = 0
CC = cl
CPP = cl
CXX = cl
CCREATEPCHFLAG =
CPPCREATEPCHFLAG =
CUSEPCHFLAG =
CPPUSEPCHFLAG =
FIRSTC = ABOUTDLG.C
FIRSTCPP =
RC = rc
CFLAGS_D_WEXE = /nologo /W3 /Gf /Zi /Od /D "WINVER"="0x0300" /D "_DEBUG" /FR /GA /GEs /GEm /Fd"WIN16APP.PDB"
CFLAGS_R_WEXE = /nologo /W3 /Gf /O1 /D "WINVER"="0x0300" /D "NDEBUG" /FR /GA /GEs /GEm
LFLAGS_D_WEXE = /NOLOGO /NOD /PACKC:61440 /ALIGN:16 /ONERROR:NOEXE /CO
LFLAGS_R_WEXE = /NOLOGO /NOD /PACKC:61440 /ALIGN:16 /ONERROR:NOEXE
LIBS_D_WEXE = oldnames libw slibcew commdlg.lib olecli.lib olesvr.lib shell.lib
LIBS_R_WEXE = oldnames libw slibcew commdlg.lib olecli.lib olesvr.lib shell.lib
RCFLAGS = /nologo
RESFLAGS = /nologo /30
RUNFLAGS =
DEFFILE = WIN16APP.DEF
OBJS_EXT =
LIBS_EXT =
!if "$(DEBUG)" == "1"
CFLAGS = $(CFLAGS_D_WEXE)
LFLAGS = $(LFLAGS_D_WEXE)
LIBS = $(LIBS_D_WEXE)
MAPFILE = nul
RCDEFINES = $(D_RCDEFINES)
!else
CFLAGS = $(CFLAGS_R_WEXE)
LFLAGS = $(LFLAGS_R_WEXE)
LIBS = $(LIBS_R_WEXE)
MAPFILE = nul
RCDEFINES = $(R_RCDEFINES)
!endif
!if [if exist MSVC.BND del MSVC.BND]
!endif
SBRS = ABOUTDLG.SBR \
		MAINWND.SBR \
		WINMAIN.SBR

ABOUTDLG_DEP = aboutdlg.h \
	globals.h \
	resource.h

MAINWND_DEP = aboutdlg.h \
	globals.h \
	mainwnd.h \
	resource.h

RESOURCE_RCDEP = resource.h \
	app.ico

WINMAIN_DEP = globals.h \
	mainwnd.h \
	resource.h

all:	$(PROJ).EXE $(PROJ).BSC

ABOUTDLG.OBJ:	ABOUTDLG.C $(ABOUTDLG_DEP)
	$(CC) $(CFLAGS) $(CCREATEPCHFLAG) /c ABOUTDLG.C

MAINWND.OBJ:	MAINWND.C $(MAINWND_DEP)
	$(CC) $(CFLAGS) $(CUSEPCHFLAG) /c MAINWND.C

RESOURCE.RES:	RESOURCE.RC $(RESOURCE_RCDEP)
	$(RC) $(RCFLAGS) $(RCDEFINES) -r RESOURCE.RC

WINMAIN.OBJ:	WINMAIN.C $(WINMAIN_DEP)
	$(CC) $(CFLAGS) $(CUSEPCHFLAG) /c WINMAIN.C

$(PROJ).EXE::	RESOURCE.RES

$(PROJ).EXE::	ABOUTDLG.OBJ MAINWND.OBJ WINMAIN.OBJ $(OBJS_EXT) $(DEFFILE)
	echo >NUL @<<$(PROJ).CRF
ABOUTDLG.OBJ +
MAINWND.OBJ +
WINMAIN.OBJ +
$(OBJS_EXT)
$(PROJ).EXE
$(MAPFILE)
$(LIBS)
$(DEFFILE);
<<
	link $(LFLAGS) @$(PROJ).CRF
	$(RC) $(RESFLAGS) RESOURCE.RES $@
	@copy $(PROJ).CRF MSVC.BND

$(PROJ).EXE::	RESOURCE.RES
	if not exist MSVC.BND 	$(RC) $(RESFLAGS) RESOURCE.RES $@

run: $(PROJ).EXE
	$(PROJ) $(RUNFLAGS)

$(PROJ).BSC: $(SBRS)
	bscmake @<<
/o$@ $(SBRS)
<<


text:
#include "AboutDlg.h"
#include "Globals.h"
#include "Resource.h"

/* Dialog procedure for our "about" dialog */
BOOL CALLBACK AboutDialogProc(HWND hwndDlg, UINT uMsg, WPARAM wParam, LPARAM lParam)
{
  switch (uMsg)
  {
    case WM_COMMAND:
    {
      WORD id = wParam;

      switch (id)
      {
        case IDOK:
        case IDCANCEL:
        {
          EndDialog(hwndDlg, id);
          return TRUE;
        }
      }
      break;
    }

    case WM_INITDIALOG:
    {
      return TRUE;
    }
  }

  return FALSE;
}

/* Show our "about" dialog */
void ShowAboutDialog(HWND owner)
{
  DialogBox(g_hInstance, MAKEINTRESOURCE(IDD_ABOUTDIALOG), owner, &AboutDialogProc);
}


text:
#ifndef ABOUTDLG_H
#define ABOUTDLG_H

#include <windows.h>

/* Dialog procedure for our "about" dialog */
BOOL CALLBACK __export AboutDialogProc(HWND hwndDlg, UINT uMsg, WPARAM wParam, LPARAM lParam);

/* Show our "about" dialog */
void ShowAboutDialog(HWND owner);

#endif


text:
//Microsoft App Studio generated resource script.
//
#include "Resource.h"

#define APSTUDIO_READONLY_SYMBOLS
/////////////////////////////////////////////////////////////////////////
//
// Generated from the TEXTINCLUDE 2 resource.
//
#define APSTUDIO_HIDDEN_SYMBOLS
#include "windows.h"
#undef APSTUDIO_HIDDEN_SYMBOLS

/////////////////////////////////////////////////////////////////////////////////////
#undef APSTUDIO_READONLY_SYMBOLS

//////////////////////////////////////////////////////////////////////////////
//
// Icon
//

IDI_APPICON             ICON    DISCARDABLE     "APP.ICO"

//////////////////////////////////////////////////////////////////////////////
//
// Menu
//

IDR_MAINMENU MENU DISCARDABLE
BEGIN
    POPUP "&File"
    BEGIN
        MENUITEM "E&xit",                       ID_FILE_EXIT
    END
    POPUP "&Help"
    BEGIN
        MENUITEM "&About",                      ID_HELP_ABOUT
    END
END

//////////////////////////////////////////////////////////////////////////////
//
// Dialog
//

IDD_ABOUTDIALOG DIALOG DISCARDABLE  0, 0, 147, 67
STYLE DS_MODALFRAME | WS_POPUP | WS_CAPTION | WS_SYSMENU
CAPTION "About"
FONT 8, "MS Sans Serif"
BEGIN
    ICON            IDI_APPICON,IDC_STATIC,7,7,20,20
    LTEXT           "Win16 Example application.",IDC_STATIC,34,7,91,8
    LTEXT           "\2512017 Transmission Zero",IDC_STATIC,34,17,86,8
    DEFPUSHBUTTON   "OK",IDOK,90,46,50,14,WS_GROUP
END

//////////////////////////////////////////////////////////////////////////////
//
// Accelerator
//

IDR_ACCELERATOR ACCELERATORS DISCARDABLE
BEGIN
    "a",            ID_HELP_ABOUT,          ASCII,  ALT, NOINVERT
END

#ifdef APSTUDIO_INVOKED
//////////////////////////////////////////////////////////////////////////////
//
// TEXTINCLUDE
//

1 TEXTINCLUDE DISCARDABLE
BEGIN
    "Resource.h\0"
END

2 TEXTINCLUDE DISCARDABLE
BEGIN
    "#define APSTUDIO_HIDDEN_SYMBOLS\r\n"
    "#include ""windows.h""\r\n"
    "#undef APSTUDIO_HIDDEN_SYMBOLS\r\n"
    "\r\n"
    "\0"
END

3 TEXTINCLUDE DISCARDABLE
BEGIN
    "#include ""Win16App.ver""\r\n"
    "\0"
END

/////////////////////////////////////////////////////////////////////////////////////
#endif    // APSTUDIO_INVOKED

#ifndef APSTUDIO_INVOKED
////////////////////////////////////////////////////////////////////////////////
//
// Generated from the TEXTINCLUDE 3 resource.
//
#include "Win16App.ver"

/////////////////////////////////////////////////////////////////////////////////////
#endif    // not APSTUDIO_INVOKED


text:
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.

In jurisdictions that recognize copyright laws, the author or authors
of this software dedicate any and all copyright interest in the
software to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and
successors. We intend this dedication to be an overt act of
relinquishment in perpetuity of all present and future rights to this
software under copyright law.

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

For more information, please refer to <http://unlicense.org>


text:
NAME           WIN16APP
DESCRIPTION    'Win16 Example Application'
EXETYPE        WINDOWS 3.00
REALMODE
STUB           'WINSTUB.EXE'
CODE           MOVEABLE PRELOAD DISCARDABLE
DATA           MOVEABLE PRELOAD MULTIPLE
HEAPSIZE       1024
STACKSIZE      4096


text:
#include <ver.h>

/* Executable version information */
VS_VERSION_INFO    VERSIONINFO DISCARDABLE
FILEVERSION        1,0,0,0
PRODUCTVERSION     1,0,0,0
FILEFLAGSMASK      VS_FFI_FILEFLAGSMASK
#ifdef _DEBUG
  FILEFLAGS        VS_FF_DEBUG | VS_FF_PRERELEASE
#else
  FILEFLAGS        0
#endif
FILEOS             VOS_DOS_WINDOWS16
FILETYPE           VFT_APP
FILESUBTYPE        VFT2_UNKNOWN
BEGIN
  BLOCK "StringFileInfo"
  BEGIN
    BLOCK "080904E4"
    BEGIN
      VALUE "CompanyName", "Transmission Zero\0"
      VALUE "FileDescription", "Win16 Example application\0"
      VALUE "FileVersion", "1.0.0.0\0"
      VALUE "InternalName", "Win16App\0"
      VALUE "LegalCopyright", "©2017 Transmission Zero\0"
      VALUE "OriginalFilename", "Win16App.exe\0"
      VALUE "ProductName", "Win16 Test application\0"
      VALUE "ProductVersion", "1.0.0.0\0"
    END
  END
  BLOCK "VarFileInfo"
  BEGIN
    VALUE "Translation", 0x809, 1252
  END
END


text:
#ifndef MAINWND_H
#define MAINWND_H

#include <windows.h>

/* Window procedure for our main window */
LRESULT CALLBACK __export MainWndProc(HWND hWnd, UINT msg, WPARAM wParam, LPARAM lParam);

/* Register a class for our main window */
BOOL RegisterMainWindowClass(void);

/* Create an instance of our main window */
HWND CreateMainWindow(void);

#endif


text:
40
targetIdent
0
MProject
1
MComponent
0
2
WString
4
WEXE
3
WString
5
w_6en
1
0
0
4
MCommand
0
5
MCommand
0
6
MItem
12
Win16App.exe
7
WString
4
WEXE
8
WVList
4
9
MVState
10
WString
7
WINLINK
11
WString
11
?????Stack:
0
12
WString
4
4096
0
13
MCState
14
WString
7
WINLINK
15
WString
34
?????Requires Windows 3.0 or later
0
1
16
MVState
17
WString
7
WINLINK
18
WString
11
?????Stack:
1
19
WString
4
4096
0
20
MCState
21
WString
7
WINLINK
22
WString
34
?????Requires Windows 3.0 or later
1
1
23
WVList
1
24
ActionStates
25
WString
5
&Make
26
WVList
0
-1
1
1
0
27
WPickList
13
28
MItem
3
*.c
29
WString
4
COBJ
30
WVList
14
31
MRState
32
WString
3
WCC
33
WString
21
?????Compiler default
1
0
34
MVState
35
WString
3
WCC
36
WString
19
?????Other options:
1
37
WString
4
-zWs
0
38
MRState
39
WString
3
WCC
40
WString
21
?????Compiler default
1
0
41
MRState
42
WString
3
WCC
43
WString
25
?????Inline with emulator
1
1
44
MRState
45
WString
3
WCC
46
WString
16
??6??Small model
1
1
47
MRState
48
WString
3
WCC
49
WString
16
??6??Large model
1
0
50
MRState
51
WString
3
WCC
52
WString
21
?????Compiler default
0
0
53
MVState
54
WString
3
WCC
55
WString
19
?????Other options:
0
56
WString
4
-zWs
0
57
MRState
58
WString
3
WCC
59
WString
21
?????No optimizations
0
1
60
MRState
61
WString
3
WCC
62
WString
26
?????Fastest possible code
0
0
63
MRState
64
WString
3
WCC
65
WString
21
?????Compiler default
0
0
66
MRState
67
WString
3
WCC
68
WString
25
?????Inline with emulator
0
1
69
MRState
70
WString
3
WCC
71
WString
16
??6??Small model
0
1
72
MRState
73
WString
3
WCC
74
WString
16
??6??Large model
0
0
75
WVList
0
-1
1
1
0
76
MItem
10
AboutDlg.c
77
WString
4
COBJ
78
WVList
0
79
WVList
0
28
1
1
0
80
MItem
9
MainWnd.c
81
WString
4
COBJ
82
WVList
0
83
WVList
0
28
1
1
0
84
MItem
9
WinMain.c
85
WString
4
COBJ
86
WVList
0
87
WVList
0
28
1
1
0
88
MItem
3
*.h
89
WString
3
NIL
90
WVList
0
91
WVList
0
-1
1
1
0
92
MItem
10
AboutDlg.h
93
WString
3
NIL
94
WVList
0
95
WVList
0
88
1
1
0
96
MItem
9
Globals.h
97
WString
3
NIL
98
WVList
0
99
WVList
0
88
1
1
0
100
MItem
9
MainWnd.h
101
WString
3
NIL
102
WVList
0
103
WVList
0
88
1
1
0
104
MItem
10
Resource.h
105
WString
3
NIL
106
WVList
0
107
WVList
0
88
1
1
0
108
MItem
5
*.ico
109
WString
3
NIL
110
WVList
0
111
WVList
0
-1
1
1
0
112
MItem
7
App.ico
113
WString
3
NIL
114
WVList
0
115
WVList
0
108
1
1
0
116
MItem
4
*.rc
117
WString
5
WRESC
118
WVList
0
119
WVList
0
-1
1
1
0
120
MItem
11
Resource.rc
121
WString
5
WRESC
122
WVList
0
123
WVList
0
116
1
1
0