Writing SAS dates to SQL Server databse

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

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;

Advertisement

Answer