sourabh2404 commented 9 months ago

I am trying to read data from a sas dataset as -

data1 = sas.sd2df(table=table_name, libref=libref, dtype ='str'),

But I'm getting an error as - 'utf-8' codec can't decode byte 0xaa in position 203: invalid start byte.

I have tried using the following way also but still the error persists:-

try: data1 = sas.sd2df(table=table_name, libref=libref, dtype ='str') except UnicodeDecodeError: sas.encoding = 'iso-8859-1' data1 = sas.sd2df(table=table_name, libref=libref, dtype ='str')

tomweber-sas commented 9 months ago

Hey, I can help you with that. but I need information. Can you show me the code your running and the complete output along with displaying the output from submitting your SASsession object: print(sas). Can you also print the log after you get the error; print(sas.saslog()) And can you run a proc datasets on the table you're using so I can see info on it.

Thanks! Tom

sourabh2404 commented 9 months ago

Hi Tom,

I cannot print the SAS log since it is on a client server. Yes can I run a SAS code, if you want to see the dataset info.

Basically I'm trying to read a SAS dataset table using saspy in python, for most of the tables it is working, but for few tables I'm getting the error similar to 'utf-8' codec can't decode byte 0xaa in position 203: invalid start byte

My default encoding is utf-8

<

            iomj     = {'java': JavaPath,  
                'iomhost': Host,
                'iomport': Port,
                'encoding': 'utf-8',
               'omruser':OMRUserName,
                'omrpw':OMRPassword
            }
           sas = saspy.SASsession(**iomj)
            try:
                data1 = sas.sd2df(table=table_name, libref=libref,  dtype ='str')
            except UnicodeDecodeError:
                 sas.encoding = 'iso-8859-1'
                 data1 = sas.sd2df(table=table_name, libref=libref,  dtype ='str')

Some more error details are as follows:-

< Traceback (most recent call last): File "saspy\sasbase.py", line 1650, in sd2df File "saspy\sasbase.py", line 1832, in sasdata2dataframe File "saspy\sasioiom.py", line 1730, in sasdata2dataframe File "saspy\sasioiom.py", line 2156, in sasdata2dataframeDISK File "pandas\util_decorators.py", line 211, in wrapper File "pandas\util_decorators.py", line 331, in wrapper File "pandas\io\parsers\readers.py", line 950, in read_csv File "pandas\io\parsers\readers.py", line 611, in _read File "pandas\io\parsers\readers.py", line 1778, in read File "pandas\io\parsers\c_parser_wrapper.py", line 230, in read File "pandas_libs\parsers.pyx", line 808, in pandas._libs.parsers.TextReader.read_low_memory File "pandas_libs\parsers.pyx", line 866, in pandas._libs.parsers.TextReader._read_rows File "pandas_libs\parsers.pyx", line 852, in pandas._libs.parsers.TextReader._tokenize_rows File "pandas_libs\parsers.pyx", line 1965, in pandas._libs.parsers.raise_parser_error UnicodeDecodeError: 'utf-8' codec can't decode byte 0xaa in position 107393: invalid start byte

tomweber-sas commented 9 months ago

Can you run this and send the output

 iomj  = {'java': JavaPath,  
          'iomhost': Host,
          'iomport': Port,
          'encoding': 'utf-8',
          'omruser': OMRUserName,
          'omrpw': OMRPassword
         }
sas = saspy.SASsession(**iomj)
print(sas)
sd = sas.sasdata(table_name, libref)
sd.contents('text')
data1 = sas.sd2df(table=table_name, libref=libref,  dtype ='str')
print(sas.saslog())

tomweber-sas commented 9 months ago

And, just for sake of argument, can you run this and show the output. Can you tell me why you are trying to convert all data from the data set into strings in python?

data1 = sas.sd2df(table=table_name, libref=libref)
print(sas.lastlog())
print(data1.info())

Thanks! Tom

sourabh2404 commented 9 months ago

Hi Tom,

for the below code, please see the log details- ` sas = saspy.SASsession(**iomj) logging.info(f'SAS Connection Details {sas}') sasdata = sas.sasdata(table=table_name, libref=libref) sasdata.contents('text')
try: data1 = sas.sd2df(table=table_name, libref=libref, keep_default_na=False, dtype ='str') except UnicodeDecodeError:

sas.encoding = 'iso-8859-1'

             data1 = sas.sd2df(table=table_name, libref=libref, dsopts=options, keep_default_na=False)

logging.info(f'SAS Log Details {sas.saslog()}')`

Log Details-

`10/04/2023 10:14:05: working on SOURCE_TABLE_ABC_1 10/04/2023 10:14:11: SAS Connection Details Access Method = IOM SAS Config name = iomj SAS Config file = D:\MyCode\sascfg.pyc WORK Path = /saswork/prod/SAS_work7BEA00006853_uakurwasadd23/SAS_work676000006853_uakurwasadd23/ SAS Version = 9.04.01M6P11072018 SASPy Version = 5.0.2 Teach me SAS = False Batch = False Results = Pandas SAS Session Encoding = utf-8 Python Encoding value = utf-8 SAS process Pid value = 26707

10/04/2023 10:14:48: SAS Log Details 1 The SAS System Wednesday, October 4, 2023 10:14:00 AM

NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA. NOTE: SAS (r) Proprietary Software 9.4 (###########) Licensed to XXXXXXXXXXXXX NOTE: This session is executing on the Linux 3.10.0-1160.76.1.el7.x86_64 (LIN X64) platform.

NOTE: Analytical products:

  SAS/STAT 15.1
  SAS/ETS 15.1
  SAS/IML 15.1

NOTE: Additional host information:

Linux LIN X64 3.10.0-1160.76.1.el7.x86_64 #1 SMP Tue Jul 26 14:15:37 UTC 2022 x86_64 Red Hat Enterprise Linux Server release 7.9 (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: SAS Initialization used (Total process time): real time 0.00 seconds cpu time 0.00 seconds

NOTE: The autoexec file, /sasapp/config/Lev1/SASRWA/WorkspaceServer/autoexec.sas, was executed at server initialization. 1 ;';";/; 2 options svgtitle='svgtitle'; options validvarname=any validmemname=extend pagesize=max nosyntaxcheck; ods graphics on; 3
4 ;';";/; 5

2 The SAS System Wednesday, October 4, 2023 10:14:00 AM

6
7 %put E3969440A681A2408885998500000001; E3969440A681A2408885998500000001 8

3 The SAS System Wednesday, October 4, 2023 10:14:00 AM

9 ;';";*/; 10 data null; length x $ 4096; 11 x = resolve('%sysfunc(pathname(work))'); put 'WORKPATH=' x 'WORKPATHEND='; 12 x = resolve('&SYSENCODING'); put 'ENCODING=' x 'ENCODINGEND='; 13 x = resolve('&SYSVLONG4'); put 'SYSVLONG=' x 'SYSVLONGEND='; 14 x = resolve('&SYSJOBID'); put 'SYSJOBID=' x 'SYSJOBIDEND='; 15 x = resolve('&SYSSCP'); put 'SYSSCP=' x 'SYSSCPEND='; 16 run;

17
18
19 ;';";*/; 20

4 The SAS System Wednesday, October 4, 2023 10:14:00 AM

21
22 %put E3969440A681A2408885998500000002; E3969440A681A2408885998500000002 23

5 The SAS System Wednesday, October 4, 2023 10:14:00 AM

28
29 ;';";*/; 30

6 The SAS System Wednesday, October 4, 2023 10:14:00 AM

31
32 %put E3969440A681A2408885998500000003; E3969440A681A2408885998500000003 33

7 The SAS System Wednesday, October 4, 2023 10:14:00 AM

34 ;';";*/; 35 data null; e = exist("INPUT_LIBRARY.'SOURCE_TABLE_ABC'n"); 36 v = exist("INPUT_LIBRARY.'SOURCE_TABLE_ABC'n", 'VIEW'); 37 if e or v then e = 1; 38 te='TABLE_EXISTS='; put te e;run;

39
40
41 ;';";*/; 42

8 The SAS System Wednesday, October 4, 2023 10:14:00 AM

43
44 %put E3969440A681A2408885998500000004; E3969440A681A2408885998500000004 45

9 The SAS System Wednesday, October 4, 2023 10:14:00 AM

49
50 ;';";*/; 51 ods html5 (id=saspy_internal) close;ods listing; 52

10 The SAS System Wednesday, October 4, 2023 10:14:00 AM

53
54 %put E3969440A681A2408885998500000005; E3969440A681A2408885998500000005 55

11 The SAS System Wednesday, October 4, 2023 10:14:00 AM

56 ;';";*/; 57 data null; e = exist("work.'_variables'n"); 58 v = exist("work.'_variables'n", 'VIEW'); 59 if e or v then e = 1; 60 te='TABLE_EXISTS='; put te e;run;

61
62
63 ;';";*/; 64

12 The SAS System Wednesday, October 4, 2023 10:14:00 AM

65
66 %put E3969440A681A2408885998500000006; E3969440A681A2408885998500000006 67

13 The SAS System Wednesday, October 4, 2023 10:14:00 AM

68 ;';";*/; 69 data work.sasdata2dataframe / view=work.sasdata2dataframe; set work.'_variables'n ;run;

70 data null; file LOG; d = open('work.sasdata2dataframe'); 71 length var $256; 72 lrecl = attrn(d, 'LRECL'); nvars = attrn(d, 'NVARS'); 73 lr='LRECL='; vn='VARNUMS='; vl='VARLIST='; vt='VARTYPE='; 74 put lr lrecl; put vn nvars; put vl; 75 do i = 1 to nvars; var = compress(varname(d, i), '00'x); put var; end; 76 put vt; 77 do i = 1 to nvars; var = vartype(d, i); put var; end; 78 run;

LRECL= 334 VARNUMS= 8 VARLIST= Member Num Variable Type Len Pos Format Informat VARTYPE= C N C C N N C C NOTE: View WORK.SASDATA2DATAFRAME.VIEW used (Total process time): real time 0.00 seconds cpu time 0.00 seconds

79
80 ;';";*/; 81

14 The SAS System Wednesday, October 4, 2023 10:14:00 AM

82
83 %put E3969440A681A2408885998500000008; E3969440A681A2408885998500000008 84

15 The SAS System Wednesday, October 4, 2023 10:14:00 AM

85 ;';";*/; 86 proc delete data=work.sasdata2dataframe(memtype=view);run;

87 data work._n_u_ll;output;run;

88 data null; set work._n_u_ll work.'_variables'n ;put 'FMT_CATS='; 89 _tom = vformatn('Member'n);put _tom; 90 _tom = vformatn('Num'n);put _tom; 91 _tom = vformatn('Variable'n);put _tom; 92 _tom = vformatn('Type'n);put _tom; 93 _tom = vformatn('Len'n);put _tom; 94 _tom = vformatn('Pos'n);put _tom; 95 _tom = vformatn('Format'n);put _tom; 96 _tom = vformatn('Informat'n);put _tom; 97 stop; 98 run;

99 proc delete data=work._n_u_ll;run;

100
101 ;';";*/; 102

16 The SAS System Wednesday, October 4, 2023 10:14:00 AM

103
104 %put E3969440A681A2408885998500000009; E3969440A681A2408885998500000009 105

17 The SAS System Wednesday, October 4, 2023 10:14:00 AM

106 data null; set work.'_variables'n ; 107 format 'Num'n best32.; format 'Len'n best32.; format 'Pos'n best32.; 108 file _tomods1 lrecl=1048576 dlm='02'x recfm=v termstr=NL encoding='utf-8'; 109 'Member'n = translate('Member'n, '2020'x, '0102'x); if missing('Num'n) then 'Num'n = .; 'Variable'n = 109 ! translate('Variable'n, '2020'x, '0102'x); 'Type'n = translate('Type'n, '2020'x, '0102'x); if missing('Len'n) then 'Len'n 109 ! = .; if missing('Pos'n) then 'Pos'n = .; 'Format'n = translate('Format'n, '2020'x, '0102'x); 'Informat'n = 109 ! translate('Informat'n, '2020'x, '0102'x); 110 put 'Member'n 'Num'n 'Variable'n 'Type'n 'Len'n 'Pos'n 'Format'n 'Informat'n '01'x; 111 run;

NOTE: The file _TOMODS1 is: Filename=/saswork/prod/SAS_work7BEA00006853_uakurwasadd23/SAS_work676000006853_uakurwasadd23/_tomods1, Owner Name=userid1, Group Name=sas_grp, Access Permission=-rw-rw-r--, Last Modified=04Oct2023:10:14:11

112

18 The SAS System Wednesday, October 4, 2023 10:14:00 AM

113
114

19 The SAS System Wednesday, October 4, 2023 10:14:00 AM

115
116 %put E3969440A681A2408885998500000007; E3969440A681A2408885998500000007 117

20 The SAS System Wednesday, October 4, 2023 10:14:00 AM

118 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue; 118 ! ods graphics on / outputfmt=png; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 119 ;';";*/; 120 proc delete data=work._variables; run;

121
122 ;';";*/; 123 ods html5 (id=saspy_internal) close;ods listing; 124

21 The SAS System Wednesday, October 4, 2023 10:14:00 AM

125
126 %put E3969440A681A2408885998500000010; E3969440A681A2408885998500000010 127

22 The SAS System Wednesday, October 4, 2023 10:14:00 AM

128 ;';";*/; 129 data null; e = exist("INPUT_LIBRARY.'SOURCE_TABLE_ABC'n"); 130 v = exist("INPUT_LIBRARY.'SOURCE_TABLE_ABC'n", 'VIEW'); 131 if e or v then e = 1; 132 te='TABLE_EXISTS='; put te e;run;

133
134
135 ;';";*/; 136

23 The SAS System Wednesday, October 4, 2023 10:14:00 AM

137
138 %put E3969440A681A2408885998500000011; E3969440A681A2408885998500000011 139

24 The SAS System Wednesday, October 4, 2023 10:14:00 AM

143
144 ;';";*/; 145 ods html5 (id=saspy_internal) close;ods listing; 146

25 The SAS System Wednesday, October 4, 2023 10:14:00 AM

147
148 %put E3969440A681A2408885998500000012; E3969440A681A2408885998500000012 149

26 The SAS System Wednesday, October 4, 2023 10:14:00 AM

150 ;';";*/; 151 data null; e = exist("work.'_attributes'n"); 152 v = exist("work.'_attributes'n", 'VIEW'); 153 if e or v then e = 1; 154 te='TABLE_EXISTS='; put te e;run;

155
156
157 ;';";*/; 158

27 The SAS System Wednesday, October 4, 2023 10:14:00 AM

159
160 %put E3969440A681A2408885998500000013; E3969440A681A2408885998500000013 161

28 The SAS System Wednesday, October 4, 2023 10:14:00 AM

162 ;';";*/; 163 data null; e = exist("work.'_attributes'n"); 164 v = exist("work.'_attributes'n", 'VIEW'); 165 if e or v then e = 1; 166 te='TABLE_EXISTS='; put te e;run;

167
168
169 ;';";*/; 170

29 The SAS System Wednesday, October 4, 2023 10:14:00 AM

171
172 %put E3969440A681A2408885998500000014; E3969440A681A2408885998500000014 173

30 The SAS System Wednesday, October 4, 2023 10:14:00 AM

174 ;';";*/; 175 data work.sasdata2dataframe / view=work.sasdata2dataframe; set work.'_attributes'n ;run;

176 data null; file LOG; d = open('work.sasdata2dataframe'); 177 length var $256; 178 lrecl = attrn(d, 'LRECL'); nvars = attrn(d, 'NVARS'); 179 lr='LRECL='; vn='VARNUMS='; vl='VARLIST='; vt='VARTYPE='; 180 put lr lrecl; put vn nvars; put vl; 181 do i = 1 to nvars; var = compress(varname(d, i), '00'x); put var; end; 182 put vt; 183 do i = 1 to nvars; var = vartype(d, i); put var; end; 184 run;

LRECL= 374 VARNUMS= 7 VARLIST= Member Label1 cValue1 nValue1 Label2 cValue2 nValue2 VARTYPE= C C C N C C N NOTE: View WORK.SASDATA2DATAFRAME.VIEW used (Total process time): real time 0.00 seconds cpu time 0.00 seconds

185
186 ;';";*/; 187

31 The SAS System Wednesday, October 4, 2023 10:14:00 AM

188
189 %put E3969440A681A2408885998500000016; E3969440A681A2408885998500000016 190

32 The SAS System Wednesday, October 4, 2023 10:14:00 AM

191 ;';";*/; 192 proc delete data=work.sasdata2dataframe(memtype=view);run;

193 data work._n_u_ll;output;run;

194 data null; set work._n_u_ll work.'_attributes'n ;put 'FMT_CATS='; 195 _tom = vformatn('Member'n);put _tom; 196 _tom = vformatn('Label1'n);put _tom; 197 _tom = vformatn('cValue1'n);put _tom; 198 _tom = vformatn('nValue1'n);put _tom; 199 _tom = vformatn('Label2'n);put _tom; 200 _tom = vformatn('cValue2'n);put _tom; 201 _tom = vformatn('nValue2'n);put _tom; 202 stop; 203 run;

204 proc delete data=work._n_u_ll;run;

205
206 ;';";*/; 207

33 The SAS System Wednesday, October 4, 2023 10:14:00 AM

208
209 %put E3969440A681A2408885998500000017; E3969440A681A2408885998500000017 210

34 The SAS System Wednesday, October 4, 2023 10:14:00 AM

211 data null; set work.'_attributes'n ; 212 format 'nValue1'n best32.; format 'nValue2'n best32.; 213 file _tomods1 lrecl=1048576 dlm='02'x recfm=v termstr=NL encoding='utf-8'; 214 'Member'n = translate('Member'n, '2020'x, '0102'x); 'Label1'n = translate('Label1'n, '2020'x, '0102'x); 'cValue1'n = 214 ! translate('cValue1'n, '2020'x, '0102'x); if missing('nValue1'n) then 'nValue1'n = .; 'Label2'n = translate('Label2'n, 214 ! '2020'x, '0102'x); 'cValue2'n = translate('cValue2'n, '2020'x, '0102'x); if missing('nValue2'n) then 'nValue2'n = .; 215 put 'Member'n 'Label1'n 'cValue1'n 'nValue1'n 'Label2'n 'cValue2'n 'nValue2'n '01'x; 216 run;

NOTE: The file _TOMODS1 is: Filename=/saswork/prod/SAS_work7BEA00006853_uakurwasadd23/SAS_work676000006853_uakurwasadd23/_tomods1, Owner Name=userid1, Group Name=sas_grp, Access Permission=-rw-rw-r--, Last Modified=04Oct2023:10:14:12

217

35 The SAS System Wednesday, October 4, 2023 10:14:00 AM

218
219

36 The SAS System Wednesday, October 4, 2023 10:14:00 AM

220
221 %put E3969440A681A2408885998500000015; E3969440A681A2408885998500000015 222

37 The SAS System Wednesday, October 4, 2023 10:14:00 AM

223 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue; 223 ! ods graphics on / outputfmt=png; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 224 ;';";*/; 225 proc delete data=work._attributes; run;

226
227 ;';";*/; 228 ods html5 (id=saspy_internal) close;ods listing; 229

38 The SAS System Wednesday, October 4, 2023 10:14:00 AM

230
231 %put E3969440A681A2408885998500000018; E3969440A681A2408885998500000018 232

39 The SAS System Wednesday, October 4, 2023 10:14:00 AM

233 ;';";*/; 234 data null; e = exist("work.'_EngineHost'n"); 235 v = exist("work.'_EngineHost'n", 'VIEW'); 236 if e or v then e = 1; 237 te='TABLE_EXISTS='; put te e;run;

238
239
240 ;';";*/; 241

40 The SAS System Wednesday, October 4, 2023 10:14:00 AM

242
243 %put E3969440A681A2408885998500000019; E3969440A681A2408885998500000019 244

41 The SAS System Wednesday, October 4, 2023 10:14:00 AM

245 ;';";*/; 246 data null; e = exist("work.'_EngineHost'n"); 247 v = exist("work.'_EngineHost'n", 'VIEW'); 248 if e or v then e = 1; 249 te='TABLE_EXISTS='; put te e;run;

250
251
252 ;';";*/; 253

42 The SAS System Wednesday, October 4, 2023 10:14:00 AM

254
255 %put E3969440A681A2408885998500000020; E3969440A681A2408885998500000020 256

43 The SAS System Wednesday, October 4, 2023 10:14:00 AM

257 ;';";*/; 258 data work.sasdata2dataframe / view=work.sasdata2dataframe; set work.'_EngineHost'n ;run;

259 data null; file LOG; d = open('work.sasdata2dataframe'); 260 length var $256; 261 lrecl = attrn(d, 'LRECL'); nvars = attrn(d, 'NVARS'); 262 lr='LRECL='; vn='VARNUMS='; vl='VARLIST='; vt='VARTYPE='; 263 put lr lrecl; put vn nvars; put vl; 264 do i = 1 to nvars; var = compress(varname(d, i), '00'x); put var; end; 265 put vt; 266 do i = 1 to nvars; var = vartype(d, i); put var; end; 267 run;

LRECL= 361 VARNUMS= 4 VARLIST= Member Label1 cValue1 nValue1 VARTYPE= C C C N NOTE: View WORK.SASDATA2DATAFRAME.VIEW used (Total process time): real time 0.00 seconds cpu time 0.01 seconds

268
269 ;';";*/; 270

44 The SAS System Wednesday, October 4, 2023 10:14:00 AM

271
272 %put E3969440A681A2408885998500000022; E3969440A681A2408885998500000022 273

45 The SAS System Wednesday, October 4, 2023 10:14:00 AM

274 ;';";*/; 275 proc delete data=work.sasdata2dataframe(memtype=view);run;

276 data work._n_u_ll;output;run;

277 data null; set work._n_u_ll work.'_EngineHost'n ;put 'FMT_CATS='; 278 _tom = vformatn('Member'n);put _tom; 279 _tom = vformatn('Label1'n);put _tom; 280 _tom = vformatn('cValue1'n);put _tom; 281 _tom = vformatn('nValue1'n);put _tom; 282 stop; 283 run;

284 proc delete data=work._n_u_ll;run;

285
286 ;';";*/; 287

46 The SAS System Wednesday, October 4, 2023 10:14:00 AM

288
289 %put E3969440A681A2408885998500000023; E3969440A681A2408885998500000023 290

47 The SAS System Wednesday, October 4, 2023 10:14:00 AM

291 data null; set work.'_EngineHost'n ; 292 format 'nValue1'n best32.; 293 file _tomods1 lrecl=1048576 dlm='02'x recfm=v termstr=NL encoding='utf-8'; 294 'Member'n = translate('Member'n, '2020'x, '0102'x); 'Label1'n = translate('Label1'n, '2020'x, '0102'x); 'cValue1'n = 294 ! translate('cValue1'n, '2020'x, '0102'x); if missing('nValue1'n) then 'nValue1'n = .; 295 put 'Member'n 'Label1'n 'cValue1'n 'nValue1'n '01'x; 296 run;

NOTE: The file _TOMODS1 is: Filename=/saswork/prod/SAS_work7BEA00006853_uakurwasadd23/SAS_work676000006853_uakurwasadd23/_tomods1, Owner Name=userid1, Group Name=sas_grp, Access Permission=-rw-rw-r--, Last Modified=04Oct2023:10:14:13

297

48 The SAS System Wednesday, October 4, 2023 10:14:00 AM

298
299

49 The SAS System Wednesday, October 4, 2023 10:14:00 AM

300
301 %put E3969440A681A2408885998500000021; E3969440A681A2408885998500000021 302

50 The SAS System Wednesday, October 4, 2023 10:14:00 AM

303 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue; 303 ! ods graphics on / outputfmt=png; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 304 ;';";*/; 305 proc delete data=work._EngineHost; run;

306
307 ;';";*/; 308 ods html5 (id=saspy_internal) close;ods listing; 309

51 The SAS System Wednesday, October 4, 2023 10:14:00 AM

310
311 %put E3969440A681A2408885998500000024; E3969440A681A2408885998500000024 312

52 The SAS System Wednesday, October 4, 2023 10:14:00 AM

313 ;';";*/; 314 data null; e = exist("work.'_Variables'n"); 315 v = exist("work.'_Variables'n", 'VIEW'); 316 if e or v then e = 1; 317 te='TABLE_EXISTS='; put te e;run;

318
319
320 ;';";*/; 321

53 The SAS System Wednesday, October 4, 2023 10:14:00 AM

322
323 %put E3969440A681A2408885998500000025; E3969440A681A2408885998500000025 324

54 The SAS System Wednesday, October 4, 2023 10:14:00 AM

325 ;';";*/; 326 data null; e = exist("work.'_Variables'n"); 327 v = exist("work.'_Variables'n", 'VIEW'); 328 if e or v then e = 1; 329 te='TABLE_EXISTS='; put te e;run;

330
331
332 ;';";*/; 333

55 The SAS System Wednesday, October 4, 2023 10:14:00 AM

334
335 %put E3969440A681A2408885998500000026; E3969440A681A2408885998500000026 336

56 The SAS System Wednesday, October 4, 2023 10:14:00 AM

337 ;';";*/; 338 data work.sasdata2dataframe / view=work.sasdata2dataframe; set work.'_Variables'n ;run;

339 data null; file LOG; d = open('work.sasdata2dataframe'); 340 length var $256; 341 lrecl = attrn(d, 'LRECL'); nvars = attrn(d, 'NVARS'); 342 lr='LRECL='; vn='VARNUMS='; vl='VARLIST='; vt='VARTYPE='; 343 put lr lrecl; put vn nvars; put vl; 344 do i = 1 to nvars; var = compress(varname(d, i), '00'x); put var; end; 345 put vt; 346 do i = 1 to nvars; var = vartype(d, i); put var; end; 347 run;

LRECL= 334 VARNUMS= 8 VARLIST= Member Num Variable Type Len Pos Format Informat VARTYPE= C N C C N N C C NOTE: View WORK.SASDATA2DATAFRAME.VIEW used (Total process time): real time 0.00 seconds cpu time 0.01 seconds

348
349 ;';";*/; 350

57 The SAS System Wednesday, October 4, 2023 10:14:00 AM

351
352 %put E3969440A681A2408885998500000028; E3969440A681A2408885998500000028 353

58 The SAS System Wednesday, October 4, 2023 10:14:00 AM

354 ;';";*/; 355 proc delete data=work.sasdata2dataframe(memtype=view);run;

356 data work._n_u_ll;output;run;

357 data null; set work._n_u_ll work.'_Variables'n ;put 'FMT_CATS='; 358 _tom = vformatn('Member'n);put _tom; 359 _tom = vformatn('Num'n);put _tom; 360 _tom = vformatn('Variable'n);put _tom; 361 _tom = vformatn('Type'n);put _tom; 362 _tom = vformatn('Len'n);put _tom; 363 _tom = vformatn('Pos'n);put _tom; 364 _tom = vformatn('Format'n);put _tom; 365 _tom = vformatn('Informat'n);put _tom; 366 stop; 367 run;

368 proc delete data=work._n_u_ll;run;

369
370 ;';";*/; 371

59 The SAS System Wednesday, October 4, 2023 10:14:00 AM

372
373 %put E3969440A681A2408885998500000029; E3969440A681A2408885998500000029 374

60 The SAS System Wednesday, October 4, 2023 10:14:00 AM

375 data null; set work.'_Variables'n ; 376 format 'Num'n best32.; format 'Len'n best32.; format 'Pos'n best32.; 377 file _tomods1 lrecl=1048576 dlm='02'x recfm=v termstr=NL encoding='utf-8'; 378 'Member'n = translate('Member'n, '2020'x, '0102'x); if missing('Num'n) then 'Num'n = .; 'Variable'n = 378 ! translate('Variable'n, '2020'x, '0102'x); 'Type'n = translate('Type'n, '2020'x, '0102'x); if missing('Len'n) then 'Len'n 378 ! = .; if missing('Pos'n) then 'Pos'n = .; 'Format'n = translate('Format'n, '2020'x, '0102'x); 'Informat'n = 378 ! translate('Informat'n, '2020'x, '0102'x); 379 put 'Member'n 'Num'n 'Variable'n 'Type'n 'Len'n 'Pos'n 'Format'n 'Informat'n '01'x; 380 run;

NOTE: The file _TOMODS1 is: Filename=/saswork/prod/SAS_work7BEA00006853_uakurwasadd23/SAS_work676000006853_uakurwasadd23/_tomods1, Owner Name=userid1, Group Name=sas_grp, Access Permission=-rw-rw-r--, Last Modified=04Oct2023:10:14:13

381

61 The SAS System Wednesday, October 4, 2023 10:14:00 AM

382
383

62 The SAS System Wednesday, October 4, 2023 10:14:00 AM

384
385 %put E3969440A681A2408885998500000027; E3969440A681A2408885998500000027 386

63 The SAS System Wednesday, October 4, 2023 10:14:00 AM

387 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue; 387 ! ods graphics on / outputfmt=png; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 388 ;';";*/; 389 proc delete data=work._Variables; run;

390
391 ;';";*/; 392 ods html5 (id=saspy_internal) close;ods listing; 393

64 The SAS System Wednesday, October 4, 2023 10:14:00 AM

394
395 %put E3969440A681A2408885998500000030; E3969440A681A2408885998500000030 396

65 The SAS System Wednesday, October 4, 2023 10:14:00 AM

397 ;';";*/; 398 data null; e = exist("work.'_Sortedby'n"); 399 v = exist("work.'_Sortedby'n", 'VIEW'); 400 if e or v then e = 1; 401 te='TABLE_EXISTS='; put te e;run;

402
403
404 ;';";*/; 405

66 The SAS System Wednesday, October 4, 2023 10:14:00 AM

406
407 %put E3969440A681A2408885998500000031; E3969440A681A2408885998500000031 408

67 The SAS System Wednesday, October 4, 2023 10:14:00 AM

409 ;';";*/; 410 data null; e = exist("work.'_Sortedby'n"); 411 v = exist("work.'_Sortedby'n", 'VIEW'); 412 if e or v then e = 1; 413 te='TABLE_EXISTS='; put te e;run;

414
415
416 ;';";*/; 417

68 The SAS System Wednesday, October 4, 2023 10:14:00 AM

418
419 %put E3969440A681A2408885998500000032; E3969440A681A2408885998500000032 420

69 The SAS System Wednesday, October 4, 2023 10:14:00 AM

421 ;';";*/; 422 data work.sasdata2dataframe / view=work.sasdata2dataframe; set work.'_Sortedby'n ;run;

423 data null; file LOG; d = open('work.sasdata2dataframe'); 424 length var $256; 425 lrecl = attrn(d, 'LRECL'); nvars = attrn(d, 'NVARS'); 426 lr='LRECL='; vn='VARNUMS='; vl='VARLIST='; vt='VARTYPE='; 427 put lr lrecl; put vn nvars; put vl; 428 do i = 1 to nvars; var = compress(varname(d, i), '00'x); put var; end; 429 put vt; 430 do i = 1 to nvars; var = vartype(d, i); put var; end; 431 run;

LRECL= 926 VARNUMS= 4 VARLIST= Member Label1 cValue1 nValue1 VARTYPE= C C C N NOTE: View WORK.SASDATA2DATAFRAME.VIEW used (Total process time): real time 0.00 seconds cpu time 0.00 seconds

432
433 ;';";*/; 434

70 The SAS System Wednesday, October 4, 2023 10:14:00 AM

435
436 %put E3969440A681A2408885998500000034; E3969440A681A2408885998500000034 437

71 The SAS System Wednesday, October 4, 2023 10:14:00 AM

438 ;';";*/; 439 proc delete data=work.sasdata2dataframe(memtype=view);run;

440 data work._n_u_ll;output;run;

441 data null; set work._n_u_ll work.'_Sortedby'n ;put 'FMT_CATS='; 442 _tom = vformatn('Member'n);put _tom; 443 _tom = vformatn('Label1'n);put _tom; 444 _tom = vformatn('cValue1'n);put _tom; 445 _tom = vformatn('nValue1'n);put _tom; 446 stop; 447 run;

448 proc delete data=work._n_u_ll;run;

449
450 ;';";*/; 451

72 The SAS System Wednesday, October 4, 2023 10:14:00 AM

452
453 %put E3969440A681A2408885998500000035; E3969440A681A2408885998500000035 454

73 The SAS System Wednesday, October 4, 2023 10:14:00 AM

455 data null; set work.'_Sortedby'n ; 456 format 'nValue1'n best32.; 457 file _tomods1 lrecl=1048576 dlm='02'x recfm=v termstr=NL encoding='utf-8'; 458 'Member'n = translate('Member'n, '2020'x, '0102'x); 'Label1'n = translate('Label1'n, '2020'x, '0102'x); 'cValue1'n = 458 ! translate('cValue1'n, '2020'x, '0102'x); if missing('nValue1'n) then 'nValue1'n = .; 459 put 'Member'n 'Label1'n 'cValue1'n 'nValue1'n '01'x; 460 run;

NOTE: The file _TOMODS1 is: Filename=/saswork/prod/SAS_work7BEA00006853_uakurwasadd23/SAS_work676000006853_uakurwasadd23/_tomods1, Owner Name=userid1, Group Name=sas_grp, Access Permission=-rw-rw-r--, Last Modified=04Oct2023:10:14:14

461

74 The SAS System Wednesday, October 4, 2023 10:14:00 AM

462
463

75 The SAS System Wednesday, October 4, 2023 10:14:00 AM

464
465 %put E3969440A681A2408885998500000033; E3969440A681A2408885998500000033 466

76 The SAS System Wednesday, October 4, 2023 10:14:00 AM

467 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue; 467 ! ods graphics on / outputfmt=png; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 468 ;';";*/; 469 proc delete data=work._Sortedby; run;

470
471 ;';";*/; 472 ods html5 (id=saspy_internal) close;ods listing; 473

77 The SAS System Wednesday, October 4, 2023 10:14:00 AM

474
475 %put E3969440A681A2408885998500000036; E3969440A681A2408885998500000036 476

78 The SAS System Wednesday, October 4, 2023 10:14:00 AM

477 ;';";*/; 478 data null; e = exist("INPUT_LIBRARY.'SOURCE_TABLE_ABC'n"); 479 v = exist("INPUT_LIBRARY.'SOURCE_TABLE_ABC'n", 'VIEW'); 480 if e or v then e = 1; 481 te='TABLE_EXISTS='; put te e;run;

482
483
484 ;';";*/; 485

79 The SAS System Wednesday, October 4, 2023 10:14:00 AM

486
487 %put E3969440A681A2408885998500000037; E3969440A681A2408885998500000037 488

80 The SAS System Wednesday, October 4, 2023 10:14:00 AM

489 ;';";*/; 490 proc contents data=INPUT_LIBRARY.'SOURCE_TABLE_ABC'n ;run;

491
492 ;';";*/; 493

81 The SAS System Wednesday, October 4, 2023 10:14:00 AM

494
495 %put E3969440A681A2408885998500000038; E3969440A681A2408885998500000038 496

82 The SAS System Wednesday, October 4, 2023 10:14:00 AM

497 ;';";*/; 498 data null; e = exist("INPUT_LIBRARY.'SOURCE_TABLE_ABC'n"); 499 v = exist("INPUT_LIBRARY.'SOURCE_TABLE_ABC'n", 'VIEW'); 500 if e or v then e = 1; 501 te='TABLE_EXISTS='; put te e;run;

502
503
504 ;';";*/; 505

83 The SAS System Wednesday, October 4, 2023 10:14:00 AM

506
507 %put E3969440A681A2408885998500000039; E3969440A681A2408885998500000039 508

84 The SAS System Wednesday, October 4, 2023 10:14:00 AM

509 ;';";*/; 510 data work.sasdata2dataframe / view=work.sasdata2dataframe; set INPUT_LIBRARY.'SOURCE_TABLE_ABC'n (obs=1000001 firstobs=1 ) 510 ! ;run;

511 data null; file LOG; d = open('work.sasdata2dataframe'); 512 length var $256; 513 lrecl = attrn(d, 'LRECL'); nvars = attrn(d, 'NVARS'); 514 lr='LRECL='; vn='VARNUMS='; vl='VARLIST='; vt='VARTYPE='; 515 put lr lrecl; put vn nvars; put vl; 516 do i = 1 to nvars; var = compress(varname(d, i), '00'x); put var; end; 517 put vt; 518 do i = 1 to nvars; var = vartype(d, i); put var; end; 519 run;

LRECL= 627 VARNUMS= 90 VARLIST= WSID ITC ITSEQ ORGNO CUSTN CARDN TCRLMT LASTLIMIT INTROACNO INDC USER1 ASSGNC FDPLEDGE FDBRCH BILCYCL EXPIRYD USERDATA OVERLIMIT CRDSCORE CDFEED SMSA ACORNCDE DSACDE SMSANEW ACORNNEW DSANEW AGENTBANK RECOMI RECOMID RECOMD RECOMT RULESMSAI RULESMSAID RULESMSAD RULESMSAT FULLDOCD MASIND MASNEW AGE COURADR SCRNI SCRNID SCRND SCRNT REVWI REVWID REVWD REVWT CP LZCODE CENSUS CSBYPAS CSCNT CSRPT SCLS SCLSD SCDSC SCSYSD SCVAL DSR1 DSR2 GVD1 GVD2 SCSYSD1 OVRCHGID OVRCHGD OVRCHGT EXTCUST GSALARY OSALARY HNWI TASSET RPYMCCH RPYMNCCH TRPYMT AGINCOME MGINCOME APNET CRDTLMT APCRLMT TDEBT TLIQAST DSR TDEBTP TLIQASTP EXIST1 EXIST2 EXIST3 EXIST4 DATE_LOADED VARTYPE= N C N C N N N N N C N C N C N N N N N C N C C N N N N C C N N C C N N N C C N C C C N N C C N N C N N C C C C C C C N N N N N C C N N C N N C N N N N N N N N N N N N N N C C C C N NOTE: View WORK.SASDATA2DATAFRAME.VIEW used (Total process time): real time 0.00 seconds cpu time 0.00 seconds

520
521 ;';";*/; 522

85 The SAS System Wednesday, October 4, 2023 10:14:00 AM

523
524 %put E3969440A681A2408885998500000041; E3969440A681A2408885998500000041 525

86 The SAS System Wednesday, October 4, 2023 10:14:00 AM

526 ;';";*/; 527 proc delete data=work.sasdata2dataframe(memtype=view);run;

528 data work._n_u_ll;output;run;

529 data null; set work._n_u_ll INPUT_LIBRARY.'SOURCE_TABLE_ABC'n ;put 'FMT_CATS='; 530 _tom = vformatn('WSID'n);put _tom; 531 _tom = vformatn('ITC'n);put _tom; 532 _tom = vformatn('ITSEQ'n);put _tom; 533 _tom = vformatn('ORGNO'n);put _tom; 534 _tom = vformatn('CUSTN'n);put _tom; 535 _tom = vformatn('CARDN'n);put _tom; 536 _tom = vformatn('TCRLMT'n);put _tom; 537 _tom = vformatn('LASTLIMIT'n);put _tom; 538 _tom = vformatn('INTROACNO'n);put _tom; 539 _tom = vformatn('INDC'n);put _tom; 540 _tom = vformatn('USER1'n);put _tom; 541 _tom = vformatn('ASSGNC'n);put _tom; 542 _tom = vformatn('FDPLEDGE'n);put _tom; 543 _tom = vformatn('FDBRCH'n);put _tom; 544 _tom = vformatn('BILCYCL'n);put _tom; 545 _tom = vformatn('EXPIRYD'n);put _tom; 546 _tom = vformatn('USERDATA'n);put _tom; 547 _tom = vformatn('OVERLIMIT'n);put _tom; 548 _tom = vformatn('CRDSCORE'n);put _tom; 549 _tom = vformatn('CDFEED'n);put _tom; 550 _tom = vformatn('SMSA'n);put _tom; 551 _tom = vformatn('ACORNCDE'n);put _tom; 552 _tom = vformatn('DSACDE'n);put _tom; 553 _tom = vformatn('SMSANEW'n);put _tom; 554 _tom = vformatn('ACORNNEW'n);put _tom; 555 _tom = vformatn('DSANEW'n);put _tom; 556 _tom = vformatn('AGENTBANK'n);put _tom; 557 _tom = vformatn('RECOMI'n);put _tom; 558 _tom = vformatn('RECOMID'n);put _tom; 559 _tom = vformatn('RECOMD'n);put _tom; 560 _tom = vformatn('RECOMT'n);put _tom; 561 _tom = vformatn('RULESMSAI'n);put _tom; 562 _tom = vformatn('RULESMSAID'n);put _tom; 563 _tom = vformatn('RULESMSAD'n);put _tom; 564 _tom = vformatn('RULESMSAT'n);put _tom; 565 _tom = vformatn('FULLDOCD'n);put _tom; 566 _tom = vformatn('MASIND'n);put _tom; 567 _tom = vformatn('MASNEW'n);put _tom; 568 _tom = vformatn('AGE'n);put _tom; 569 _tom = vformatn('COURADR'n);put _tom; 570 _tom = vformatn('SCRNI'n);put _tom; 571 _tom = vformatn('SCRNID'n);put _tom; 572 _tom = vformatn('SCRND'n);put _tom; 573 _tom = vformatn('SCRNT'n);put _tom; 574 _tom = vformatn('REVWI'n);put _tom; 575 _tom = vformatn('REVWID'n);put _tom; 576 _tom = vformatn('REVWD'n);put _tom; 577 _tom = vformatn('REVWT'n);put _tom; 578 _tom = vformatn('CP'n);put _tom; 579 _tom = vformatn('LZCODE'n);put _tom; 580 _tom = vformatn('CENSUS'n);put _tom; 581 _tom = vformatn('CSBYPAS'n);put _tom; 582 _tom = vformatn('CSCNT'n);put _tom; 583 _tom = vformatn('CSRPT'n);put _tom; 584 _tom = vformatn('SCLS'n);put _tom; 585 _tom = vformatn('SCLSD'n);put _tom; 586 _tom = vformatn('SCDSC'n);put _tom; 587 _tom = vformatn('SCSYSD'n);put _tom; 588 _tom = vformatn('SCVAL'n);put _tom; 589 _tom = vformatn('DSR1'n);put _tom; 590 _tom = vformatn('DSR2'n);put _tom; 591 _tom = vformatn('GVD1'n);put _tom; 592 _tom = vformatn('GVD2'n);put _tom; 593 _tom = vformatn('SCSYSD1'n);put _tom; 594 _tom = vformatn('OVRCHGID'n);put _tom; 595 _tom = vformatn('OVRCHGD'n);put _tom; 596 _tom = vformatn('OVRCHGT'n);put _tom; 597 _tom = vformatn('EXTCUST'n);put _tom; 598 _tom = vformatn('GSALARY'n);put _tom; 599 _tom = vformatn('OSALARY'n);put _tom; 600 _tom = vformatn('HNWI'n);put _tom; 601 _tom = vformatn('TASSET'n);put _tom; 602 _tom = vformatn('RPYMCCH'n);put _tom; 603 _tom = vformatn('RPYMNCCH'n);put _tom; 604 _tom = vformatn('TRPYMT'n);put _tom; 605 _tom = vformatn('AGINCOME'n);put _tom; 606 _tom = vformatn('MGINCOME'n);put _tom; 607 _tom = vformatn('APNET'n);put _tom; 608 _tom = vformatn('CRDTLMT'n);put _tom; 609 _tom = vformatn('APCRLMT'n);put _tom; 610 _tom = vformatn('TDEBT'n);put _tom; 611 _tom = vformatn('TLIQAST'n);put _tom; 612 _tom = vformatn('DSR'n);put _tom; 613 _tom = vformatn('TDEBTP'n);put _tom; 614 _tom = vformatn('TLIQASTP'n);put _tom; 615 _tom = vformatn('EXIST1'n);put _tom; 616 _tom = vformatn('EXIST2'n);put _tom; 617 _tom = vformatn('EXIST3'n);put _tom; 618 _tom = vformatn('EXIST4'n);put _tom; 619 _tom = vformatn('DATE_LOADED'n);put _tom; 620 stop; 621 run;

622 proc delete data=work._n_u_ll;run;

623
624 ;';";*/; 625

87 The SAS System Wednesday, October 4, 2023 10:14:00 AM

626
627 %put E3969440A681A2408885998500000042; E3969440A681A2408885998500000042 628

88 The SAS System Wednesday, October 4, 2023 10:14:00 AM

629 data null; set INPUT_LIBRARY.'SOURCE_TABLE_ABC'n (obs=1000001 firstobs=1 ); 630 format 'WSID'n best32.; format 'ITSEQ'n best32.; format 'CUSTN'n best32.; format 'CARDN'n best32.; format 'TCRLMT'n 630 ! best32.; format 'LASTLIMIT'n best32.; format 'INTROACNO'n best32.; format 'USER1'n best32.; format 'FDPLEDGE'n best32.; 630 ! format 'BILCYCL'n best32.; format 'EXPIRYD'n best32.; format 'USERDATA'n best32.; format 'OVERLIMIT'n best32.; format 630 ! 'CRDSCORE'n best32.; format 'SMSA'n best32.; format 'SMSANEW'n best32.; format 'ACORNNEW'n best32.; format 'DSANEW'n 630 ! best32.; format 'AGENTBANK'n best32.; format 'RECOMD'n E8601DA10.; 631 format 'RECOMT'n E8601TM15.6; format 'RULESMSAD'n E8601DA10.; format 'RULESMSAT'n E8601TM15.6; format 'FULLDOCD'n 631 ! E8601DA10.; format 'AGE'n best32.; format 'SCRND'n E8601DA10.; format 'SCRNT'n E8601TM15.6; format 'REVWD'n E8601DA10.; 631 ! format 'REVWT'n E8601TM15.6; format 'LZCODE'n best32.; 632 format 'CENSUS'n best32.; format 'SCVAL'n best32.; format 'DSR1'n best32.; 633 format 'DSR2'n best32.; format 'GVD1'n best32.; format 'GVD2'n best32.; format 'OVRCHGD'n E8601DA10.; format 'OVRCHGT'n 633 ! E8601TM15.6; format 'GSALARY'n best32.; format 'OSALARY'n best32.; 634 format 'TASSET'n best32.; format 'RPYMCCH'n best32.; format 'RPYMNCCH'n best32.; format 'TRPYMT'n best32.; format 634 ! 'AGINCOME'n best32.; format 'MGINCOME'n best32.; format 'APNET'n best32.; format 'CRDTLMT'n best32.; format 'APCRLMT'n 634 ! best32.; 635 format 'TDEBT'n best32.; format 'TLIQAST'n best32.; format 'DSR'n best32.; format 'TDEBTP'n best32.; format 'TLIQASTP'n 635 ! best32.; format 'DATE_LOADED'n E8601DA10.; 636
637 file _tomods1 lrecl=1048576 dlm='02'x recfm=v termstr=NL encoding='utf-8'; 638 if missing('WSID'n) then 'WSID'n = .; 'ITC'n = translate('ITC'n, '2020'x, '0102'x); if missing('ITSEQ'n) then 'ITSEQ'n = 638 ! .; 'ORGNO'n = translate('ORGNO'n, '2020'x, '0102'x); if missing('CUSTN'n) then 'CUSTN'n = .; if missing('CARDN'n) then 638 ! 'CARDN'n = .; if missing('TCRLMT'n) then 'TCRLMT'n = .; if missing('LASTLIMIT'n) then 'LASTLIMIT'n = .; if 638 ! missing('INTROACNO'n) then 'INTROACNO'n = .; 'INDC'n = translate('INDC'n, '2020'x, '0102'x); 639 if missing('USER1'n) then 'USER1'n = .; 'ASSGNC'n = translate('ASSGNC'n, '2020'x, '0102'x); if missing('FDPLEDGE'n) then 639 ! 'FDPLEDGE'n = .; 'FDBRCH'n = translate('FDBRCH'n, '2020'x, '0102'x); if missing('BILCYCL'n) then 'BILCYCL'n = .; if 639 ! missing('EXPIRYD'n) then 'EXPIRYD'n = .; if missing('USERDATA'n) then 'USERDATA'n = .; if missing('OVERLIMIT'n) then 639 ! 'OVERLIMIT'n = .; if missing('CRDSCORE'n) then 'CRDSCORE'n = .; 'CDFEED'n = translate('CDFEED'n, '2020'x, '0102'x); 640 if missing('SMSA'n) then 'SMSA'n = .; 'ACORNCDE'n = translate('ACORNCDE'n, '2020'x, '0102'x); 'DSACDE'n = 640 ! translate('DSACDE'n, '2020'x, '0102'x); if missing('SMSANEW'n) then 'SMSANEW'n = .; if missing('ACORNNEW'n) then 640 ! 'ACORNNEW'n = .; if missing('DSANEW'n) then 'DSANEW'n = .; if missing('AGENTBANK'n) then 'AGENTBANK'n = .; 'RECOMI'n = 640 ! translate('RECOMI'n, '2020'x, '0102'x); 'RECOMID'n = translate('RECOMID'n, '2020'x, '0102'x); if missing('RECOMD'n) then 640 ! 'RECOMD'n = .; 641 if missing('RECOMT'n) then 'RECOMT'n = .; 'RULESMSAI'n = translate('RULESMSAI'n, '2020'x, '0102'x); 'RULESMSAID'n = 641 ! translate('RULESMSAID'n, '2020'x, '0102'x); if missing('RULESMSAD'n) then 'RULESMSAD'n = .; if missing('RULESMSAT'n) then 641 ! 'RULESMSAT'n = .; if missing('FULLDOCD'n) then 'FULLDOCD'n = .; 'MASIND'n = translate('MASIND'n, '2020'x, '0102'x); 641 ! 'MASNEW'n = translate('MASNEW'n, '2020'x, '0102'x); if missing('AGE'n) then 'AGE'n = .; 'COURADR'n = 641 ! translate('COURADR'n, '2020'x, '0102'x); 642 'SCRNI'n = translate('SCRNI'n, '2020'x, '0102'x); 'SCRNID'n = translate('SCRNID'n, '2020'x, '0102'x); if 642 ! missing('SCRND'n) then 'SCRND'n = .; if missing('SCRNT'n) then 'SCRNT'n = .; 'REVWI'n = translate('REVWI'n, '2020'x, 642 ! '0102'x); 'REVWID'n = translate('REVWID'n, '2020'x, '0102'x); if missing('REVWD'n) then 'REVWD'n = .; if 642 ! missing('REVWT'n) then 'REVWT'n = .; 'CP'n = translate('CP'n, '2020'x, '0102'x); if missing('LZCODE'n) then 'LZCODE'n = . 642 ! ; 643 if missing('CENSUS'n) then 'CENSUS'n = .; 'CSBYPAS'n = translate('CSBYPAS'n, '2020'x, '0102'x); 'CSCNT'n = 643 ! translate('CSCNT'n, '2020'x, '0102'x); 'CSRPT'n = translate('CSRPT'n, '2020'x, '0102'x); 'SCLS'n = translate('SCLS'n, 643 ! '2020'x, '0102'x); 'SCLSD'n = translate('SCLSD'n, '2020'x, '0102'x); 'SCDSC'n = translate('SCDSC'n, '2020'x, '0102'x); 643 ! 'SCSYSD'n = translate('SCSYSD'n, '2020'x, '0102'x); if missing('SCVAL'n) then 'SCVAL'n = .; if missing('DSR1'n) then 643 ! 'DSR1'n = .; 644 if missing('DSR2'n) then 'DSR2'n = .; if missing('GVD1'n) then 'GVD1'n = .; if missing('GVD2'n) then 'GVD2'n = .; 644 ! 'SCSYSD1'n = translate('SCSYSD1'n, '2020'x, '0102'x); 'OVRCHGID'n = translate('OVRCHGID'n, '2020'x, '0102'x); if 644 ! missing('OVRCHGD'n) then 'OVRCHGD'n = .; if missing('OVRCHGT'n) then 'OVRCHGT'n = .; 'EXTCUST'n = translate('EXTCUST'n, 644 ! '2020'x, '0102'x); if missing('GSALARY'n) then 'GSALARY'n = .; if missing('OSALARY'n) then 'OSALARY'n = .; 645 'HNWI'n = translate('HNWI'n, '2020'x, '0102'x); if missing('TASSET'n) then 'TASSET'n = .; if missing('RPYMCCH'n) then 645 ! 'RPYMCCH'n = .; if missing('RPYMNCCH'n) then 'RPYMNCCH'n = .; if missing('TRPYMT'n) then 'TRPYMT'n = .; if 645 ! missing('AGINCOME'n) then 'AGINCOME'n = .; if missing('MGINCOME'n) then 'MGINCOME'n = .; if missing('APNET'n) then 645 ! 'APNET'n = .; if missing('CRDTLMT'n) then 'CRDTLMT'n = .; if missing('APCRLMT'n) then 'APCRLMT'n = .; 646 if missing('TDEBT'n) then 'TDEBT'n = .; if missing('TLIQAST'n) then 'TLIQAST'n = .; if missing('DSR'n) then 'DSR'n = .; 646 ! if missing('TDEBTP'n) then 'TDEBTP'n = .; if missing('TLIQASTP'n) then 'TLIQASTP'n = .; 'EXIST1'n = translate('EXIST1'n, 646 ! '2020'x, '0102'x); 'EXIST2'n = translate('EXIST2'n, '2020'x, '0102'x); 'EXIST3'n = translate('EXIST3'n, '2020'x, '0102'x) 646 ! ; 'EXIST4'n = translate('EXIST4'n, '2020'x, '0102'x); if missing('DATE_LOADED'n) then 'DATE_LOADED'n = .; 647
648 put 'WSID'n 'ITC'n 'ITSEQ'n 'ORGNO'n 'CUSTN'n 'CARDN'n 'TCRLMT'n 'LASTLIMIT'n 'INTROACNO'n 'INDC'n 649 'USER1'n 'ASSGNC'n 'FDPLEDGE'n 'FDBRCH'n 'BILCYCL'n 'EXPIRYD'n 'USERDATA'n 'OVERLIMIT'n 'CRDSCORE'n 'CDFEED'n 650 'SMSA'n 'ACORNCDE'n 'DSACDE'n 'SMSANEW'n 'ACORNNEW'n 'DSANEW'n 'AGENTBANK'n 'RECOMI'n 'RECOMID'n 'RECOMD'n 651 'RECOMT'n 'RULESMSAI'n 'RULESMSAID'n 'RULESMSAD'n 'RULESMSAT'n 'FULLDOCD'n 'MASIND'n 'MASNEW'n 'AGE'n 651 ! 'COURADR'n 652 'SCRNI'n 'SCRNID'n 'SCRND'n 'SCRNT'n 'REVWI'n 'REVWID'n 'REVWD'n 'REVWT'n 'CP'n 'LZCODE'n 653 'CENSUS'n 'CSBYPAS'n 'CSCNT'n 'CSRPT'n 'SCLS'n 'SCLSD'n 'SCDSC'n 'SCSYSD'n 'SCVAL'n 'DSR1'n 654 'DSR2'n 'GVD1'n 'GVD2'n 'SCSYSD1'n 'OVRCHGID'n 'OVRCHGD'n 'OVRCHGT'n 'EXTCUST'n 'GSALARY'n 'OSALARY'n 655 'HNWI'n 'TASSET'n 'RPYMCCH'n 'RPYMNCCH'n 'TRPYMT'n 'AGINCOME'n 'MGINCOME'n 'APNET'n 'CRDTLMT'n 'APCRLMT'n 656 'TDEBT'n 'TLIQAST'n 'DSR'n 'TDEBTP'n 'TLIQASTP'n 'EXIST1'n 'EXIST2'n 'EXIST3'n 'EXIST4'n 'DATE_LOADED'n 657 '01'x; 658 run;

NOTE: The file _TOMODS1 is: Filename=/saswork/prod/SAS_work7BEA00006853_uakurwasadd23/SAS_work676000006853_uakurwasadd23/_tomods1, Owner Name=userid1, Group Name=sas_grp, Access Permission=-rw-rw-r--, Last Modified=04Oct2023:10:14:15

659

89 The SAS System Wednesday, October 4, 2023 10:14:00 AM

660
661

90 The SAS System Wednesday, October 4, 2023 10:14:00 AM

662
663 %put E3969440A681A2408885998500000040; E3969440A681A2408885998500000040 664

Error caught from the code-

Traceback (most recent call last): File "mycode.py", line 269, in init File "saspy\sasbase.py", line 1650, in sd2df File "saspy\sasbase.py", line 1832, in sasdata2dataframe File "saspy\sasioiom.py", line 1730, in sasdata2dataframe File "saspy\sasioiom.py", line 2156, in sasdata2dataframeDISK File "pandas\util_decorators.py", line 211, in wrapper File "pandas\util_decorators.py", line 331, in wrapper File "pandas\io\parsers\readers.py", line 950, in read_csv File "pandas\io\parsers\readers.py", line 611, in _read File "pandas\io\parsers\readers.py", line 1778, in read File "pandas\io\parsers\c_parser_wrapper.py", line 230, in read File "pandas_libs\parsers.pyx", line 808, in pandas._libs.parsers.TextReader.read_low_memory File "pandas_libs\parsers.pyx", line 866, in pandas._libs.parsers.TextReader._read_rows File "pandas_libs\parsers.pyx", line 852, in pandas._libs.parsers.TextReader._tokenize_rows File "pandas_libs\parsers.pyx", line 1965, in pandas._libs.parsers.raise_parser_error UnicodeDecodeError: 'utf-8' codec can't decode byte 0xaa in position 224910: invalid start byte

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "mycode.py", line 272, in init File "saspy\sasbase.py", line 1650, in sd2df File "saspy\sasbase.py", line 1832, in sasdata2dataframe File "saspy\sasioiom.py", line 1730, in sasdata2dataframe File "saspy\sasioiom.py", line 2156, in sasdata2dataframeDISK File "pandas\util_decorators.py", line 211, in wrapper File "pandas\util_decorators.py", line 331, in wrapper File "pandas\io\parsers\readers.py", line 950, in read_csv File "pandas\io\parsers\readers.py", line 611, in _read File "pandas\io\parsers\readers.py", line 1778, in read File "pandas\io\parsers\c_parser_wrapper.py", line 230, in read File "pandas_libs\parsers.pyx", line 808, in pandas._libs.parsers.TextReader.read_low_memory File "pandas_libs\parsers.pyx", line 866, in pandas._libs.parsers.TextReader._read_rows File "pandas_libs\parsers.pyx", line 852, in pandas._libs.parsers.TextReader._tokenize_rows File "pandas_libs\parsers.pyx", line 1965, in pandas._libs.parsers.raise_parser_error UnicodeDecodeError: 'utf-8' codec can't decode byte 0xaa in position 239665: invalid start byte

`

tomweber-sas commented 9 months ago

Hey @sourabh2404, thanks for that! That helps some. There's one thing I can't explain that isn't right, but I think I see enough to not worry about it. The sasdata.contents('text') didn't execute as that. It ran, looking at the log, using PANDAS as the results not TEXT. I find that suspicious, but I can't explain it, unless the output isn't exactly from running that exact code. But, I believe I see enough of that info from the code generated by the sd2df method, since I have to gather all of the metadata about the table in question to be able to generate the code to transfer it over correctly to allow Pandas to generate the data frame.

The other things I see now from the print(sas) output is that you have a recent, though not the latest, version of SASPy. And you are connecting to 9.4M6, which is fine and I see it's a Workspace server on linux. All good. I looked and I don't see any fixes or changes since the saspy version your using, up to the current version, that would explain this. So everything on your side looks good. Also, that the SAS session encoding is utf-8, same as Python, so no transcoding should be required on my end.

This leads me to believe that you have an (some) invalid characters in a (some) variable(s) in your SAS data set. Unlike other data stores (databases, Python in this case, ...) which validate character data, SAS doesn't internally enforce that the data you store in CHAR variable be valid character data for the encoding it's using. It simply allows a fixed number of bytes to be in a CHAR variable. It's only when you try to then transfer that data to a store which validates the characters that you run into these errors.

Do you know how the table in question came to be created? The most likely case is that data was imported from somewhere and some multibyte character was truncated, leaving an invalid character, which SAS didn't care about, but is causing an error exporting it to a system that does care.

Sorry, typed this in yesterday but got caught up w/ other things before hitting send. I was tying to find a way to validate data in the data set on the SAS side, but I'm not finding anything yet that's straight forward. But still looking into it. Tom

sourabh2404 commented 9 months ago

Hi Tom,

Thank you for your response. Is there a way where I can exclude or ignore this invalid character value while reading the data using SASPY? Or at least I can save the row which is giving the error somewhere so that I can have a look at the value which may be causing the error?

tomweber-sas commented 9 months ago

I finally found what I was hoping to find, and it appears to work! In the NLS doc there's a macro defined that can be used to validate characters in a SAS Data Set: %VALIDCHS

This macro can identify invalid chars in CHAR variables for a given encoding. I tested this out with a data set that I created using UTF-8 encoding and a mix of multi/single byte chars, which ended up truncated. SAS didn't complain that chars were truncated nor does it complain when trying to process the data (print it or read it otherwise). That's as I described above. Here's the test case I used for this. So, if you can use this macro to analyze the data set you're having this problem with, you may be able to edit/update it to fix the errors; the tool tells you which variable in which observation so you know where.

Create a data set w/ truncated multibyte (no error or warnings):

>>> sas
Access Method         = IOM
SAS Config name       = iomj
SAS Config file       = /opt/tom/github/saspy/saspy/sascfg_personal.py
WORK Path             = /sastmp/SAS_work3AD60003C39B_tom64-7/SAS_work7BB40003C39B_tom64-7/
SAS Version           = 9.04.01M8P01182023
SASPy Version         = 5.4.0
Teach me SAS          = False
Batch                 = False
Results               = Pandas
SAS Session Encoding  = utf-8
Python Encoding value = utf_8
SAS process Pid value = 246683

>>> sas.submitLOG('''
... data work.a; length a $20 b 8 c 8 d $20;
... input a b c d;
... cards;
... sdfsdf 32 45 srfwfrw
... .  32 45 sefsdf
... sdfsd . . .
... . . . .
... asdas . . sdfsd
... 引擎34引擎34引擎34 3 3 引擎34
... 引擎34 4 4 引擎34引擎34引擎34
... ;
... run;
... ''')

7                                                          The SAS System                    Wednesday, October  4, 2023 02:32:00 PM

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         data work.a; length a $20 b 8 c 8 d $20;
38         input a b c d;
39         cards;

NOTE: The data set WORK.A has 7 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.01 seconds

47         ;
48         run;
49

Try to use sd2df() on it and get a failure:

>>> df = sas.sd2df('a')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/opt/tom/github/saspy/saspy/sasbase.py", line 1684, in sd2df
    return self.sasdata2dataframe(table, libref, dsopts, method, **kwargs)
  File "/opt/tom/github/saspy/saspy/sasbase.py", line 1866, in sasdata2dataframe
    df = self._io.sasdata2dataframe(table, libref, dsopts, method=method, **kwargs)
  File "/opt/tom/github/saspy/saspy/sasioiom.py", line 1693, in sasdata2dataframe
    return self.sasdata2dataframeDISK(table, libref, dsopts, rowsep, colsep,
  File "/opt/tom/github/saspy/saspy/sasioiom.py", line 2121, in sasdata2dataframeDISK
    df = pd.read_csv(sockout, index_col=idx_col, engine=eng, header=None, names=dvarlist,
  File "/opt/tom/anaconda3/lib/python3.9/site-packages/pandas/util/_decorators.py", line 311, in wrapper
    return func(*args, **kwargs)
  File "/opt/tom/anaconda3/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 680, in read_csv
    return _read(filepath_or_buffer, kwds)
  File "/opt/tom/anaconda3/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 575, in _read
    parser = TextFileReader(filepath_or_buffer, **kwds)
  File "/opt/tom/anaconda3/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 933, in __init__
    self._engine = self._make_engine(f, self.engine)
  File "/opt/tom/anaconda3/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 1235, in _make_engine
    return mapping[engine](f, **self.options)
  File "/opt/tom/anaconda3/lib/python3.9/site-packages/pandas/io/parsers/c_parser_wrapper.py", line 75, in __init__
    self._reader = parsers.TextReader(src, **kwds)
  File "pandas/_libs/parsers.pyx", line 544, in pandas._libs.parsers.TextReader.__cinit__
  File "pandas/_libs/parsers.pyx", line 734, in pandas._libs.parsers.TextReader._get_header
  File "pandas/_libs/parsers.pyx", line 847, in pandas._libs.parsers.TextReader._tokenize_rows
  File "pandas/_libs/parsers.pyx", line 1952, in pandas._libs.parsers.raise_parser_error
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe6 in position 94: invalid continuation byte
>>>

Run SAS code to print it, and saspy to print it too (using 'text' results); they are the same; displaying invalid chars at the end dues to truncated data:

>>> sas.submitLST('''proc print data=a; proc print data=b; run;''')

                                                           The SAS System                Wednesday, October  4, 2023 02:32:00 PM   9

                                  Obs    a                        b     c    d

                                   1     sdfsdf                  32    45    srfwfrw
                                   2                             32    45    sefsdf
                                   3     sdfsd                    .     .
                                   4                              .     .
                                   5     asdas                    .     .    sdfsd
                                   6     引擎34引擎34引      3     3    引擎34
                                   7     引擎34                 4     4    引擎34引擎34引

>>> sd = sas.sasdata('a', results='text')
>>> sd.head(88)

                                                           The SAS System                Wednesday, October  4, 2023 02:32:00 PM   1

                                  Obs    a                        b     c    d

                                   1     sdfsdf                  32    45    srfwfrw
                                   2                             32    45    sefsdf
                                   3     sdfsd                    .     .
                                   4                              .     .
                                   5     asdas                    .     .    sdfsd
                                   6     引擎34引擎34引      3     3    引擎34
                                   7     引擎34                 4     4    引擎34引擎34引

>>> sd.contents()

                                                           The SAS System                Wednesday, October  4, 2023 02:32:00 PM   2

                                                       The CONTENTS Procedure

               Data Set Name        WORK.A                                                   Observations          7
               Member Type          DATA                                                     Variables             4
               Engine               V9                                                       Indexes               0
               Created              10/04/2023 14:37:12                                      Observation Length    56
               Last Modified        10/04/2023 14:37:12                                      Deleted Observations  0
               Protection                                                                    Compressed            NO
               Data Set Type                                                                 Sorted                NO
               Label
               Data Representation  SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64
               Encoding             utf-8  Unicode (UTF-8)

                                                 Engine/Host Dependent Information

              Data Set Page Size          65536
              Number of Data Set Pages    1
              First Data Page             1
              Max Obs per Page            1166
              Obs in First Data Page      7
              Number of Data Set Repairs  0
              Filename                    /sastmp/SAS_work3AD60003C39B_tom64-7/SAS_work7BB40003C39B_tom64-7/a.sas7bdat
              Release Created             9.0401M8
              Host Created                Linux
              Inode Number                2934902554
              Access Permission           rw-r--r--
              Owner Name                  sastpw
              File Size                   128KB
              File Size (bytes)           131072

                                             Alphabetic List of Variables and Attributes

                                                    #    Variable    Type    Len

                                                    1    a           Char     20
                                                    2    b           Num       8
                                                    3    c           Num       8
                                                    4    d           Char     20

Run the Macro to see if it can tell us where the bad chars are (it can , and does):

>>> sas.submitLST('''%VALIDCHS(dsnm=a);''')
/opt/tom/github/saspy/saspy/sasioiom.py:1041: 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")

50                                                         The SAS System                    Wednesday, October  4, 2023 02:32:00 PM

268
269        %VALIDCHS(dsnm=a);
WARNING: Found invalid character in the variable: a at the observation number:6.
WARNING: Found invalid character in the variable: d at the observation number:7.
ERROR: Detected the issue in the dataset: a (encoding: utf-8).
270
271

sourabh2404 commented 9 months ago

Thank you, Tom.

I may not be able to edit or update the data as it is in a client's environment. Is there a way through which I can load this data using SASPY where it ignores the invalid characters?

tomweber-sas commented 9 months ago

Yes, that answer would simply be to add encoding_errors='replace' (or whatever value you want; there's a few) to the sd2df() parameters. I pass extra kwargs (that I'm not explicitly supplying) to pandas read_csv. So that should allow it to continue. However, having tried that I see it has no effect. And I did verify that I passed it along to pandas. And I see why it's not solving the problem.

As it turns out, I'm the one reading the data being streamed from SAS and providing the read() method for pandas to call. This way SAS can be writing and Pandas reading the data concurrently; it was one of the last performance optimization reworkings I did. So, I need to actually specify errors='whatever' in my read() method when decoding the byte stream into charaters that pandas is using to read in the data. Unfortunately, there's not a way to get thet into that routine with me adding that. So, I'm going to add support for passing in errors= on sd2df() which I'll pass along to the decode() method in my read routine to allow you to specify what you want to do with transcoding errors: see decode()

I'll need to add this to all access methods and push it, doc it, test it, ... and then build a new release. I've prototyped it with my test case and it works, but that still means you have some corrupt data somewhere, and this doesn't fix that. It just lets you propagate invalid data and keep going. So be sure you understand that. You should still use the macro I provided above to see where your invalid data is and verify that with what you end up with in the dataframe.

While I work on that, can you run the macro and provide it's output so we can verify that's actually the problem with the data? I need to add this support either way, but since there is a way to assess your data, let's do that to see that it is your problem to begin with.

Here's the output with this fix, from the example dataset posted above:

>>> df = sas.sd2df('a')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/opt/tom/github/saspy/saspy/sasbase.py", line 1684, in sd2df
    return self.sasdata2dataframe(table, libref, dsopts, method, **kwargs)
  File "/opt/tom/github/saspy/saspy/sasbase.py", line 1866, in sasdata2dataframe
    df = self._io.sasdata2dataframe(table, libref, dsopts, method=method, **kwargs)
  File "/opt/tom/github/saspy/saspy/sasioiom.py", line 1693, in sasdata2dataframe
    return self.sasdata2dataframeDISK(table, libref, dsopts, rowsep, colsep,
  File "/opt/tom/github/saspy/saspy/sasioiom.py", line 2122, in sasdata2dataframeDISK
    df = pd.read_csv(sockout, index_col=idx_col, engine=eng, header=None, names=dvarlist,
  File "/opt/tom/anaconda3/lib/python3.9/site-packages/pandas/util/_decorators.py", line 311, in wrapper
    return func(*args, **kwargs)
  File "/opt/tom/anaconda3/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 680, in read_csv
    return _read(filepath_or_buffer, kwds)
  File "/opt/tom/anaconda3/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 575, in _read
    parser = TextFileReader(filepath_or_buffer, **kwds)
  File "/opt/tom/anaconda3/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 933, in __init__
    self._engine = self._make_engine(f, self.engine)
  File "/opt/tom/anaconda3/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 1235, in _make_engine
    return mapping[engine](f, **self.options)
  File "/opt/tom/anaconda3/lib/python3.9/site-packages/pandas/io/parsers/c_parser_wrapper.py", line 75, in __init__
    self._reader = parsers.TextReader(src, **kwds)
  File "pandas/_libs/parsers.pyx", line 544, in pandas._libs.parsers.TextReader.__cinit__
  File "pandas/_libs/parsers.pyx", line 734, in pandas._libs.parsers.TextReader._get_header
  File "pandas/_libs/parsers.pyx", line 847, in pandas._libs.parsers.TextReader._tokenize_rows
  File "pandas/_libs/parsers.pyx", line 1952, in pandas._libs.parsers.raise_parser_error
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe6 in position 94: invalid continuation byte
>>>
>>> df = sas.sd2df('a', errors='replace')
>>> df
            a     b     c           d
0      sdfsdf  32.0  45.0     srfwfrw
1         NaN  32.0  45.0      sefsdf
2       sdfsd   NaN   NaN         NaN
3         NaN   NaN   NaN         NaN
4       asdas   NaN   NaN       sdfsd
5  引擎34引擎34引�   3.0   3.0        引擎34
6        引擎34   4.0   4.0  引擎34引擎34引�
>>>
>>> df = sas.sd2df('a', errors='ignore')
>>> df
           a     b     c          d
0     sdfsdf  32.0  45.0    srfwfrw
1        NaN  32.0  45.0     sefsdf
2      sdfsd   NaN   NaN        NaN
3        NaN   NaN   NaN        NaN
4      asdas   NaN   NaN      sdfsd
5  引擎34引擎34引   3.0   3.0       引擎34
6       引擎34   4.0   4.0  引擎34引擎34引
>>>
>>> sas.submitLST('''proc print data=a; run;''', results='text')

                                                           The SAS System                 Thursday, October  5, 2023 02:06:00 PM   1

                                  Obs    a                        b     c    d

                                   1     sdfsdf                  32    45    srfwfrw
                                   2                             32    45    sefsdf
                                   3     sdfsd                    .     .
                                   4                              .     .
                                   5     asdas                    .     .    sdfsd
                                   6     引擎34引擎34引      3     3    引擎34
                                   7     引擎34                 4     4    引擎34引擎34引

tomweber-sas commented 9 months ago

I've pushed the fix to the main branch. Can you install from there to try it out? I'll get a new release built tomorrow so you'll be able to install the latest from PyPI then, but if you can test it out to see that it addresses your case, that'll be great. Also looking to see the output from the macro, assessing the data in the dataset.

Thanks. Tom

tomweber-sas commented 9 months ago

I've built this into the latest production release V5.4.1. So, you can pull that from PyPI, or conda-forge later today after it gets built there.

sourabh2404 commented 8 months ago

Hi Tom,

Thank you for the response. I will test the file with the latest release and will post the results.

sourabh2404 commented 8 months ago

Hi Tom,

after upgrading my saspy library, I'm getting an error in the below code:-

data1 = sas.sd2df(table=table_name, libref=libref, keep_default_na=False, errors='ignore')

Error:-

File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\saspy\sasbase.py", line 1686, in sd2df return self.sasdata2dataframe(table, libref, dsopts, method, kwargs) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\saspy\sasbase.py", line 1872, in sasdata2dataframe df = self._io.sasdata2dataframe(table, libref, dsopts, method=method, kwargs) File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\saspy\sasioiom.py", line 1693, in sasdata2dataframe return self.sasdata2dataframeDISK(table, libref, dsopts, rowsep, colsep, File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\site-packages\saspy\sasioiom.py", line 2122, in sasdata2dataframeDISK df = pd.read_csv(sockout, index_col=idx_col, engine=eng, header=None, names=dvarlist, TypeError: pandas.io.parsers.readers.read_csv() got multiple values for keyword argument 'keep_default_na'

Can you please advise.

tomweber-sas commented 8 months ago

yes, just remove keep_default_na=False, from your sd2df call. I'm providing that keyword to read_csv in this release.

tomweber-sas commented 8 months ago

Did you ever run the %VALIDCHS(dsnm=x); to see if/where the invalid char were in the data set? Did using errors= get the data transferred, even with the bad data, using the new version?

sourabh2404 commented 8 months ago

Hi Tom,

Apologies for the late response.

Yes, with the updated version now the data is able to load, and error is not appearing anymore.

tomweber-sas commented 8 months ago

That's great. Did you ever run that macro to see where the bad characters are? Remember, this doesn't fix any of that. Just let's you propagate them. Are you good with closing this issue then? Thanks! Tom

tomweber-sas commented 8 months ago

I'll close this. Let me know if you need anything else. Thanks! Tom

sassoftware / saspy

'utf-8' codec can't decode byte while reading data using sas sd2df #562

sas.encoding = 'iso-8859-1'