topic Re: Check if ODBC connection is created or not in SAS Programming

Check if ODBC connection is created or not

shymaa — Wed, 18 May 2016 16:32:26 GMT

Hello,

I created ODBC connection from StartControl Panel System and SecurityAdministrative ToolsData Sources (ODBC).

In SAS code i created the connection as follow:

proc sql;

connect to odbc as mydb (dsn=&dsn);

execute
(set nocount on) by mydb;

i need to check if mydb object is actually connected to DB or not. so please help me to implement this in SAS

i think it will be like that:

if not mydb then -- if the connection isn't created, then create the connection

proc sql;

connect to odbc as mydb (dsn=&dsn);

execute
(set nocount on) by mydb;

endif

Re: Check if ODBC connection is created or not

SASKiwi — Wed, 18 May 2016 19:54:31 GMT

An ODBC connection in SAS SQL only lasts as long as the SQL step boundary (the QUIT statement or another DATA or PROC statement) then it is automatically disconnected. Why do you want to check your connection? If it doesn't work then you get appropriate messages in your SAS log.

proc sql
  connect to ODBC as mydb (....); <== creates connection

  SQL statements

quit; <== removes connection

Re: Check if ODBC connection is created or not

ballardw — Wed, 18 May 2016 22:38:21 GMT

You should be able to assign a library to the odbc connection and then use any of the SAS tools for examining the contents of the library.

Something like:

libname test odbc noprompt="dsn=&dsn";

If the library gets assigned you should see data there.

Re: Check if ODBC connection is created or not

Ksharp — Thu, 19 May 2016 01:36:15 GMT

Check function libname() or libref() .

%if (%sysfunc(libref(sashelp))) %then %put ERROR ;

Re: Check if ODBC connection is created or not

shymaa — Thu, 19 May 2016 08:43:37 GMT

Hi SASkiwi,

Thanks for your help 🙂

yes, i found that sometimes the connection isn't created and the below line write in CSV file which contains the statistical result.

Line: connect to odbc as mydb (dsn=&dsn)

so i need to put the connection in for loop and it will end once the connection is created something like that

do i=1 to 5;

if connect to odbc as mydb (dsn=&dsn) then --> if the connection created

end;

else

i++;

End;

run;

thanks,