Macros to Handle Large Data Volumes

%RMCMB

%RMCMB Overview

%RMCMB Syntax

%RMCMB Required Arguments

%RMMKLIKE Required Arguments

%RMMKLIKE Options

%RMMKLIKE Example

%RMCMB

%RMCMB Overview

%RMCMB combines up to 255 similar tables into one table. It creates a table in the target library path from like-named tables in source library paths. For each set of the like-named tables that are specified by the SOURCE1-SOURCEN parameters, the macro creates a single table in the path that is specified by the TARGET parameter. (%RMCMB uses SQL to create the table by taking the union of the source tables.)

All rows and columns that are found in the source tables are kept in the new target table.

%RMCMB Syntax

%RMCMB(
SOURCE1=path-to-source1-table
,TARGET=path-to-target-table
<,SOURCE2=path-to-source2-table...,SOURCEN=path-to-sourceN-table>
<,MEMTYPE=DATA | VIEW>
);

%RMCMB Required Arguments

SOURCE1=path-to-source1-table

specifies the first path to use as the source file system location from which tables will be combined.

Do not enclose this value in quotation marks or brackets. A value for this parameter is required.

TARGET=path-to-target-table

specifies the path to use as the target file system location to which the new table will be written.

Do not enclose this value in quotation marks or brackets. A value for this parameter is required.

%RMCMB Options

MEMTYPE=DATA | VIEW

specifies what type of target to create.

DATA	Specify `DATA` to create a SAS data table. This is the default value for this option.
VIEW	Specify `VIEW` to create an SQL view.

SOURCE2=path-to-source2-table...SOURCEN=path-to-sourceN-table

specifies additional paths to use as the source file system location from which tables will be combined.

Do not enclose this value in quotation marks or brackets. Values for these parameters are optional.

Note: No more than 255 source locations can be specified.

%RMCMB Note

When creating a SAS data table, the data is automatically compressed. When creating a SAS SQL view, the data is not compressed. The SAS SQL view uses embedded libref information for each source location. Therefore, the view can be used later without re-specifying the various locations.

%RMCMB Example

The following example combines two source tables into a target that is a view:

%rmcmb(source1=path-to-source1-location,
       source2=path-to-source2-location,
       target=path-to-target-location,
       memtype=view
);

%RMMKLIKE

%RMMKLIKE Overview

%RMMKLIKE creates a set of physical libraries, based on an existing job that is specified by the user. The job must contain a staging or Aggregation transformation. For each SAS library that is currently used to contain target tables from that transformation, %RMMKLIKE creates a new physical location. This macro can be executed multiple times in order to produce multiple mirrored sets of libraries.

Note: If the specified job contains a staging or Aggregation transformation where all of the target tables are contained in one SAS library, only one new physical location is created.

%RMMKLIKE does not copy any SAS catalogs or data sets to the newly created mirrored libraries. When running the deployed jobs with the overridden target libraries, the target tables are written to the specified libraries. The original location is used but only for referencing internal control information written at deployment time. The overridden location is used for everything else.

Windows Specifics: The new physical location is a directory. The directory name is formed from the combination of the user-specified root location and the last element of the existing physical location for the current SAS library. For example, if the user specifies ROOT=C:\TEMP\new, and the SAS library containing target tables for the specified job is currently C:\users\myid\datamart\stg938, then the new location would be created at C:\TEMP\new\stg938.

UNIX Specifics: The new physical location is a directory. The directory name is formed from the combination of the user-specified root location and the last element of the existing physical location for the current SAS library. For example, if the user specifies ROOT=/tmp/myid/new, and the SAS library containing target tables for the specified job is currently /u/myid/datamart/stg938, then the new location would be created at /tmp/myid/new/stg938.

z/OS Specifics: For zFS, refer to the UNIX Specifics. For traditional z/OS file system libraries, the new physical location is also a traditional file system library. The attributes of the new library (such as DCB and size) are based on the existing library. For example, if the user specifies ROOT=MYID.NEW, and the SAS library containing target tables for the specified job is currently MYID.OLD.DM.STG938, then the new location would be created at MYID.NEW.STG938.

%RMMKLIKE Syntax

%RMMKLIKE(
JOB=complete-metadata-path-to-the-job
,ROOT=physical-root-location
<,METAPASS=metadata-server-password>
<,METAPORT=metadata-server-port>

<,METAREPOSITORY=name-of-metadata-repository>
<,METASERVER=name-of-metadata-server>
<,METAUSER=user-ID>
);

%RMMKLIKE Required Arguments

JOB=complete-metadata-path-to-the-job

specifies the complete metadata path, including the job name, to an existing job that contains one or more SAS IT Resource Management transformations. This argument must be specified completely.

This is an example of a completely specified path to a job: /My Folder/Subfolder/MyJob.

Note: If a job has been deployed, there is a metadata object for the job and a separate metadata object for the deployed job. For the purposes of %RMMKLIKE, use the original job object, not the deployed job.

ROOT=physical-root-location

specifies the new path root to use in constructing the new names. If the user-specified root location does not exist, the %RMMKLIKE macro creates it.

A value for this parameter is required.

The following paths are valid examples:

Windows Specifics: C:\public\newLocation

UNIX Specifics: /tmp/newLocation

Note: The previous (UNIX) path is also a valid example for the z/OS ZFS file system.

z/OS Specifics: MYGRP.NEWLOC.

%RMMKLIKE Options

METAPASS=metadata-server-password

specifies the password to use for authenticating the user ID to the metadata server. Do not enclose the value for this parameter in quotation marks.

You can use the PWENCODE form of the password. For more information about the PWENCODE procedure, see the Base SAS Procedures Guide.

Tip

If you use the PWENCODE form of the password, be sure to handle embedded braces ({}) carefully. Do not confuse them with square brackets ([]).

METAPORT=metadata-server-port

specifies the port to use in accessing the metadata server, in numeric (integer) format. Do not enclose the value for this parameter in quotation marks.

METAREPOSITORY=name-of-metadata-repository

specifies the name of the repository in which to look for the specified job. The default value for this parameter is Foundation. Do not enclose the value for this parameter in quotation marks.

METASERVER=name-of-metadata-server

specifies the domain name of the metadata server in TCP/IP format. Do not enclose the value for this parameter in quotation marks.

METAUSER=user-ID

specifies the user ID to use in accessing the metadata server. Do not enclose the value for this parameter in quotation marks.

Note: The METAPASS, METAPORT, METAREPOSITORY, METASERVER, and METAUSER parameters are optional. You can specify any or all of them as SAS options instead. However, you must do one or the other.

%RMMKLIKE Example

The following example pertains to the Windows environment:

%rmmklike(
          job=/My Folder/Subfolder/MyJob,
          metapass=mypassword,
          metaport=8561,
          metarepository=MyRepository,
          metaserver=mybox.subdomain.domain.com,
          metauser=myuserID,
          root=C:\public\newLocation
         );