Conversion SAS data to pandas dataframe failed with "sasdata"

[ghost]

I tried several times to get SAS data with the saspy library, but I can't get it even if my saspy connexion seems right.

To connect to my SAS serveur :

import saspy #(version 1.3.5)
import pandas #(version 3.7.2)
sas = saspy.SASsession(cfgfile='C:/Users/guillaume_hu/AppData/Local/Continuum/anaconda3/pkgs/saspy-3.7.2-pyhd3eb1b0_0/site-packages/saspy/sascfg_personal.py', user='xxx.xxx', pw='xxxx')
sas

Access Method = COM
SAS Config name = iomcom
SAS Config file = C:/Users/guillaume_hu/AppData/Local/Continuum/anaconda3/pkgs/saspy-3.7.2-pyhd3eb1b0_0/site-packages/saspy/sascfg_personal.py
WORK Path = F:\SASWORK_TD5696_VPPRDSAS1_\Prc2
SAS Version = 9.04.01M4P11092016
SASPy Version = 3.7.2
Teach me SAS = False
Batch = False
Results = Pandas
SAS Session Encoding = wlatin1
Python Encoding value = windows-1252
SAS process Pid value = 5696

Here is the content of « sascfg_personal.py » :

SAS_config_names=['iomcom']
SAS_config_options = {'lock_down': False,
'verbose' : True,
'prompt' : True
}
SAS_output_options = {'output' : 'html5'} # not required unless changing any of the default

iomcom = {
'iomhost' : 'xxxxxx.xxxxxxx.com',
'iomport' : 8591,
'class_id': '440196d4-90f0-11d0-9f41-00a024bb830c',
'provider': 'sas.iomprovider',
'encoding': 'windows-1252'}

I can access to all the following assigned library :

assigned_librefs = sas.assigned_librefs()
print(assigned_librefs)
>> ['WORK', 'STPSAMP', 'REFENV', 'VEX', 'ADC_2', 'SITADEL', 'IMIAA', 'ADC', 'REFSPE', 'ARIEL', 'REFGEOG', 'SUIVI_2', 'MARCHE1', 'EPCI', 'SAS_VA', 'SASDATA', 'MISSION', 'REF_GEN', 'DATA_IM', 'MARCHE', 'SASHELP', 'MAPS', 'MAPSSAS', 'MAPSGFK', 'SASUSER']

I can get access to tables :

            sas.sasdata('DE', 'MARCHE')
                           >> Libref  = MARCHE

Table = DE
Dsopts = {}
Results = Pandas

SAS request works fine and give SAS HMTL results :

            sas.submitLST("""

libname work listo;

proc sql OUTOBS=6;
select raison_sociale_acheteur, objet
from MARCHE.DE (encoding=latin1)
quit;
""")

Unfortunately the conversion from SAS data to pandas dataframe failed :

            cars = sas.sasdata('cars', 'sashelp')

cars.to_df()

---

com_error Traceback (most recent call last)
C:\Users\GUILLA~1\AppData\Local\Temp/ipykernel_19628/1595749334.py in
----> 1 cars.to_df()

~\AppData\Local\Continuum\anaconda3\lib\site-packages\saspy\sasdata.py in to_df(self, method, **kwargs)
1208 if self.sas.sascfg.pandas:
1209 raise type(self.sas.sascfg.pandas)(self.sas.sascfg.pandas.msg)
-> 1210 df = self.sas.sasdata2dataframe(self.table, self.libref, self.dsopts, method, **kwargs)
1211 self.sas._lastlog = self.sas._io._log[lastlog:]
1212 return df

~\AppData\Local\Continuum\anaconda3\lib\site-packages\saspy\sasbase.py in sasdata2dataframe(self, table, libref, dsopts, method, **kwargs)
1683 df = None
1684 else:
-> 1685 df = self._io.sasdata2dataframe(table, libref, dsopts, method=method, **kwargs)
1686
1687 self._lastlog = self._io._log[lastlog:]

~\AppData\Local\Continuum\anaconda3\lib\site-packages\saspy\sasiocom.py in sasdata2dataframe(self, table, libref, dsopts, method, **kwargs)
774 print("'dtype=' is only used with the CSV version of this method. option ignored.")
775
--> 776 header, rows, meta = self.read_sasdata(table, libref, dsopts=dsopts)
777 df = pd.DataFrame.from_records(rows, columns=header, **kwargs)
778

~\AppData\Local\Continuum\anaconda3\lib\site-packages\saspy\sasiocom.py in read_sasdata(self, table, libref, dsopts)
570 export = EXPORT.format(tgt=TARGET, tbl=tablepath, dopt=dsopts)
571 self.workspace.LanguageService.Submit(export)
--> 572 meta = self._schema(TARGET)
573
574 # Connect RecordSet object to ADODB connection with params:

~\AppData\Local\Continuum\anaconda3\lib\site-packages\saspy\sasiocom.py in _schema(self, table, libref)
393 metadata = {}
394 while not schema.EOF:
--> 395 col_info = {x.Name: x.Value for x in schema.Fields}
396 if col_info['FORMAT_NAME'] in self._sb.sas_date_fmts:
397 col_info['CONVERT'] = lambda x: self._sb.SAS_EPOCH + datetime.timedelta(days=x) if x else x

~\AppData\Local\Continuum\anaconda3\lib\site-packages\saspy\sasiocom.py in (.0)
393 metadata = {}
394 while not schema.EOF:
--> 395 col_info = {x.Name: x.Value for x in schema.Fields}
396 if col_info['FORMAT_NAME'] in self._sb.sas_date_fmts:
397 col_info['CONVERT'] = lambda x: self._sb.SAS_EPOCH + datetime.timedelta(days=x) if x else x

~\AppData\Local\Continuum\anaconda3\lib\site-packages\win32com\client\dynamic.py in getattr(self, attr)
626 )
627 try:
--> 628 ret = self.oleobj.Invoke(retEntry.dispid, 0, invoke_type, 1)
629 except pythoncom.com_error as details:
630 if details.hresult in ERRORS_BAD_CONTEXT:

com_error: (-2147352567, 'Une exception s’est produite.', (0, 'Provider', "Mécanisme d'accès non valide.", None, 1240640, -2147217920), None)

Is there something wrong with my SASPY config or with the versionning between my SAS server, SASpy and Pandas?

Thanks all for your help

Here is my SAS environment (quite old I know) :
Version en cours : 9.04.01M4P110916
Système d'exploitation : WX64_SV .
---Logiciel Base SAS
---SAS/STAT
---SAS/GRAPH
---SAS Integration Technologies
---SAS/Secure 168-bit
---SAS/Secure Windows
---SAS Enterprise Guide
---SAS/ACCESS Interface to PC Files
---SAS/ACCESS Interface to ODBC
---SAS Workspace Server for Local Access
---High Performance Suite
---SAS Add-in for Microsoft Excel
---SAS Add-in for Microsoft Outlook
---SAS Add-in for Microsoft PowerPoint

[tomweber-sas]

Hey, so I think the problem is with using the COM access method. The error is happening somewhere between it and the third party interface it's using to try to interact with SAS. Also, the saspy version you have is a year old, so there could be something that was fixed in that access method since then, but you should really use the IOM access method instead. It works and performs significantly better and has much better functionality (https://sassoftware.github.io/saspy/configuration.html#iom-using-com).
Can you try using IOM instead? (https://sassoftware.github.io/saspy/configuration.html#iom-using-java).
The other thing I see is that the encodings don't match, and it could be that there's a transcoding issue due to that, though again, the error is down in some third party code that access method uses, so I can't really tell what the problem is with it.

Can you get the current version of SASPy and then use the IOM access method; it should resolve whatever is the problem here.

Thanks,
Tom

[ghost]

Hi Tom,
I did update my saspy from 3.7.2 to 4.3.0. I still get the same COM error.
I looked at my connexion settings and as I described before I already use the IOM access method.

Here is the content of « sascfg_personal.py » :

SAS_config_names=['iomcom']
SAS_config_options = {'lock_down': False,
'verbose' : True,
'prompt' : True
}
SAS_output_options = {'output' : 'html5'} # not required unless changing any of the default
iomcom = {
'iomhost' : 'xxx.xxxx.com',
'iomport' : 8591,
'class_id': '440196d4-90f0-11d0-9f41-00a024bb830c',
'provider': 'sas.iomprovider',
'encoding': 'windows-1252'}

Should I use IOM using Java Instead ?

Thank for your help,

Guillaume

[tomweber-sas]

Yes, use IOM and it should all work as expected. COM was a user contribution years ago, and I have no control over how it works, certainly not like my 3 access methods. So just use IOM instead and it should solve whatever this problem happens to be.

[ghost]

I modified the sascfg_personal.py and changed to the "winiomwin" option. It works fine.
Thanks Tom for your help.
Guillaume