topic Multiple Update in Mathematical Optimization, Discrete-Event Simulation, and OR

Multiple Update

Santha — Fri, 20 Nov 2020 18:18:59 GMT

Hi Rob

I tried to post this in other community (SAS Programming and SAS procedures) not OR/MS but i did not get any answers. So I am reaching out to you to see if you have any suggestions .

Hi I am working in CAS environment (SAS Viya). I want to get the job done, i dont mind if i have to use fedsql or just proc sql or any other thing that you would recommend.

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

Kurt_Bremser — Fri, 20 Nov 2020 18:23:11 GMT

Then the reason is clear: you can't do an update in CAS. You will probably need to do the data changes in SAS 9.4 and re-upload the dataset.

Re: Multiple Update

Santha — Fri, 20 Nov 2020 20:54:54 GMT

hmm. yes i guess it is clear.

why would update option not available in CAS.

Re: Multiple Update

SASKiwi — Fri, 20 Nov 2020 23:14:13 GMT

If you refer to the FEDSQL doc for CAS you will see there is no UPDATE statement listed: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=casfedsql&docsetTarget=p01kq5ka44c2l8n1j2yr40apac0n.htm&locale=en

DATA step SET and MERGE statements are available. Refer to the same documentation.

BTW, please don't post multiple times in different forums. Be patient waiting for answers. If you want a faster response contact SAS Tech Support.

Re: Multiple Update

Santha — Wed, 25 Nov 2020 19:39:07 GMT

Thank you for your feedback. I will not post in multiple forums.

I was able to get my answers through CASL update table thing. That worked.

Thanks for the support