topic Re: Updating/Replacing a LASR table from a project in SAS Visual Analytics

Updating/Replacing a LASR table from a project

ghartge — Wed, 25 Apr 2018 15:56:42 GMT

Greetings everyone,

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,

Gary

Re: Updating/Replacing a LASR table from a project

alexal — Wed, 25 Apr 2018 16:03:23 GMT

@ghartge,

I hope that helps.

Re: Updating/Replacing a LASR table from a project

ghartge — Wed, 25 Apr 2018 19:40:22 GMT

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.

Thanks,

Gary

Re: Updating/Replacing a LASR table from a project

FredrikE — Thu, 26 Apr 2018 06:24:25 GMT

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;
 select myTable;
quit;

%macro registertable( REPOSITORY=Foundation, REPOSID=, LIBRARY=, TABLE=, FOLDER=, TABLEID=, PREFIX= );

/* Mask special characters */

 %let REPOSITORY=%superq(REPOSITORY);
 %let LIBRARY =%superq(LIBRARY);
 %let FOLDER =%superq(FOLDER);
 %let TABLE =%superq(TABLE);

 %let REPOSARG=%str(REPNAME="&REPOSITORY.");
 %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.;
 %else
 %PUT INFO: Registering &SELECTOBJ. to &LIBRARY. library.;

 proc metalib;
 omr (
 library="&LIBRARY." 
 %str(&REPOSARG.) 
 ); 
 %if ("&TABLEID." eq "") %THEN %DO;
 %if ("&FOLDER." ne "") %THEN %DO;
 folder="&FOLDER.";
 %end;
 %end;
 %if ("&PREFIX." ne "") %THEN %DO;
 prefix="&PREFIX.";
 %end;
 select ("&SELECTOBJ."); 
 run; 
 quit;

%mend;

/* Synchronize table registration */
%registerTable(
 LIBRARY=%nrstr(/Path/to/LASRLib)
 , REPOSITORY=%nrstr(Foundation)
 , TABLE=%nrstr(myTable)
 , FOLDER=%nrstr(/Path/to/metadataFolder)
 );

Works great and the RLS is still there 🙂

If you stlll have problems with RLS, you can add them using scripts after your reload:

http://documentation.sas.com/?docsetId=bisecag&docsetTarget=n1sjpmdvbsgp7bn1vpx0hsvsizcz.htm&docsetVersion=9.4&locale=sv

//Fredrik