SAS Procedures :: SAS(R) 9.3 LIBNAME Engine for DataFlux(R) Federation Server: User's Guide

COMPARE Procedure

You can submit the COMPARE procedure to compare the contents of two tables, selected columns in different tables, or columns within the same table.

If you compare the contents of columns from different data sources, PROC COMPARE might report numeric precision differences due to a data source's implementation of a data type.

To compare columns from different data sources, you can specify equality criterion to control how values are compared. Two options for the PROC COMPARE statement determine the equality criterion for comparing values:

CRITERION=

specifies the criterion for judging the equality of numeric values.

METHOD=

specifies the method for judging the equality of numeric values.

For more information about these options, see the COMPARE procedure in Base SAS Procedures Guide.

CONTENTS Procedure

You can submit the CONTENTS procedure (or the CONTENTS statement in the DATASETS procedure) to list the attributes of a table (such as the date when the table was created, the date when data was last modified, and so on) or the contents of a directory.

However, there are some limitations for the CONTENTS procedure:

Index information is not displayed.
The number of observations (rows) are not displayed for tables that have row-level permission.
The results of a PROC CONTENTS depend on the data source. For example, some of the attributes that list for a SAS data set do not list for a third-party database table.

SORT Procedure

When you use the SORT procedure, you will observe behavior differences:

The engine does not support file replacement. That is, you cannot overwrite an existing table that has the same name. For example, you must specify the OUT= option for the SORT procedure:
```
libname myfiles fedsvr server="d1234.us.company.com" 
   port=2171 user="myid" pwd="mypwd"
   dsn=basedsn; 

proc sort data=myfiles.table1 out=myfiles.table1_sorted;
   by column1;
run;
```
There might be behavior differences based on the accessed data source. For example, you can sort the rows of a SAS data set and store the output to another SAS data set. However, for a third-party DBMS, sorting data often has no effect on how it is stored. When you access third-party DBMS tables, they are stored in the format of the database, which differs from the format of SAS files, such as a SAS data set.

Because you cannot depend on your data to be sorted in the database, you must sort the data at the time of query. Furthermore, when you sort DBMS data, the results might vary depending on whether the DBMS places data with null values (which are translated to SAS missing values) at the beginning or the end of the result set.

When the output table is written, the sorted data is written to the output table in the sorted order. However, when an application subsequently reads the output table from the DBMS, the DBMS sends the data back in an order unrelated to the sort.

A solution might be to specify a SAS file such as a SAS data set as the output table in the OUT= option as shown in the following code:
```
libname oradata fedsvr server="d1234.us.company.com" 
   port=2171 user="myid" pwd="mypwd" dsn=oradsn; 

proc sort data=oradata.table1 out=work.table1_sorted;
   by column1 column2;
run;

proc print data=work.table1_sorted;
run;
```

SQL Procedure

The engine supports the SQL procedure so that you can retrieve and manipulate data, create tables, and add or modify data.

You can also use the PROC SQL pass-through facility to connect to a data source, and submit DataFlux FedSQL statements. However, you cannot use the PROC SQL pass-through facility to send DBMS-specific SQL statements directly to a DBMS for execution.