topic Re: Can we write SQL in SAS intelligent Decision? in Decisioning

Can we write SQL in SAS intelligent Decision?

Mayt — Wed, 24 Jul 2024 14:33:05 GMT

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:

Can I write SQL within ds2 code files?
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

Re: Can we write SQL in SAS intelligent Decision?

SASJedi — Wed, 24 Jul 2024 15:10:22 GMT

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;

Re: Can we write SQL in SAS intelligent Decision?

SASKiwi — Thu, 25 Jul 2024 01:46:36 GMT

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;

Re: Can we write SQL in SAS intelligent Decision?

Mayt — Fri, 26 Jul 2024 09:18:36 GMT

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;

Re: Can we write SQL in SAS intelligent Decision?

Mayt — Fri, 26 Jul 2024 09:20:57 GMT

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

Re: Can we write SQL in SAS intelligent Decision?

SASJedi — Fri, 26 Jul 2024 13:57:28 GMT

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