BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
dcortell
Pyrite | Level 9

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?

1 ACCEPTED SOLUTION

Accepted Solutions
SASJedi
SAS Super FREQ

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

Check out my Jedi SAS Tricks for SAS Users

View solution in original post

5 REPLIES 5
SASJedi
SAS Super FREQ

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

Check out my Jedi SAS Tricks for SAS Users
dcortell
Pyrite | Level 9

Question: I checked, and all the table involved are not promoted

15pg.png

 

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
Pyrite | Level 9

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
Pyrite | Level 9

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
SAS Super FREQ

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? 

 

Check out my Jedi SAS Tricks for SAS Users

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 5 replies
  • 1304 views
  • 0 likes
  • 2 in conversation