Running PROC COPY In-Database for Greenplum

Details about Running PROC COPY for Greenplum

Copying data in-database results in faster copy times because the data does not have to be loaded into or moved out of SAS. You can run the COPY procedure in-database to copy Greenplum data from one schema to another. The librefs for the IN= and OUT= options for PROC COPY must match for the SERVER=, PORT=, and DATABASE= values. The user who is specified for the IN= location must have Read permission for that location. The user who is specified for the OUT= location must have Read permission for the IN= location and Write permission for the OUT= location when data is copied in-database.

Here are some points to keep in mind when you copy data in-database:

• You can specify the ACCEL | NOACCEL option in the PROC COPY statement when you copy data. When the IN= and OUT= locations are database locations, the ACCEL option specifies that data is copied in-database. The ACCEL option is enabled by default.
• You can also specify the REPLACE | NOREPLACE system option with PROC COPY. The REPLACE | NOREPLACE system option specifies whether to replace a data set if it already exists. The REPLACE system option is enabled by default.
  Note: When the REPLACE system option is enabled, an existing target table is deleted before PROC COPY attempts to copy data.
• In-database copy is not supported if the input library is a concatenated library. You can copy to a concatenated library if the first target library matches the input library.
• The OVERRIDE= and CLONE | NOCLONE options in the COPY statement are ignored when copying data in-database.
• You can specify PRESERVE_NAMES=YES in the LIBNAME statement to preserve the case of table or column names. This also enables you to copy names with special characters. PRESERVE_NAMES= is an alias for both the PRESERVE_TAB_NAMES= and PRESERVE_COL_NAMES= LIBNAME options.
• Data types that are not supported on the SAS Viya platform, such as BINARY or DECIMAL, are retained when you copy data in-database. If the data is moved into and out of a SAS Viya offering (without copying data in-database), then data types or attributes are converted to types and attributes that are supported on the SAS Viya platform.
• Indexes and integrity constraints are copied with a table when it is copied in-database. These are not copied if the table is moved into and out of a SAS Viya offering (without copying data in-database) during the copy operation.

To verify whether the COPY procedure ran in-database, specify MSGLEVEL=i in the OPTIONS statement.

See Also

• COPY Procedure in Base SAS Procedures Guide
• DATASETS Procedure in Base SAS Procedures Guide
• MSGLEVEL= System Option in SAS System Options: Reference
• REPLACE System Option in SAS System Options: Reference

PROC COPY Example

This example shows how to copy data in Greenplum between schemas in a database. This example copies the table My_table from schema Inschema into the schema Outsch. By specifying the REPLACE option in the OPTIONS statement, you ensure that you replace a data set if it already exists. The ACCEL option for the PROC COPY statement is enabled by default. This option controls whether data is copied in-database.

libname gpin greenplm user="myuser" database=userdb port=5432 
   server="gpserver.com" password=mypwd schema=inschema;
libname gpout greenplm user="myuser" database=userdb port=5432
   server="gpserver.com" password=mypwd schema=outsch;

options replace;

proc copy in=gpin out=gpout;
   select my_table;
run;
quit;

proc contents data=gpout.my_table; run;