Proc SQL - Import a table function

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Proc SQL - Import a table function

Hi,

 

I'm trying to import a table function from my data base with Proc Sql but I'm having troubles with the parameters of this function.

In sql I import like this - select * from xxx (GETDATE()) in SAS i'm doing like this 

Proc sql;
Create table SBK as
Select *
From xxx (today());
Quit;

 

But the function is not recognized, anyone know how to do it?

Thanks


Accepted Solutions
Solution
‎01-03-2017 12:41 PM
Super User
Super User
Posts: 6,842

Re: Proc SQL - Import a table function

Looks like it worked!

NOTE: Table WORK.MYDATA created, with 383834 rows and 32 columns.

You don't need to use the EXECUTE () statement.  That is for commands you want to run that do NOT return results.

View solution in original post


All Replies
Super User
Posts: 19,051

Re: Proc SQL - Import a table function

What should happen with the today() portion of the query? 

Occasional Contributor
Posts: 6

Re: Proc SQL - Import a table function

Hi thanks for your answer,

I should have the cut off date as result.

Super Contributor
Posts: 339

Re: Proc SQL - Import a table function

Either

 Select * From xxx (Where=(Date=Today()));

* .. OR ..;

Select * From xxx Where Date=Today();

* ??;
Occasional Contributor
Posts: 6

Re: Proc SQL - Import a table function

Thanks for the help.
I try to use both examples you send me and I have this error in log.
Parameters were not supplied for the function
Super User
Posts: 11,107

Re: Proc SQL - Import a table function

Post the LOG with the code and the error message. Paste into the {i} code box.

Super User
Super User
Posts: 6,842

Re: Proc SQL - Import a table function

You query does not look like SQL to me.  If you want the select the result of the TODAY() function then you should reference the function in the list of values in the SELECT statement not treat it as if it was a table and try to reference it in the FROM clause.  However PROC SQL does expect to see a FROM clause in a query.  You could try referencing a table like SASHELP.CLASS that should always exist.

proc sql;
  create table SBK as
    select today() format=date9. as today
    from sashelp.class(obs=1)
  ;
quit;

 

 

 

Occasional Contributor
Posts: 6

Re: Proc SQL - Import a table function

Hi Tom,

The example you send me works but don't work to what I pretend.
Probably I don't explain well, I want to access to a sql function (not a table) using SAS.
It's that possible?
Thanks in advanced
Super User
Posts: 19,051

Re: Proc SQL - Import a table function

Yes, you can use functions but they have to be SAS functions and valid SAS code. The code you've posted isn't valid so without knowing anything besides a desire to use a function, all we can say is, yes you can use functions. 

 

Is it a DB function or a user defined function or a SAS function? 

 

See the documentation and examples. If you need further help post more details. 

 

http://support.sas.com/documentation/cdl/en/sqlproc/69822/HTML/default/viewer.htm#p0jhh7e1ipagann1by...

Super User
Super User
Posts: 6,842

Re: Proc SQL - Import a table function

[ Edited ]

If you want to run SQL code in your foreign database (Oracle, MySQL, Teradata, SQL Server, etc) then you have two methods in PROC SQL.  You can query using the FROM CONNECTION TO XXX syntax of a SELECT statement or you can just run code by using the EXECUTE statement.  The code you send must be valid syntax for the foreign database.  To retreive the data from running a procedure in the database it return results that look to SAS like the result of a query.  You might need to wrap the function call inside of SELECT statement in the foreign database code.

 

Example:

 

proc sql ;
  connect to xxx ..... ;
  create table work.mydata as 
    select * from connection to xxx
    ( select * from mytable_function() )
  ;
  execute (myfunction()) by xxx ;
quit;

 

Occasional Contributor
Posts: 6

Re: Proc SQL - Import a table function

Hi Tom,

I ran the query you sent me and I got results but with an error (proc sql;
25 connect to ODBC as teste
26 (noprompt = XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
27 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
28 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
29 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX);
30
31 create table work.mydata as
32 select * from connection to teste
33 ( select * from dbo.CutOffLegalPhases ('2017-01-01') );
NOTE: Compressing data set WORK.MYDATA decreased size by 84.70 percent.
Compressed is 1432 pages; un-compressed would require 9362 pages.
NOTE: Table WORK.MYDATA created, with 383834 rows and 32 columns.

34 execute ( dbo.CutOffLegalPhases('2017-01-01') ) by teste;
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '2017-01-01'.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
35 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 36.92 seconds
cpu time 4.44 seconds

Solution
‎01-03-2017 12:41 PM
Super User
Super User
Posts: 6,842

Re: Proc SQL - Import a table function

Looks like it worked!

NOTE: Table WORK.MYDATA created, with 383834 rows and 32 columns.

You don't need to use the EXECUTE () statement.  That is for commands you want to run that do NOT return results.

Occasional Contributor
Posts: 6

Re: Proc SQL - Import a table function

It works Tom, thanks a lot.

 

Best regards.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 336 views
  • 3 likes
  • 5 in conversation