Possible issues when creating sas xport files from python
GitHub
https://tinyurl.com/2p8dzn7n
https://github.com/rogerjdeangelis/utl-possible-issues-when-creating-sas-xport-files-from-python
/*
/ | | | | _
_ | _
_ \ / _|
| | | |
__ \ || | | | | | | | | | | | (| | | | || |
|/_,|| || ||| || ||_,|| _, |
|___/
*/
Thre appears to be an issue with the V56 xport files created
by the Python xport package(and pyreadstat).
Platform: SAS 9.4M7 Python 3.9 and Win`0 64bit
Gregory Warnes R SASxport is rock solid and more flexible.
I suspect the same issue exits with pyreadstat. I could not import using either package.
Here is a comparison of the differences between a python xport file
and a sas created xport file with the same data
From _ Record 80 byte Card
........................XXX.....XXXXXXXX...................................XX..X
SAS 2 SAS SAS SASLIB 9.4 X64_10PR 30DEC21:09:48:27
PYTHON 2 SAS SAS SASLIB 30DEC21:09:36:25
........XXXXXXX.........XXX.....XXXXXXXX...................................XX..X
SAS 6 SAS SASDATA SASDATA 9.4 X64_10PR 30DEC21:09:48:27
PYTHON 6 SAS SASDATA 30DEC21:09:36:25
The python xport file can be fixed by slugging the SAS record in python records 2 and 6.
If I subsitute the SAS card for the python card
Here is some code that corrects the Python xport file
filename pyx "c:/temp/example.xpt" lrecl=80 recfm=f;
filename pyfix "c:/temp/examplefix.xpt" lrecl=80 recfm=f;
data null;
infile pyx;
input lyn $char80.;
select(n);
when(2) substr(lyn,1,40)='SAS SAS SASLIB 9.4 X64_10PR';
when(6) substr(lyn,1,40)='SAS SASDATA SASDATA 9.4 X64_10PR';
otherwise;
end;
file pyfix;
put lyn $char80.;
run;quit;
proc fslist file=pyfix;
run;quit;
/* _ _
__ _ _ __ __ | | _ ()_
/ |
\ / | | | | / __| / __| | (_| | | | | (_| | | |_| \__ \ \__ \ \__,_|_| |_|\__,_|_|\__, |___/_|___/ _ |___/ _ _ _ __| |_ __ ___ _ __ __| | _____ ___ __ _ __ _ _| |_| |__ ___ _ __ / _
| __/ _ \|
\ / |/ _ \ \ /\ / /
\ | _ \| | | | __|
_ \ / _ | `_
| (| | | | () | |) | (| | () \ V V /| | | | | |) | || | || | | | () | | | |
_,|| _/| ./ _,|_/ _/_/ || || | ./ _, |_|| ||_/|| ||
|| || |_/
*/
- create python v5 trasport file;
proc datasets lib=work kill;
run;quit;
%utlfkil(c:/temp/py_pgm.py);
%utlfkil(c:/temp/py_pgm.log);
%utlfkil(c:/temp/example.xpt);
filename ft15f001 "c:/temp/py_pgm.py";
parmcards4;
import xport.v56
import pandas as pd;
df = pd.DataFrame({
'ALPHA': ['A','B' , 'C'],
'BETA': ['x', 'y', 'z'],
})
ds = xport.Dataset(df)
with open('c:/temp/example.xpt', 'wb') as f:
xport.v56.dump(ds, f)
print(df)
;;;;
run;quit;
- EXECUTE THE PYTHON PROGRAM;
options noxwait noxsync;
filename rut pipe "c:\Python39\python.exe c:/temp/py_pgm.py 2> c:/temp/py_pgm.log";
run;quit;
data null;
file print;
infile rut;
input;
put infile;
putlog infile;
run;quit;
libname pyxpt xport "c:/temp/example.xpt";
proc contents data=xpt.all;
run;quit;
/*
Directory
Libref XPT
Engine XPORT
Physical Name c:\temp\example.xpt
Member Obs, Entries
Type or Indexes Vars Label
1 DATA 0 0
*/
proc datasets lib=work kill;
run;quit;
data pytest;
set pyxpt.sasdata;
run;quit;
proc datasets lib=work kill;
run;quit;
*ERROR: File PYXPT.SASDATA.DATA does not exist.;
filename pyfsl "c:/temp/sasxpt.xpt" lrecl=80 recfm=f;
proc fslist file=e9;
run;quit;
filename pyfsl clear;
/*
HEADER RECORDLIBRARY HEADER RECORD!!!!!!!000000000000000000000000000000
SAS SAS SASLIB 9.4 X64_10PR 30DEC21:10:21:15
30DEC21:10:21:15
HEADER RECORDMEMBER HEADER RECORD!!!!!!!000000000000000001600000000140
HEADER RECORDDSCRPTR HEADER RECORD!!!!!!!000000000000000000000000000000
SAS SASDATA SASDATA 9.4 X64_10PR 30DEC21:10:21:15
30DEC21:10:21:15
HEADER RECORDNAMESTR HEADER RECORD!!!!!!!000000000200000000000000000000
� � �ALPHA
� � �BETA
�
HEADER RECORD*******OBS HEADER RECORD!!!!!!!000000000000000000000000000000
AxByCz
*/
/* _ _ _
__ | | __ | | _ () ___ __ _ ___
/ | |/ _
| | | | / __| / __| / __|/ ` / __|
| (| | | (| | | || _ \ __ \ __ \ (| _
_,||_,||_, |/|/ |/_,|/
|_/
*/
proc datasets lib=work nodetails nolist kill;
run;quit;
- create an equaivalent xport file using sas;
libname sasxpt xport "c:/temp/sasxpt.xpt";
data xpt.sasdata;
ALPHA='A';BETA='x';output;
ALPHA='B';BETA='y';output;
ALPHA='C';BETA='z';output;
run;quit;
proc contents data=xpt.all;
run;quit;
/*
Data Set Name XPT.SASDATA Observations .
Member Type DATA Variables 2
Engine XPORT Indexes 0
Created 12/30/2021 10:21:15 Observation Length 2
Last Modified 12/30/2021 10:21:15 Deleted Observations 0
Protection Compressed NO
Data Set Type Sorted NO
Label
Data Representation Default
Encoding Default
Alphabetic List of Variables and Attributes
Variable Type Len
1 ALPHA Char 1
2 BETA Char 1
*/
proc print data=sasxpt.sasdata;
run;quit;
data sastst;
set sasxpt.sasdata;
run;quit;
/*
Up to 40 obs WORK.SASTST total obs=3 30DEC2021:10:22:59
Obs ALPHA BETA
1 A x
2 B y
3 C z
*/
libname sasxpt clear;
filename e9 "c:/temp/sasxpt.xpt" lrecl=80 recfm=f;
proc fslist file=e9;
run;quit;
/* SAS Xport file
HEADER RECORDLIBRARY HEADER RECORD!!!!!!!000000000000000000000000000000
SAS SAS SASLIB 9.4 X64_10PR 30DEC21:10:21:15
30DEC21:10:21:15
HEADER RECORDMEMBER HEADER RECORD!!!!!!!000000000000000001600000000140
HEADER RECORDDSCRPTR HEADER RECORD!!!!!!!000000000000000000000000000000
SAS SASDATA SASDATA 9.4 X64_10PR 30DEC21:10:21:15
30DEC21:10:21:15
HEADER RECORDNAMESTR HEADER RECORD!!!!!!!000000000200000000000000000000
� � �ALPHA
� � �BETA
�
HEADER RECORD*******OBS HEADER RECORD!!!!!!!000000000000000000000000000000
AxByCz
*/
/*__ _ _ _ _
/ ()_ __ _ __ _ | || |__ ___ _ __ __ ___ __ ___ _ | |
| || \ / / | _ \| | | | __|
\ / _ | _ \ \ \/ /
_ \ / _ | `| |
| | |> < | |) | || | || | | | () | | | | > <| |) | () | | | |
|| |/_/_\ | ./ _, |_|| ||_/|| || /_/_\ ./ _/|| _|
|| |/ ||
*/
filename sasx "c:/temp/sasxpt.xpt" lrecl=80 recfm=f;
data sasfyl;
infile sasx ;
input rec $char80.;
run;quit;
filename pyx "c:/temp/example.xpt.xpt" lrecl=80 recfm=f;
data pyfyl;
infile "c:/temp/example.xpt" lrecl=80 recfm=f;
input rec $char80.;
run;quit;
proc compare data=sasfyl compare=pyfyl outnoequal out=long outbase outcompare ;
run;quit;
TYPE OBS rec
........................XXX.....XXXXXXXX...................................XX..X
SAS 2 SAS SAS SASLIB 9.4 X64_10PR 30DEC21:09:48:27
PYTHON 2 SAS SAS SASLIB 30DEC21:09:36:25
........XXXXXXX.........XXX.....XXXXXXXX...................................XX..X
SAS 6 SAS SASDATA SASDATA 9.4 X64_10PR 30DEC21:09:48:27
PYTHON 6 SAS SASDATA 30DEC21:09:36:25
filename pyx "c:/temp/example.xpt" lrecl=80 recfm=f;
filename pyfix "c:/temp/examplefix.xpt" lrecl=80 recfm=f;
data null;
infile pyx;
input lyn $char80.;
select(n);
when(2) substr(lyn,1,40)='SAS SAS SASLIB 9.4 X64_10PR';
when(6) substr(lyn,1,40)='SAS SASDATA SASDATA 9.4 X64_10PR';
otherwise;
end;
file pyfix;
put lyn $char80.;
run;quit;
proc fslist file=pyfix;
run;quit;
libname pyok xport "c:/temp/examplefix.xpt" ;
proc contents data=pyok.all;
run;quit;
data finTst;
set pyok.sasdata;
run;quit;
ASCII Flatfile Ruler & Hex
utlrulr
c:/temp/examplefix.xpt
c:\temp\delete.txt
/*__ _ _ _
/ ()_ _____ | | __ ___ __ | |
| || \ / / _ / | \ \/ /
| __|
| | |> < __/ (| | > <| |) | |_
|| |/_/__|_,| /_/_\ ._/ _|
||
*/
--- Record Number --- 1 --- Record Length ---- 80
HEADER RECORD*******LIBRARY HEADER RECORD!!!!!!!000000000000000000000000000000
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
44444525444542222222444545524444452544454222222233333333333333333333333333333322
8514520253F24AAAAAAAC92212908514520253F24111111100000000000000000000000000000000
--- Record Number --- 2 --- Record Length ---- 80
SAS SAS SASLIB 9.4 X64_10PR 30DEC21:09:36:25
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
54522222545222225454442232322222533533552222222222222222222222223344433333333333
3130000031300000313C92009E400000864F10020000000000000000000000003045321A09A36A25
--- Record Number --- 3 --- Record Length ---- 80
30DEC21:09:36:25
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
33444333333333332222222222222222222222222222222222222222222222222222222222222222
3045321A09A36A250000000000000000000000000000000000000000000000000000000000000000
--- Record Number --- 4 --- Record Length ---- 80
HEADER RECORD*******MEMBER HEADER RECORD!!!!!!!000000000000000001600000000140
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
44444525444542222222444445224444452544454222222233333333333333333333333333333322
8514520253F24AAAAAAAD5D252008514520253F24111111100000000000000000160000000014000
--- Record Number --- 5 --- Record Length ---- 80
HEADER RECORD*******DSCRPTR HEADER RECORD!!!!!!!000000000000000000000000000000
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
44444525444542222222454555524444452544454222222233333333333333333333333333333322
8514520253F24AAAAAAA433204208514520253F24111111100000000000000000000000000000000
--- Record Number --- 6 --- Record Length ---- 80
SAS SASDATA SASDATA 9.4 X64_10PR 30DEC21:09:36:25
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
54522222545445425454454232322222533533552222222222222222222222223344433333333333
3130000031341410313414109E400000864F10020000000000000000000000003045321A09A36A25
--- Record Number --- 7 --- Record Length ---- 80
30DEC21:09:36:25
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
33444333333333332222222222222222222222222222222222222222222222222222222222222222
3045321A09A36A250000000000000000000000000000000000000000000000000000000000000000
--- Record Number --- 8 --- Record Length ---- 80
HEADER RECORD*******NAMESTR HEADER RECORD!!!!!!!000000000200000000000000000000
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
44444525444542222222444455524444452544454222222233333333333333333333333333333322
8514520253F24AAAAAAAE1D534208514520253F24111111100000000020000000000000000000000
--- Record Number --- 9 --- Record Length ---- 80
........ALPHA ........
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
00000000445442222222222222222222222222222222222222222222222222220000000022222222
020001011C0810000000000000000000000000000000000000000000000000000000000000000000
--- Record Number --- 10 --- Record Length ---- 80
....................................................................BETA
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
00000000000000000000000000000000000000000000000000000000000000000000445422222222
00000000000000000000000000000000000000000000000000000000000002000102254100000000
--- Record Number --- 11 --- Record Length ---- 80
........ ....................
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
22222222222222222222222222222222222222222222000000002222222200000000000000000000
00000000000000000000000000000000000000000000000000000000000000000001000000000000
--- Record Number --- 12 --- Record Length ---- 80
........................................
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
00000000000000000000000000000000000000002222222222222222222222222222222222222222
00000000000000000000000000000000000000000000000000000000000000000000000000000000
--- Record Number --- 13 --- Record Length ---- 80
HEADER RECORD*******OBS HEADER RECORD!!!!!!!000000000000000000000000000000
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
44444525444542222222445222224444452544454222222233333333333333333333333333333322
8514520253F24AAAAAAAF23000008514520253F24111111100000000000000000000000000000000
--- Record Number --- 14 --- Record Length ---- 80
AxByCz
1...5....10...15...20...25...30...35...40...45...50...55...60...65...70...75...8
47474722222222222222222222222222222222222222222222222222222222222222222222222222
18293A00000000000000000000000000000000000000000000000000000000000000000000000000