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

问题描述

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

我得到了带有 sas 日期 DataEndDay 的 SAS 数据，我想将其写入数据库.以下位正在使用中(缓冲区只是为了加速测试失败):

libname 值 oledb provider=sqloledb schema="dbo" INSERTBUFF=100properties=("用户 ID"="&username." Password="&pw."数据源"= & 数据库.初始目录"=& 目录.)；proc sql noprint；插入到 value.Data_upload_from_me( <some_columns...>,<更多列...>,数据结束日)选择<some_columns_source...>,<more-columns_source...>,数据结束日从 work.SAS_data_to_publish;退出;

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

选择<some_columns_source...>,<more-columns_source...>,'2018-12-12'从 work.SAS_data_to_publish;退出;

但如果我在 SAS 数据步中将 SAS 日期转换为字符串:

数据 SAS_data_to_publish ;设置 SAS_data_to_publish ；dataEndday0 = put(DataEndDay, yymmddd10.);DataEndDay1 = quote(dataEndday0, "'") ;跑;

并尝试编写其中任何一个，我得到转换错误:

<块引用>

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

当我选择字符串时，它看起来还不错:

proc sql;从 SAS_data_to_publish 中选择 DataEndDay1；退出;

<块引用>

'2018-12-12'

以前我已经设法用类似的技巧编写了 dateTimes，这很有效:

proc 格式；图片sjm.= .其他='%Y-%0m-%0d %0H:%0M:%0S:000'(数据类型=日期时间);跑;要写入的数据；设置 save.raw_data_to_be_written;DataEndDay0 = put(dhms(DataEndDay,0,0,0), sjm.-L);跑;

有人遇到过类似的问题吗?我怎么能写日期?我可以要求他们将列更改为 dateTime，也许....

提前谢谢你.

我设法开发了一种变通方法，它有效但很丑陋，而且 - 坦率地说 - 我不喜欢它.碰巧我的日期对于所有行都是相同的，所以我可以将它分配给宏变量，然后在数据库写入中使用它.

数据_NULL_；设置 SAS_data_to_publish；调用 symput('foobar', quote( put (DataEndDay , yymmddd10.-L), "'") ) ;跑;……选择<some_columns_source...>,<more-columns_source...>,&foobar.从 work.SAS_data_to_publish;退出;

当然，如果 DataEndDay 发生变化，这将立即失败，但可能表明 Proc SQL 的 select 子句中有某些问题......

Edit Edit 将问题粘贴到 SAS 论坛

解决方案

我终于设法解决了这个问题.问题在于缺失值.当我将值作为字符串传递到数据库中时，解析器将缺失值解释为实点而不是空字符串.以下作品:

数据上传；设置上传；CreatedReportdate2 = PUT(CreatedReportdate , yymmddn8.);跑;libname uplad_db odbc noprompt =驱动程序=SQL Server；服务器=&ser​​ver.;Uid=&user.;Pwd=&pw.;DATABASE=&db.;"插入缓冲区=32767；过程 sql;插入 uplad_db.upload_table(……)选择CreatedReportdate2 ='.' 时的情况然后 '' 否则 CreatedReportdate2 结束，...从上传；退出;

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

解决方案

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屋！