Writing SAS dates to SQL Server databse

Pinegulf — Tue, 18 Dec 2018 13:04:59 GMT

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;

Re: Writing SAS dates to SQL Server databse

Ksharp — Tue, 18 Dec 2018 13:21:06 GMT

1) Does your variable 'DataEndDay' has format DATE9. ?

2)You could try make a new variable which is datetime variable.and insert it again.

new_date=dhms(DataEndDay,0,0,0);

format new_date datetime.;

Re: Writing SAS dates to SQL Server databse

Pinegulf — Tue, 18 Dec 2018 13:24:42 GMT

Tried with

insert into ...

<stuff>

select ...

,DataEndDay format=DATE9. informat=DATE9.

Did not work.

Edit: Besides DATA9. does not look like it would 'fit':

12DEC2018

topic Re: Writing SAS dates to SQL Server databse in New SAS User

Writing SAS dates to SQL Server databse

Re: Writing SAS dates to SQL Server databse

Re: Writing SAS dates to SQL Server databse