topic Append records to a CAS table in SAS Programming

Append records to a CAS table

jblack38 — Thu, 30 Sep 2021 15:00:40 GMT

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;

Re: Append records to a CAS table

jblack38 — Thu, 30 Sep 2021 15:28:39 GMT

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;

Re: Append records to a CAS table

jblack38 — Fri, 01 Oct 2021 18:23:30 GMT

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;