Configuring Cloud Data Exchange File Transfer Service

The SAS Viya Cloud Data Exchange’s File Transfer Service enables SAS users to copy data files between a Remote Data Agent Server and the Co-located Data Agent Server storage. The File Transfer Services uses a secure, encrypted network connection to transfer the files between the Remote Data Agent and Co-located Data Agent storage. When the Remote Data Agent is not able to access a data file (.txt, .csv, .json) from an on-premises environment, it is convenient to copy the file to the SAS Viya environment and access it by using the SAS Compute Server. There could also be a use case to copy the SAS Viya environment data files to the on-premises storage for another process. The Cloud Data Exchange File Transfer Service can address all these use cases.

In this post, I discuss the configuration of the File Transfer Services at the Co-located Data Agent and Remote Data Agent.

A Cloud Data Exchange (CDE) administrator can create and configure the File Transfer Services in the SAS Viya CDE environment at the Remote Data Agent Server and Co-located Data Agent Server to enable the file transfer. The CDE environment is configured using the SAS-Viya CLI statement.

Pre-requisite

The CDE Remote Data Agent is configured with the SAS Viya environment.
The CDE Remote Data Agent is mounted to a storage for data file transfer.
The CDE Co-located Data Agent is mounted to a storage for data file transfer.

The following pics describe the CDE File Transfer Service and file transfer path between the Remote Data Agent Server and the SAS Viya Co-located Data Agent Server.

01_UK_CDE_Configure_FileTransfer_Service_Data_Source_1.png

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

Steps to create CDE File Transfer Service and Data Source

The following steps outline the creation of the CDE File Transfer Service and data source at the Remote Data Agent and Co-located Data Agent.

Log in and authenticate for sas-viya CLI statement execution

A CDE Administrator must log in and authenticate to the SAS Viya environment before executing sas-viya CLI statement to configure the CDE components.

Code:

export SAS_CLI_PROFILE=Default;
export SSL_CERT_FILE=~/.certs/my_trustedcerts.pem ;

sas-viya profile set-output text ;
sas-viya auth login --user $Uid --password $Pwd

Log:

[cloud-user@pdcesx41524 ~]$ sas-viya auth login --user $Uid --password $Pwd
https://camel-p41524-rg.gelenable.sas.com
Login succeeded. Token saved.
[cloud-user@pdcesx41524 ~]$

Create a File Transfer Service at the Co-located Data Agent (CDA) Server.

The following sas-viya CLI statement creates a File Transfer Data Service, catalog, and schema on CDA. The File Transfer service schema contains the path/location for the source/target data files. The --depth parameter in the schema creation CLI statement indicates the depth/layer of subdirectories for data files.

Code:

# CDA
# Set #cda to name of your data agent and #cat is catalog name
cda=sas-data-agent-server-colocated
cat=FT_NFS_CDA_CAT
sas-viya dagentsrv servers set-default --data-agent $cda

# Create File Transfer Service
sas-viya dagentsrv service create file-transfer --name FT_NFS_CDA


# Create a Catalog under File Transfer Service
sas-viya dagentsrv catalog create file-transfer --name $cat --data-service FT_NFS_CDA

# Create a data file schema under the Catalog and File Transfer Service
# --depth 2 indicates how many subdirectories of data are available.
sas-viya dagentsrv schema create file-transfer --catalog $cat --name FT_NFS_CDA_SCHEMA1 --primary-path "/mnt/viya-share/data/CDA" --depth 2 --encoding utf8

Log:

[cloud-user@pdcesx41524 ~]$ cda=sas-data-agent-server-colocated
[cloud-user@pdcesx41524 ~]$ cat=FT_NFS_CDA_CAT
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv servers set-default --data-agent $cda
The default data agent server was successfully set.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv service create file-transfer --name FT_NFS_CDA
The data service was successfully created.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv catalog create file-transfer --name $cat --data-service FT_NFS_CDA
The catalog was successfully created.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv schema create file-transfer --catalog $cat --name FT_NFS_CDA_SCHEMA1 --primary-path "/mnt/viya-share/data/CDA" --depth 2 --encoding utf8
The schema was successfully created.
[cloud-user@pdcesx41524 ~]$

Create a File Transfer Service at the Remote Data Agent (RDA) Server.

The following sas-viya CLI statement creates a File Transfer Data Service, catalog, and schema on RDA. The File Transfer service schema contains the path/location for the source/target data files. The --depth parameter in the schema creation CLI statement indicates the depth/layer of subdirectories for data files.

Code:

# RDA
# Set #rda to name of your data agent and #cat is catalog name
rda=sas-data-agent-server-remote
cat=FT_NFS_RDA_CAT
sas-viya dagentsrv servers set-default --data-agent $rda

# Create File Transfer Service
sas-viya dagentsrv service create file-transfer --name FT_NFS_RDA

# Create a Catalog under File Transfer Service
sas-viya dagentsrv catalog create file-transfer --name $cat --data-service FT_NFS_RDA

# Create a data file schema under the Catalog and File Transfer Service
# --depth 2 indicates how many subdirectories of data are available.
sas-viya dagentsrv schema create file-transfer --catalog $cat --name FT_NFS_RDA_SCHEMA1 --primary-path "/mnt/viya-share/data/RDA" --depth 2 --encoding utf8

Log:

[cloud-user@pdcesx41524 ~]$ # RDA
[cloud-user@pdcesx41524 ~]$ # Set #rda to name of your data agent and #cat is catalog name
[cloud-user@pdcesx41524 ~]$ rda=sas-data-agent-server-remote
[cloud-user@pdcesx41524 ~]$ cat=FT_NFS_RDA_CAT
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv servers set-default --data-agent $rda
The default data agent server was successfully set.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv service create file-transfer --name FT_NFS_RDA
The data service was successfully created.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv catalog create file-transfer --name $cat --data-service FT_NFS_RDA
The catalog was successfully created.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv schema create file-transfer --catalog $cat --name FT_NFS_RDA_SCHEMA1 --primary-path "/viya-share/gelenv/data/RDA" --depth 2 --encoding utf8
The schema was successfully created.
[cloud-user@pdcesx41524 ~]$

Create a Data Agent Data-source Service at CDA with reference to the RDA File Transfer Service.

The RDA File Transfer Service is available on the Remote Data Server. You need to create a Data Agent Service at CDA with reference to the RDA File Transfer Service. Having a Data Agent (reference to RDA service) at CDA enables users to access the RDA schema and objects from the CDA server. This will help in the later stage when you create a Federated Data source for file transfer activity.

In the first phase of the Data Agent service, you create a Security domain with user credentials and later use it in the data agent creation. The users in the security domain can only avail this feature to access the files at RDA and use it in file copy operations from RDA to CDA storage.

The following sas-viya CLI statement creates a Security Domain with user credentials and later creates the Data Agent Service with the security domain.

Code:

cda=sas-data-agent-server-colocated
myuser=sastest1@gelenable.sas.com
myuserid=sastest1@gelenable.sas.com
mypassword=$AZUPW
rda=sas-data-agent-server-remote
sas-viya dagentsrv servers set-default --data-agent $cda


# Create Security domain with credentials. Domain name can be whatever you want
sas-viya dagentsrv security domain create --domain RDA --type password

sas-viya dagentsrv security credentials create --domain RDA --identity $myuser --username "$myuserid" --password "$mypassword"

# data agent Data service
sas-viya dagentsrv service create data-agent --name RDA_FT --domain RDA  --data-agent-id "$rda" --dsn FT_NFS_RDA

Log:

[cloud-user@pdcesx41524 ~]$ cda=sas-data-agent-server-colocated
[cloud-user@pdcesx41524 ~]$ myuser=sastest1@gelenable.sas.com
[cloud-user@pdcesx41524 ~]$ myuserid=sastest1@gelenable.sas.com
[cloud-user@pdcesx41524 ~]$ mypassword=$AZUPW
[cloud-user@pdcesx41524 ~]$ rda=sas-data-agent-server-remote
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv servers set-default --data-agent $cda
The default data agent server was successfully set.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ # Create domain with credentials.  Domain name can be whatever you want
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv security domain create --domain RDA --type password
The domain was successfully created.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv security credentials create --domain RDA --identity $myuser --username "$myuserid" --password "$mypassword"
The credential was successfully created.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41350 ~]$ # data agent Data service
[cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv service create data-agent --name RDA_FT --domain RDA  --data-agent-id "$rda" --dsn FT_NFS_RDA
The data service was successfully created.
[cloud-user@pdcesx41350 ~]$

Create a Federated Data Source at CDA for File Transfer.

Now that there is a Data Agent File Transfer Service (referenced to RDA File Transfer Service), and a File Transfer Service at CDA, a Federated Data Source service can be created using these two file transfer services. The Federated Data source will enable the user to access the objects from CDA and RDA using a fully qualified path under the same Co-located Data Agent(CDA) server. This enables the file transfer between the CDA and RDA environments.

The following statement lists the available data sources at CDA, and creates a Federated Data Source using the Data Agent File Transfer Service (referenced to RDA File Transfer Service), and a File Transfer Service. Notice the DSN names FT_NFS_CDA and RDA_FT; both are available at CDA. FT_NFS_CDA is the File Transfer Service for the CDA schema, and RDA_FT is the Data Agent File Transfer Service referenced to the RDA File Transfer Service.

\Code:

cda=sas-data-agent-server-colocated
sas-viya dagentsrv servers set-default --data-agent $cda

sas-viya dagentsrv data-sources list

# Create a Federated Data Source service
sas-viya dagentsrv dsns create federated --name X_FT_NFS_CDA_AND_RDA_FT --using FT_NFS_CDA,RDA_FT

Log:

[cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv data-sources list
Name         ID                           Type
ADMIN        ADMIN                        DSN
BASE         BASE                         DSN
BASE         BASE__DATA_SERVICE__         Service
FT_NFS_CDA   FT_NFS_CDA                   DSN
FT_NFS_CDA   FT_NFS_CDA__DATA_SERVICE__   Service
RDA_FT       RDA_FT                       DSN
RDA_FT       RDA_FT__DATA_SERVICE__       Service
[cloud-user@pdcesx41350 ~]$

[cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv dsns create federated --name X_FT_NFS_CDA_AND_RDA_FT --using FT_NFS_CDA,RDA_FT
The data source name was successfully created.
[cloud-user@pdcesx41350 ~]$

List and test the Federated Data Source.

The following statement lists the available data sources at CDA, including the Federated Data Source, and test the Federated Data Source for file access.

Code:

cda=sas-data-agent-server-colocated
sas-viya dagentsrv servers set-default --data-agent $cda

sas-viya dagentsrv data-sources list

sas-viya dagentsrv  data-sources test --name X_FT_NFS_CDA_AND_RDA_FT

Log:

[cloud-user@pdcesx41350 ~]$
[cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv data-sources list
Name                      ID                           Type
ADMIN                     ADMIN                        DSN
BASE                      BASE                         DSN
BASE                      BASE__DATA_SERVICE__         Service
FT_NFS_CDA                FT_NFS_CDA                   DSN
FT_NFS_CDA                FT_NFS_CDA__DATA_SERVICE__   Service
RDA_FT                    RDA_FT                       DSN
RDA_FT                    RDA_FT__DATA_SERVICE__       Service
X_FT_NFS_CDA_AND_RDA_FT   X_FT_NFS_CDA_AND_RDA_FT      DSN
[cloud-user@pdcesx41350 ~]$


[cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv  data-sources test --name X_FT_NFS_CDA_AND_RDA_FT
Successfully connected to data source X_FT_NFS_CDA_AND_RDA_FT.
[cloud-user@pdcesx41350 ~]$

List files from the Federated Data Source.

You can access the files from both the underlying data sources (RDA File Transfer Service and CDA File Transfer Service). You can list the files from RDA and CDA data sources by specifying the corresponding catalog and schema in the CLI statement.

Code:

cda=sas-data-agent-server-colocated
sas-viya dagentsrv servers set-default --data-agent $cda

##list files/tables from RDA
sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_RDA_CAT --schema FT_NFS_RDA_SCHEMA1

#list files/tables from CDA
sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_CDA_CAT --schema FT_NFS_CDA_SCHEMA1

Log:

[cloud-user@pdcesx41350 ~]$ 
[cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_RDA_CAT --schema FT_NFS_RDA_SCHEMA1
Name                          Type    Native Catalog
books_RDA.txt                 TABLE
cars_source_RDA.csv           TABLE
prdsale_source_RDA.sas7bdat   TABLE
[cloud-user@pdcesx41350 ~]$


[cloud-user@pdcesx41350 ~]$ 
[cloud-user@pdcesx41350 ~]$ sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_CDA_CAT --schema FT_NFS_CDA_SCHEMA1
Name                          Type    Native Catalog
books_CDA.txt                 TABLE
cars_source_CDA.csv           TABLE
prdsale_source_CDA.sas7bdat   TABLE
[cloud-user@pdcesx41350 ~]$

Copy data files between the CDA and RDA storage using the Federated Data Source.

Now that both RDA and CDA data files are available from CDA, it can be copied to each other. The data file can be copied using “dagetsrv ds copy” or “dagetsrv sql “ CLI statement. The data files is accessed specifying the corresponding catalog and schema in the CLI statement. The following CLI statement copies the data files from the CDA to RDA and RDA to CDA using “ds copy” and SQL statement. The “select * from SourceFile” in the SQL statement does not actually execute a query to the data file; it simply interprets that the named file needs to be read and copied over to the target site.

Code:

cda=sas-data-agent-server-colocated
sas-viya dagentsrv servers set-default --data-agent $cda

## Copy files from CDA to RDA
sas-viya dagentsrv ds copy --dsn X_FT_NFS_CDA_AND_RDA_FT --from FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"prdsale_source_CDA.sas7bdat\"  --to FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"prdsale_source_CDA.sas7bdat\"

sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"books_CDA.txt\" AS SELECT * FROM FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"books_CDA.txt\" " --dsn X_FT_NFS_CDA_AND_RDA_FT

sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"cars_source_CDA.csv\" AS SELECT * FROM FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"cars_source_CDA.csv\" " --dsn X_FT_NFS_CDA_AND_RDA_FT

## Copy files from RDA to CDA
sas-viya dagentsrv ds copy --dsn X_FT_NFS_CDA_AND_RDA_FT --from FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"prdsale_source_RDA.sas7bdat\"  --to FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"prdsale_source_RDA.sas7bdat\"

sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"books_RDA.txt\" AS SELECT * FROM FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"books_RDA.txt\" " --dsn X_FT_NFS_CDA_AND_RDA_FT

sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"cars_source_RDA.csv\" AS SELECT * FROM FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"cars_source_RDA.csv\" " --dsn X_FT_NFS_CDA_AND_RDA_FT

Log:

## Copy files from CDA to RDA
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv ds copy --dsn X_FT_NFS_CDA_AND_RDA_FT --from FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"prdsale_source_CDA.sas7bdat\"  --to FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"prdsale_source_CDA.sas7bdat\"
The table was successfully copied.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"books_CDA.txt\" AS SELECT * FROM FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"books_CDA.txt\" " --dsn X_FT_NFS_CDA_AND_RDA_FT
SQL statement successfully executed.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"cars_source_CDA.csv\" AS SELECT * FROM FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"cars_source_CDA.csv\" " --dsn X_FT_NFS_CDA_AND_RDA_FT
SQL statement successfully executed.
[cloud-user@pdcesx41524 ~]$

## Copy files from RDA to CDA

[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv ds copy --dsn X_FT_NFS_CDA_AND_RDA_FT --from FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"prdsale_source_RDA.sas7bdat\"  --to FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"prdsale_source_RDA.sas7bdat\"

The table was successfully copied.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"books_RDA.txt\" AS SELECT * FROM FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"books_RDA.txt\" " --dsn X_FT_NFS_CDA_AND_RDA_FT
SQL statement successfully executed.
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv sql --sql "CREATE TABLE FT_NFS_CDA_CAT.FT_NFS_CDA_SCHEMA1.\"cars_source_RDA.csv\" AS SELECT * FROM FT_NFS_RDA_CAT.FT_NFS_RDA_SCHEMA1.\"cars_source_RDA.csv\" " --dsn X_FT_NFS_CDA_AND_RDA_FT
SQL statement successfully executed.
[cloud-user@pdcesx41524 ~]$

List the files from the RDA and CDA storage after the file copy operation.

You can list the files from RDA and CDA data sources by specifying the corresponding catalog and schema in the CLI statement.

Code:

cda=sas-data-agent-server-colocated
sas-viya dagentsrv servers set-default --data-agent $cda

##list files/tables from RDA
sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_RDA_CAT --schema FT_NFS_RDA_SCHEMA1

#list files/tables from CDA
sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_CDA_CAT --schema FT_NFS_CDA_SCHEMA1

Log:

cloud-user@pdcesx41524 ~]$ #list table from CDA
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_CDA_CAT --schema FT_NFS_CDA_SCHEMA1


Name                           Type    Native Catalog
books_CDA.txt                  TABLE
books_RDA.txt                  TABLE
cars_source_CDA.csv            TABLE
cars_source_RDA.csv            TABLE
prdsale_source_CDA.sas7bdat    TABLE
prdsale_source_RDA.sas7bdat    TABLE
prdsale_source_RDA1.sas7bdat   TABLE
[cloud-user@pdcesx41524 ~]$
[cloud-user@pdcesx41524 ~]$ #list table from RDA
[cloud-user@pdcesx41524 ~]$ sas-viya dagentsrv data-sources tables list --data-source X_FT_NFS_CDA_AND_RDA_FT --catalog FT_NFS_RDA_CAT --schema FT_NFS_RDA_SCHEMA1
Name                          Type    Native Catalog
books_CDA.txt                 TABLE
books_RDA.txt                 TABLE
cars_source_CDA.csv           TABLE
cars_source_RDA.csv           TABLE
prdsale_source_CDA.sas7bdat   TABLE
prdsale_source_RDA.sas7bdat   TABLE
[cloud-user@pdcesx41524 ~]$

Copy data files between CDA and RDA from the SAS Compute Server

With the Federated Data Source configured with File Transfer Service from both RDA and CDA environments, SAS users can now access and copy the data files between CDA and RDA storage.

The following SAS code describes the file transfer from RDA to CDA and vice versa. Notice, you can copy a variety of file types between the CDA and RDA, including a .json file.

Code:

%let Rcat=FT_NFS_RDA_CAT ;
%let Rsch=FT_NFS_RDA_SCHEMA1 ;
%let Ccat=FT_NFS_CDA_CAT ;
%let Csch=FT_NFS_CDA_SCHEMA1 ;
%let Xdsn=X_FT_NFS_CDA_AND_RDA_FT ;


/* copy file from RDA to CDA */
PROC SQL;
connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ;
EXECUTE( CREATE TABLE &Ccat..&Csch.."prdsale_source_RDA11.sas7bdat" AS SELECT * FROM &Rcat..&Rsch.."prdsale_source_RDA.sas7bdat") BY CDE;
run;QUIT;

/* copy file from RDA to CDA */
PROC SQL;
connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ;
EXECUTE( CREATE TABLE &Ccat..&Csch.."books_RDA11.txt" AS SELECT * FROM &Rcat..&Rsch.."books_RDA.txt") BY CDE;
run;QUIT;

/* copy file from RDA to CDA */
PROC SQL;
connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ;
EXECUTE( CREATE TABLE &Ccat..&Csch.."reviews_RDA11.json" AS SELECT * FROM &Rcat..&Rsch.."reviews_RDA.json") BY CDE;
run;QUIT;

/* copy file from CDA to RDA */
PROC SQL;
connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ;
EXECUTE( CREATE TABLE &Rcat..&Rsch.."books_CDA11.txt" AS SELECT * FROM &Ccat..&Csch.."books_CDA.txt") BY CDE;
run;QUIT;

/* copy file from CDA to RDA */
PROC SQL;
connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ;
EXECUTE( CREATE TABLE &Rcat..&Rsch.."cars_source_CDA11.csv" AS SELECT * FROM &Ccat..&Csch.."cars_source_CDA.csv") BY CDE;
run;QUIT;

/* copy file from CDA to RDA */
PROC SQL;
connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ;
EXECUTE( CREATE TABLE &Rcat..&Rsch.."reviews_CDA11.json" AS SELECT * FROM &Ccat..&Csch.."reviews_CDA.json") BY CDE;
run;QUIT;

Log:

80   %let Rcat=FT_NFS_RDA_CAT ;
81   %let Rsch=FT_NFS_RDA_SCHEMA1 ;
82   %let Ccat=FT_NFS_CDA_CAT ;
83   %let Csch=FT_NFS_CDA_SCHEMA1 ;
84   %let Xdsn=X_FT_NFS_CDA_AND_RDA_FT ;
85
86
87   /* copy file from RDA to CDA */
88   PROC SQL;
89   connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ;
90   EXECUTE( CREATE TABLE &Ccat..&Csch.."prdsale_source_RDA11.sas7bdat" AS SELECT * FROM
90 ! &Rcat..&Rsch.."prdsale_source_RDA.sas7bdat") BY CDE;
91   run;QUIT;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.73 seconds
      cpu time            0.17 seconds

92
93   /* copy file from RDA to CDA */
94   PROC SQL;
95   connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ;
96   EXECUTE( CREATE TABLE &Ccat..&Csch.."books_RDA11.txt" AS SELECT * FROM &Rcat..&Rsch.."books_RDA.txt") BY CDE;
97   run;QUIT;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.27 seconds
      cpu time            0.16 seconds

98
99   /* copy file from RDA to CDA */
100  PROC SQL;
101  connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ;
102  EXECUTE( CREATE TABLE &Ccat..&Csch.."reviews_RDA11.json" AS SELECT * FROM &Rcat..&Rsch.."reviews_RDA.json") BY CDE;
103  run;QUIT;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.22 seconds
      cpu time            0.13 seconds

104
105
106  /* copy file from CDA to RDA */
107  PROC SQL;
108  connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ;
109  EXECUTE( CREATE TABLE &Rcat..&Rsch.."books_CDA11.txt" AS SELECT * FROM &Ccat..&Csch.."books_CDA.txt") BY CDE;
110  run;QUIT;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.28 seconds
      cpu time            0.15 seconds

111
112  /* copy file from CDA to RDA */
113  PROC SQL;
114  connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ;
115  EXECUTE( CREATE TABLE &Rcat..&Rsch.."cars_source_CDA11.csv" AS SELECT * FROM &Ccat..&Csch.."cars_source_CDA.csv") BY CDE;
116  run;QUIT;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.25 seconds
      cpu time            0.14 seconds

117
118  /* copy file from CDA to RDA */
119  PROC SQL;
120  connect to CDE (dataagentname="sas-data-agent-server-colocated" dsn=&Xdsn) ;
121  EXECUTE( CREATE TABLE &Rcat..&Rsch.."reviews_CDA11.json" AS SELECT * FROM &Ccat..&Csch.."reviews_CDA.json") BY CDE;
122  run;QUIT;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.23 seconds
      cpu time            0.16 seconds

Verify the CDA and RDA data files from the SAS Compute Server

The following SAS code lists the files from CDA and RDA under the respective LIBNAME reference.

Code:

%let Rcat=FT_NFS_RDA_CAT ;
%let Rsch=FT_NFS_RDA_SCHEMA1 ;
%let Ccat=FT_NFS_CDA_CAT ;
%let Csch=FT_NFS_CDA_SCHEMA1 ;
%let Xdsn=X_FT_NFS_CDA_AND_RDA_FT ;


LIBNAME cdefedC cde dataagentname="sas-data-agent-server-colocated"
dsn=&Xdsn catalog=&Ccat schema=&Csch preserve_tab_names=yes;

LIBNAME cdefedR cde dataagentname="sas-data-agent-server-colocated"
dsn=&Xdsn catalog=&Rcat schema=&Rsch  preserve_tab_names=yes;

Results:

02_UK_CDE_Configure_FileTransfer_Service_Data_Source_2.png