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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: