Creates a group of tables based on the contents of a delimited file.

161219_MLoadTables.sas

/*-----------------------------------------------------------------------------|
| Description : Loads a set of libraries and creates a group of tables based   |
|               on the contents of a delimited file (e.g., a CSV file).        |
| Assumptions : The delimited file has the structure used in the examples      |
|               (please refer to it, see below).                               |
| Parameters  : InputFilePathWithTableNames - Path of the delimited file with  |
|                                             the libraries and the tables to  |
|                                             be loaded.                       |
|               OutputTable                 - Table with the contents of the   |
|                                             input file (Default: None).      |
|               DelimiterFileFields         - Delimiter character used in the  |
|                                             input file (Default: ,).         |
| Output      : In case the "OutputTable" is specified, it creates a table     |
|               with the contents of the delimited file; otherwise, it only    |
|               loads the libraries and creates the tables.                    |
|-----------------------------------------------------------------------------*/
/*  Examples:
* Creates the file(s) and the table(s) so as to execute the examples. ;
*   Creates a sample CSV in the WORK with the SAS tables to be loaded. ;
%LET _MLT_WORK_PATH = %SYSFUNC(GETOPTION(WORK)); %PUT &_MLT_WORK_PATH.;
DATA TABLES;
  INFILE DATALINES
         DSD DLM = ','
         MISSOVER;
  LENGTH INPUT_LIBRARY
         OUTPUT_LIBRARY   $8
         INPUT_PATH
         OUTPUT_PATH      $120
         INPUT_TABLE
         OUTPUT_TABLE     $11;
  INPUT INPUT_LIBRARY     $
        INPUT_PATH        $
        INPUT_TABLE       $
        OUTPUT_LIBRARY    $
        OUTPUT_PATH       $
        OUTPUT_TABLE      $
        DESCRIPTION     & $37.;
   INPUT_PATH  = "&_MLT_WORK_PATH.";
   OUTPUT_PATH = INPUT_PATH;
DATALINES;
INPUT,,TEMPORAL_01,OUTPUT,,TABLE_01,"Firstly, this is the description 01."
INPUT,,TEMPORAL_02,OUTPUT,,TABLE_02,
INPUT,,TEMPORAL_03,OUTPUT,,TABLE_03,"Secondly, this is the description 02."
;
RUN;
PROC EXPORT DATA    = TABLES
            OUTFILE = "&_MLT_WORK_PATH./TABLES.CSV"
            DBMS    = DLM
            REPLACE;
  DELIMITER = ",";
RUN;
PROC SQL; DROP TABLE TABLES; QUIT;
*   Creates the tables to be copied. ;
DATA TEMPORAL_01; VARIABLE_TABLE_01 = "TABLE 01"; RUN;
DATA TEMPORAL_02; VARIABLE_TABLE_02 = "TABLE 02"; RUN;
DATA TEMPORAL_03; VARIABLE_TABLE_03 = "TABLE 03"; RUN;
* Ex_01;
%MLoadTables(InputFilePathWithTableNames = &_MLT_WORK_PATH./TABLES.CSV,
             OutputTable                 = TABLES
             );
* Output: It loads the libraries and creates the tables described in the
          "TABLES.CSV" file, and it creates a table ("TABLES") with the contents
          of this file. ;
* Ex_02;
%MLoadTables(InputFilePathWithTableNames = &_MLT_WORK_PATH./TABLES.CSV);
* Output: It only loads the libraries and creates the tables described in the
          "TABLES.CSV" file;
*/
/*-----------------------------------------------------------------------------|
| Date                Author                          Description              |
|------------------------------------------------------------------------------|
| December 19, 2016   Cesar R. Urteaga-Reyesvera      Creation.                |
|-----------------------------------------------------------------------------*/
%MACRO MLoadTables(InputFilePathWithTableNames = /* Path of the delimited file
                                                    with the libraries and
                                                    tables names.            */,
                   OutputTable = /* Table with the new names.                */,
                   RemoveDuplicates = 0 /* Specify if we want to remove the
                                           duplicates.                       */,
                   DelimiterFileFields = %STR(,) /* Delimiter character used in
                                                    the input file.          */
                   );
  %LOCAL _MLT_TABLES_LIBRARIES;
  %IF &OutputTable. = %THEN
    %LET _MLT_TABLES_LIBRARIES = _TABLES_LIBRARIES;
  %ELSE
    %LET _MLT_TABLES_LIBRARIES = &OutputTable.;
  * We load the delimited file. ;
  PROC IMPORT DATAFILE = "&InputFilePathWithTableNames."
              OUT      = &_MLT_TABLES_LIBRARIES.
              DBMS     = DLM    /* Delimited file that does not end in "CSV". */
              REPLACE;          /* Overwrite the output table.                */
    DELIMITER = "&DelimiterFileFields.";
    GETNAMES  = YES;
  RUN;
  * We store the libraries and tables in local macro variables. ;
  DATA _NULL_;
    SET &_MLT_TABLES_LIBRARIES. NOBS = _NO_TABLES END = _LAST_RECORD;
    ARRAY VARIABLES[*] INPUT_LIBRARY
                       INPUT_PATH
                       INPUT_TABLE
                       OUTPUT_LIBRARY
                       OUTPUT_PATH
                       OUTPUT_TABLE;
    DO I = 1 TO HBOUND(VARIABLES);
      CALL SYMPUTX(COMPRESS('_MLT_'||VNAME(VARIABLES[I])||'_'||_N_),
                   VARIABLES[I],
                   'L');
    END;
    IF _LAST_RECORD THEN CALL SYMPUTX('_MLT_NO_TABLES', _NO_TABLES, 'L');
  RUN;
  * We load the libraries and create the tables. ;
  %DO I = 1 %TO &_MLT_NO_TABLES.;
    * We check if the libraries exist.;
    %IF %SYSFUNC(LIBNAME(&&_MLT_INPUT_LIBRARY_&I..,
                         &&_MLT_INPUT_PATH_&I.., ,
                         ACCESS = READONLY)) %THEN
      %PUT %SYSFUNC(SYSMSG());
    %IF %SYSFUNC(LIBNAME(&&_MLT_OUTPUT_LIBRARY_&I..,
                         &&_MLT_OUTPUT_PATH_&I..)
                        ) %THEN
      %PUT %SYSFUNC(SYSMSG());
    * We create a table only if the table exists. ;
    %IF %SYSFUNC(EXIST(&&_MLT_INPUT_LIBRARY_&I...&&_MLT_INPUT_TABLE_&I..)) %THEN
      %DO;
        %IF ~&RemoveDuplicates. %THEN
          %DO;
            DATA &&_MLT_OUTPUT_LIBRARY_&I...&&_MLT_OUTPUT_TABLE_&I..;
              SET
                  &&_MLT_INPUT_LIBRARY_&I...&&_MLT_INPUT_TABLE_&I..;
            RUN;
          %END;
        %ELSE
          %DO;
            PROC SQL;
              CREATE TABLE &&_MLT_OUTPUT_LIBRARY_&I...&&_MLT_OUTPUT_TABLE_&I.. AS
                SELECT DISTINCT
                                *
                FROM &&_MLT_INPUT_LIBRARY_&I...&&_MLT_INPUT_TABLE_&I..;
            QUIT;
          %END;
      %END;
    %ELSE
      %PUT WARNING: &&_MLT_INPUT_LIBRARY_&I...&&_MLT_INPUT_TABLE_&I is missing.;
  %END;
  * If we do not specify an output table, we remove it. ;
  %IF &OutputTable. = %THEN
    %DO;
      PROC SQL; DROP TABLE &_MLT_TABLES_LIBRARIES.; QUIT;
    %END;
  %ELSE
    %DO;
      DATA &_MLT_TABLES_LIBRARIES.;
        SET &_MLT_TABLES_LIBRARIES.;
        INPUT_TABLE_EXISTS  = EXIST(COMPRESS(INPUT_LIBRARY  ||
                                             "."            ||
                                             INPUT_TABLE    ));
        OUTPUT_TABLE_EXISTS  = EXIST(COMPRESS(OUTPUT_LIBRARY||
                                              "."           ||
                                              OUTPUT_TABLE  ));
      RUN;
    %END;
%MEND MLoadTables;