Avoiding duplicate caslib errors

lancman — Thu, 19 Mar 2020 12:24:08 GMT

I have some code to extract data from Oracle tables. I want to set each table as a Viya data source in a caslib but they have different authdomain credentials so I'm using a macro loop.

%let auth_list=
TABLEAUTH1
TABLEAUTH2
;

%let path=ORA_LIB;

%macro ora_libs;
%local i;
%do i=1 %to %sysfunc(countw(&auth_list));
  	%let authdomain = %scan(&auth_list, &i);
   	%let schema = &authdomain.;

/* loop through each schema */
caslib &schema. drop;

caslib &schema. datasource=(
    srctype="oracle",
    authdomain=&authdomain. path=&path. schema=&schema.
    );
[...]

%end;
%mend;

First time through the code runs fine, each pass sets up the caslib and connects to the table. But if I try to re-run the code without dropping the caslibs first I get an error telling me I am creating a duplicate caslib.

ERROR: Duplicate Caslib
ERROR: Could not add caslib 'TABLEAUTH1'. Make sure that the caslib does not already exist and that you have permissions to add 
        caslibs to Cloud Analytic Services.
ERROR: The action stopped due to errors.

I can get round this by using caslib schema drop; - but that gives errors on the first pass when the lib is not there!

ERROR: The caslib TABLEAUTH1 does not exist in this session.

Any thoughts on how to resolve this without things getting too complicated? I can live with the 'caslib does not exist' error but it will be neater not to have to.

Re: Avoiding duplicate caslib errors

RichardDeVen — Thu, 19 Mar 2020 15:06:43 GMT

Use the function CLIBEXIST(session name,caslib name) to check if the CASLIB already exists.

%if %sysfunc(clibexist(mySession,&schema)) %then %do;
  caslib &schema. drop;
%end;

Re: Avoiding duplicate caslib errors

lancman — Thu, 19 Mar 2020 15:17:05 GMT

I knew there must be a simple solution!

Thank you.

topic Re: Avoiding duplicate caslib errors in SAS Programming

Avoiding duplicate caslib errors

Re: Avoiding duplicate caslib errors

Re: Avoiding duplicate caslib errors