SQL Cross Via SAS – 9TO5SAS

The SQL move by SAS Facility is an extension to the SQL process that allows you to ship DBMS-specific statements to a database administration system and retrieve DBMS knowledge instantly.

To make use of the SQL Process Cross-Via Facility, you will need to have SAS/ACCESS software program as a result of it makes use of SAS/ACCESS to connect with the DBMS. Thus, when utilizing the SQL Process Cross-Via Facility, you specify DBMS syntax, not SAS SQL.

The aim of this part is to offer normal details about the SQL Process Cross-Via Facility.

PROC SQL will attempt to move as a lot logic as doable to the database, however there are occasions when it will be unable to.

If SAS features are utilized in a question with out an equal within the database (or within the SAS/ACCESS engine), the question can’t be handed to the database.

The information is pulled into SAS when the question shouldn’t be totally handed to the database. Here’s a case that makes a bigger distinction than you may notice.

libname odb <database> path=dbserver person=... password=...;
proc sql;
   create desk odb.new as
   choose * from db.oracledata the place flag=1;
stop;

It can pull all the information matching flag=1 from the database and cargo it again into SAS. Because the variety of rows will increase, the method slows down. On this case, a pass-through could be a lot sooner.

proc sql;
   join dbase (server=dbserver person=person password=move);
   execute (create desk odb.new as
   choose * from db.oracledata the place flag=1) as dbase;
   disconnect dbase;
stop;

SQL Process Cross-Via Facility consists of three statements and one element:

• The CONNECT assertion establishes a reference to the DBMS.
• The EXECUTE assertion sends dynamic, non-query DBMS-specific SQL statements to the DBMS.
• The CONNECTION TO element within the FROM clause of a PROC SQL SELECT assertion retrieves knowledge instantly from a DBMS.
• The DISCONNECT assertion terminates the reference to the DBMS.

Any arguments you specify within the corresponding CONNECT assertion are saved everytime you create a PROC SQL view.

A SAS program can use the PROC SQL view to ascertain the suitable connection to a DBMS.

The syntax for the SQL Process Cross-Via Facility

A SQL process pass-through facility is used to ship an SQL assertion to a DBMS that’s DBMS-specific and doesn’t require a question:

PROC SQL;	<CONNECT TO DBMS-name <AS alias><
<(connect-statement-argument-1=worth
...<connect-statement-argument-n=worth>)>>
<(DBMS-argument-1=worth
...<dbms-argument-n=worth>)>>;
	EXECUTE (DBMS-SQL-statement)
BY dbms-name|alias;
	<DISCONNECT FROM dbms-name|alias;>

<QUIT;>

Utilizing SAS/ACCESS LIBNAME statements or PROC SQL Cross-Via statements CONNECTION TO, you possibly can hook up with a DBMS and question its knowledge:

PROC SQL;	<CONNECT TO DBMS-name <AS alias><
<(connect-statement-argument-1=worth
...<connect-statement-argument-n=worth>)>>
<(DBMS-argument-1=worth
...<dbms-argument-n=worth>)>>;
	SELECT column-list	FROM CONNECTION TO dbms-name|alias
(DBMS-query)
non-obligatory PROC SQL clauses;

	<DISCONNECT FROM dbms-name|alias;>

<QUIT;>

The next pass-through facility instance sends a question to an ORACLE database for processing:

proc sql;
   hook up with oracle as myconn (person=smith password=secret 
      path='myoracleserver');

   choose * 
      from connection to myconn
         (choose empid, lastname, firstname, wage
            from staff
            the place wage>75000);

   disconnect from myconn;
stop;

On this instance, the CONNECT assertion establishes a reference to an ORACLE database utilizing the arguments USER=, PASSWORD=, and PATH=.

The CONNECTION TO element within the FROM clause of the SELECT assertion permits knowledge to be retrieved from the database.

The DBMS-specific assertion that’s despatched to ORACLE is enclosed in parentheses. The DISCONNECT assertion terminates the connection to ORACLE.

To retailer the identical question in a PROC SQL, use the CREATE VIEW assertion:

libname viewlib 'SAS-library';
proc sql;    
   hook up with oracle as myconn (person=usr password=move 
      path='myoracleserver'); 
   create view viewlib.wage as
      choose * 
         from connection to myconn       
            (choose empid, lastname, firstname, wage          
               from staff          
               the place wage>75000);  
   disconnect from myconn; 
stop; 

SQL Process Cross-Via Facility Return Codes

An error situation is logged within the SAS log once you use the PROC SQL statements out there within the SQL Process Cross-Via Facility.

Moreover, the SQL Process Cross-Via Facility generates return codes and messages that you would be able to entry by SAS macro variables:

SQLXRC – It contains the DBMS return code that identifies the DBMS error.

SQLXMSG – It contains descriptive information about the DBMS error generated by the DBMS.

In the SAS log, the SQLXRC and SQLXMSG macro variables are printed. After executing a SQL Procedure Pass-Through Facility statement, the macro variables SQLXRC and SQLXMSG are reset.

As you can see, the benefits of using SQL pass through SAS are many. These are just a few of the key points to consider. Ultimately, it is a viable option that SAS programmers should consider repeatedly.

Related