SASpy and user defined SAS formats

[dacodevet]

Is there a way to associate sas format catalogs with sas datasets when converting sas to df using(sd2df())? The process abends when it tries to find formats for certain SAS datasets(other SAS datasets are converted successfully). I've already tried:

sas = saspy.SASsession(cfgname='winlocal', results='HTML')
sas.submit('''options fmtsearch=(xxx.my_formats);''')
stdf = sas.sd2df(table=xname, libref='XXX', method='CSV')

I really want to avoid creating a dictionary of that catalog or exporting the SAS dataset to csv to create the dataframe.

Thanks

[tomweber-sas]

Hey @dacodevet, saspy has no idea of SAS format catalogs and format search paths or anything like that. What you have above, with setting the format search path sounds like the right thing. It's on SAS to be able to use formats, and find them, on it's own. Can you bring up SAS on it's own, submit that same options fmtsearch statement and proc print the datasets in question? Is it SAS itself that's abending? What version do you have? Also, I why, out of curiosity, are you using the method=csv? The default should be better. BTW, what version of saspy are you using? And I'm assuming (never a good thing) you're using the IOM access method to connect to a local windows SAS install? More details would be helpful, but lets start with those. If you can show me your 'winlocal' configuration definition, that would help too.
Thanks!
Tom

[dacodevet]

Hi @tomweber-sas, Thanks for the quick reply! Yes, everything works fine in the local SAS environment. It is an error on the SAS side. I used that method because I read something about using that with large datasets(BTW, I had the same result with the default option)
I'm running SAS v9.4m4
Saspy is 4.1.0

iomwin = {'java' : '/usr/bin/java',
'iomhost' : 'windows.iom.host',
'iomport' : 8591,
}
cpW = "C:\Program Files\SASHome94\SASDeploymentManager\9.4\products\deploywiz__94330__prt__xx__sp0__1\deploywiz\sas.svc.connection.jar"

cpW += ";C:\Program Files\SASHome94\SASDeploymentManager\9.4\products\deploywiz__94330__prt__xx__sp0__1\deploywiz\log4j.jar"

cpW += ";C:\Program Files\SASHome94\SASDeploymentManager\9.4\products\deploywiz__94330__prt__xx__sp0__1\deploywiz\sas.security.sspi.jar"

cpW += ";C:\Program Files\SASHome94\SASDeploymentManager\9.4\products\deploywiz__94330__prt__xx__sp0__1\deploywiz\sas.core.jar"

cpW += ";C:\Users\qha1\AppData\Local\Programs\Python\Python310\Lib\site-packages\saspy\java\saspyiom.jar"

winlocal = {'java' : 'C:\Program Files\Java\jre1.8.0_321\bin\java',
'encoding' : 'windows-1252',
'classpath' : cpW
}

[tomweber-sas]

Ok, the versions and config are fine. Though you don't need that classpath in there at all. That's all handled for you now. So can you see any error or anything about what is happening on the SAS side? I don't really have any idea of what's happening. Have you tried anything to see what's going on? try submitting proc print of one of the trouble data sets after setting your fmtsearch, or anything else to try to see the log and see if there's a problem on the SAS side?

[dacodevet]

So the error just says it can't find the associated format and then terminates. I'm sure it would work within the submit() method with explicit SAS code. I'm guessing there is no way to set global system options like: (options dlcreatedir nomprint nomlogic nosymbolgen nospool notes;) setting within saspy.SASsession()

BTW, is there anyway to get the SAS log in python for the sd2df() method. I've tried print(sas.lastlog()) and I only see python related messages:

C:\Users\qha1\AppData\Local\Programs\Python\Python310\lib\site-packages\saspy\sasioiom.py:1011: UserWarning: Noticed 'ERROR:' in LOG, you ought to take a look and see if there was a problem
warnings.warn("Noticed 'ERROR:' in LOG, you ought to take a look and see if there was a problem")
Traceback (most recent call last):
File "C:\Users\qha1\OneDrive - CDC\Python Scripts\Python programs\mmria_csv_sas.py", line 104, in
stdf = sas.sd2df(table=xname, libref='MMRIA', method='CSV')
File "C:\Users\qha1\AppData\Local\Programs\Python\Python310\lib\site-packages\saspy\sasbase.py", line 1603, in sd2df
return self.sasdata2dataframe(table, libref, dsopts, method, **kwargs)
File "C:\Users\qha1\AppData\Local\Programs\Python\Python310\lib\site-packages\saspy\sasbase.py", line 1785, in sasdata2dataframe
df = self._io.sasdata2dataframe(table, libref, dsopts, method=method, **kwargs)
File "C:\Users\qha1\AppData\Local\Programs\Python\Python310\lib\site-packages\saspy\sasioiom.py", line 1708, in sasdata2dataframe
return self.sasdata2dataframeCSV(table, libref, dsopts, **kwargs)
File "C:\Users\qha1\AppData\Local\Programs\Python\Python310\lib\site-packages\saspy\sasioiom.py", line 1800, in sasdata2dataframeCSV
del varcat[nvars]
IndexError: list assignment index out of range
SAS Connection terminated. Subprocess id was 19320

[tomweber-sas]

Well, the traceback above is from a sd2df_CSV(), and it seems SAS terminated, so I can't really get the log from it to then have available. As for options, you can set any options you like. sas.submit('options x=y;'). For invocation only options, saspy is starting up tour local SAS, so you can configure your sasv9.cfg file with whatever invocation options you like.
I don't see any errors about the format, you mentioned you saw that somewhere? Can you try anything other than sd2df_CSV(), like proc print, to see if SAS doesn't terminate and we can see the SASLOG and see if there's something useful in it?

[dacodevet]

Ok. Let me clarify the format error. One of the datasets needed formats that had been associated. The previous datasets in the same session using sd2df() were successful as noted below. I tried to view that next SAS dataset in my local SAS and got the format not found error which indicates it couldn't find the format catalog(Output noted at the bottom).

### Python output of previous dataset that didn't use a user defined format:

The SAS System

NOTE: View WORK.SASDATA2DATAFRAME.VIEW used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: View WORK.SASDATA2DATAFRAME.VIEW used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: There were 25 observations read from the data set LIBX.ARBT_DAT.

NOTE: The file _TOMODSX is:
Filename=C:\Users\qha1\AppData\Local\Temp\1\tmp7chyvmox\tomodsx,
RECFM=V,LRECL=4194304,File Size (bytes)=0,
Last Modified=15Apr2022:12:07:44,
Create Time=15Apr2022:12:07:44

NOTE: 397 records were written to the file _TOMODSX.
The minimum record length was 45.
The maximum record length was 157.
NOTE: View WORK.SASDATA2DATAFRAME.VIEW used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

NOTE: There were 396 observations read from the data set LIBX.ARBT_DAT.
NOTE: There were 396 observations read from the data set WORK.SASDATA2DATAFRAME.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

The next SAS dataset to be converted LOG from local SAS:

libname LIBX"C:\Users\qxx1\Downloads\used\SAS_data";

NOTE: Libref LIBX refers to the same physical library as TMP2.
NOTE: Libref LIBX was successfully assigned as follows:
Engine: V9
Physical Name: C:\Users\qha1\Downloads\used\SAS_data
ERROR: Format FMTBI_LBORFD not found or couldn't be loaded for variable s_r_type.
ERROR: Format FMTYNU not found or couldn't be loaded for variable s_im_gesta.
ERROR: Format FMTBI_BUNIT not found or couldn't be loaded for variable badbw_uo_meu.
ERROR: Format FMTBI_IGDER not found or couldn't be loaded for variable sbad_gde.
ERROR: Format FMTBI_INLIVING not found or couldn't be loaded for variable sbad_iil_reor.
ERROR: Format FMTYNU not found or couldn't be loaded for variable sbad_iia_dh.
ERROR: Format FMTYNU not found or couldn't be loaded for variable sbad_ww2_hrs.
ERROR: Format FMTYNU not found or couldn't be loaded for variable smod_wdwb_unuc.
ERROR: Format FMTYNU not found or couldn't be loaded for variable smod_wdwvb_usuc.
ERROR: Format FMTBI_DELER not found or couldn't be loaded for variable smod_f_dliv.
ERROR: Format FMTBI_DIVERTE not found or couldn't be loaded for variable smod_fro_dliv.
ERROR: Format FMTYNNAU not found or couldn't be loaded for variable smod_icwl_atm.

[tomweber-sas]

ok, so do you have the format library that has these formats and can you add it to the fmtsearch, so they can be found and used?

[dacodevet]

Yes. I have a program that creates the formats.

[tomweber-sas]

then why aren't they found? Those are SAS errors saying they aren't there.

[dacodevet]

That's the issue. I can use fmtsearch in SAS and all is well. It finds the catalog and processes the dataset. In python I'm using similar code but it appears that the options are not being recognized. Now that I think about it. I believe it's creating a new session each time I call the method there by the options aren't enabled when needed. I think I will switch to a SAS context structure to keep everything together. I let you know what happens.

[tomweber-sas]

Well, have you looked at the log after you've submitted the options and assigned the library and such to see if there was a problem? Can you compare that w/ submitting the exact same things in a stand alone SAS session? If you're running the same SAS session, and it can find them in it one way, it should find them the other way. I don't have enough info from either case to see what the problem is myself. Can you do a Teams call and show me what you're doing? That would probably go a long way.

[tomweber-sas]

Since I don't know what you're running, i've tried to put together what I thing should be similar. Are you running your SAS session w/ errorabend or something that would terminate SAS if there was an error? In the case below, I just get the error about the format not found till I add it to the fmtsearch and then everything runs as expected.

>>> sas
Access Method         = STDIO
SAS Config name       = tdi
SAS Config file       = /opt/tom/github/saspy/saspy/sascfg_personal.py
WORK Path             = /usr/tmp/SAS_workF294000050A9_tom64-5/
SAS Version           = 9.04.01M7P08052020
SASPy Version         = 4.2.0
Teach me SAS          = False
Batch                 = False
Results               = Pandas
SAS Session Encoding  = utf-8
Python Encoding value = utf_8
SAS process Pid value = 20649


>>> sas.submitLOG("""
...    libname x '.';
...    proc format library=x;
...       value  $city 'BR1'='Birmingham UK'
...                    'BR2'='Plymouth UK'
...                    'BR3'='York UK'
...                    'US1'='Denver USA'
...                    'US2'='Miami USA'
...                    other='INCORRECT CODE';
...    run;
... """)

21   ods listing close;ods html5 (id=saspy_internal) file=stdout options(bitmap_mode='inline') device=svg style=HTMLBlue; ods
21 ! graphics on / outputfmt=png;
NOTE: Writing HTML5(SASPY_INTERNAL) Body file: STDOUT
22
23
24      libname x '.';
NOTE: Libref X was successfully assigned as follows:
      Engine:        V9
      Physical Name: /opt/tom/github/saspy/saspy
25      proc format library=x;
26         value  $city 'BR1'='Birmingham UK'
27                      'BR2'='Plymouth UK'
28                      'BR3'='York UK'
29                      'US1'='Denver USA'
30                      'US2'='Miami USA'
NOTE: Format $CITY is already on the library X.FORMATS.
NOTE: Format $CITY has been written to X.FORMATS.
31                      other='INCORRECT CODE';
32      run;
NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

33
34
35   ods html5 (id=saspy_internal) close;ods listing;

>>>
>>> sd = sas.sasdata('cars','sashelp', results='text')
>>> sd
Libref  = sashelp
Table   = cars
Dsopts  = {}
Results = text

>>>
>>> sd.head()


                                                           The SAS System                     Friday, April 15, 2022 04:22:00 PM   2

                                       Drive                   Engine                                 MPG_
 Obs Make  Model          Type  Origin Train     MSRP  Invoice  Size  Cylinders Horsepower MPG_City Highway Weight Wheelbase Length

   1 Acura MDX            SUV    Asia  All    $36,945  $33,337   3.5      6         265       17       23    4451     106      189
   2 Acura RSX Type S 2dr Sedan  Asia  Front  $23,820  $21,761   2.0      4         200       24       31    2778     101      172
   3 Acura TSX 4dr        Sedan  Asia  Front  $26,990  $24,647   2.4      4         200       22       29    3230     105      183
   4 Acura TL 4dr         Sedan  Asia  Front  $33,195  $30,299   3.2      6         270       20       28    3575     108      186
   5 Acura 3.5 RL 4dr     Sedan  Asia  Front  $43,755  $39,014   3.5      6         225       18       24    3880     115      197
>>>
>>> sd.dsopts={'format':{'origin':'$city.'}}
>>> sd.head()
/opt/tom/github/saspy/saspy/sasiostdio.py:1006: UserWarning: Noticed 'ERROR:' in LOG, you ought to take a look and see if there was a problem
  warnings.warn("Noticed 'ERROR:' in LOG, you ought to take a look and see if there was a problem")

>>> print(sas.lastlog())

61   ;*';*";*/;
62   data _null_; e = exist("sashelp.'cars'n");
63   v = exist("sashelp.'cars'n", 'VIEW');
64    if e or v then e = 1;
65   put 'TABLE_EXISTS=' e 'TAB_EXTEND=';run;
TABLE_EXISTS=1 TAB_EXTEND=
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

66
67   ;*';*";*/;%put %upcase(e3969440a681a2408885998500000009);
E3969440A681A2408885998500000009
68   ;*';*";*/;
69   proc print data=sashelp.'cars'n (obs=5 );
ERROR: The format $CITY was not found or could not be loaded.
70      format origin $city. ;;run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

71
72   ;*';*";*/;%put %upcase(e3969440a681a2408885998500000010);
E3969440A681A2408885998500000010
>>>
>>> df = sd.to_df()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/opt/tom/github/saspy/saspy/sasdata.py", line 1213, in to_df
    df = self.sas.sasdata2dataframe(self.table, self.libref, self.dsopts, method, **kwargs)
  File "/opt/tom/github/saspy/saspy/sasbase.py", line 1790, in sasdata2dataframe
    df = self._io.sasdata2dataframe(table, libref, dsopts, method=method, **kwargs)
  File "/opt/tom/github/saspy/saspy/sasiostdio.py", line 2317, in sasdata2dataframe
    rowrep, colrep, port=port, wait=wait, **kwargs)
  File "/opt/tom/github/saspy/saspy/sasiostdio.py", line 2561, in sasdata2dataframeDISK
    lrecl = int(l2[0])
ValueError: invalid literal for int() with base 10: 'vn=VARNUMS= vl=VARLIST= vt=VARTYPE= i=1 var=  _ERROR_=1 _N_=1'
>>> print(sas.lastlog())

87
88   data work.sasdata2dataframe / view=work.sasdata2dataframe; set sashelp.'cars'n ;
89      format origin $city. ;;run;
                    ------
                    48
ERROR 48-59: The format $CITY was not found or could not be loaded.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

90   data _null_; file STDERR;d = open('work.sasdata2dataframe');
91   lrecl = attrn(d, 'LRECL'); nvars = attrn(d, 'NVARS');
92   lr='LRECL='; vn='VARNUMS='; vl='VARLIST='; vt='VARTYPE=';
93   put lr lrecl; put vn nvars; put vl;
94   do i = 1 to nvars; var = varname(d, i); put var; end;
95   put vt;
96   do i = 1 to nvars; var = vartype(d, i); put var; end;
97   run;
NOTE: The file STDERR is:
      Pipe command="<standard error>"

NOTE: Argument 1 to function ATTRN(0,'LRECL') at line 91 column 9 is invalid.
NOTE: Argument 1 to function ATTRN(0,'NVARS') at line 91 column 36 is invalid.LRECL= .
VARNUMS= .
VARLIST=

ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero,
       or invalid.
d=0 lrecl=. nvars=. lr=LRECL= vn=VARNUMS= vl=VARLIST= vt=VARTYPE= i=1 var=  _ERROR_=1 _N_=1

NOTE: 3 records were written to the file STDERR.
      The minimum record length was 8.
      The maximum record length was 10.
NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to
      missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 91:9    1 at 91:36
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

98
>>>
>>> sas.submitLOG("options fmtsearch=(x);")

127  ods listing close;ods html5 (id=saspy_internal) file=stdout options(bitmap_mode='inline') device=svg style=HTMLBlue; ods
127! graphics on / outputfmt=png;
NOTE: Writing HTML5(SASPY_INTERNAL) Body file: STDOUT
128
129  options fmtsearch=(x);
130
131  ods html5 (id=saspy_internal) close;ods listing;

>>> sd.head()


                                                           The SAS System                     Friday, April 15, 2022 04:22:00 PM   3

                                                                                                              M
                                                            D                            E         H          P
                                                            r                            n    C    o          G           W
                                                            i                            g    y    r     M    _           h
                                                            v                    I       i    l    s     P    H           e
                                               O            e                    n       n    i    e     G    i     W     e     L
                      M                        r            T                    v       e    n    p     _    g     e     l     e
          M           o            T           i            r         M          o       S    d    o     C    h     i     b     n
   O      a           d            y           g            a         S          i       i    e    w     i    w     g     a     g
   b      k           e            p           i            i         R          c       z    r    e     t    a     h     s     t
   s      e           l            e           n            n         P          e       e    s    r     y    y     t     e     h

    1   Acura   MDX              SUV     INCORRECT CODE   All      $36,945    $33,337   3.5   6   265   17   23   4451   106   189
    2   Acura   RSX Type S 2dr   Sedan   INCORRECT CODE   Front    $23,820    $21,761   2.0   4   200   24   31   2778   101   172
    3   Acura   TSX 4dr          Sedan   INCORRECT CODE   Front    $26,990    $24,647   2.4   4   200   22   29   3230   105   183
    4   Acura   TL 4dr           Sedan   INCORRECT CODE   Front    $33,195    $30,299   3.2   6   270   20   28   3575   108   186
    5   Acura   3.5 RL 4dr       Sedan   INCORRECT CODE   Front    $43,755    $39,014   3.5   6   225   18   24   3880   115   197
>>> df = sd.to_df()
>>> df.head()
    Make           Model   Type          Origin DriveTrain     MSRP  Invoice  EngineSize  Cylinders  Horsepower  MPG_City  MPG_Highway  Weight  Wheelbase  Length
0  Acura             MDX    SUV  INCORRECT CODE        All  36945.0  33337.0         3.5        6.0       265.0      17.0         23.0  4451.0      106.0   189.0
1  Acura  RSX Type S 2dr  Sedan  INCORRECT CODE      Front  23820.0  21761.0         2.0        4.0       200.0      24.0         31.0  2778.0      101.0   172.0
2  Acura         TSX 4dr  Sedan  INCORRECT CODE      Front  26990.0  24647.0         2.4        4.0       200.0      22.0         29.0  3230.0      105.0   183.0
3  Acura          TL 4dr  Sedan  INCORRECT CODE      Front  33195.0  30299.0         3.2        6.0       270.0      20.0         28.0  3575.0      108.0   186.0
4  Acura      3.5 RL 4dr  Sedan  INCORRECT CODE      Front  43755.0  39014.0         3.5        6.0       225.0      18.0         24.0  3880.0      115.0   197.0

[dacodevet]

Sorry. Yes.
SAS Connection terminated. Subprocess id was 2220
[Finished in 18.6s]

[tomweber-sas]

can you show the code you submit with all of the output (like I showed up above, so I can actually know what you're doing and what there really is)? To get that warning about finding ERROR in the log, the log has to be there or I couldn't issue that message.

[tomweber-sas]

are you running SAS with ERRORABEND System Option or something that causes it to terminate if there's an error? Even so, the log had to have come back to have seen an error in it to issue the warning in the first place, so the log should be there. Or do you have the log redirected somewhere, like with proc printto or something so that it can't be accessed by saspy? Or have everything turned off with nonotes, nosource, ... SAS options that would keep us from seeing anything useful?

Without actually seeing what you're really doing and what is really there I don't know what is happening.

I can ask again if you can do a teams meeting so you can show me what you're doing and I can see what is going on. I can set that up easily enough.

Tom

[dacodevet]

hey Tom, I am going to redesign my code. When I pull out this specific piece of code which is where I was running into issues, it runs fine.
import pandas as pd
import os
import glob
import saspy

Establish Sas session

sas = saspy.SASsession(cfgname='winlocal')
sas.saslib('aptcore', path='C:\Users\qha1\Downloads\used\SAS_data')
sdir = 'C:\Users\qha1\Downloads\used\SAS_data'
sas.saslib('aptcore', path=sdir)
sasf = os.path.join(sdir, '**', '*.sas7bdat')
sas.submitLOG('''options fmtsearch=(aptcore.A_Formats);''')
for filesas in glob.iglob(sasf,recursive=True):
bname = os.path.basename(filesas)
xname = os.path.splitext(bname)[0]
# sas.symput('dset', xname)
# sas.submitLST('proc print data=mmria.&dset. (obs=10); run;', results='text', method='listandlog')
# sas.submitLOG('''proc print data=mmria.&dset. (obs=10); run;''')
stdf = sas.sd2df(table=xname, libref='MMRIA')
print(sas.lastlog())
There is other code I have that extracts a zipfile and converts csv files to dataframes then calls multiple sas programs.

Sorry for all the trouble. Thanks for your attention.

[tomweber-sas]

Sounds like you didn't have the format library found and now you do. I'm looking at catching the failure in sd2df when it's not getting the info it needs (because of the error like that), and displaying the log explicitly. I'll try to get this into the next release, in all of the access methods. That would have helped you to begin with. Here's an example given the same simple example I ran up above, but handling the exception:

>>> import saspy; sas = saspy.SASsession(cfgname='iomj'); sas
SAS Connection established. Subprocess id is 9472

Access Method         = IOM
SAS Config name       = iomj
SAS Config file       = /opt/tom/github/saspy/saspy/sascfg_personal.py
WORK Path             = /sastmp/SAS_work643B000023E0_tom64-5/SAS_workAA26000023E0_tom64-5/
SAS Version           = 9.04.01M7D08052020
SASPy Version         = 4.2.0
Teach me SAS          = False
Batch                 = False
Results               = Pandas
SAS Session Encoding  = utf-8
Python Encoding value = utf_8
SAS process Pid value = 9184


>>> sas.submitLOG("""
...    libname x '.';
...    proc format library=x;
...       value  $city 'BR1'='Birmingham UK'
...                    'BR2'='Plymouth UK'
...                    'BR3'='York UK'
...                    'US1'='Denver USA'
...                    'US2'='Miami USA'
...                    other='INCORRECT CODE';
...    run;
... """)

7                                                          The SAS System                        Tuesday, April 19, 2022 11:29:00 AM

34         ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue;
34       ! ods graphics on / outputfmt=png;
NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1
35
36
37            libname x '.';
NOTE: Libref X was successfully assigned as follows:
      Engine:        V9
      Physical Name: /opt/tom/gitshit/notebooks
38            proc format library=x;
39               value  $city 'BR1'='Birmingham UK'
40                            'BR2'='Plymouth UK'
41                            'BR3'='York UK'
42                            'US1'='Denver USA'
43                            'US2'='Miami USA'
44                            other='INCORRECT CODE';
NOTE: Format $CITY has been written to X.FORMATS.
45            run;

NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


46
47
48
49         ods html5 (id=saspy_internal) close;ods listing;
50

8                                                          The SAS System                        Tuesday, April 19, 2022 11:29:00 AM

51
>>>
>>> sd = sas.sasdata('cars','sashelp', results='text')
>>> sd
Libref  = sashelp
Table   = cars
Dsopts  = {}
Results = text

>>> sd.head()

                                                           The SAS System                    Tuesday, April 19, 2022 11:29:00 AM   2

                                       Drive                   Engine                                 MPG_
 Obs Make  Model          Type  Origin Train     MSRP  Invoice  Size  Cylinders Horsepower MPG_City Highway Weight Wheelbase Length

   1 Acura MDX            SUV    Asia  All    $36,945  $33,337   3.5      6         265       17       23    4451     106      189
   2 Acura RSX Type S 2dr Sedan  Asia  Front  $23,820  $21,761   2.0      4         200       24       31    2778     101      172
   3 Acura TSX 4dr        Sedan  Asia  Front  $26,990  $24,647   2.4      4         200       22       29    3230     105      183
   4 Acura TL 4dr         Sedan  Asia  Front  $33,195  $30,299   3.2      6         270       20       28    3575     108      186
   5 Acura 3.5 RL 4dr     Sedan  Asia  Front  $43,755  $39,014   3.5      6         225       18       24    3880     115      197

>>> sd.dsopts={'format':{'origin':'$city.'}}
>>> sd.to_df()
Invalid output produced durring sasdata2dataframe step. Step failed.
Printing the error: invalid literal for int() with base 10: 'vn=VARNUMS= vl=VARLIST= vt=VARTYPE= i=1 _ERROR_=1 _N_=1'
Printing the SASLOG as diagnostic

21                                                         The SAS System                        Tuesday, April 19, 2022 11:29:00 AM

120
121        data work.sasdata2dataframe / view=work.sasdata2dataframe; set sashelp.'cars'n ;
122             format origin $city. ;;run;
                          ______
                          48

ERROR 48-59: The format $CITY was not found or could not be loaded.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


123        data _null_; file LOG; d = open('work.sasdata2dataframe');
124        length var $256;
125        lrecl = attrn(d, 'LRECL'); nvars = attrn(d, 'NVARS');
126        lr='LRECL='; vn='VARNUMS='; vl='VARLIST='; vt='VARTYPE=';
127        put lr lrecl; put vn nvars; put vl;
128        do i = 1 to nvars; var = compress(varname(d, i), '00'x); put var; end;
129        put vt;
130        do i = 1 to nvars; var = vartype(d, i); put var; end;
131        run;

NOTE: Argument 1 to function ATTRN(0,'LRECL') at line 125 column 9 is invalid.
NOTE: Argument 1 to function ATTRN(0,'NVARS') at line 125 column 36 is invalid.
LRECL= .
VARNUMS= .
VARLIST=
ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero,
       or invalid.
d=0 var=  lrecl=. nvars=. lr=LRECL= vn=VARNUMS= vl=VARLIST= vt=VARTYPE= i=1 _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to
      missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 125:9    1 at 125:36
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


132
133
134

22                                                         The SAS System                        Tuesday, April 19, 2022 11:29:00 AM

135
>>>

[tomweber-sas]

FYI, I just pushed the changes to catch and log failures in the intermediate steps of the sd2df() methods. That's where you were hitting your error. Those methods have to do a number of different SAS submits to gather metadata and stuff before then trying to transfer data. Your error was in one of those since SAS couldn't access the data set due to the format not being available. If you want to try this out (don't have to), you can pull the code from main (main branch in github) and give it a try with the case where you were getting the error. Should see something like what's above.

Thanks,
Tom

[dacodevet]

Thanks @tomweber-sas for working on this. I will try it out.

[dacodevet]

Hi @tomweber-sas, I finally got to the bottom of what was causing my issues and I'm not sure if I missed an option or some other configuration. In the sd2df method, It writes out the dataset variables, types, formats and builds varlist etc. In my case, there were blank lines and titles(The SAS System) being written in the middle of my variable list which in turn produces a SAS error resulting in:
_tom = vformatn(' 'n);put _tom;
_tom = vformatn(' The SAS System);put _tom; *longer than 32 bytes error
My work around was to partition on 'VARLIST=' and use a pipe separator to split on instead of \n. So now nvars aligns with the number of pipe delims whereas the \n split count had the extra lines.

  code  = "data work.sasdata2dataframe / view=work.sasdata2dataframe; set "+tabname+self._sb._dsopts(dsopts)+";run;\n"
  code += "data _null_; file LOG notitles; d = open('work.sasdata2dataframe');\n"
  code += "length var $256;\n"
  code += "lrecl = attrn(d, 'LRECL'); nvars = attrn(d, 'NVARS');\n"
  code += "lr='LRECL='; vn='VARNUMS='; vl='VARLIST='; vt='VARTYPE=';\n"
  code += "put lr lrecl; put vn nvars; put vl;\n"
###   code += "do i = 1 to nvars; var = compress(varname(d, i), '00'x);  put var '|'; end;\n"
  code += "put vt;\n"
  code += "do i = 1 to nvars; var = vartype(d, i); put var; end;\n"
  code += "run;"

  ll = self.submit(code, "text")
 
  l2 = ll['LOG'].rpartition("LRECL= ")
  l2 = l2[2].partition("\n")
  lrecl = int(l2[0])

  l2 = l2[2].partition("VARNUMS= ")
  l2 = l2[2].partition("\n")
  nvars = int(l2[0])

  ### l2 = l2[2].partition("VARLIST=")
  ### varlist = l2[2].split("|", nvars)

[tomweber-sas]

I think I get what you're saying. Can you send me the log so I can actually see what it did, and the output from proc options, so I can see what would be causing that? Having never run into this before, I'm wondering if it's something specific to your setup or something. I'd like to be able to reproduce it to determine the right way to handle it.
Thanks!
Tom

[dacodevet]

I will get the log for you but I'll have to change the code back to produce it, may be tomorrow. FYI, I duplicated that code, writing to a local text file and it produced the lists correctly without any of the line breaks. It seems to be something specific to the LOG file setup.

codex = "data work.sasdata2dataframe / view=work.sasdata2dataframe; set "+tabname+self._sb._dsopts(dsopts)+";run;\n"

codex += "data null; file ### my_local_file notitles; d = open('work.sasdata2dataframe');\n"

codex += "length var $256;\n"
codex += "lrecl = attrn(d, 'LRECL'); nvars = attrn(d, 'NVARS');\n"
codex += "lr='LRECL='; vn='VARNUMS='; vl='VARLIST='; vt='VARTYPE=';\n"
codex += "put lr lrecl; put vn nvars; put vl;\n"
codex += "do i = 1 to nvars; var = compress(varname(d, i), '00'x); put var '|'; end;\n"
codex += "put vt;\n"
codex += "do i = 1 to nvars; var = vartype(d, i); put var; end;\n"
codex += "run;"

ll = self.submit(codex, "text")