Writing SAS dates to SQL Server databse

Question

How to write SAS dates to Microsoft SQL Server 2016 Date data type in database?

I got SAS data with a sas date DataEndDay and I want to write that into a database. The following bit is in use (buffer is just to speed up the testing-failing) :

libname valu oledb provider=sqloledb schema="dbo" INSERTBUFF=100
        properties=("User ID"="&username." Password="&pw."
                    "data source" = &database. 
                    "initial catalog"=&catalog.);

proc sql noprint;
 insert into valu.Data_upload_from_me
(   <some_columns...>, 
   <more-columns...>
,DataEndDay
)

select 
<some_columns_source...>,
<more-columns_source...>
,DataEndDay 
from work.SAS_data_to_publish 
;quit;

Of course because SAS dates are numbers, direct writing is going to fail. What works is if I hard-code this as:

select 
<some_columns_source...>,
<more-columns_source...>
,'2018-12-12' 
from work.SAS_data_to_publish 
;quit;

But If I convert the SAS date to string in SAS datasteps:

data SAS_data_to_publish ; 
    set SAS_data_to_publish ;
     dataEndday0 = put(DataEndDay, yymmddd10.);
     DataEndDay1 = quote(dataEndday0, "'") ;
run;

and try to write either of these, I get conversion error:

ERROR: ICommand::Execute failed. : Conversion failed when converting date and/or time from character string.

When I select the string it looks pretty ok:

proc sql; select DataEndDay1 from SAS_data_to_publish; quit;

'2018-12-12'

previously I've managed to write dateTimes with similar trick, which works:

proc format;
    picture sjm
    . = .
    other='%Y-%0m-%0d %0H:%0M:%0S:000' (datatype=datetime)
;run;

data to_be_written; 
    set save.raw_data_to_be_written;
    DataEndDay0 = put(dhms(DataEndDay,0,0,0), sjm. -L);
run;

Anyone ran into similar issues? How could I write the dates? I could ask them to change the column to dateTime, maybe....

Thank you in advance.

Edit:

I managed to develop a work-around, which works but is ugly and -frankly- I don't like it. It so happens that my date is same for all rows, so I can assing it to macro variable and then use it in database writing.

data _NULL_; 
  set SAS_data_to_publish; 
  call symput('foobar', quote( put (DataEndDay , yymmddd10. -L), "'") ) ; 
run;

....
select 
  <some_columns_source...>,
  <more-columns_source...>
 ,&foobar.
from work.SAS_data_to_publish 
;quit;

Of course this would fail immediately should DataEndDay vary, but maybe demonstrates that something is off in Proc SQLs select clause....

Edit Edit Pasted the question to SAS forums

Answer 1

I finally managed to crack the issue. The issue was for the missing values. As I am passing the values as strings into the database the parser interpreted missing values as real dots instead of empty strings. The following works:

data upload; 
  set upload; 
  CreatedReportdate2 = PUT(CreatedReportdate , yymmddn8.);
run;

libname uplad_db odbc  noprompt = 
        "DRIVER=SQL Server;  server=&server.; Uid=&user.;Pwd=&pw.; DATABASE=&db.;" 
        INSERTBUFF=32767;

proc sql; 
  insert into uplad_db.upload_table 
  (.... ) 
 select 
  case when CreatedReportdate2 ='.'  then '' else CreatedReportdate2 end, 
  ... 
 from upload; 
quit;

Answer 2

SAS does not really properly support the SQL server DATE data type. I imagine this is due to the fact that it's newer, but for whatever reason you have to pass the data as strings.

For missing values, it's important to have a blank string, not a . character. The easiest workaround here is to set:

options missing=' ';

That will allow you to insert data properly. You can then return it to . if you wish. In a production application that might be used by others, I'd consider storing aside the option value temporarily then resetting to that, in order to do no harm.

Answer 3

Normally I just use PROC APPEND to insert observations into a remote database.

proc append base=valu.Data_upload_from_me force
   data=work.SAS_data_to_publish 
;
run;

Make sure your date variable in your SAS dataset use the same data type as the corresponding variable names in your target database table. So if your MS SQL database uses TIMESTAMP fields for date values then make sure your SAS dataset uses DATETIME values.

If you want to use constants then make sure to use SAS syntax in your SAS code and MS SQL syntax in any pass through code.

data test;
   date = '01JAN2017'd ;
   datetime = '01JAN2017:00:00'dt ;
run;

proc sql ;
  connect to oledb .... ;
  execute (  ... date = '2017-01-01' .... datetime='2017-01-01 00:00' ...)
  by oledb;
quit;