SAS ensuring consistency between csv export

Question

For a while I used manual export in SAS guide (-> Export then choose csv instead of sasbdat) to export my database. Now I am trying to export it automatically. I sligthly modified the base SAS example to perform the export :

proc export data=work.data_test
     outfile="c:\myfiles\test.csv"
     dbms=csv 
     replace;
run;

But this is causing some type error when I try to read the file in python (Columns (1,2,5,6,9,[...]) have mixed types), while I don't get this error when I export the file manually.

Those options:

 Options NOFMTERR;
 OPTIONS LOCALE=FRENCH;

are used to match the rest of my SAS code.

Any idea how to ensure consistence between the proc export and a manual export in SAS ?

Most of us won't have a SAS installation to reproduce your export, and since your question is about using Python to import the files, it would be sensible to add small samples of the format that works, and the format that doesn't, to your question. Whether the data comes from SAS or not is really not relevant to solving your Python problem. — BoarGules
How is python determining the "type" of the text in the CSV file? For example is it just assuming if a field in the CSV has the optional quotes around it that it is "character" even if the content is only digits? Or if some of the numbers in a column are exact integers and so do not have a decimal point in the CSV file that the type is integer instead of real? Please show examples of the difference in the text in the two different CSV files. — Tom
@BoarGules: I am on the path of dealing with this 'properly' with a datatypes dictionnary for reading data in Python. However It's not just a Python data reading problem, i'd like to know how to avoid that SAS export incoherence in the first place. — lcrmorin
@Tom: It appears that some columns of the csv files both contain number and number as strings. Id'ont have this problem with a manual export. — lcrmorin
Everything in a CSV file is strings so I don't understand "number as strings" comment. Please see if you can isolate the issue with just a few columns and rows and post both versions of the CSV file. — Tom

Reeza Reeza · Accepted Answer · 2021-02-25T18:18:05

There are multiple ways to export a file and the GUI uses a different method than PROC EXPORT.

I think it used to be ODS CSV that did the export but I'm not 100% sure. I'd probably try using ODS CSV and a data step instead and see if either work for you.

ods csv file='path to csv file';
ods noptitle;title;
proc print data=......;
run;

ods csv close;

Different methods handle formats and types differently, ie PROC EXPORT didn't use to export formats to Excel at one time, whereas ODS EXCEL will export formatted values to Excel.

Or try a manual data step. Rather than write this manually, after running PROC EXPORT check your log and copy paste the code form there into your program. You can then adjust the formats, type and length for a variable if desired.

Or you could try using saspy to read the SAS data set directly rather than using an intermediate data transfer.

SAS ensuring consistency between csv export

1 Answers