topic Re: proc sql prallel processing unsing rsubmit in SAS Programming

proc sql prallel processing unsing rsubmit

Asbon — Wed, 13 Sep 2023 10:59:36 GMT

Hi,

Need help with below coding.

can we connect oracle server using rsubmit miltiple time for prallel processing. or is there any code enhancement required

* Parallel Processing *;

options sascmd="sas";

* Prosess 1 *;

signon task1;

rsubmit task1 wait=no;

proc sql;

connect to oracle (user="&id" orapw="&pw" path="@path" preserve_comments);

create table aaa as

select * from connection to oracle

(SELECT * from abc);

disconnect from oracle;

quit;

endrsubmit;

* Prosess 2 *;

signon task2;

rsubmit task2 wait=no;

proc sql;

connect to oracle (user="&id" orapw="&pw" path="@path" preserve_comments);

create table bbb as

select * from connection to oracle

(select * fro xyz);

disconnect from oracle;

quit;

endrsubmit;

* Prosess 3 *;

signon task3;

rsubmit task3 wait=no;

proc sql;

connect to oracle (user="&id" orapw="&pw" path="@path" preserve_comments);

create table ccc as

select * from connection to oracle

(select * from www );

disconnect from oracle;

quit;

endrsubmit;

waitfor _all_;

signoff _all_;

help is appreciated.

Thanks.

Re: proc sql prallel processing unsing rsubmit

SASKiwi — Wed, 13 Sep 2023 22:15:52 GMT

That looks about right. Have you tried it?

Re: proc sql prallel processing unsing rsubmit

sbxkoenk — Wed, 13 Sep 2023 22:57:32 GMT

Base SAS + SAS/CONNECT - A simple method to generate load on any number of licensed cores
Posted 04-08-2021 05:46 AM | by SimonWilliams

https://communities.sas.com/t5/SAS-Communities-Library/Base-SAS-SAS-CONNECT-A-simple-method-to-generate-load-on-any/ta-p/732174

But the below blog is MUCH easier to understand :

Running SAS programs in parallel using SAS/CONNECT®

By Leonid Batkhan on SAS Users January 13, 2021

https://blogs.sas.com/content/sgf/2021/01/13/running-sas-programs-in-parallel-using-sas-connect/

Koen

Re: proc sql prallel processing unsing rsubmit

Patrick — Wed, 13 Sep 2023 23:44:06 GMT

That looks about right. Just be aware that the rsubmit blocks run in their own SAS session and though any work table you create is in WORK of the child session.

You need to either load such a WORK table to the parent WORK or easier write it to a disk location that's also directly accessible by the parent session.

Re: proc sql prallel processing unsing rsubmit

Asbon — Thu, 14 Sep 2023 01:06:42 GMT

below is the error that i'm getting.
macro variables are not getting resolved. with in sql passthrough.
if it one rsubmit it's working fine.

NOTE: Remote submit to TASK1 commencing.
1 proc sql;
2 connect to oracle (user="&id" orapw="&pw" path="@path"
2 ! preserve_comments);
WARNING: Apparent symbolic reference id not resolved.
WARNING: Apparent symbolic reference pw not resolved.
WARNING: Apparent symbolic reference id not resolved.
WARNING: Apparent symbolic reference pw not resolved.
ERROR: ORACLE connection error: ORA-01017: invalid username/password; logon
denied.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.

Re: proc sql prallel processing unsing rsubmit

Asbon — Thu, 14 Sep 2023 01:08:25 GMT

Re: proc sql prallel processing unsing rsubmit

SASKiwi — Thu, 14 Sep 2023 02:34:05 GMT

Looks like your macro variables ID and PW are not defined in all SAS sessions. You can define them once in the parent session, then do this to push them to the other sessions:

%let ID = MyID;
%let PW = MyPW;

signon task1;

%syslput _all_ / remote = task1;