topic Re: Multiple Update in SAS Programming

Multiple Update

Santha — Wed, 18 Nov 2020 21:32:51 GMT

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.

Re: Multiple Update

ballardw — Wed, 18 Nov 2020 21:38:43 GMT

"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?

Re: Multiple Update

Santha — Wed, 18 Nov 2020 22:10:27 GMT

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.

Re: Multiple Update

ballardw — Wed, 18 Nov 2020 22:34:08 GMT

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.

Re: Multiple Update

Santha — Wed, 18 Nov 2020 23:04:18 GMT

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

Re: Multiple Update

Santha — Wed, 18 Nov 2020 23:09:08 GMT

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'

Re: Multiple Update

Santha — Thu, 19 Nov 2020 18:42:48 GMT

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?

Multiple Update in a table

Santha — Thu, 19 Nov 2020 18:59:39 GMT

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.

Re: Multiple Update in a table

Kurt_Bremser — Fri, 20 Nov 2020 05:37:14 GMT

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;

Re: Multiple Update in a table

Santha — Fri, 20 Nov 2020 18:15:33 GMT

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?

Re: Multiple Update in a table

Kurt_Bremser — Fri, 20 Nov 2020 18:19:44 GMT

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

Try it.

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