Hi all
As result of a cas action set, I have a set of tables as output (saved in the GMS caslib) which I want promote to global level: If I perform the following:
%macro promoting;
%do o=1 %to 6;
proc casutil;
promote casdata="gms.&&table&o"
Incaslib="Global Marketing (DNFS)"
outcaslib="Global Marketing (DNFS)";
run;
%end;
%mend promoting;
I'm getting error that exists already a table with those names (despite not promoted), so I can't perform the promotion.
I checked documentation and this seems should be the way to approach the promotion. Any idea about the failures?
If I was doing this job, I'd use CASL and CAS actions. For example:
/* Create some session-scoped tables in a global-scoped caslib */
proc casutil;
load data=sashelp.cars casout="cars1" outcaslib="casuser" replace;
load data=sashelp.cars casout="cars2" outcaslib="casuser" replace;
load data=sashelp.cars casout="cars3" outcaslib="casuser" replace;
load data=sashelp.cars casout="cars4" outcaslib="casuser" replace;
load data=sashelp.cars casout="cars5" outcaslib="casuser" replace;
run; quit;
proc cas;
/* Use table.tableInfo to get a list of the tables in the specified caslib */
table.tableInfo result=r/
caslib="casuser";
/* Extract just the table names to a CASL array */
tableList=r.tableInfo[,1];
/* Use a CASL DO OVER loop to execute table.promote for each table in the array */
do thisTable over tableList;
table.promote /
caslib="casuser"
,name=thisTable
/* DROP=TRUE gets rid of the session table creating the globla table */
,drop=TRUE
;
end;
run;
quit;
and here's the applicable notes from the log:
NOTE: Cloud Analytic Services promoted table CARS1 in caslib CASUSER(myUserID) to table CARS1 in caslib CASUSER(myUserID). NOTE: Cloud Analytic Services promoted table CARS2 in caslib CASUSER(myUserID) to table CARS2 in caslib CASUSER(myUserID). NOTE: Cloud Analytic Services promoted table CARS3 in caslib CASUSER(myUserID) to table CARS3 in caslib CASUSER(myUserID). NOTE: Cloud Analytic Services promoted table CARS4 in caslib CASUSER(myUserID) to table CARS4 in caslib CASUSER(myUserID). NOTE: Cloud Analytic Services promoted table CARS5 in caslib CASUSER(myUserID) to table CARS5 in caslib CASUSER(myUserID).
No muss, no fuss, no macro required, and no need to count ampersands...
If you want to hand-type the names of the tables just skip the table.tableInfo step, and modify the tableList assignment statement to something like this:
tableList={"table1","table2","table3"};
May the SAS be with you!
Mark
If I was doing this job, I'd use CASL and CAS actions. For example:
/* Create some session-scoped tables in a global-scoped caslib */
proc casutil;
load data=sashelp.cars casout="cars1" outcaslib="casuser" replace;
load data=sashelp.cars casout="cars2" outcaslib="casuser" replace;
load data=sashelp.cars casout="cars3" outcaslib="casuser" replace;
load data=sashelp.cars casout="cars4" outcaslib="casuser" replace;
load data=sashelp.cars casout="cars5" outcaslib="casuser" replace;
run; quit;
proc cas;
/* Use table.tableInfo to get a list of the tables in the specified caslib */
table.tableInfo result=r/
caslib="casuser";
/* Extract just the table names to a CASL array */
tableList=r.tableInfo[,1];
/* Use a CASL DO OVER loop to execute table.promote for each table in the array */
do thisTable over tableList;
table.promote /
caslib="casuser"
,name=thisTable
/* DROP=TRUE gets rid of the session table creating the globla table */
,drop=TRUE
;
end;
run;
quit;
and here's the applicable notes from the log:
NOTE: Cloud Analytic Services promoted table CARS1 in caslib CASUSER(myUserID) to table CARS1 in caslib CASUSER(myUserID). NOTE: Cloud Analytic Services promoted table CARS2 in caslib CASUSER(myUserID) to table CARS2 in caslib CASUSER(myUserID). NOTE: Cloud Analytic Services promoted table CARS3 in caslib CASUSER(myUserID) to table CARS3 in caslib CASUSER(myUserID). NOTE: Cloud Analytic Services promoted table CARS4 in caslib CASUSER(myUserID) to table CARS4 in caslib CASUSER(myUserID). NOTE: Cloud Analytic Services promoted table CARS5 in caslib CASUSER(myUserID) to table CARS5 in caslib CASUSER(myUserID).
No muss, no fuss, no macro required, and no need to count ampersands...
If you want to hand-type the names of the tables just skip the table.tableInfo step, and modify the tableList assignment statement to something like this:
tableList={"table1","table2","table3"};
May the SAS be with you!
Mark
Question: I checked, and all the table involved are not promoted
So the discussed code:
proc cas;
tableList={"topic_scoring_v1",
"mkt_topicmodel_svdu",
"mkt_topicmodel_topics",
"mkt_topicmodel_docpro",
"mkt_topicmodel_config",
"mkt_topicmodel_terms"
};
do thisTable over tableList;
table.promote /
caslib="Global Marketing (DNFS)"
,name=thisTable
/* DROP=TRUE gets rid of the session table creating the globla table */
,drop=TRUE,
targetLib="Global Marketing (DNFS)" ;
end;
run;
Should promote the tables and drop the session versions. However, I get the following :
80 proc cas;
81
82 tableList={"topic_scoring_v1",
83 "mkt_topicmodel_svdu",
84 "mkt_topicmodel_topics",
85 "mkt_topicmodel_docpro",
86 "mkt_topicmodel_config",
87 "mkt_topicmodel_terms"
88 };
89
90 do thisTable over tableList;
91 table.promote /
92 caslib="Global Marketing (DNFS)"
93 ,name=thisTable
94 /* DROP=TRUE gets rid of the session table creating the globla table */
95 ,drop=TRUE,
96 targetLib="Global Marketing (DNFS)";
97 end;
98 run;
NOTE: Active Session now CASAUTO.
ERROR: The target table topic_scoring_v1 of the promotion already exists. Please specify a different name.
ERROR: La acción se ha detenido debido a los errores.
ERROR: The target table mkt_topicmodel_svdu of the promotion already exists. Please specify a different name.
ERROR: La acción se ha detenido debido a los errores.
ERROR: The target table mkt_topicmodel_topics of the promotion already exists. Please specify a different name.
ERROR: La acción se ha detenido debido a los errores.
ERROR: The target table mkt_topicmodel_docpro of the promotion already exists. Please specify a different name.
ERROR: La acción se ha detenido debido a los errores.
ERROR: The target table mkt_topicmodel_config of the promotion already exists. Please specify a different name.
ERROR: La acción se ha detenido debido a los errores.
ERROR: The target table mkt_topicmodel_terms of the promotion already exists. Please specify a different name.
ERROR: La acción se ha detenido debido a los errores.
Should have the "Drop" option allowed to promote the tables and at the same time drop the session versions? Why I'm getting the error then?
I add that to drop the old versions of the promoted tables, before the code before and the action set, I run the following drop codes:
proc casutil session=casauto;
dropTable casdata="topic_scoring_v1";
quit;
Update: I have also tried the following approach to drop table, but same errors when trying promotion with new versions:
proc cas;
session casauto;
tableList={"topic_scoring_v1",
"mkt_topicmodel_svdu",
"mkt_topicmodel_topics",
"mkt_topicmodel_docpro",
"mkt_topicmodel_config",
"mkt_topicmodel_terms"
};
/********do loop ***********************************/
do thisTable over tableList;
/**if table exists**/
table.tableExists result=rc/
name=thisTable, caslib="Global Marketing (DNFS)";
print rc;
if rc.exists^=0 then do;
/**drop till removed if exists **/
do until(rc=0);
table.dropTable /
name=thisTable, caslib="Global Marketing (DNFS)"
;
table.tableExists result=rc/
name=thisTable, caslib="Global Marketing (DNFS)";
print rc;
end;
/************************/
end;
/***end do loop **************************************/
end;
run;
quit;
I could use some clarification here. What is the goal? Do you want to drop all copies of the tables listed (that is, both session-scoped and global-scoped copies of a table - as seems to be indicated by your most recent entry above) or do you want to promote all listed tables and ensure only the promoted copy remains (as indicated by your original framing of the problem)? If the original interpretation of the problem was correct, what in the provided solution isn't working for you?
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.