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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.