Append records to a CAS table

[jblack38]

I am working in SAS Studio in Viya 3.5.

I want to maintain an Audit_History table that contains the contents of the SystemData.Audit table.  The Audit table only contains data for one week.  I want a complete history.

I tried some simple methods ways that did not work.  This code seems to be a brute force method but it works.  It updates my Audit_History table (step 4).  The record count in step 5 is correct.  But when I check the table in Environment Manager, it has not been updated.  I tried to add a promote (step 6), but it generates an error "The target table Audit_History already exists".

I'd like to know why step 4 does not update the table in the vAdm CAS library.  What do I need to do to update it?

Do you have any suggestions for a better way to maintain my Audit_History table?

Thank you for any help!

Jerry

LIBNAME vSYS cas caslib="SystemData";
LIBNAME vAdm cas caslib="AdminDataCAS";

/* 1. this creates a Work table from the contents of the SystemData.Audit table 
	Some of the columns are not valid SAS names */
proc sql;
    create table wrk_audit as
	        SELECT 	ID
					, 'Time Stamp'n as Timestamp
					, Type
					, Action
					, State
					, Description
					, 'User ID'n	as User_ID
					, Application
					, 'Remote Address'n as Remote_Address
					, URI

        FROM vSys.Audit;
quit;

/* 2. Get the current contents of Audit_History */
data cur_audit_history;
	set vAdm.Audit_History;
run;

/* 3. Remove duplicates */
proc sql;
    create table audit_history_nodups as
		SELECT 	*
        FROM cur_audit_history

		UNION 
		SELECT *
		FROM wrk_audit;
quit;

/* 4. Update the Audit_History table */
data vAdm.Audit_History;    
  set audit_history_nodups;						/* This is the source table */
run;

/* 5. Check how many records are in a table */
proc sql;
	select count(*)
	from	vAdm.Audit_History;
quit;

/* 6. Tried to promote.  ERROR: Already exists */
/*
proc casutil;
	promote casdata="Audit_History"
	incaslib="AdminDataCAS";
run;
*/
/* This creates a .sashdat file from a table in memory.  */
proc casutil;
    save casdata="Audit_History"			/* This is the input table to be saved */
 	incaslib="AdminDataCAS" outcaslib="AdminDataCAS"
	     casout="Audit_History" replace;  /* This is the outut file */
												/* use replace in case the file exists */
quit;

[jblack38]

I just tried this code with the same result, the query that gets the record count is correct (80,000).  But when I look at the table in Environment Manager it shows it has not been updated and it shows the old record count (150,000).

proc casutil;
 	load data=audit_history_nodups 
	casout="Audit_History" outcaslib="AdminDataCAS" replace;
quit;

/* Check how many records are in Audit_History */
proc sql;
	select count(*)
	from	Vadm.audit_history;
quit;

[jblack38]

Okay, I created some code that works.  But as I said in the original post, I'm sure there is an easier way.  I had to drop the table and re-create it to get it to show up in the CAS library in Environment Manager.  Here is my solution in case someone else is having difficulties similar to mine.  

I appreciate any recommendations anyone has to simplify it.

Thanks!

Jerry

LIBNAME vSYS cas caslib="SystemData";
LIBNAME vAdm cas caslib="AdminDataCAS";

/* 1. this creates a Work table from the contents of the SystemData.Audit table 
	Some of the columns are not valid SAS names */
proc sql;
    create table wrk_audit as
	        SELECT 	ID
					, 'Time Stamp'n as Timestamp
					, Type
					, Action
					, State
					, Description
					, 'User ID'n	as User_ID
					, Application
					, 'Remote Address'n as Remote_Address
					, URI

        FROM vSys.Audit;
quit;

/* 2. Get the current contents of my Audit_History table */
data cur_audit_history;
	set vAdm.Audit_History;
run;

/* 3. Combine the tables and remove duplicates */
proc sql;
    create table audit_history_nodups as
		SELECT 	*
        FROM cur_audit_history

		UNION 
		SELECT *
		FROM wrk_audit;
quit;

/* 4. Drop the Audit History table before we re-create it with new data */
proc casutil;
 	droptable casdata="audit_history" 
	incaslib="AdminDataCAS";
quit;

/* 5. Add new records to the Audit_History table */
proc casutil;
 	load data=audit_history_nodups 
	casout="Audit_History" outcaslib="AdminDataCAS" 
	promote
	;
quit;

/* 6. Create a .sashdat file from the  memory version.  */
proc casutil;
    save casdata="Audit_History"			/* This is the input table to be saved */
 	incaslib="AdminDataCAS" outcaslib="AdminDataCAS"
	     casout="Audit_History" replace;  /* This is the outut file */
												/* use replace in case the file exists */
quit;