DATA Step, Macro, Functions and more

Up load SAS data set to MS SQL, Datetime issues...

Reply
Frequent Contributor
Posts: 90

Up load SAS data set to MS SQL, Datetime issues...

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

PROC Star
Posts: 1,760

Re: Up load SAS data set to MS SQL, Datetime issues...

Posted in reply to kjohnsonm

This looks like a warning (not an error) from proc append. You can ignore it.

This just means that the different tables you are appending do not write the datetime in the same manner.

MS SQL will write then in its own way, so it doesn't matter.

To avoid the message, use the same format eveywhere. I dont know why you used DATETIME20 at times and DATETIME22 at other times.

 

 

 

Super User
Posts: 11,343

Re: Up load SAS data set to MS SQL, Datetime issues...

Posted in reply to kjohnsonm

First, that is a WARNING, not an ERROR. Those messages generally are to remind that you might get unexpected results in some situations. For instance with a variable in datetime19.2 format in SAS may actually have significant fractions of a second and if you append that to data in datetime16 format that has no second fractions then if the original format of datetime16 is used then all the values from the appended data would not show the fractional seconds. So SAS is being helpful to preserve showing all the value but tells you it did.

 

You should look at the actual values when assigning formats. if you are not displaying fractional seconds then the datetime20 is going to display with leading spaces anyway. It works best to have the SAME format for both files.

 

You don't show any code that looks like it is uploading to MS SQL. Please show that code and log results, especially if there are errors or notes about zero records uploaded.

Frequent Contributor
Posts: 90

Re: Up load SAS data set to MS SQL, Datetime issues...

I have not changed the upload macro in a month or more, I am not sure why it would bomb now...

 

libname UDmy_lib odbc dsn=UDxxxxxx schema = dbo user=xxxx password=xxxxxxx;

%macro update_my_db(my_table,mdataset);
data UDmy_lib.&my_table.s;
set &mdataset.;
run;

proc sql;
delete * from UDmy_lib.&my_table.
/*where (snapshot=&snapshot. or snapshot='precen') and strm="&strm.";*/
quit;

proc datasets library = UDmy_lib ;
append base = &my_table. data = &my_table.s; 
delete &my_table.s;
run;quit;

%mend update_my_db;

%update_my_db(COMBO,COMBO)

and the error points at the warnings:   ERROR: No appending done because of anomalies listed above. Use FORCE option to append these files.

Frequent Contributor
Posts: 90

Re: Up load SAS data set to MS SQL, Datetime issues...

Posted in reply to kjohnsonm
sorry for not being complete before with my post.
Super User
Posts: 19,855

Re: Up load SAS data set to MS SQL, Datetime issues...

Posted in reply to kjohnsonm

Something must have changed in your process. Are you dealing with Excel files at any point? 

 

 

Frequent Contributor
Posts: 90

Re: Up load SAS data set to MS SQL, Datetime issues...

[ Edited ]

...in this case no excel.  [And yes I am changing the date to a required "datetime" format in SQL and I just had most all of 14 defined as whatever the SAS to SQL hand off did by default from the base DBF files.   some were varch, some were different versions of mmddyy#.   it was a mixed bag by default.   That is why I gave the history of this data, I have worked here about a year, the datasets are some 30 years old and we are going back and trying to make a data warehouse out of it...]

PROC Star
Posts: 1,760

Re: Up load SAS data set to MS SQL, Datetime issues...

Posted in reply to kjohnsonm

Use the force option if you won't change the formats. The data is still valid.

Frequent Contributor
Posts: 90

Re: Up load SAS data set to MS SQL, Datetime issues...

Well first sorry for the delay, I was out on vacation and thus could not provide feedback. I guess the issue was my interpretation of the error and warning messages, and the volume. I received the warning about datetime20. Datetime22. Like 15 times, and a final line saying it was erroring out because of the conditions above. That does not seem very helpful to me and since I had not changed the upload macro only data types I thought SAS and MS SQL would just someone auto negotiate the formatting. Where the real error was is the fact that I had missed one change on the MS SQL date format side and fixed it on the SAS side. Plus a few were set to no nulls on SQL server. If SAS would bring back more of the full error on the linked lib source I do not think I would have had this problem, however it’s a lot of work for SAS to program and trap all logs for all systems there product can connect too thus do not expect much if any improvement here… I will just have to be more diligent
Frequent Contributor
Posts: 90

Re: Up load SAS data set to MS SQL, Datetime issues...

Posted in reply to kjohnsonm
PS when i write datetime20. or datetime22. I did not mean at the same run time, I meant neither one fixed the issue on separate code exe
Ask a Question
Discussion stats
  • 9 replies
  • 537 views
  • 1 like
  • 4 in conversation