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,
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.
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:
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.