So I ran into an issue when writing SAS dates to MS SQL database. (original question @Stacoverflow: stackoverflow.com/questions/53781236) How to write SAS dates to Microsoft SQL Server 2016 Date data type in database? The version I'm running is: %put &sysvlong.; 9.04.01M2P072314 %put &SYSVLONG4.; 9.04.01M2P07232014 And SAS/ACCESS 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;
... View more
