Promote a session scope table to global scope

[dcortell]

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?

[SASJedi · Accepted solution]

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

[dcortell]

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?

[dcortell]

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;

[dcortell]

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;

[SASJedi]

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?