将SAS日期写入SQL Server数据库 [英] Writing SAS dates to SQL Server databse

问题描述

如何将SAS日期写入Microsoft SQL Server 2016 Date 数据库中的数据类型？

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

我有SAS日期为 DataEndDay 的数据，我想将其写入数据库。正在使用以下位（缓冲区只是为了加快测试失败的速度）：

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;

当然，因为SAS日期是数字，所以直接写入将失败。如果我将其硬编码为：

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;

但是如果我将SAS日期转换为SAS数据步骤中的字符串：

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:

错误：ICommand :: Execute失败。 ：从字符串转换日期和/或时间时转换失败。

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'

'2018-12-12'

以前，我已经设法用类似的技巧写了dateTimes，它的工作原理是：

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;

有人遇到类似的问题吗？如何写日期？
我可以要求他们将列更改为dateTime，也许....

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

谢谢。

编辑：

我设法开发了一种可行的解决方法但很丑陋-坦率地说-我不喜欢它。碰巧我的日期在所有行中都是相同的，因此我可以将其关联到宏变量，然后在数据库编写中使用它。

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;

当然，如果 DataEndDay 有所不同，但可能表明在 Proc SQL 的select子句中有问题...。

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

编辑编辑将问题粘贴到 SAS论坛

推荐答案

我终于设法解决了这个问题。问题是缺少值。当我将值作为字符串传递到数据库时，解析器将缺少的值解释为实点而不是空字符串。可以进行以下工作：

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;

这篇关于将SAS日期写入SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！