ERROR: Write access to member SASUSER.nametable.DATA is denied

[Mouadbenaou]

Describe the bug trying to save proc sql query in my sasuser Folder using proc sql Threads; create table sasuser.nametable as select ..... quit;

the query work fine in sas entreprise guide and the table is saved correctly

• OS: windows
• SAS Version: 9.4
• SASPy Version: 3.3.6

[tomweber-sas]

Hey, so I don't have enough info here to know why this would be. But, you mention connecting via EG, so I'm guessing you have Workspace servers you connect to (have to be w/ EG). If you were connected to the same Workspace server w/ saspy, then this would indeed seem odd. So, my first question is what's your configuration definition? Are you connecting to the actual workspace server that you connect to with EG? I don't see what the sasuser library is, not that I would know much about that, but the error about not having permission to write to is is coming from SAS, not saspy, What other information is in the LOG? print(sas.saslog()) Is there anything in there that gives any more insight about the problem?

[Mouadbenaou]

this is the output of sas.saslog() when i first start the session.

Le Système SAS mardi 12 mai 2020 18:24:00 NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA. NOTE: SAS (r) Proprietary Software 9.4 (TS1M6 MBCS3170) Licensed to ******* ******* INTTECH, Site 50102200. NOTE: This session is executing on the Linux 3.10.0-957.el7.x86_64 (LIN X64) platform. NOTE: Analytical products: SAS/STAT 15.1 NOTE: Additional host information: Linux LIN X64 3.10.0-957.el7.x86_64 #1 SMP Thu Oct 4 20:48:51 UTC 2018 x86_64 Red Hat Enterprise Linux Server release 7.6 (Maipo) You are running SAS 9. Some SAS 8 files will be automatically converted by the V9 engine; others are incompatible. Please see http://support.sas.com/rnd/migration/planning/platform/64bit.html PROC MIGRATE will preserve current SAS file attributes and is recommended for converting all your SAS libraries from any SAS 8 release to SAS 9. For details and examples, please see http://support.sas.com/rnd/migration/index.html This message is contained in the SAS news file, and is presented upon initialization. Edit the file "news" in the "misc/base" directory to display site-specific news and information in the program log. The command line option "-nonews" will prevent this display. NOTE: Initialisation de SAS a utilisé (Durée totale du traitement) : real time 0.00 seconds cpu time 0.00 seconds NOTE: The autoexec file, /SAS92/config/Lev1/SASApp/WorkspaceServer/autoexec.sas, was executed at server initialization.

[tomweber-sas]

Well, that doesn't have anything about the code you ran that got the error. Can you provide any information that shows how you are connecting to SAS, the code you run and the output, including the log? Are you connecting to the workspace server you use w/ EG?

[Mouadbenaou]

yes Im connected to the workspace server I use with Guide. the code is : sasquery = """libname t oracle User = &UserOracle Pass = &PassOracle Path = &PathOracle schema=BI_PROD ; options compress=yes mprint=1 sumsize=max sortsize=max threads=yes ; libname z oracle User = &UserOracle Pass = &PassOracle Path = &PathOracle schema=DWNM ; PROC SQL THREADS; CREATE TABLE SASUSER.third_march as SELECT COUNT(t1.ID_DIA) FROM z.DW_F_RECARGAS t1, z.DW_D_ORIGEN_RECARGA t2 WHERE ( t2.ID_ORIGEN_RECARGA=t1.ID_ORIGEN_RECARGA ) AND (t1.ID_DIA = '03MAR2020:00:00:00'dt AND t2.DES_ORIGEN_RECARGA IN ('IN', 'ETU')); RUN; """ ll = sas.submit(sasquery)

and the error log is Le Système SAS mardi 12 mai 2020 18:24:00 85 86 ods listing close; 87 ods html5 (id=saspy_internal) options(bitmap_mode='inline') 88 file="/saswork1/SAS_workCDAB00006959_sasprodapp/SAS_workC67F00006959_sasprodapp/saspy_results.html" 89 device=svg 90 style=HTMLBlue; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: /saswork1/SAS_workCDAB00006959_sasprodapp/SAS_workC67F00006959_sasprodapp/saspy_results.html 91 ods graphics on / outputfmt=png; 92 ;*';*";*/;quit;run;libname t oracle User = &UserOracle Pass = &PassOracle Path = &PathOracle schema=BI_PROD ; NOTE: Libref T was successfully assigned as follows: Engine: ORACLE Physical Name: (DESCRIPTION =(ADDRESS_LIST =(ADDRESS=(PROTOCOL=TCP)(HOST=10.128.32.84)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME = NM))) 93 options compress=yes mprint=1 sumsize=max sortsize=max threads=yes ; 94 libname z oracle User = &UserOracle Pass = &PassOracle Path = &PathOracle schema=DWNM ; NOTE: Libref Z was successfully assigned as follows: Engine: ORACLE Physical Name: (DESCRIPTION =(ADDRESS_LIST =(ADDRESS=(PROTOCOL=TCP)(HOST=10.128.32.84)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME = NM))) 95 PROC SQL THREADS; 96 CREATE TABLE SASUSER.third_march as 97 SELECT 98 COUNT(t1.ID_DIA) 99 FROM 100 z.DW_F_RECARGAS t1, 101 z.DW_D_ORIGEN_RECARGA t2 102 WHERE 103 ( t2.ID_ORIGEN_RECARGA=t1.ID_ORIGEN_RECARGA ) 104 AND (t1.ID_DIA = '03MAR2020:00:00:00'dt 105 AND t2.DES_ORIGEN_RECARGA IN ('IN', 'ETU')); ERROR: Write access to member SASUSER.THIRD_MARCH.DATA is denied. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 106 RUN; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. 107 ;*';*";*/;quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL a utilisé (Durée totale du traitement) : real time 0.00 seconds cpu time 0.00 seconds 107 ! run; 108 ods html5 (id=saspy_internal) close; 109 ods listing; 110

[tomweber-sas]

ok, thanks! That's better. So, can you see what directory your sasuser is assigned to, and what the permissions are? Does that table happen to already exist or something, such that maybe that's why you can't write it or something like that? I don't have any info why you don't have permission for write access. Are you connected to the workspace server with the same credentials? Can you show me your configuration definition your are using to connect to this workspace server? Since you're comparing EG to saspy, I would like to see this directory and it's permissions and you'r user, between the two connections. That's the thing to look at it seems.

[Mouadbenaou]

well I found an aternative. I create a new libname with my sasuser path and it works . !! and even if the file already exists it can over-wirte it maybe you should consider raising error or add a new config . Tks for your collaboration

[tomweber-sas]

Well, that doesn't explain what the problem is. Can you show me your configuration definition? Are you really using remote IOM to connect to the same workspace server? Not using STDIO (over SSH) which will start a SAS session from the same installed location but won't be that wortkspace server, nor be configured the same or anything like that./ That's a common mistake, and will present differences like permissions and resource differences and the such.

[Mouadbenaou]

yes indeed , here's my output when i start the session

[tomweber-sas]

Oh, you're using the COM access method, not IOM. In that case, I'd be curious to see if things behave as expected if you used the IOM access method. Do you have java installed on your client machine? That's all you need to try out IOM. If I could actually see your configuration definition - that's the python dictionary in your sascfg_personal.py file with the name 'iomcom', then I can provide the correct version that would work with IOM. Though, basically, you would still need the iomhost/port, your same credentials, so all that's the same. If you have 'class_id' and 'provider', remove those and add 'java' with the path to your java.exe file and you should be able to connect using my IOM access method. The COM access method is a user contributed Access Method, which is very complete, though written completely differently than my 3 access methods. So, I can't guarantee that it will or can always perform identically to the ones I provide. I try to support it as best I can, but it isn't one I can always control. Could you give the IOM one a try and see if this problem still happens or not? Thanks! Tom

[FriedEgg]

The write ability of the SASUSER library is set by the RSASUSER System Option and can only be set on invocation of SAS (in a config file, as a command line option or in an environment variable), which should occur the same way regardless of access method (COM/IOM). So, my concern would be that you're not connecting to the same Workspace in saspy (with RSASUSER) and eg (with NORSASUSER).

Run the following in both methods (saspy/eg) and compare the logs. They should be identical.

proc options option=rsasuser; run;
proc options option=sasuser; run;
%put &SYSPROCESSMODE (&SYSPROCESSNAME &SYSSCP._&SYSSCPL);
%put &SYSHOSTNAME (&SYSHOSTINFOLONG);

[Mouadbenaou]

@tomweber-sas i cant do the IOM method because I dont the jar files that come with sas installation ( i dont have the sas instaltion files ) @FriedEgg @tomweber-sas you were right , I wasnt conncted to the same workspace.

[tomweber-sas]

@Mouadbenaou you stated you have V3.3.6 of saspy, If so, you can use IOM now. As of V3.3.3, I've been allowed to include the 4 IOM client jars in the saspy repo. And, you no longer need to specify the classpath since I can generate it myself. You really only need to point to java and it all works; easy. I've still never seen your config definitiion, but as you're not connecting to the workspace server you thought, there are two ways to address that. If you have a different iomhost/iomport, then that's kinda obvious. Using the same ones as EG is configured will solve that. However, there is also the appserver key in the configuration definition you can use, when there is more than one AppServer associated with the objectspawner, so you can specifically have it spawn the correct workspace server by name. else the Object Spawner just tries the first workspace server in it's list.
There's only one case, now, where you would need any jars from the SAS install, and that's only if your workspace server is running encrypted. I can't provide the jars that support encryption in the saspy repo; legal issue. But, if that's not the case, then you should be good to go with the IOM access method now!

@FriedEgg , as always, thanks for the insight and help! always appreciated. Tom

[Mouadbenaou]

@tomweber-sas Thnak you for all the insights , I 'll try IOM method. and give a response asap. I just have one more question , is there a way to make sure that the lunched process to its end even if my session is dead ( now with remote work the connection kinda dies and I have to run the work again ) Thank you in advance

[tomweber-sas]

@Mouadbenaou I'm not sure I completely understand your question. Can you give an example?

One thing that might be relevant (I'm not sure yet), is that a remote IOM connection can be 'broken' if the network connection is interrupted. Given the nature of a Worksspace server, being a single use process, it that happens it's gone and you can't get back to that same session. You would have to make a new connection and get a new server, and resubmit all your code.

The IOM interface, and the saspy IOM access method do have a feature that has to do with this. IOM provides it's client (saspy in this case) the ability to temporarily disconnect from a Workspace server, and then, within a time limit defined in metadata, reconnect to that same Workspace server. I added support for this upon a user request where the user had their laptop in a docking station at their desk, and had a working session. But, when going to a meeting, the computer would switch from LAN to Wifi and the connection would be severed. Thus, having to reconnect to a new session and rerun everything.

The IOM access method has a disconnect() method on the SASsession object, which can be used (again, if this is enabled in OMR), to tell saspy to disconnect (and acquire the needed token to be able to reconnect), and then the next time you submit anything through saspy, it will first reconnect to that server and continue. This is documented here: https://sassoftware.github.io/saspy/advanced-topics.html#disconnecting-from-an-iom-session-and-reconnecting-back-to-it

I'm not sure if that is what you are looking for or not, but it seems related to what you're asking. The other thing that might be part of your questions if being able to progammaticaly assess if the session is still connected? The SASsession has a number of attributes, one being the SAS pid (process id). The last attr displayed when submitting the SASsession object.

>>> sas
Access Method         = STDIO
SAS Config name       = sdssas
SAS Config file       = /opt/tom/github/saspy/saspy/sascfg_personal.py
WORK Path             = /sastmp/SAS_work8D9300000FF2_tom64-5/
SAS Version           = 9.04.01M7D05142020
SASPy Version         = 3.3.6
Teach me SAS          = False
Batch                 = False
Results               = Pandas
SAS Session Encoding  = latin1
Python Encoding value = latin_1
SAS process Pid value = 4082

>>> sas.SASpid
'4082'
>>>
>>> sas.endsas()
SAS Connection terminated. Subprocess id was 4052
>>> sas
Access Method         = STDIO
SAS Config name       = sdssas
SAS Config file       = /opt/tom/github/saspy/saspy/sascfg_personal.py
WORK Path             = /sastmp/SAS_work8D9300000FF2_tom64-5/
SAS Version           = 9.04.01M7D05142020
SASPy Version         = 3.3.6
Teach me SAS          = False
Batch                 = False
Results               = Pandas
SAS Session Encoding  = latin1
Python Encoding value = latin_1
SAS process Pid value = None

>>>

As you can see, that pid value is the pid of the SAS session. and the SASpid attribute has it's value. If the SASsession gets disconnected (on purpose like above, or otherwise), the value of that attribute will be removed, be None. This allows you to progammaticaly check it see the state of the connection. Other than terminating it yourself, you may see the following message when trying to run a method if the seesion has been disconnected/terminated:

'SAS process has terminated unexpectedly. Pid State= (4167, 65280)'

Then you'll also have SAS process Pid value = None

Again, I'm not really sure what you were asking, but these at the two things that come to mind. Can you expand on your question, and/or do either of these things answer or help?

Thanks! Tom

[Mouadbenaou]

@tomweber-sas yes this helps a lot. But still dont know if the running queries ( ex : from orcale database ) will continue running when I disconnect from the session and connect back to it ( in case its note terminated ) ??

[tomweber-sas]

Hey @Mouadbenaou, so, you are looking to submit some long running code, and want to then disconnect and reconnect later, while that code still runs to completion? So, for instance, something like this?

>>> import saspy
>>> sas = saspy.SASsession(cfgname='iomj')
>>> sas
>>> #do some work w/ saspy
>>> sas.assigned_librefs()
>>> sas.submitLOG("data a; x=1;run;")
>>>
>>> # some long running Oracle query. submit, disconnect temporarily
>>> sas.submitLOG("proc sql; create table b as select * from  Ora.table; quit;") 
>>> sas.disconnect()
>>>
>>> # reconnect and see that it completed and the table is there
>>> sas.list_tables()
>>>

So, this won't work only because the submit methods are synchronous. Python is single threaded and generally synchronous. So, you won't execute the disconnect() until after the submit() of the long running query completes.

BUT, there is a way. I have an advanced, internal method (starts w/ and underscore to show private), which you can use for this scenario. SASsession has an _asubmit() method to asynchronously submit code and then come right back to be able to do some other work. It returns nothing, and getting back the log/lst isn't something that then happens for you. So, it's not something I would expose for every day use. But, if what you are submitting, is just something that does work like creating a table that takes a while, and there's no listing to worry about, you could run the following code:

>>> import saspy
>>> sas = saspy.SASsession(cfgname='iomj')
>>> sas
>>> #do some work w/ saspy
>>> sas.assigned_librefs()
>>> sas.submitLOG("data a; x=1;run;")
>>>
>>> # some long running Oracle query. submit, disconnect temporarily
>>> # results= happens to be required on _asubmit() , so set it to text
>>> sas._asubmit("proc sql; create table b as select * from  Ora.table; quit;", 'text')
>>> sas.disconnect()  # this will now run (and disconnect), while SAS is running the long query
>>>
>>> # reconnect and see that it completed and the table is there
>>> sas.submitLOG("")  # this will get the log from the asynchronous submit
>>> sas.list_tables()  # this will show the tables that are in work
>>>

Is this what you are actually trying to do? Do you have disconnecting enabled for your workspace server (just submit it and it sill tell you either way). Why, out of curiosity, are you disconnecting? To switch networks, or some other reason?

Thanks, Tom

[tomweber-sas]

@Mouadbenaou are you good to go on this? Is it doing what you need? Was this what you were trying to do?

Thanks, Tom

[tomweber-sas]

Closing up old, idle issues. if you still need something on this, you can reopen it. If you need anything else, feel free to open a new issue. Thanks! Tom