BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cmachado
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

12 REPLIES 12
Reeza
Super User

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

cmachado
Fluorite | Level 6

Hi thanks for your answer,

I should have the cut off date as result.

user24feb
Barite | Level 11

Either

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

* .. OR ..;

Select * From xxx Where Date=Today();

* ??;
cmachado
Fluorite | Level 6
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
ballardw
Super User

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

Tom
Super User Tom
Super User

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;

 

 

 

cmachado
Fluorite | Level 6
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
Reeza
Super User

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...

Tom
Super User Tom
Super User

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;

 

cmachado
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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.

cmachado
Fluorite | Level 6

It works Tom, thanks a lot.

 

Best regards.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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