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;