Ok, there we go. And, ok, even if it 'seemed' to work (didn't error), that doesn't mean it's right. So, lets get it right! If you left off any of these extra parameters, it would load the table, and it still wouldn't be what you wanted.
Looking at what you've provided, there are only numerics and objects. That's perfectly reasonable. To import those to SAS is easy, numerics become floats and objects become characters (as @Tom mentioned, we only have those two types, and dates/times/datetimes are specific floats).
Clearly, col6 is a string representation of a date. But, it's not a date, it's just a character string (an object). So, to have this actually be a date (specific float value and a date format) in SAS we have to convert this column of the dataframe to a datetime64 type. That's the pandas type for date/time/datetime (which is really only a datetime). This is then where the datetimes= option comes in; when you have a pandas datetime64, but only want either the date or time part of it in SAS. So we will end up using datetimes={'col6' : 'date'} on df2sd, but not until we convert that pandas column to a datetime64 instead of an object. After that, I don't see any other cases that are an issue. Setting formats (correctly) are up to you as the rest are just numbers or strings; just specify valid format names.
So, how to convert col6 to a correct datetime64? In pandas, you can do this different ways, but here's one to try (if your dataframe was named df)
df['Col6'] = pd.to_datetime(df['Col6'])
I created a samlple df column 'n1' is like your Col6:
>>> rows = [[datetime.datetime(1965, 1, 1, 8, 0, 1), '15-Jul-19', 1.30, 'a'],
... [datetime.datetime(1966, 1, 1, 7, 0, 2), '15-Jul-19', 2.30, 'b'],
... [datetime.datetime(1967, 1, 1, 6, 0, 3), '15-Jul-19', 3.30, ' '],
... [datetime.datetime(1968, 1, 1, 5, 0, 4), '15-Jul-19', 4.30, ''],
... [None, '15-Jul-19', 5.0, 'b'],
... [None, None, None, 'b'],
... ]
>>> df = pd.DataFrame.from_records(rows, columns=['dt','n1','n2', 's1'])
>>> df
dt n1 n2 s1
0 1965-01-01 08:00:01 15-Jul-19 1.3 a
1 1966-01-01 07:00:02 15-Jul-19 2.3 b
2 1967-01-01 06:00:03 15-Jul-19 3.3
3 1968-01-01 05:00:04 15-Jul-19 4.3
4 NaT 15-Jul-19 5.0 b
5 NaT None NaN b
>>> df.dtypes
dt datetime64[ns]
n1 object
n2 float64
s1 object
dtype: object
>>>
As you can see n1 is just a string, but has dd-mmm-yyyy date in it that we read as a date.
I ran the code above and it was converted to the dateteime64 type. Now. since it's really only a date, you can use datetimes={'n2' : 'date'} on df2sd when loading this, along with outfmts={'n2' : ‘YYMMDD.’} or any valid SAS date format.
>>> df.dtypes; df; df['n1'] = pd.to_datetime(df['n1'])
dt datetime64[ns]
n1 object
n2 float64
s1 object
dtype: object
dt n1 n2 s1
0 1965-01-01 08:00:01 15-Jul-19 1.3 a
1 1966-01-01 07:00:02 15-Jul-19 2.3 b
2 1967-01-01 06:00:03 15-Jul-19 3.3
3 1968-01-01 05:00:04 15-Jul-19 4.3
4 NaT 15-Jul-19 5.0 b
5 NaT None NaN b
# after the conversion:
>>> df.dtypes; df
dt datetime64[ns]
n1 datetime64[ns]
n2 float64
s1 object
dtype: object
dt n1 n2 s1
0 1965-01-01 08:00:01 2019-07-15 1.3 a
1 1966-01-01 07:00:02 2019-07-15 2.3 b
2 1967-01-01 06:00:03 2019-07-15 3.3
3 1968-01-01 05:00:04 2019-07-15 4.3
4 NaT 2019-07-15 5.0 b
5 NaT NaT NaN b
>>>
So you see here that this is now, actually, a date (datetime) datatype and it will load into SAS as a datetime, unless you use datetimes= to say only the date or time part - which you will.
I think that's all you needed for this, was just to convert that date to a date type, then use the options when loading it into SAS.
BTW, use
print(sas.saslog()) to see the whole log and
SASdataobject.contents() to see what the dataset really is defined as (formats and data types and everything)
See how that works,
Tom
... View more
