The University has a number of staff who are experienced in writing reports and doing statistical analysis using the popular programming language SAS. The Windows version of SAS can now access the ODS as well. 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. Even those already experienced with SAS on another platform, such as the Enterprise Server (Mercury) are likely to find the transition to SAS for Windows challenging. The other tools mentioned on this website are a more viable alternative for most users. However, for those already experienced with SAS, it may be a very valuable tool for use with the ODS.

SAS for Windows version 8.1 is available at no charge from the ITC Research Computing Support Center in Wilson Hall. (See License Restrictions.) Workshops in basic SAS usage are available from ITC Training (although these classes will not cover working with the ODS.

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 futher 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;

datauser;
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.