DS2 :: SAS® Federation Server 4.2: Administrator's Guide, Second Edition

About DS2

DS2 is a SAS proprietary programming language that is used for advanced data manipulation. DS2 provides capabilities not available through SQL, such as scoring models. In addition, you can use DS2 code to run data quality functions on SAS Federation Server. DS2 is included with Base SAS and intersects with the SAS DATA step.

To invoke DS2, you must configure a DSN that uses the DS2 dialect and grant users CONNECT permission to the DSN. In addition, users must have EXECUTE permissions on DS2 objects, such as packages and threads, before any SQL functions can be run against them.

DS2 objects inherit privileges from the server in the following order:

SERVER
(DATA) SERVICE
CATALOG
SCHEMA
PACKAGE
FUNCTION

For more information about DS2, see the SAS DS2 Language Reference.

Viewing DS2 Package Contents

To view the contents of DS2 programs, use the DESCRIBE PACKAGE or DESCRIBE THREAD command. Only administrators or schema owners can run these commands, unless the PL Source Management Security option is set to FALSE.

DESCRIBE PACKAGE DS2-program-name
DESCRIBE THREAD DS2-program-name

User Permissions

Users require EXECUTE permissions on DS2 objects, so they can submit a DS2 code stream or manage packages and threads. Use the GRANT, DENY, or REVOKE DDL statements to establish permissions for a user or group.

Here are examples that grant, deny, and revoke the EXECUTE permission on a DS2 package or thread:

GRANT EXECUTE on package basecat.basetest.pkgGrade to "user-name"
DENY EXECUTE on package basecat.basetest.pkgGrade to "user-name"
REVOKE EXECUTE on package basecat.basetest.pkgGrade from "user-name"

Only administrators and schema owners can CREATE or DROP a DS2 object.

The table below summarizes permissions for DS2 objects.

Object Permission		DS2 Data Program	DS2 Thread	DS2 Package	Method in a DS2 Package
CREATE		Not applicable	Connect DS2 DSN / EXECUTE on server (when connection string is used). Schema owner	Connect DS2 DSN / EXECUTE on server (when connection string is used). Schema owner	Created along with a DS2 package.
DROP		Not applicable	Connect DS2 DSN / EXECUTE on server (when connection string is used). Schema owner	Connect DS2 DSN / EXECUTE on server (when connection string is used). Schema owner	Dropped along with a DS2 package.
EXECUTE	SQL/Call statement	Not applicable	Not applicable	Not applicable	Connect DS2 DSN with FEDSQL dialect. EXECUTE on the DS2 method.
EXECUTE	DS2 program	Connect DS2 DSN / EXECUTE on server (when connection string is used).	Connect DS2 DSN / EXECUTE on server (when connection string is used). EXECUTE on the DS2 thread.	Connect DS2 DSN / EXECUTE on server (when connection string is used). EXECUTE on the DS2 thread.	Connect DS2 DSN / EXECUTE on server (when connection string is used). EXECUTE on the package that contains the method.

Administrators have the required privileges to run or manage DS2 objects for all of the cases described above.

Note: Regarding input/output tables: If a DS2 program reads data from an input data set, SELECT privilege is required on the input table. If a DS2 program creates and output data set, then CREATE, INSERT, SELECT, and DROP are required on the output table.

Securing DS2 Objects

DS2 objects that must be secured include package functions and threads. Typically, a function is invoked through a CALL statement, a FedSQL function, or referenced from a DS2 data program. Functions and objects referenced from a DS2 program are secured with the EXECUTE privilege. To grant or deny the EXECUTE privilege on a DS2 package or thread, use the following DDL statements:

GRANT EXECUTE on package package-name to username

DENY EXEUCTE on package package-name to username

When placing the EXECUTE privilege on a function, and specifying names in the statement, only three levels of naming are allowed. Therefore, place quotation marks around the “packagename.functionname” so that it is treated as one object name. Here are some examples:

GRANT/DENY EXECUTE on FUNCTION basecat.basetest."pkgGrade.compute" to username
REVOKE EXECUTE on FUNCTION basecat.basetest."pkgGrade.compute" from username

You can also place the EXECUTE privilege on specific DS2 object containers, which are inherited by other DS2 objects:

GRANT EXECUTE on SERVICE BASE to PUBLIC;
DENY EXECUTE on CATALOG basecat to PUBLIC;
GRANT EXECUTE on SCHEMA basecat.basetest to public;

Invoking DS2

Configure a DS2 DSN

You can configure a standard DSN or a federated DSN with the DS2 dialect. Use the CREATE DSN statement with the LANG option to invoke the DS2 dialect, [for example, {OPTIONS (LANG DS2)}]. Here is the syntax:

CREATE DSN dsn-name under BASE noprompt 'catalog="catalog-name";
schema="schema-name"' {OPTIONS ( LANG DS2 )};

Here are some examples of a standard DSN using the DS2 dialect:

CREATE DSN test UNDER data-service-name NOPROMPT
'CATALOG=LD_CAT1_BASE;SCHEMA=(NAME=LD_SCHEMA1_BASE)' {OPTIONS (LANG DS2,
SECURITY YES, CREDENTIALS_SEARCH_ORDER(SHARED))}

A federated DS2 DSN can include DS2, FedSQL, or native child DSNs. Here is an example of a federated DSN that uses DS2:

CREATE DSN federated-DS2 UNDER BASE {OPTIONS ( LANG DS2 )} ADD (DSN1, DSN2, DSN3)

You can also configure DSNs with the DS2 dialect using SAS Federation Server Manager. Create a new standard or federated DSN and select the DS2 language at the Syntax dialog box.

DS2 DSN, SAS Federation Server Manager

See the SAS Federation Server Manager: User’s Guide for detailed instructions to create DSNs.

Execute the Code

After creating the DSN with the DS2 dialect, execute the code using the Console in SAS Federation Server Manager.

Open the Console in Federation Server Manager and perform the following actions:
1. Using the Server list menu, select the federation server that contains the Base DSN for DS2.
2. Using the Connection list menu, select the Base DSN for DS2.
3. Using the list menu at Select connection language, select DS2 and click OK.
Enter, or copy, your statement in the Console and click Submit.
```
data outtable(overwrite=yes);
dcl double x;
method run();
x=1;
output;
end;
```