Hi all,
I am taking many years of DBF data files via a SAS program and trying to stack it into a MS SQL server into one table. So I have numerous of date fields that I have the mandate to put into "datetime" format in MS SQL. I have all the data stacked in one set with the format DATETIME20. or DATETIME22. ...and both give me errors like this: "WARNING: Variable {my_field} has format DATETIME22. on the BASE data set and format DATETIME20. on the DATA data set. DATETIME22. used." < - - I don't understand what this means so I flip flopped the formats from datetime22. to datetime20. I still get the same basic error for the whole lot of my date vars.
proc sql;
create table my_data_set1 as
select
*,
dhms({field_1},0,0,0) as {field_1}n format=DATETIME20.,
dhms(input({field_2},mmddyy10.),0,0,0) as {field_2}n format=DATETIME20.,
/* etc */
from my_data_set;
quit;
/* or */
proc sql;
create table my_data_set1 as
select *, dhms({field_1},0,0,0) as {field_1}n format=DATETIME22., dhms(input({field_2},mmddyy10.),0,0,0) as {field_2}n format=DATETIME22.,
/* etc */
from my_data_set;
quit;
I drop the original fields later in my code and rename the {field}n back to {field}. I deleted all my data in MS SQL table (that previously loaded), modified the table to take the mandated date formats I checked all the proc contents and visually check the data and it looks like it all converted correctly per field. I am at a loss as to why it won't upload. Does anyone know what I am doing wrong? Is SAS more forgiving, do I have to check and make sure that each and every obs is within SQL's ‘datetime’ range? The dates should all be post Jan 1, 1900 - 2017 or so... but it is possible that a typo exists somewhere that I have not 100% vetted. TIA. -KJ
... View more
