topic table.deleterows in SAS Programming

table.deleterows

aloou — Tue, 08 Mar 2022 10:32:04 GMT

Hello everyone,

i am having a problem with this proc cas , the where statement is not accepted and gets me an error.

do you have ideas ?

thanks

proc cas;
table.deleterows /
table={name="TEST" caslib="mine" where='ID in (select id from triangle)'};
run;

Re: table.deleterows

ballardw — Tue, 08 Mar 2022 13:55:40 GMT

"Not accepted" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

Re: table.deleterows

Kurt_Bremser — Tue, 08 Mar 2022 15:40:45 GMT

I do not think you can use a SQL subselect for the IN operator there.

The ERROR message should look like this:

ERROR: Syntax error detected on line 2 at column 10.  Found SELECT, expecting a quoted string, a numeric constant, a datetime 
       constant, a missing value, -.

which means you must supply a list of literals.

Use PROC SQL to store the results into a macro variable for use in the WHERE, like

proc sql noprint;
select distinct quote(trim(id),"'") into :id_list separated by ","
from triangle;
quit;

and then do

table.deleterows / 
table={name="TEST" caslib="mine" where="ID in (&id_list.)"};

Disclaimer: I have yet to start with Viya/CAS, but from a few quick tries I arrived at this suggestion. Have no idea if it works.