Multiple Update

[Santha]

Hi 

I am using CAS and trying to do a simple update query. 

updating multiple columns of a table with some values. But i am not successful at it. 

what is the best way?

i tried proc sql, proc fedsql with no success. 

[ballardw]

"Unsuccessful" 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-dat... 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.

Is there a single key (one or more variables) to match the records on or are you attempting to update multiple records from one update record? Or multiple transactions to apply to a single record?

[Santha]

Hi Ballardw

thank you.  here is the error ERROR: Update access is not supported for file CASUSER.MSD.DATA.

my code below

proc sql;
update CASUSER.MSD
set RateIncludeOcean='YES',
RateExcludeReason='XXXX'
; 

Here is my fedsql 

proc fedsql sessref=casauto;
update CASUSER.MSD
set 
{
RateIncludeOcean='YES',
RateExcludeReason='XXXX'
}
;

 Here is the error for FEDSQL: 

ERROR: Unsupported SQL statement.

ERROR: The action stopped due to errors.

ERROR: The FedSQL action was not successful.

[ballardw]

The Fedsql should be

proc fedsql sessref=casauto;
update CASUSER.MSD
{
set 
RateIncludeOcean='YES',
RateExcludeReason='XXXX'
}
;

from the Fedsql documentation.

One suspects there may be more going on with your CASUSER library.

From Proc sql documentation:

Restriction:	You cannot use UPDATE on a table that is accessed by an engine that does not support UPDATE processing.

So your CASUSER library may be on one of those engines or is for some reason read-only for you.

Or something else entirely.

[Santha]

hi ballardw

when i did the code. this is what i got

   OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
75    
76    proc fedsql sessref=casauto;
77    update CASUSER.MSD
78    {
79    set
80    RateIncludeOcean='YES',
81    RateExcludeReason='XXXX'
82    }
83    ;
ERROR: Unsupported SQL statement.
ERROR: The action stopped due to errors.
ERROR: The FedSQL action was not successful.
NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.
84    

[Santha]

I am trying to get the SAS way of doing the following SQL code which is super simple.

UPDATE CASUSER.MSD

SET RateIncludeOcean = 'YES', RateExcludeReasonOcean = 'XXXX'

[Santha]

hi ballardw

was wondering if you were able to try a similar multiple update query on your end. what is the best way to deal with this one?

[Santha]

Hi I am working in CAS environment (SAS Viya).

I am trying to get the SAS way of doing the following SQL code which is super simple. What is the best and easiest way to get this done?

Update CASUSER.MSD

Set RateIncludeOcean = 'YES', RateExcludeReasonOcean = 'XXXX'

Here is my FedSQL way but not successful

OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
75 
76 proc fedsql sessref=casauto;
77 update CASUSER.MSD
78 {
79 set
80 RateIncludeOcean='YES',
81 RateExcludeReason='XXXX'
82 }
83 ;
ERROR: Unsupported SQL statement.
ERROR: The action stopped due to errors.
ERROR: The FedSQL action was not successful.
NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.
84

Here is my proc sql way

proc sql;
update CASUSER.MSD
set RateIncludeOcean='YES',
RateExcludeReason='XXXX'
; 

and here is the error for proc sql way ERROR: Update access is not supported for file CASUSER.MSD.DATA.

[Kurt_Bremser]

The FEDSQL Reference for CAS lists only three statements: CREATE TABLE, DROP TABLE and SELECT, while the "standard" reference for SAS 9.4 lists practically all statements available in PROC SQL.

In a non-CAS environment, the curly brackets are also not needed, as this worked for me in University Edition:

data test;
x1 = "XXX";
x2 = "YYYY";
run;

proc fedsql;
update test
  set
    x1 = 'AAA',
    x2 = 'ZZZZ'
;
quit;

[Santha]

ok i get that fedsql in CAS environment does not apply to UPDATE statement. 

How about proc sql? have you tried it in a CAS environment?

[Kurt_Bremser]

I do not have a CAS installation, I only read the docs 😉

Try it.