topic Re: delete rows from a LASR table in SAS Visual Analytics

delete rows from a LASR table

Rahul_SAS — Thu, 19 Nov 2015 12:21:14 GMT

Hi Experts,

I am trying to delete some observations from a LASR tablel test but geting the

ERROR: Update access is not supported for file LASRLIB.test.

proc sql noprint;
delete
FROM LASRLIB.test

where businessdate = today() - 3;
quit;

-Rahul

Re: delete rows from a LASR table

PeterWijers — Thu, 19 Nov 2015 19:02:44 GMT

Rahul, why not just reload the data in the data query builder using the exception as a filter.
Peter

Re: delete rows from a LASR table

Rahul_SAS — Fri, 20 Nov 2015 05:30:01 GMT

Hi Peter,

Thanks for your prompt reply.

I can't reload the data on daily basis as table size is to large it takes 10-12 hours for full load so I loading data on daily basis by incremental load using Append Tables.

But here I need to delete last 3 days data from LASR Table and append updated last 3 days data.

Regards

Rahul

Re: delete rows from a LASR table

PeterWijers — Fri, 20 Nov 2015 06:00:18 GMT

Rahul,

then please check out this web page. The answes should be there

http://support.sas.com/documentation/cdl/en/inmsref/67213/HTML/default/viewer.htm#p00z16nlibqearn11f67sudjjtd9.htm

I never tested it sadly....so good to see your feedback.

Greetings Peter

Re: delete rows from a LASR table

Rahul_SAS — Fri, 20 Nov 2015 06:51:11 GMT

Hi Peter,

Sorry to say but didn't get anything helpful in the given link.

Regards

Rahul

Re: delete rows from a LASR table

PeterWijers — Fri, 20 Nov 2015 07:03:29 GMT

No Problem, that is all I can contribute for now.
Maybe check with your local SAS support.
Greetings and good luck.

Re: delete rows from a LASR table

TriciaAanderud — Mon, 23 Nov 2015 15:34:24 GMT

PROC IMSTAT allows you to make changes to data that is loaded in LASR.

Check out this SAS Global Forum paper: Tips and Techniques for Efficiently Updating and Loading Data into SAS® Visual Analytics

Re: delete rows from a LASR table

jakubniescior — Fri, 14 Jul 2017 14:49:58 GMT

I just wanted to know if you managed to find anything related to this question, because I have similar problem.

I need to delete ~1000 rows every hour from roughly table with 22 000 000 rows of data.

If you have any questions I asked in topic "Best way for frequent and fast refresh of data on LASR server".

Re: delete rows from a LASR table

PeterWijers — Fri, 14 Jul 2017 15:18:21 GMT

Hi there

this is the sas code if you want to delete LASR data with scheduled scripting

libname LASR sasiola PORT=10010 HOST="xxxxxxx.xxxxxxx.xxx" ; (server name)
OPTION NOSYNTAXCHECK;
option DBIDIRECTEXEC;

PROC IMSTAT data=LASR.Cust_Compl /*noprint*/;
WHERE Company <> '';
RUN;
deleterows / purge;
RUN;

Later I add the new data with proc append.

greetings and good luck.

Re: delete rows from a LASR table

jitinsethi07 — Thu, 07 Dec 2017 10:53:51 GMT

Hi,

This conversation is partially related to my issue too, kindly see:

https://communities.sas.com/t5/SAS-Visual-Analytics/Deleting-LASR-records-using-proc-imstat-with-Merge-statement/m-p/419082#M8901

1) I have a dataset A (having millions of records) in LASR library.

2) Importing B file with few hundred rows.

3) Need to compare A and B and delete common records from A (lasr library) without creating a temporary copy.

Solution:

Using imstat statement--(not working)

proc imstat data=LASRLIB.A;
merge LASRLIB.A (in=a)
B (in=b);
if a and b;

run;
deleterows / purge;
run;

(Note: Merge won't work with proc imstat, it is just to show what I exactly need.)

Is there any other way to do it?

Please let me know in case of any gaps.