ImportDataFile.sas

/****************************************************************************/
/*  Program Name  : ImportDataFile                                          */
/*  Purpose       : Import data file as fixed width character data with     */
/*                  automatic input variable naming.                        */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Author        : Matt Trzcinski                                          */
/*  Last Update   : 2018/03/15                                              */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Output(s)     : SAS dataset, macro variable &listHeader                 */
/*  Usage         :    dirData= Directory containing data file.             */
/*                    fileName= Filename including file extension. Must be  */
/*                              .csv, .txt, .tsv, .xls, or .xlsx.           */
/*                     dataset= Name of dataset output to WORK library.     */
/*                   delimiter= (optional) Delimiting string given in       */
/*                              quotes. Default for CSV is a comma, for     */
/*                              TXT/TSV a tab. This parameter may not be    */
/*                              set for Excel files. Doing so generates a   */
/*                              warning.                                    */
/*                  overOption= (optional) INFILE option. Default is        */
/*                               MISSOVER.  Other choices are FLOWOVER,     */
/*                               STOPOVER, TRUNCOVER, or SCANOVER.          */
/*                   headerRow= (optional) Row corresponding to header in   */
/*                              an Excel file. Accepts R#C#:R#C#, but       */
/*                              should be given as R#. Default is R1.       */
/*                       sheet= Name of worksheet. Required for XLS or XLSX.*/
/*                       range= Range of spreadsheet to be imported.        */
/*                              Required for XLS and XLSX. Use form         */
/*                              R#C#:R#C#.  See example below.              */
/*                      prefix= (optional) String to append to beginning of */
/*                              each variable name. Default is no prefix.   */
/*                        case= (optional) Toggle mix case variable naming. */
/*                              Must be lower/upper/mixed. Default is       */
/*                              lower.                                      */
/*                   defLength= (optional) Character field length.  Default */
/*                              value is 100.                               */
/*  Structure     : --Utilities--                                           */
/*                  ClearFileRef                                            */
/*                  CompareVariablesToDDERange                              */
/*                  EstablishSystemLink                                     */
/*                  EstablishWorkbookLink                                   */
/*                  IsEmpty                                                 */
/*                  IsFileRef                                               */
/*                  IsFilePath                                              */
/*                  GetObsCount                                             */
/*                  GetVarCount                                             */
/*                  ListLength                                              */
/*                  ListElement                                             */
/*                  RemoveAllFormattingFromSheet                            */
/*                  SetSystemOptions                                        */
/*                                                                          */
/*                  --Main/Sub Macros--                                     */
/*                  ImportDataFile                                          */
/*                   EstablishDDELink                                       */
/*                   ReadHeaderIntoList                                     */
/*                   CreateLengthStatement                                  */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Example(s)    :                                                         */
/*                  %ImportDataFile(                                        */
/*                         dirData= &dirBasePath.                           */
/*                    ,   fileName= &xlFile.                                */
/*                    ,    dataset= xl_import                               */
/*                    ,     prefix= raw_                                    */
/*                    ,      sheet= &xlSheet.                               */
/*                    ,      range= R2C1:R13C18                             */
/*                    ,  defLength= 100                                     */
/*                    , overOption= MISSOVER                                */
/*                  );                                                      */
/*                                                                          */
/*  Template      :                                                         */
/*                  %ImportDataFile(                                        */
/*                         dirData=                                         */
/*                    ,   fileName=                                         */
/*                    ,    dataset=                                         */
/*                    ,  delimiter=                                         */
/*                    , overOption=                                         */
/*                    ,  headerRow=                                         */
/*                    ,      sheet=                                         */
/*                    ,      range=                                         */
/*                    ,     prefix=                                         */
/*                    ,       case=                                         */
/*                    ,  defLength=                                         */
/*                  );                                                      */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Note(s)       : Although similar to PROC IMPORT, %ImportDataFile offers */
/*                  greater control and a cleaner import with a uniform     */
/*                  data flow.  Data is imported as fixed width character   */
/*                  data with no meta information (no formats, informats,   */
/*                  or labels).  It also has greater flexibility in regards */
/*                  to special characters within the data.  This allows the */
/*                  user to construct their data from a neutral starting    */
/*                  point as they see fit.                                  */
/*                                                                          */
/*                  %ImportDataFile assumes data contains headers.  For     */
/*                  CSV/TXT/TSV data, it is assumed these headers are       */
/*                  in the first row and that data begins on the second     */
/*                  row.  These assumptions are not made for Excel files.   */
/*                                                                          */
/*                  In order to import an Excel file, said Excel file must  */
/*                  be opened prior to running %ImportDataFile.  This may   */
/*                  be handled via %ExcelOpen.  %ExcelOpen is not included  */
/*                  here for simplicity and separation of responsibility.   */
/*                                                                          */
/*                  Sometimes when reading a workbook from a network drive, */
/*                  program execution continues before the workbook is      */
/*                  loaded and a link can be established, causing errors.   */
/*                  This program ensures that this doesn't happen through   */
/*                  use of %EstablishDDELink.                               */
/*                                                                          */
/*                  Sometimes a workbook will open with 'PROTECTED VIEW'    */
/*                  toggled on.  When workbook protection is enabled, a     */
/*                  DDE link cannot be established.  In the workbook, a     */
/*                  gold status bar will appear beneath the ribbon with a   */
/*                  'Enable Editing' button.  Click this button to disable  */
/*                  the 'PROTECTED VIEW'.  This should only need to be done */
/*                  a single time.                                          */
/*                                                                          */
/*  Development   : At heart, this program is an automation of a data step  */
/*                  import.  %EstablishDDELink is used to ensure that       */
/*                  the execution timing of Excel does not interfere with   */
/*                  the overall process.  %ReadHeaderIntoList does the      */
/*                  heavy lifting of automatically reading in the headers   */
/*                  and making sure they are turned into a valid length     */
/*                  statement.  Aside from the actual creation of the       */
/*                  length statement, some fiddling occurs regarding the    */
/*                  INFILE statement, as this varies slightly depending on  */
/*                  the data type being imported. Documentation is provided */
/*                  below for %ReadHeaderIntoList and %EstablishDDELink.    */
/*                  Please see these for more details.                      */
/*                                                                          */
/*                  This process is composed in three primary parts:        */
/*                    - Establish a DDE link, if necessary                  */
/*                    - Get data variables by reading in header             */
/*                    - Read in the remaining data                          */
/*                  The remainder of this program consists of support       */
/*                  functions (with about 25% alone being validation).      */
/*                  Each individual part can exist independently of this    */
/*                  program.  However, each has been optimized to work      */
/*                  within the current context, such as removing redundant  */
/*                  validations and assigning default values once.          */
/*                                                                          */
/*                  This program relies on Excel 4.0 macro functions. The   */
/*                  macrofun.hlp file can be downloaded from Microsoft      */
/*                  here:                                                   */
/*                                                                          */
/*                        https://www.microsoft.com/en-us/download/         */
/*                            details.aspx?displaylang=en&id=1465           */
/*                                                                          */
/*                  In order to view the help file, it may be necessary     */
/*                  to also install WinHlp32.exe:                           */
/*                                                                          */
/*                        https://support.microsoft.com/en-us/help/917607/  */
/*                          error-opening-help-in-windows-based-programs-   */
/*                          feature-not-included-or-h                       */
/*                                                                          */
/*                  (Yes, that address just ends in an 'h') Relevant        */
/*                  excerpts have been included at the bottom of this       */
/*                  program in the event that the given links go dead.      */
/*                                                                          */
/****************************************************************************/

/****************************************************************************/
/*  Program Name  : EstablishDDELink                                        */
/*  Purpose       : Macro utility to establish DDE Link with Excel before   */
/*                  allowing program execution to continue.  Terminates     */
/*                  program if connection is not established within 10      */
/*                  seconds.                                                */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Last Update   : 2017/04/29                                              */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Input(s)      : Optional inputs marked with *.                          */
/*                  fileRef    = File reference. Cannot exceed 8 characters.*/
/*                  *dirData   = Directory of workbook.                     */
/*                  *fileName  = Name of workbook, including file extension.*/
/*                  *sheetName = Name of worksheet to connect with.         */
/*                  *range     = Range within workhseet, given as R#C#:R#C# */
/*  Output(s)     : None                                                    */
/*  Usage         : Two types of DDE links can be established: System and   */
/*                  Workbook.  A system link requires only the fileRef      */
/*                  input. A workbook link requires all inputs.             */
/*  Macro(s)      : ClearFileRef, EstablishSystemLink,                      */
/*                  EstablishWorkbookLink, SetSystemOptions                 */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Example       : System Link:                                            */
/*                                                                          */
/*                      %EstablishDDELink(fileRef=xlDDE);                   */
/*                                                                          */
/*                      data _null_;                                        */
/*                        file xlDDE;                                       */
/*                        put '[QUIT()]';                                   */
/*                      run;                                                */
/*                                                                          */
/*                  Workbook Link:                                          */
/*                                                                          */
/*                      %EstablishDDELink(    fileRef= xlBook               */
/*                                        ,   dirData= C:\temp              */
/*                                        ,  fileName= example.xlsx         */
/*                                        , sheetName= Sheet1               */
/*                                        ,     range= R2C1:R400C5          */
/*                      );                                                  */
/*                                                                          */
/*                      data _raw_example;                                  */
/*                        infile xlBook dlm='09'x notab missover;           */
/*                        length                                            */
/*                          var1 $ 100                                      */
/*                          var2 $ 100                                      */
/*                          var3 8                                          */
/*                        ;                                                 */
/*                        input (_all_) (:) ;                               */
/*                      run;                                                */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Note          : Sometimes when reading a workbook from a network drive  */
/*                  program execution continues before the workbook is      */
/*                  loaded and a link can be established, causing errors.   */
/*                  This program ensures that this doesn't happen.          */
/*                                                                          */
/*                  Sometimes a workbook will open with 'PROTECTED VIEW'    */
/*                  toggled on.  When workbook protection is enabled, a     */
/*                  DDE link cannot be established.  In the workbook, a     */
/*                  gold status bar will appear beneath the ribbon with a   */
/*                  'Enable Editing' button.  Click this button to disable  */
/*                  the 'PROTECTED VIEW'.  This should only need to be done */
/*                  a single time.                                          */
/*                                                                          */
/*  Developer     :                                                         */
/*                                                                          */
/****************************************************************************/

/****************************************************************************/
/*  Program Name  : ReadHeaderIntoList                                      */
/*  Purpose       : Reads header from a data file and outputs a macro       */
/*                  variable list of valid SAS variable names.              */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Last Update   : 2017/06/30                                              */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Input(s)      : reference= Either a SAS fileref (such as DDE link) or   */
/*                             an absolute file path (C:\test\file.csv).    */
/*                  delimiter= Delimiter string.                            */
/*                     prefix= (optional) String to be prefixed to each     */
/*                             header variable name.  Default is nothing.   */
/*                       case= (optional) Set case of output headers to     */
/*                              upper, lower, or mixed. Default is mixed.   */
/*  Output(s)     : Global macro variable &listHeader                       */
/*  Macro(s)      : IsEmpty, IsFileRef, IsFilePath, ListLength,             */
/*                  SetSystemOptions                                        */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Example(s)    : %ReadHeaderIntoList(                                    */
/*                      reference=  xlDDE                                   */
/*                    , delimiter= '09'x                                    */
/*                    ,      case= lower                                    */
/*                    ,   prefix= raw_                                      */
/*                  );                                                      */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Notes(s)      :                                                         */
/*                                                                          */
/*  Development   : Reads in the first row of data as a single string, then */
/*                  creates a pipe-delimited list by replacing the          */
/*                  delimiter string.  All further processing is handled    */
/*                  via a pipe-delimited macro list.                        */
/*                                                                          */
/*                  Ideally, we would want ReadHeaderIntoList to            */
/*                  behave as a macro function and return the list. The     */
/*                  problem is that                                         */
/*                                                                          */
/*                     "You cannot use a macro variable reference to        */
/*                      retrieve the value of a macro variable in the same  */
/*                      program (or step) in which SYMPUT creates that      */
/*                      macro variable and assigns it a value."             */
/*                      - SAS 9.3 Macro Language Reference:                 */
/*                          CALL SYMPUT Rountine                            */
/*                                                                          */
/*                  If we try to return listHeader as the last line of the  */
/*                  macro, SAS generates errors.  If we try to use PROC SQL */
/*                  to assign listHeader, the same problem occurs. Since    */
/*                  data can only be read in using the DATA step and the    */
/*                  only way to access the header outside of the data step  */
/*                  is to use SYMPUT, listHeader must be global.            */
/*                                                                          */
/*                  The NOQUOTELENMAX option must be toggled because of the */
/*                  lines:                                                  */
/*                                                                          */
/*                  header_i = scan("%superq(rawListHeader)", i, '|', 'm'); */
/*                  element_i = scan("&listValid.", i, '|');                */
/*                                                                          */
/*                  The macro variables are being called within a DATA      */
/*                  step and so must be quoted.  However, this triggers     */
/*                  the quote warning.                                      */
/*                                                                          */
/*                  Sometimes when importing an excel spreadsheet, extra    */
/*                  blank columns will be imported.  This is due to         */
/*                  trailing tab characters in subsequent columns.  One     */
/*                  method of correcting this is to DELETE these columns    */
/*                  in Excel.  Or, the blank columns can be retained and    */
/*                  removed during a cleaning process.                      */
/*                                                                          */
/****************************************************************************/

********************************************************************

** Utilities / Sub Macros

********************************************************************;
%macro ClearFileRef(fileRef);
  filename &fileRef. clear;
%mend;

%macro CompareVariablesToDDERange();
  %local columnIndex numberOfDDEColumns;

  %let columnIndex        = %eval(%sysfunc(findc(&range., 'C', ib)) + 1);
  %let numberOfDDEColumns = %sysfunc(substr(&range., &columnIndex));
  %if %ListLength(&listHeader) ^= &numberOfDDEColumns %then
    %put WARNING: [MACRO] Data file contains %ListLength(&listHeader) variables. RANGE argument has &numberOfDDEColumns columns.;
%mend;

%macro EstablishSystemLink(fileRef);
  filename &fileRef. dde 'excel|system';
%mend;

%macro EstablishWorkbookLink(fileRef, dirData, fileName, sheetName, range);
  filename &fileRef. dde "excel|&dirData.\[&fileName.]&sheetName.!&range.";
%mend;

%macro IsEmpty(macroVariable);
  %sysevalf(%superq(&macroVariable)=, boolean)
%mend;

%macro IsFileRef(reference);
  %local fileRefExists externalFileExists returnValue;

  %let fileRefExists      = %sysfunc(fexist(&reference.));
  %let externalFileExists = %sysfunc(fileexist(&reference.));
  %if &fileRefExists. = 1 and &externalFileExists. = 0 %then %let returnValue = 1;
  %else %let returnValue = 0;
  &returnValue
%mend;

%macro IsFilePath(reference);
  %local fileRefExists externalFileExists returnValue;

  %let fileRefExists      = %sysfunc(fexist(&reference.));
  %let externalFileExists = %sysfunc(fileexist(&reference.));
  %if &fileRefExists. = 0 and &externalFileExists. = 1 %then %let returnValue = 1;
  %else %let returnValue = 0;
  &returnValue
%mend;

%macro GetObsCount(dataset);
  %local exists returnValue closed;

  %let exists = %sysfunc(open(&dataset));
  %if &exists. %then %do;
    %let returnValue  = %sysfunc(attrn(&exists, nobs));
    %let closed       = %sysfunc(close(&exists));
    %end;
  %else %do;
    %put ERROR: [&SYSMACRONAME.] Dataset %upcase(&dataset) does not exist.;
    %abort cancel;
    %end;
  &returnValue
%mend;

%macro GetVarCount(dataset);
  %local exists varCount closed;

  %let exists = %sysfunc(open(&dataset));
  %if &exists. %then %do;
    %let varCount = %sysfunc(attrn(&exists, nvars));
    %let closed   = %sysfunc(close(&exists));
    %end;
  %else %do;
    %put ERROR: [&SYSMACRONAME.] Dataset %upcase(&dataset) does not exist.;
    %abort cancel;
    %end;
  &varCount
%mend;

%macro ListLength(list);
  %local count;

  %if %sysevalf(%superq(list)=, boolean) %then %let count = 0;
  %else %let count = %eval(%sysfunc(countc(&list., |)) + 1);
  &count
%mend;

%macro ListElement(list, n);
  %local nthElement;

  %let nthElement = %sysfunc(scan(%superq(&list.), &n., |, m));
  &nthElement
%mend;

%macro RemoveAllFormattingFromSheet(fileRef, sheet);
  data _null_;
    file &fileRef.;
    /* Select sheet of interest */
    put "[WORKBOOK.ACTIVATE(""&sheet."")]";
    /* Select first cell */
    put '[FORMULA.GOTO("R1C1")]';
    /* Apply dummy filter of ">2" to first column */
    put '[FILTER(1, ">2")]';
    /* Disable filters */
    put '[FILTER()]';
    /* Select all */
    put '[SELECT("R[0]C[0]:R[1048575]C[16383]", "R[0]C[0]")]';
    /* Unhide rows */
    put '[ROW.HEIGHT(,,TRUE, 2)]';
    /* Unhide columns */
    put '[COLUMN.WIDTH(,,TRUE, 2)]';
    /* Remove all formatting */
    put '[CLEAR(2)]';
    /* Autofit column width */
    put '[COLUMN.WIDTH(,,TRUE, 3)]';
  run;
%mend;

%macro SetSystemOptions(opt1, opt2, opt3);
  options &opt1. &opt2. &opt3.;
%mend;

%macro ImportDataFile(dirData=, fileName=, dataset=, delimiter=, overOption=MISSOVER, headerRow=R1, sheet=, range=, prefix=, case=lower, defLength=100) / minoperator mindelimiter=',';
%put NOTE: [MACRO] Executing: ImportDataFile(dirData=&dirData, fileName=&fileName, dataset=&dataset, delimiter=&delimiter, overOption=&overOption, headerRow=&headerRow, sheet=&sheet, range=&range, prefix=&prefix, case=&case, defLength=&defLength);

  %local
    macroStart
    case
    extension
    HeaderRef
    lengthStatement
    delimiter
    InfileRef
    infileStatement
    numberOfRecords
    numberOfVars
    duration
  ;

  %global
    listHeader
    originalNOTES
    originalQUOTELENMAX
  ;

  %let macroStart           = %sysfunc(datetime());
  %let originalNOTES        = %sysfunc(getoption(notes));
  %let originalQUOTELENMAX  = %sysfunc(getoption(noquotelenmax));

  %SetSystemOptions(nonotes);

********************************************************************

** Validation

********************************************************************;
  %if %IsEmpty(dirData) %then %do;
    %put ERROR: [&SYSMACRONAME.] DIRDATA argument is blank.;
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

  %if %IsEmpty(fileName) %then %do;
    %put ERROR: [&SYSMACRONAME.] FILENAME argument is blank.;
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

  %if %IsEmpty(dataset) %then %do;
    %put ERROR: [&SYSMACRONAME.] DATASET argument is blank.;
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

  %if not(%IsEmpty(prefix)) and not(%sysfunc(nvalid(&prefix, v7))) %then %do;
    %put ERROR: [&SYSMACRONAME.] Invalid PREFIX="&prefix.";
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

  %let case = %upcase(&case.);

  %if not(&case. in (LOWER, UPPER, MIXED)) %then %do;
    %put ERROR: [&SYSMACRONAME.] Invalid case option: &case. Must be LOWER, UPPER, MIX.;
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

  %let extension  = %upcase(%scan(&fileName., 1, '.', b));

  %if not(&extension. in (TXT, TSV, CSV, XLS, XLSX)) %then %do;
    %put ERROR: [&SYSMACRONAME.] Invalid file type: &extension. Must be TXT, TSV, CSV, XLS, XLSX.;
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

  %if &extension. in (XLS, XLSX) and %IsEmpty(sheet) %then %do;
    %put ERROR: [&SYSMACRONAME.] SHEET argument undefined.;
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

  %if &extension. in (XLS, XLSX) and %IsEmpty(range) %then %do;
    %put ERROR: [&SYSMACRONAME.] RANGE argument undefined.;
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

  %if not(&extension. in (XLS, XLSX)) and not(%IsEmpty(sheet)) %then %do;
    %put ERROR: [&SYSMACRONAME.] SHEET argument only valid for XLS or XLSX files.;
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

  %if not(&extension. in (XLS, XLSX)) and not(%IsEmpty(range)) %then %do;
    %put ERROR: [&SYSMACRONAME.] RANGE argument only valid for XLS or XLSX files.;
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

**********************************

*** Define delimiter

**********************************;
%if %IsEmpty(delimiter) %then %do;
    %if       &extension. in (XLS, XLSX)  %then %let delimiter = '09'x;
    %else %if &extension. = CSV           %then %let delimiter = ',';
    %else %if &extension. in (TXT, TSV)   %then %let delimiter = '09'x;
    %else %do;
      %put ERROR: [&SYSMACRONAME.] Delimiter error.;
      %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
      %abort cancel;
      %end;
    %end;

  %if &extension. in (XLS, XLSX) and &delimiter ^= '09'x %then %do;
    %put WARNING: [&SYSMACRONAME.] Delimiter for Excel file must be '09'x.;
    %put WARNING: [&SYSMACRONAME.] Delimiter set to '09'x.;
    %let delimiter = '09'x;
    %end;

********************************************************************

** Prep Excel Worksheet

********************************************************************;
  %if &extension. in (XLS, XLSX) %then %do;
    %let DDECommandRef = DDEcmd;
    %EstablishDDELink(fileRef=&DDECommandRef.);
    %RemoveAllFormattingFromSheet(fileRef=&DDECommandRef., sheet=&sheet.);
    %end;

********************************************************************

** Get header

********************************************************************;

**********************************

*** Define file reference

**********************************;
  %if &extension. in (XLS, XLSX) %then %do;
    %let HeaderRef = DDEHead;
    %EstablishDDELink(
          fileRef= &HeaderRef.
      ,   dirData= &dirData.
      ,  fileName= &fileName.
      , sheetName= &sheet.
      ,     range= &headerRow.
    );
    %end;
  %else %if &extension. in (CSV, TXT, TSV) %then
    %let HeaderRef = %sysfunc(dequote(&dirData.))\&fileName.;

  %ReadHeaderIntoList(reference=&HeaderRef., delimiter=&delimiter., prefix=&prefix., case=&case.);

********************************************************************

** Create length statement

********************************************************************;
  %let lengthStatement = %CreateLengthStatement(&listHeader., &defLength.);

********************************************************************

** Import data

********************************************************************;

**********************************

*** Define infile statement

**********************************;
  %if &extension. in (XLS, XLSX) %then %do;
    %let InfileRef = DDESheet;
    %EstablishDDELink(
          fileRef= &InfileRef.
      ,   dirData= &dirData.
      ,  fileName= &fileName.
      , sheetName= &sheet.
      ,     range= &range.
    );
    %let infileStatement = infile &InfileRef. dlmstr=&delimiter. dsd notab &overOption.;
    %CompareVariablesToDDERange();
    %end;
  %else %if &extension. in (CSV, TXT, TSV) %then %do;
    %let InfileRef       = %sysfunc(dequote(&dirData.))\&fileName.;
    %let infileStatement = infile "&InfileRef." dlmstr=&delimiter. dsd &overOption. firstobs = 2 end=last_record;
    %end;

**********************************

*** Perform import

**********************************;
  data &dataset.;
    &infileStatement.;

    length &lengthStatement. ;

    input (_all_) (:) ;

  run;

********************************************************************

** Housekeeping

********************************************************************;
  %let numberOfRecords = %GetObsCount(&dataset.);
  %let numberOfVars    = %GetVarCount(&dataset.);

  %SetSystemOptions(notes);

  %put;
  %put NOTE: [MACRO] The dataset WORK.%upcase(&dataset.) has &numberOfRecords. observations and &numberOfVars. variables.;
  %put NOTE: [MACRO] IMPORTDATAFILE macro used (Total process time):;

  %let duration = %sysfunc(putn(%sysevalf(%sysfunc(datetime()) - &macroStart.), time12.3));
  %if %sysfunc(minute("&duration."t)) > 0 %then %do;
    %put NO%str(TE-)         real time            %substr(&duration., 3, 8);
    %end;
  %else %do;
    %put NO%str(TE-)         real time            %substr(&duration., 6, 5) seconds;
    %end;

  %put;

  %SetSystemOptions(&originalNotes., &originalQUOTELENMAX.);

%mend;

%macro  EstablishDDELink(fileRef, dirData, fileName, sheetName, range);
%put NOTE: [&SYSMACRONAME] Executing: EstablishDDELink(fileRef=&fileRef, dirData=&dirData, fileName=&fileName, sheetName=&sheetName, range=&range);

  %local dirData linkConnection stopTime closeReturnCode;

********************************************************************

** Validate arguments

********************************************************************;
  %if %IsEmpty(fileRef) %then %do;
    %put ERROR: [&SYSMACRONAME] fileRef is blank.;
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

  %if %length(&fileRef.) > 8 %then %do;
    %put ERROR: [&SYSMACRONAME] Fileref &fileRef exceeds 8 character limit.;
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

  %if not %IsEmpty(dirData) %then %let dirData = %sysfunc(dequote(&dirData.));

********************************************************************

** Assign fileref according to link type

********************************************************************;
  %if     %IsEmpty(dirData)
      and %IsEmpty(fileName)
      and %IsEmpty(sheetName)
      and %IsEmpty(range) %then %EstablishSystemLink(&fileRef.);
  %else %EstablishWorkbookLink(&fileRef., &dirData., &fileName., &sheetName., &range.);

********************************************************************

** Check that link has been established

********************************************************************;
  %let linkConnection = %sysfunc(fopen(&fileRef, S));

  %if not (&linkConnection. > 0) %then %do;

    /*Run until either Excel opens (linkConnection > 0)

      or until 10 seconds have passed.*/
    %let stopTime = %sysevalf(%sysfunc(datetime()) + 10);

    %do %until (&linkConnection. > 0);
      %if (%sysfunc(datetime()) >= &stopTime.) %then %do;
        %put ERROR: [&SYSMACRONAME] DDE system link was not established. Operation timed out.;
        %ClearFileRef(fileRef.);
        %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
        %abort cancel;
        %end;

      %let linkConnection = %sysfunc(fopen(&fileRef, S));
      %end;
    %end;

********************************************************************

** Housekeeping

********************************************************************;
  %let closeReturnCode = %sysfunc(fclose(&linkConnection));

%mend;

%macro  ReadHeaderIntoList(reference, delimiter, prefix, case) / minoperator mindelimiter=',';
%put NOTE: [MACRO] Executing: ReadHeaderIntoList(reference=&reference, delimiter=&delimiter, prefix=&prefix, case=&case);

  %local  fileSpecification notab delimiter;
  %global listHeader;

  %SetSystemOptions(nonotes);

  %if %IsEmpty(reference) %then %do;
    %put ERROR: [&SYSMACRONAME.] REFERENCE argument is blank.;
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

********************************************************************

** Determine infile statement options

********************************************************************;
  /*SAS filerefs exist only for Excel files*/
  %if       %IsFileRef(&reference.)  %then %do;
    %let fileSpecification  = &reference.;
    %let notab              = notab;
    %end;
  /*Absolute references only for CSV,TXT,TSV files*/
  %else %if %IsFilePath(&reference.) %then %do;
    %let fileSpecification  = "&reference.";
    %let notab              = ;
    %let extension          = %upcase(%scan(&reference., 1, '.', b));
    %end;
  %else %do;
    %put ERROR: [&SYSMACRONAME.] Invalid input REFERENCE: [&reference.];
    %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
    %abort cancel;
    %end;

********************************************************************

** Read in header

********************************************************************;
  data _null_;
    infile &fileSpecification. dlmstr = '```#@' &notab. obs = 1 lrecl = 32767 ;
    length
      raw_header_line   $ 32767
      raw_with_pipes    $ 32767
    ;
    input raw_header_line;

    raw_with_pipes  = tranwrd(raw_header_line, &delimiter., '|');
    call symput('rawListHeader', strip(raw_with_pipes));
  run;

********************************************************************

** Transform headers into valid variable names

********************************************************************;
  %SetSystemOptions(noquotelenmax);
  data _null_;
    length
      i           8
      listLength  8
      header_i    $ 32767
      temp_i      $ 32767
      listValid   $ 32767
    ;
    listLength = %ListLength(%superq(rawListHeader));

    do i = 1 to listLength;
      header_i = scan("%superq(rawListHeader)", i, '|', 'm');

**********************************

*** Apply prefix

**********************************;
      if not missing(header_i) then prefixed_i = cats("&prefix.", header_i);
      else                          prefixed_i = header_i;

**********************************

*** Apply case

**********************************;
      if      "&case." = "LOWER" then cased_i = lowcase(prefixed_i);
      else if "&case." = "UPPER" then cased_i = upcase(prefixed_i);
      else                            cased_i = prefixed_i;

**********************************

*** Keep valid otherwise correct

**********************************;
      if nvalid(cased_i, 'v7') then do;
        if i = 1 then listValid = cased_i;
        else          listValid = catx('|', listValid, cased_i);
        end;
      else do;

**********************************

*** Fill in blank headers

**********************************;
      if missing(cased_i) and "&case." = "UPPER" then temp_i = "%upcase(&prefix.)NO_HEADER";
      else if missing(cased_i)                   then temp_i = "&prefix.no_header";

**********************************

*** Replace blanks with _ and

*** Remove invalid characters

**********************************;
      else do;
        replaced_space_with_underscore = tranwrd(strip(cased_i), ' ', '_');
        temp_i = compress(replaced_space_with_underscore, '_', 'kin');
        end;

**********************************

*** Make first char _ if digit

**********************************;
        if anydigit(temp_i) = 1 then temp_i = cats('_', temp_i);

**********************************

*** Trim length to 32

**********************************;
        if length(temp_i) > 32 then temp_i = substr(temp_i, 1, 32);

**********************************

*** Verify valid V7 name

**********************************;
        if not nvalid(temp_i, 'v7') then do;
          put 'ERROR: [&SYSMACRONAME.] Error cleaning header ' i +(-1) '. Invalid SAS name.';
          call execute('

            %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);

            data _null_;

              abort cancel nolist;

            run;');
          stop;
          end;

        if i = 1 then listValid = temp_i;
        else          listValid = catx('|', listValid, temp_i);
        end;

      output;
    end;
    call symput('listValid', strip(listValid));
  run;

********************************************************************

** Append repeated headers with incremented value

********************************************************************;
  /*Use hash table with key being each header and value

    corresponding to the number of occurences.  Create new

    header list as follows: If first occurence of a header,

    add to list.  If not first occurence, ruthlessly append

    occurence number (ensuring validity) and add to list.

    Beware: SAS documentation for hashes contains syntax

    errors.*/
  data _null_;
    length
      element_i   $ 32
      item        $ 32
      occurrences 8
      new_list    $ 32767
    ;

    declare hash h();
    h.defineKey('item');
    h.defineData('item', 'occurrences');
    h.defineDone();
    call missing(item, occurrences);

    listLength = input("%ListLength(&listValid.)", 8.);
    do i = 1 to listLength;
      element_i = scan("&listValid.", i, '|');

      if not (h.find(key: element_i) = 0) then do;
        h.add(key: element_i, data: element_i, data: 1);
        new_list = catx('|', new_list, element_i);
        end;
      else do;
        occurrences + 1;
        h.replace(key: element_i, data: element_i, data: occurrences);

        len     = length(element_i);
        digits  = ceil(log10(occurrences + 1));

        if (len + digits) > 32 then
          new_element = cats(substr(element_i, 1, len - digits), occurrences);
        else new_element = cats(element_i, occurrences);

        new_list = catx('|', new_list, new_element);
        end;
    end;

    call symput('listHeader', strip(new_list));
  run;
%mend;

%macro  CreateLengthStatement(listHeader, defLength);
  %local lengthStatement header_h;

  %let lengthStatement=;
  %do h = 1 %to %ListLength(&listHeader.);
  %let header_h = %ListElement(listHeader, &h);
    %if &h. = 1 %then %let lengthStatement = &header_h. $ &defLength. ;
    %else %let lengthStatement = &lengthStatement. &header_h. $ &defLength. ;
  %end;
  %let lengthStatement = &lengthStatement;
  &lengthStatement
%mend;

/****************************************************************************/
/*                                                                          */
/*  WORKBOOK.ACTIVATE                                                       */
/*                                                                          */
/*  Macro Sheets Only                                                       */
/*  Equivalent to activating a worksheet by clicking on its tab.            */
/*                                                                          */
/*  Syntax                                                                  */
/*                                                                          */
/*    WORKBOOK.ACTIVATE(sheet_name)                                         */
/*    Sheet_name    is the name of the document you want to activate within */
/*                  the active workbook.                                    */
/*                                                                          */
/****************************************************************************/

/****************************************************************************/
/*                                                                          */
/*  FORMULA.GOTO                                                            */
/*                                                                          */
/*  Macro Sheets Only                                                       */
/*  Equivalent to choosing the Go To command from the Edit menu or to       */
/*  pressing F5. Scrolls through the worksheet and selects a named area or  */
/*  reference. Use FORMULA.GOTO to select a range on any open workbook;     */
/*  use SELECT to select a range on the active workbook.                    */
/*                                                                          */
/*  Syntax                                                                  */
/*                                                                          */
/*  FORMULA.GOTO(reference, corner)                                         */
/*  FORMULA.GOTO?(reference, corner)                                        */
/*                                                                          */
/*  Reference    specifies where to scroll and what to select.              */
/*                                                                          */
/*    Reference should be either an external reference to a workbook, an    */
/*    R1C1-style reference in the form of text (see the second example      */
/*    following), or a name. If the Go To command has already been carried  */
/*    out, reference is optional. If reference is omitted, it is assumed to */
/*    be the reference of the cells you selected before the previous Go To  */
/*    command or FORMULA.GOTO macro function was carried out. This feature  */
/*    distinguishes FORMULA.GOTO from SELECT.                               */
/*                                                                          */
/*  Corner    is a logical value that specifies whether to scroll through   */
/*    the window so that the upper-left cell in reference is in the upper-  */
/*    left corner of the active window. If corner is TRUE, Microsoft        */
/*    Excel places reference in the upper-left corner of the window;        */
/*    if FALSE or omitted, Microsoft Excel scrolls through normally.        */
/*                                                                          */
/*  Tip Microsoft Excel keeps a list of the cells you've selected with      */
/*  previous FORMULA.GOTO functions or Go To commands. When you use         */
/*  FORMULA.GOTO with GET.WORKSPACE(41), which returns a horizontal array   */
/*  of previous Go To selections, you can backtrack through multiple        */
/*  previous selections. See the last example below.                        */
/*                                                                          */
/*  Remarks                                                                 */
/*                                                                          */
/*  * If you are recording a macro when you choose the Go To command, the   */
/*  reference you enter in the Reference box of the Go To dialog box is     */
/*  recorded as text in the R1C1 reference style.                           */
/*                                                                          */
/*  * If you are recording a macro when you double-click a cell that has    */
/*  precedents on another worksheet, Microsoft Excel records a              */
/*  FORMULA.GOTO function.                                                  */
/*                                                                          */
/*  Examples                                                                */
/*                                                                          */
/*  Each of the following macro formulas goes to cell A1 on the active      */
/*  worksheet:                                                              */
/*                                                                          */
/*  FORMULA.GOTO(!$A$1)                                                     */
/*  FORMULA.GOTO("R1C1")                                                    */
/*                                                                          */
/*                                                                          */
/*  Each of the following macro formulas goes to the cells named Sales on   */
/*  the active worksheet and scrolls through the worksheet so that the      */
/*  upper-left corner of Sales is in the upper-left corner of the window:   */
/*                                                                          */
/*  FORMULA.GOTO(!Sales, TRUE)                                              */
/*  FORMULA.GOTO("Sales", TRUE)                                             */
/*                                                                          */
/*                                                                          */
/*  The following macro formula goes to the cells that were selected by     */
/*  the third most recent FORMULA.GOTO function or Go To command:           */
/*                                                                          */
/*  FORMULA.GOTO(INDEX(GET.WORKSPACE(41), 1, 3))                            */
/*                                                                          */
/****************************************************************************/

/****************************************************************************/
/*                                                                          */
/*  FILTER                                                                  */
/*                                                                          */
/*  Filters lists of data one column at a time. Only one list can be        */
/*  filtered on any one sheet at a time.                                    */
/*                                                                          */
/*  Syntax                                                                  */
/*                                                                          */
/*  FILTER(field_num, criteria1, operation, criteria2)                      */
/*  FILTER?(field_num, criteria1, operation, criteria2)                     */
/*                                                                          */
/*  Field_num    is the number of the field that you want to filter.        */
/*               Fields are numbered from left to right starting with 1.    */
/*  Criteria1    is a text string specifying criteria for filtering a list, */
/*               such as ">2". If you want to include all items in the list,*/
/*               omit this argument.                                        */
/*  Operation    is a number that specifies how you want criteria2 used     */
/*               with criteria1:                                            */
/*                                                                          */
/*  Number      Operation Used                                                  */
/*                                                                          */
/*  1 or omitted        AND                                                       */
/*  2               OR                                                        */
/*                                                                          */
/*  Criteria2    is a text string specifying criteria for filtering a list, */
/*               such as ">2". If you include this argument, operation is   */
/*               required.                                                  */
/*                                                                          */
/*  Remarks                                                                 */
/*                                                                          */
/*  If you omit all arguments, FILTER toggles the display of filter arrows. */
/*                                                                          */
/****************************************************************************/

/****************************************************************************/
/*                                                                          */
/*  SELECT Syntax 1                                                         */
/*                                                                          */
/*  Macro Sheets Only                                                       */
/*                                                                          */
/*  Equivalent to selecting cells or changing the active cell. There are    */
/*  three syntax forms of SELECT. Use syntax 1 to select a cell on a        */
/*  worksheet or macro sheet; use one of the other syntax forms to select   */
/*  worksheet or macro sheet objects or chart items.                        */
/*                                                                          */
/*  Syntax                                                                  */
/*                                                                          */
/*  SELECT(selection, active_cell)                                          */
/*  Selection      is the cell or range of cells you want to select.        */
/*                 Selection can be a reference to the active worksheet,    */
/*                 such as !$A$1:$A$3 or !Sales, or an R1C1-style reference */
/*                 to a cell or range relative to the active cell in the    */
/*                 current selection, such as "R[-1]C[-1]:R[1]C[1]". The    */
/*                 reference must be in text form. If selection is          */
/*                 omitted, the current selection is used.                  */
/*  Active_cell    is the cell in selection you want to make the active     */
/*                 cell. Active_cell can be a reference to a single cell    */
/*                 on the active worksheet, such as !$A$1, or an R1C1-style */
/*                 reference relative to the active cell, such as           */
/*                 "R[-1]C[-1]". The reference must be in text form. If     */
/*                 active_cell is omitted, SELECT makes the cell in the     */
/*                 upper-left corner of selection the active cell.          */
/*                                                                          */
/*  Remarks                                                                 */
/*                                                                          */
/*  * Active_cell must be within selection. If it is not, an error message  */
/*    is displayed and SELECT returns the #VALUE! error value.              */
/*  *   If you are recording a macro using relative references, Microsoft     */
/*    Excel records the action using R1C1-style relative references in      */
/*    the form of text.                                                     */
/*  *   If you are recording using absolute references, Microsoft Excel       */
/*    records the action using R1C1-style absolute references in the form   */
/*    of text.                                                              */
/*  *   You cannot give an external reference to a specific sheet as the      */
/*    selection argument. The sheet on which you want to make a selection   */
/*    must be active when you use SELECT. Use FORMULA.GOTO to make a        */
/*    selection on a sheet or macro sheet in an external workbook.          */
/*                                                                          */
/*  Tip You can enter data in a cell without selecting the cell by using    */
/*      the reference arguments to the CUT, COPY, or FORMULA functions.     */
/*                                                                          */
/*  Examples                                                                */
/*                                                                          */
/*  The following macro formula selects cells C3:E5 on the active worksheet */
/*  and makes C5 the active cell:                                           */
/*                                                                          */
/*  SELECT(!$C$3:$E$5, !$C$5)                                               */
/*                                                                          */
/*                                                                          */
/*  If the active cell is C3, the following macro formula selects cells     */
/*  E5:G7 and makes cell F6 the active cell in the selection:               */
/*                                                                          */
/*  SELECT("R[2]C[2]:R[4]C[4]", "R[1]C[1]")                                 */
/*                                                                          */
/*                                                                          */
/*  You can also make multiple nonadjacent selections with SELECT.          */
/*  The following macro formula selects a number of nonadjacent ranges:     */
/*                                                                          */
/*  SELECT("R1C1, R3C2:R4C3, R8C4:R10C5")                                   */
/*                                                                          */
/*                                                                          */
/*  The following sequence of macro formulas moves the active cell right,   */
/*  left, down, and up within the selection, just as TAB, SHIFT+TAB, ENTER, */
/*  and SHIFT+ENTER do:                                                     */
/*                                                                          */
/*  SELECT(, "RC[1]")                                                       */
/*  SELECT(, "RC[-1]")                                                      */
/*  SELECT(, "R[1]C")                                                       */
/*  SELECT(, "R[-1]C")                                                      */
/*                                                                          */
/*                                                                          */
/*  Use SELECT with the OFFSET function to select a new range a specified   */
/*  distance away from the current range. For example, the following macro  */
/*  formula selects a range that is the same size as the current range, one */
/*  column over:                                                            */
/*                                                                          */
/*  SELECT(OFFSET(SELECTION(), 0, 1))                                       */
/*                                                                          */
/****************************************************************************/

/****************************************************************************/
/*                                                                          */
/*  ROW.HEIGHT                                                              */
/*                                                                          */
/*  Macro Sheets Only                                                       */
/*                                                                          */
/*  Equivalent to choosing the Height command on the Row submenu of         */
/*  the Format menu. Changes the height of the rows in a reference.         */
/*                                                                          */
/*  Syntax                                                                  */
/*                                                                          */
/*  ROW.HEIGHT(height_num, reference, standard_height, type_num)            */
/*  ROW.HEIGHT?(height_num, reference, standard_height, type_num)           */
/*                                                                          */
/*  Height_num   specifies how high you want the rows to be in              */
/*               points. If standard_height is TRUE, height_num is          */
/*   ignored.                                                               */
/*  Reference    specifies the rows for which you want to change the        */
/*               height.                                                    */
/*                                                                          */
/*  * If reference is omitted, the reference is assumed to be the           */
/*    current selection.                                                    */
/*  * If reference is specified, it must be either an external              */
/*    reference to the active worksheet, such as !$2:$4 or !Database,       */
/*    or an R1C1-style reference in the form of text or a name, such        */
/*    as "R1:R3", "R[-4]:R[-2]", or Database.                               */
/*  * If reference is a relative R1C1-style reference in the form of        */
/*    text, it is assumed to be relative to the active cell.                */
/*                                                                          */
/*  Standard_height    is a logical value that sets the row height as       */
/*                     determined by the font in each row.                  */
/*                                                                          */
/*  * If standard_height is TRUE, Microsoft Excel sets the row height       */
/*    to a standard height that may vary from row to row depending on       */
/*    the fonts used in each row, ignoring height_num.                      */
/*  * If standard_height is FALSE or omitted, Microsoft Excel sets the      */
/*    row height according to height_num.                                   */
/*                                                                          */
/*  Type_num     is a number from 1 to 3 corresponding to selecting the     */
/*               Hide, Unhide, or AutoFit commands from the Row submenu.    */
/*                                                                          */
/*  Type_num    Action taken                                                  */
/*                                                                          */
/*  1  Hides the row selection by setting the row height to 0               */
/*  2  Unhides the row selection by setting the row height to the           */
/*     value set before the selection was hidden                            */
/*  3  Sets the row selection to an AutoFit height, which varies from       */
/*     row to row depending on how large the font is in any cell in         */
/*     each row or on how many lines of text are wrapped                    */
/*                                                                          */
/*  Remarks                                                                 */
/*                                                                          */
/*  * If any of the argument settings conflict, such as when                */
/*    standard_height is TRUE and type_num is 3, Microsoft Excel uses       */
/*    the type_num argument and ignores any arguments that conflict         */
/*    with type_num.                                                        */
/*  * If you are recording a macro while using a mouse, and you change      */
/*    row heights by dragging the row border, Microsoft Excel records       */
/*    the reference of the rows using R1C1-style references in the          */
/*    form of text. If Uses Relative References is selected, Microsoft      */
/*    Excel uses R1C1-style relative references. If Uses Relative           */
/*    References is not selected, Microsoft Excel uses R1C1-style           */
/*    absolute references.                                                  */
/*                                                                          */
/****************************************************************************/

/****************************************************************************/
/*                                                                          */
/*  COLUMN.WIDTH                                                            */
/*                                                                          */
/*  Macro Sheets Only                                                       */
/*                                                                          */
/*  Equivalent to choosing the Width command on the Column submenu of the   */
/*  Format menu. Changes the width of the columns in the specified          */
/*  reference.                                                              */
/*                                                                          */
/*  Syntax                                                                  */
/*                                                                          */
/*  COLUMN.WIDTH(width_num, reference, standard, type_num, standard_num)    */
/*  COLUMN.WIDTH?(width_num, reference, standard, type_num, standard_num)   */
/*                                                                          */
/*  Width_num    specifies how wide you want the columns to be in units of  */
/*               one character of the font corresponding to the Normal      */
/*               cell style. Width_num is ignored if standard is TRUE or    */
/*               if type_num is provided.                                   */
/*                                                                          */
/*  Reference    specifies the columns for which you want to change the     */
/*               width.                                                     */
/*                                                                          */
/*    * If reference is specified, it must be either an external reference  */
/*      to the active worksheet, such as !$A:$C or !Database, or an         */
/*      R1C1-style reference in the form of text, such as "C1:C3",          */
/*      "C[-4]:C[-2]", or "Database".                                       */
/*    * If reference is a relative R1C1-style reference in the form of      */
/*      text, it is assumed to be relative to the active cell.              */
/*    * If reference is omitted, it is assumed to be the current            */
/*      selection.                                                          */
/*                                                                          */
/*  Standard    is a logical value corresponding to the Standard Width      */
/*              command from the Column submenu on the Format menu.         */
/*                                                                          */
/*    * If standard is TRUE, Microsoft Excel sets the column width to the   */
/*      currently defined standard (default) width and ignores width_num.   */
/*    * If standard is FALSE or omitted, Microsoft Excel sets the width     */
/*      according to width_num or type_num.                                 */
/*                                                                          */
/*  Type_num    is a number from 1 to 3 corresponding to the Hide, Unhide,  */
/*              or AutoFit Selection commands, respectively, on the Column  */
/*              submenu of the Format menu.                                 */
/*                                                                          */
/*  Type_num    Action taken                                                  */
/*                                                                          */
/*    1  Hides the column selection by setting the column width to 0        */
/*    2  Unhides the column selection by setting the column width to the    */
/*       value set before the selection was hidden                          */
/*    3  Sets the column selection to a best-fit width, which varies from   */
/*       column to column depending on the length of the longest data       */
/*       string in each column                                              */
/*                                                                          */
/*  Standard_num    specifies how wide the standard width is, and is        */
/*                  measured in points. If standard_num is omitted, the     */
/*                  standard width setting remains unchanged.               */
/*                                                                          */
/*  Remarks                                                                 */
/*                                                                          */
/*    * Changing the value of standard_num changes the width of all         */
/*      columns except those that have been set to a custom value.          */
/*    * If any of the argument settings conflict, such as when standard is  */
/*      TRUE and type_num is 3, Microsoft Excel uses the type_num argument  */
/*      and ignores any arguments that conflict with type_num.              */
/*    * If you are recording a macro while using a mouse and you change     */
/*      column widths by dragging the column border, Microsoft Excel        */
/*      records the references of the columns using R1C1-style references   */
/*      in the form of text.                                                */
/*                                                                          */
/****************************************************************************/

/****************************************************************************/
/*                                                                          */
/*  CLEAR                                                                   */
/*                                                                          */
/*  Macro Sheets Only                                                       */
/*                                                                          */
/*  Equivalent to choosing the Clear command from the Edit menu. Clears     */
/*  contents, formats, notes, or all of these from the active worksheet or  */
/*  macro sheet. Clears series or formats from the active chart.            */
/*                                                                          */
/*  Syntax                                                                  */
/*                                                                          */
/*  CLEAR(type_num)                                                         */
/*  CLEAR?(type_num)                                                        */
/*                                                                          */
/*  Type_num is a number from 1 to 4 specifying what to clear. Only values  */
/*  1, 2, and 3 are valid if the selected item is a chart.                  */
/*                                                                          */
/*  On a worksheet or macro sheet, or if an entire chart is selected, the   */
/*  following occurs.                                                       */
/*                                                                          */
/*  Type_num    Clears                                                        */
/*                                                                          */
/*    1  All                                                                */
/*    2  Formats (if a chart, clears the chart format or clears pictures)   */
/*    3  Contents (if a chart, clears all data series)                      */
/*    4  Notes (including sound notes; this does not apply to charts)       */
/*                                                                          */
/*  On a chart, if a single point, an entire data series, error bars, or a  */
/*  trend line is selected, the following occurs.                           */
/*                                                                          */
/*  Type_num    Clears                                                        */
/*                                                                          */
/*    1  Selected series, error bars, or trend line                         */
/*    2  Format in the selected point, series, error bars, or trend line    */
/*                                                                          */
/*  If type_num is omitted, the default values are set as shown in the      */
/*  following table.                                                        */
/*                                                                          */
/*  Active sheet                        Type_num                                  */
/*                                                                          */
/*  Worksheet                   3                                             */
/*  Macro sheet                 3                                             */
/*  Chart (with no selection)   1                                             */
/*  Chart (with item selected)  Deletes the selected item                   */
/*                                                                          */
/****************************************************************************/

Powered by peut-publier

©2020 Matt Trzcinski