Something about SAS/ACCESS Software

www.signetsoft.com

Definition of SAS/ACCESS Software

SAS/ACCESS software

enables you to read and write data to and from other vendors' database management systems (DBMS), as well as from some PC file formats. Depending on your DBMS, a SAS/ACCESS product might provide one or more of the following:

•  a dynamic LIBNAME engine
•  the SQL Pass-Through Facility
•  the ACCESS procedure and interface view engine
•  the DBLOAD procedure
•  an interface DATA step engine.

These interfaces are described in this section. Each SAS/ACCESS product provides one or more of these interfaces for each supported DBMS. See SAS Engines for more information about SAS engines.

Note:   To use the SAS/ACCESS features described in this section, you must license SAS/ACCESS software. See the SAS/ACCESS documentation for your DBMS for full documentation of the features described in this section.

Dynamic LIBNAME Engine

SAS/ACCESS LIBNAME Statement

Beginning in Version 7, you can associate a SAS libref directly with a database, schema, server, or group of tables and SAS views, depending on your DBMS. To assign a libref to DBMS data, you must use the SAS/ACCESS LIBNAME statement, which has syntax and options that are different from the Base SAS LIBNAME statement. For example, to connect to an ORACLE database, you might use the following SAS/ACCESS LIBNAME statement:

libname mydblib oracle user=smith password=secret 
   path='myoracleserver';

This LIBNAME statement connects to ORACLE by specifying the ORACLE connection options: USER=, PASSWORD=, and PATH=. In addition to the connection options, you can specify SAS/ACCESS LIBNAME options that control the type of database connection that is made. You can use additional options to control how your data is processed.

You can use a DATA step, SAS procedures, or the Explorer window to view and update the DBMS data associated with the libref, or use the DATASETS and CONTENTS procedures to view information about the DBMS objects.

See your SAS/ACCESS documentation for a full listing of the SAS/ACCESS LIBNAME options that can be used with librefs that refer to DBMS data.

Using Data Set Options with SAS/ACCESS Librefs

After you have assigned a libref to your DBMS data, you can use SAS/ACCESS data set options, and some of the Base SAS data set options, on the data. The following example associates a libref with DB2 data and uses the SQL procedure to query the data:

libname mydb2lib db2;

proc sql;
   select * 
      from mydb2lib.employees(drop=salary)
      where dept='Accounting';
quit;

The LIBNAME statement connects to DB2. You can reference a DBMS object, in this case, a DB2 table, by specifying a two-level name that is comprised of the libref and the DBMS object name. The DROP= data set option causes the SALARY column of the EMPLOYEES table on DB2 to be excluded from the data that is returned by the query.

See your SAS/ACCESS documentation for a full listing of the SAS/ACCESS data set options and the Base SAS data set options that can be used on data sets that refer to DBMS data.

Embedding a SAS/ACCESS LIBNAME Statement in a PROC SQL View

You can issue a SAS/ACCESS LIBNAME statement by itself, as shown in the previous examples, or as part of a CREATE VIEW statement in PROC SQL. The USING clause of the CREATE VIEW statement enables you to store DBMS connection information in a SAS view by embedding a SAS/ACCESS LIBNAME statement inside the SAS view. The following example uses an embedded SAS/ACCESS LIBNAME statement:

libname viewlib 'SAS-library'; 

proc sql;
   create view viewlib.emp_view as
      select * 
         from mydblib.employees
         using libname mydblib oracle user=smith password=secret 
            path='myoraclepath';
quit;   

When PROC SQL executes the SAS view, the SELECT statement assigns the libref and establishes the connection to the DBMS. The scope of the libref is local to the SAS view and does not conflict with identically named librefs that might exist in the SAS session. When the query finishes, the connection is terminated and the libref is deassigned.

Note:   You can also embed a Base SAS LIBNAME statement in a PROC SQL view. 

SQL Procedure Pass-Through Facility

The SQL Procedure Pass-Through Facility is an extension of the SQL procedure that enables you to send DBMS-specific statements to a DBMS and to retrieve DBMS data. You specify DBMS SQL syntax instead of SAS SQL syntax when you use the Pass-Through Facility. You can use Pass-Through Facility statements in a PROC SQL query or store them in a PROC SQL view.

The Pass-Through Facility consists of three statements and one component:

•  The CONNECT statement establishes a connection to the DBMS.
•  The EXECUTE statement sends dynamic, non-query DBMS-specific SQL statements to the DBMS.
•  The CONNECTION TO component in the FROM clause of a PROC SQL SELECT statement retrieves data directly from a DBMS.
•  The DISCONNECT statement terminates the connection to the DBMS.

The following Pass-Through Facility example sends a query to an ORACLE database for processing:

proc sql;
   connect to oracle as myconn (user=smith password=secret 
      path='myoracleserver');

   select * 
      from connection to myconn
         (select empid, lastname, firstname, salary
            from employees
            where salary>75000);

   disconnect from myconn;
quit;

The example uses the Pass-Through CONNECT statement to establish a connection with an ORACLE database with the specified values for the USER=, PASSWORD=, and PATH= arguments. The CONNECTION TO component in the FROM clause of the SELECT statement enables data to be retrieved from the database. The DBMS-specific statement that is sent to ORACLE is enclosed in parentheses. The DISCONNECT statement terminates the connection to ORACLE.

To store the same query in a PROC SQL , use the CREATE VIEW statement:

libname viewlib 'SAS-library';

proc sql;    
   connect to oracle as myconn (user=smith password=secret 
      path='myoracleserver'); 
    
   create view viewlib.salary as
      select * 
         from connection to myconn       
            (select empid, lastname, firstname, salary          
               from employees          
               where salary>75000);  
   
   disconnect from myconn; 
quit; 

ACCESS Procedure and Interface View Engine

The ACCESS procedure enables you to create access descriptors, which are SAS files of member type ACCESS. They describe data that is stored in a DBMS in a format that SAS can understand. Access descriptors enable you to create SAS/ACCESS views, called view descriptors. View descriptors are files of member type VIEW that function in the same way as SAS views that are created with PROC SQL, as described in Embedding a SAS/ACCESS LIBNAME Statement in a PROC SQL View and SQL Procedure Pass-Through Facility.

Note:   If a dynamic LIBNAME engine is available for your DBMS, it is recommended that you use the SAS/ACCESS LIBNAME statement to access your DBMS data instead of access descriptors and view descriptors; however, descriptors continue to work in SAS software if they were available for your DBMS in Version 6. Some new SAS features, such as long variable names, are not supported when you use descriptors.  

The following example creates an access descriptor and a view descriptor in the same PROC step to retrieve data from a DB2 table:
libname adlib 'SAS-library';
libname vlib 'SAS -library';

proc access dbms=db2;
   create adlib.order.access;
   table=sasdemo.orders;
   assign=no;
   list all;

   create vlib.custord.view;
   select ordernum stocknum shipto;
   format ordernum 5.
          stocknum 4.;
run;

proc print data=vlib.custord;
run; 



When you want to use access descriptors and view descriptors, both types of descriptors must be created before you can retrieve your DBMS data. The first step, creating the access descriptor, enables SAS to store information about the specific DBMS table that you want to query.

After you have created the access descriptor, the second step is to create one or more view descriptors to retrieve some or all of the DBMS data described by the access descriptor. In the view descriptor, you select variables and apply formats to manipulate the data for viewing, printing, or storing in SAS. You use only the view descriptors, and not the access descriptors, in your SAS programs.

The interface view engine enables you to reference your SAS view with a two-level SAS name in a DATA or PROC step, such as the PROC PRINT step in the example.

See SAS Views for more information about SAS views. See the SAS/ACCESS documentation for your DBMS for more detailed information about creating and using access descriptors and SAS/ACCESS views.



DBLOAD Procedure

The DBLOAD procedure enables you to create and load data into a DBMS table from a SAS data set, data file, SAS view, or another DBMS table, or to append rows to an existing table. It also enables you to submit non-query DBMS-specific SQL statements to the DBMS from your SAS session.

Note:   If a dynamic LIBNAME engine is available for your DBMS, it is recommended that you use the SAS/ACCESS LIBNAME statement to create your DBMS data instead of the DBLOAD procedure; however, DBLOAD continues to work in SAS software if it was available for your DBMS in Version 6. Some new SAS features, such as long variable names, are not supported when you use the DBLOAD procedure.  

The following example appends data from a previously created SAS data set named INVDATA into a table in an ORACLE database named INVOICE:
proc dbload dbms=oracle data=invdata append;
  user=smith;
  password=secret;
  path='myoracleserver';
  table=invoice;
  load;
run;





See the SAS/ACCESS documentation for your DBMS for more detailed information about the DBLOAD procedure.



(courtesy: sas.com)





 



www.signetsoft.com



Signetsoft is the Best Training institute in Bangalore for SAS, Clinical Research, Clinical Data Management, Clinical SAS, SAS Projects, Java, J2EE, Oracle, SQL, PL/SQL, Cognos, Informatica, Advanced Excel, VBA etc… For further details...



please visit: www.Signetsoft.com , Bangalore

or mails us :  info@signetsoft.com

 

www.signetsoft.com

All the classes are fully practical and 100% job oriented.





Thanks for visiting our blog.