Multiple Update

[Santha]

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.

[Kurt_Bremser]

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.

[Santha]

hmm. yes i guess it is clear.

why would update option not available in CAS. 

[SASKiwi · Accepted solution]

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=p0...

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.

[Santha]

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