BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lancman
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RichardDeVen
Barite | Level 11

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;

View solution in original post

2 REPLIES 2
RichardDeVen
Barite | Level 11

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;
lancman
Calcite | Level 5
I knew there must be a simple solution!

Thank you.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 3115 views
  • 0 likes
  • 2 in conversation