Can we write SQL in SAS intelligent Decision?

[Mayt]

Currently, I am working on a SAS Intelligent Decision(SAS ID) project. The project needs to do some tasks involving using datagrid. In this project we use oracle database.

I know that SAS ID has sql code file. But it seems we can only perform simple sql statement to table in database. (ref sas doc ) I want to perform tasks such as receive datagrid as input then manipulate data and return output. So, I guess I may need to use ds2/custom functions in SAS ID.

Unfortunately, I find it very difficult to write ds2/custom functions to perform datagrid tasks such as join with condition. I tried to use datagrid functions, but it can’t replace SQL 100%. I doubt that can we write SQL within ds2 code files? 

I have been suggested to write python in SAS ID to call function in oracle. But for some reason I can’t use python right now.

Here my questions:

1. Can I write SQL within ds2 code files?   
2. Is there anyway to perform sql statement to datagrid?

Or if you have any other suggestion please tell me.

Here are example tasks that I want to perform in SAS ID:

an example is written in microsoft sql language. It's quite simple, but there are some tasks that more complex and it would be easier to code in sql. That why I want to use SQL.

CREATE FUNCTION [dbo].[function_A]
(
	-- Add the parameters for the function here
	@Appkey NVARCHAR(34)
)
RETURNS SMALLINT
AS
BEGIN
	DECLARE @Counter SMALLINT
	
		SELECT @Counter = SUM(
			CASE WHEN COL_A > 0  THEN 1
				WHEN COL_B > 0 THEN 1
				WHEN COL_C > 0 THEN 1
				WHEN COL_D > 0 THEN 1
		ELSE 0 END) FROM TABLE_A a left join TABLE_B b on a.col_name = b.col_name WITH (NOLOCK) WHERE Appkey = @Appkey
	

	-- Return the result of the function
	RETURN @Counter

END

GO

[SASJedi]

The DS2 SET statement will accept the result of a FedSQL query as input, and can subsequently process each of the rows returned.  For example:

/* Make some test data */
data one;
	do ID=1 to 20;
		Text1='One';
		output;
	end;
run;
data two;
	do ID=1 to 20 by 2;
		Text2='Two';
		output;
	end;
run;

/* Process SQL join result in DS2 */
proc ds2;
data result/overwrite=yes;
	method run();
		set {select one.ID, coalescec(text2,text1) as Text 
				  from one left join two
				  on one.ID=two.ID};
	end;
enddata;
run;
quit;
proc print data=result;
run;

[Mayt]

I can't use it in ds2 in SAS ID

package "${PACKAGE_NAME}" /inline;
    method execute();
    /* Make some test data */
data one;
	do ID=1 to 20;
		Text1='One';
		output;
	end;
run;
data two;
	do ID=1 to 20 by 2;
		Text2='Two';
		output;
	end;
run;

/* Process SQL join result in DS2 */
proc ds2;
data result/overwrite=yes;
	method run();
		set {select one.ID, coalescec(text2,text1) as Text 
				  from one left join two
				  on one.ID=two.ID};
	end;
enddata;
run;
quit;
proc print data=result;
run;
   end;
endpackage;

[SASKiwi]

What you have posted is an example of creating a function in SQL Server. Since your database is Oracle, there is nothing stopping you from creating a similar function in that database. You will find plenty of examples on the Oracle website including this one: https://blogs.oracle.com/developers/post/anonymous-plsql-function-in-sql

You can then use SQL Passthru similar to this:

libname SQLSRVR odbc noprompt = "server=MyServer;DRIVER=SQL Server Native Client 18.0;Trusted_Connection=yes;" DATABASE = MyDatabase;

proc sql noprint;
  connect using SQLSRVR;
execute (exec Myfunction();) by SQLSRVR;
quit;

[Mayt]

It seem I need to use that code in sas studio. Can I use it in SAS ID?

[SASJedi]

PROC SQL doesn't run in MAS or CAS - only in the Compute Server. However, the Federated SQL that does run there is pretty ANSI standard.
Check out the Intelligent Decisioning docs for Developing SQL Code