topic Re: Proc SQL - Import a table function in SAS Programming

Proc SQL - Import a table function

cmachado — Tue, 03 Jan 2017 11:20:32 GMT

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

Re: Proc SQL - Import a table function

Reeza — Tue, 03 Jan 2017 11:54:51 GMT

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

Re: Proc SQL - Import a table function

cmachado — Tue, 03 Jan 2017 11:58:40 GMT

Hi thanks for your answer,

I should have the cut off date as result.

Re: Proc SQL - Import a table function

user24feb — Tue, 03 Jan 2017 12:53:43 GMT

Either

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

* .. OR ..;

Select * From xxx Where Date=Today();

* ??;

Re: Proc SQL - Import a table function

cmachado — Tue, 03 Jan 2017 12:59:05 GMT

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

Re: Proc SQL - Import a table function

Tom — Tue, 03 Jan 2017 13:50:07 GMT

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;

Re: Proc SQL - Import a table function

cmachado — Tue, 03 Jan 2017 14:43:50 GMT

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

Re: Proc SQL - Import a table function

Reeza — Tue, 03 Jan 2017 14:56:10 GMT

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#p0jhh7e1ipagann1bygfcb3mqvcg.htm

Re: Proc SQL - Import a table function

Tom — Tue, 03 Jan 2017 15:22:06 GMT

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;

Re: Proc SQL - Import a table function

ballardw — Tue, 03 Jan 2017 15:57:29 GMT

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

Re: Proc SQL - Import a table function

cmachado — Tue, 03 Jan 2017 17:01:22 GMT

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

Re: Proc SQL - Import a table function

Tom — Tue, 03 Jan 2017 17:18:53 GMT

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.

Re: Proc SQL - Import a table function

cmachado — Tue, 03 Jan 2017 17:42:30 GMT

It works Tom, thanks a lot.

Best regards.