topic Re: create a link to the parameters of the connection to the database in SAS Programming

create a link to the parameters of the connection to the database

Shukhrat — Mon, 26 Apr 2021 09:44:49 GMT

Hello.
I recently started learning programming in sas.
Who can tell you how to create a permanent symbolic link?
When performing any tasks, I often turn to the database with an sql query, which should be executed in the database server and return the result to me.

This is how I do it:

proc sql;
connect to oracle (AuthDomain = "Oracle_Auth" PATH = "@ dev-db.sas.ru:1521/SASDEV");
select * from cdm.ci_campaign;
disconnect from oracle;
quit;

Here's how I would like to simplify it:

proc sql;
&connectORA.;
select * from cdm.ci_campaign;
disconnect from oracle;
quit;

Re: create a link to the parameters of the connection to the database

Kurt_Bremser — Mon, 26 Apr 2021 10:32:53 GMT

Put your connection information into a macro:

%macro connectORA;
connect to oracle (AuthDomain = "Oracle_Auth" PATH = "@ dev-db.sas.ru:1521/SASDEV");
%mend;

and call it in your SQL:

proc sql;
%connectORA;
select * from connection to oracle (select * from cdm.ci_campaign);
disconnect from oracle;
quit;

I your example, no CONNECT and DISCONNECT would be needed, as the SELECT works directly from a SAS library (which might be defined as a connection to Oracle on its own). But I guess that CDM is in fact a schema in the Oracle database, and not available in the SAS environment on its own.