PROC SQL Syntax

Reply
Occasional Contributor
Posts: 8

PROC SQL Syntax

Hello,

 

Just a general question.  When using PROC SQL, can you literally plug in an existing SQL Query between PROC SQL; and QUIT; which you've used previously in Teradata?  Or are there significant changes to systax of the SQL query itself that need to be made in order to be successfully executed in SAS?

Super User
Posts: 19,770

Re: PROC SQL Syntax

Posted in reply to Teazelado


When using PROC SQL, can you literally plug in an existing SQL Query between PROC SQL; and QUIT; which you've used previously in Teradata?  

No. However, you can use pass through SQL, which literally allows you to pass a Teradata query to your database directly.

 



 Or are there significant changes to systax of the SQL query itself that need to be made in order to be successfully executed in SAS?

Depends. If the query uses windowing/pivot functions and functions that aren't recognized in SAS then you'll need to change the syntax. Window/Pivot will require significant changes. Functions can usually be easily changed.

 

 

Occasional Contributor
Posts: 8

Re: PROC SQL Syntax

Got it thanks.  I did some research on pass through and found this below...

PROC SQL <option(s)>;
CONNECT TO dbms-name <AS alias> <(<database-connection-arguments> <connect-statement-arguments> )>;
DISCONNECT FROM dbms-name | alias;
EXECUTE (dbms-specific-SQL-statement) BY dbms-name | alias;
SELECT column-list FROM CONNECTION TO dbms-name | alias (dbms-query)


SQL Query:  

 

So sounds to me I should be able to plug in my teradata query as is, right into the EXECUTE STATEMENT above?  It should return the results to me in SAS.  Does this sound correct?

 

Example:

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City = 'London'

 

SQL Query inserted into EXECUTE Statement:

PROC SQL <option(s)>;
CONNECT TO dbms-name <AS alias> <(<database-connection-arguments> <connect-statement-arguments> )>;
DISCONNECT FROM dbms-name | alias;
EXECUTE (SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City = 'London') BY dbms-name | alias;
SELECT column-list FROM CONNECTION TO dbms-name | alias (dbms-query)

 

Am I on the right track?

Super User
Posts: 19,770

Re: PROC SQL Syntax

Posted in reply to Teazelado

It's close but not quite there.

The documentation has a good example:

http://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#n1kbstf7vw0qcjn1ibfc...

 

This is for Oracle but I think its teh same for Teradata

 

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;
Ask a Question
Discussion stats
  • 3 replies
  • 313 views
  • 0 likes
  • 2 in conversation