04-25-2018 11:56 AM
OK, so I have a VA report that uses a table on our LASR server and everything works fine until I need to update the table. I would like to update the table from the program that creates the data when it runs every semester. I do have an "Upload to LASR" step in the Process Flow for the project.
I can delete the table using Management Console but I lose my row level security. Once I delete the table I can reload it using my project. Outside of this though, I would like to be able to update/reload any table without having to manually delete it first. I am thinking this is probably pretty routine. If I autoload the table it replaces/updates the table without issue but our Autoload places tables in a specific folder not always where we want it for organizational reasons. The attached image has autoloaded tables along with the table I am trying to update, EOT_IR_GRADE_DIST_PROVOST.
The document attached has the images and code and I have full access to the system as an administrator.
Thank you all as always,
04-25-2018 12:03 PM
04-25-2018 03:40 PM
Thank you alexal,
I am not sure that helps. You are correct in that I have a table that I need to replace all rows in, but I need to keep its metadata so I can retain my row level security settings. I am using the "Upload to LASR" functionality as described in the attachment. What I am doing is replacing all of the data in the table with new data.
I have also attempted to use a LIBNAME statement
LIBNAME TMP00012 SASIOLA HOST="SERVER_NAME" PORT=10031 TAG="VAPUBLIC" SIGNER="https://sasprod.sfcollege.edu:443/SASLASRAuthorization";
Along with PROC SQL procedures to DELETE all rows and then INSERT the new data but that does not work either. Even with admin permissions, it seems I cannot "UPDATE" a table on the LASR server with a LIBNAME statement and PROC SQL.
04-26-2018 02:24 AM
I do like this:
LIBNAME aaa SASIOLA TAG=aaa PORT=000 HOST="aaa" SIGNER="http://aaa/SASLASRAuthorization" ; /* Remove existing table from LASR if loaded already */ %macro deletedsifexists(lib,name); %if %sysfunc(exist(&lib..&name.)) %then %do; proc datasets library=&lib. nolist; delete &name.; quit; %end; %mend deletedsifexists; %deletedsifexists(MEBELA, im_medbedom);
proc copy in=fromLIB OUT=aaa;
%macro registertable( REPOSITORY=Foundation, REPOSID=, LIBRARY=, TABLE=, FOLDER=, TABLEID=, PREFIX= );
/* Mask special characters */
%let LIBRARY =%superq(LIBRARY);
%let FOLDER =%superq(FOLDER);
%let TABLE =%superq(TABLE);
%if ("&REPOSID." ne "") %THEN %LET REPOSARG=%str(REPID="&REPOSID.");
%if ("&TABLEID." ne "") %THEN %LET SELECTOBJ=%str(&TABLEID.);
%else %LET SELECTOBJ=&TABLE.;
%if ("&FOLDER." ne "") %THEN
%PUT INFO: Registering &FOLDER./&SELECTOBJ. to &LIBRARY. library.;
%PUT INFO: Registering &SELECTOBJ. to &LIBRARY. library.;
%if ("&TABLEID." eq "") %THEN %DO;
%if ("&FOLDER." ne "") %THEN %DO;
%if ("&PREFIX." ne "") %THEN %DO;
/* Synchronize table registration */
Works great and the RLS is still there
If you stlll have problems with RLS, you can add them using scripts after your reload: