Using SAS Enterprise Guide or SAS for Windows with the ODS

The University has a number of staff who are experienced in writing reports and doing statistical analysis using the popular programming language SAS. Please note, we do not recommend that anyone attempt to learn SAS solely for use with ODS data unless they have a particular need to do so. SAS is a fully featured programming language and it requires a substantial time commitment for a new user to master it.

SAS Enterprise Guide and SAS for Windows are available at no charge from the ITS Research Computing Support Center in Wilson Hall (see SAS Use and Support).

Before trying to connect to the ODS using SAS, please make sure you have followed the instructions for Access the ODS Using Other Tools.

Note: These comments assume that the user is already familiar with programming with SAS Data steps and Proc steps.

The following SAS statement can be used to connect to the ODS:

libname ods oracle Path='iods'dbprompt=yes defer=no schema='noetix_sys';

ods, where it appears after the word libname, is the name which will be used to refer to the ODS in the program. (Another name would work as well). This statement will cause the user to be presented with an Oracle log in screen when the program is executed.

The schema option above specifies the owner or schema name for the tables you will be querying. The Noetix Views are owned by NOETIX_SYS. The U.Va. custom views are owned by APPS. If you will be querying both APPS and NOETIX_SYS views, you can leave the schema option out of the libname statement and specify it whenever you refer to an ods tablename. See example below.

IMPORTANT NOTE: SAS does not permit the $ in variable names, and substitutes an underscore whenever one appears. So for example, if you wish to refer to the Noetix column A$User_Name, you should refer to it as A_User_Name.

While you can use the SAS set statement to access data from the ODS views, in our limited testing, it has proven to be somewhat unreliable, sometimes producing inexplicable errors. Proc SQL seems to be more reliable. The following Example creates and populates the SAS dataset test with the columns user_name and responsibility_name.

create table test as
proc sql;
select user_name, responsibility_name
from ods.fndap_user_responsibility (schema='noetix_sys')
where user_name = 'IS";

You can then use a data step to further process the data in test.

Once started, proc sql runs until you enter a quit; statement.

If you choose to use the set statement to access data from he ODS views, you must use the Where statement in order to query the database efficiently;

set ods.fndap_users(schema=NOETIX_SYS');
where user_name = 'IS';

In this example, the ODS is queried for information on the user IS.

Had you instead used the syntax:

data user:
set ods,fndap_users(schema='noetix_sys');
if user_name = 'IS';

then SAS would have queried the database for every record in the table noetix_sys.fndap_users, and then once the data was returned to your PC, SAS would have eliminated the records you did not want. This is very inefficient, and with larger tables would be impractical.

If you have finished using the ODS, but are still using SAS, execute the statement:

libname ods clear;

to disconnect from the ODS.