首页 文章

将JSON日期导入SAS会在预期的datetime数据中提供不正确的$格式

提问于
浏览 次
1

我正在尝试将包含一些日期列/字段的数据导入SAS . 数据采用JSON格式，因此需要在导入之前进行转换 . 我使用SAS libname JSON .

但是当我转换/导入数据时，SAS不会将日期解释为正确的日期，并允许我操纵具有日期约束的数据等等 . 相反，SAS将日期导入格式= $，无论是什么 . 但数据显示在导入的数据中 . SAS无错误地导入数据，但数据中除“date_fi”之外的任何其他日期字段都未正确格式化为日期 .

我使用以下脚本

filename resp“C：\ Temp \ transaktioner_2017-07.json”lrecl = 1000000000; filename jmap“C：\ Temp \ transaktioner.map”;文件名头“c：\ temp \ header.txt”; options metaserver =“DOMAIN”metaport = 8561 metarepository =“Foundation”metauser =“USER”metapass ='CENSORED'; libname CLIENT sasiola tag = SOMETAG port = 10011 host =“DOMAIN”signer =“https：// CENSORED”; proc http HEADEROUT = head url ='http：// VALID_PATH / acubiz_sas / _design / view / _view / bymonth？key =“2017-07”'method =“GET”CT =“application / json”out = resp;跑; libname space JSON fileref = resp map = jmap; * automap = create; LIBNAME SASDATA BASE“D：\ SASData”; * outencoding ='UTF-8';数据SASDATA.Transaktioner;设置space.Rows_value;跑;数据无效;如果存在（“Acubiz.EMS_TRANSAKTIONER”，“DATA”）则rc = dosubl（“proc sql noprint; drop table Acubiz.EMS_TRANSAKTIONER; quit;”）;跑;数据Acubiz.EMS_TRANSAKTIONER;设置sasdata.transaktioner;跑; proc metalib; omr（library =“/ Shared Data / SAS Visual Analytics / Autoload / AcubizEMSAutoload / Acubiz_EMS”repname =“Foundation”）; folder =“/ Shared Data / SAS Visual Analytics / Autoload / AcubizEMSAutoload”;选择（“EMS_TRANSAKTIONER”）;跑;放弃; libname客户端清除; libname空间清晰;

对于此转换，我使用以下称为“transaktioner.map”的JSON map.file .

字段 date_fi 以正确的日期格式导入，我可以在SAS Visual Analytics中将其作为日期格式进行操作，但 confirmeddate_fi 不会 .

这个文件最重要的部分就在这里 .

{
  "NAME": "date_fi",
  "TYPE": "NUMERIC",
  "INFORMAT": [ "e8601dt19", 19, 0 ],
  "FORMAT": ["DATETIME", 20],
  "PATH": "/root/rows/value/date_fi",
  "CURRENT_LENGTH": 20
},


{
  "NAME": "confirmeddate_fi",
  "TYPE": "NUMERIC",
  "INFORMAT": [ "e8601dt19", 19, 0 ],
  "FORMAT": ["DATETIME", 20],
  "PATH": "/root/rows/value/confirmeddate_fi",
  "CURRENT_LENGTH": 20
},

你们中的任何人都知道如何导入数据并解释日期字段 .

我一直在搞乱JSON map文件中的不同线人来解决这个问题，并设法到达我可以无错误地导入数据的地方，但SAS并没有解释日期字段 .

这里用一些例子（取自导入的数据）解释实际字段：

Reference that works
date_fi:                    "2017-07-14T00:00:00"  (Apparantly never timestamped but use T00:00:00 - checked 9 instances)

Should work
invoicedate_fi:             "2017-08-01T00:00:00"  (Apparantly never timestamped but use T00:00:00 - checked 9 instances)
invoicedate_fi:             "2017-07-19T00:00:00"
invoicedate_fi:             "2017-07-17T00:00:00"

arrivaldate_fi:             "2017-08-13T00:00:00"  (Apparantly never timestamped but use T00:00:00 - checked 9 instances)

departuredate_fi:           "2017-08-09T00:00:00"  (Apparantly never timestamped but use T00:00:00 - checked 9 instances)



Do not work as numeric - even though they are specified as dates in map-file (for use with SAS JSON Libname)
markedreadydate_fi:         "2017-08-02T11:41:56"   (This field is often but not always timestamped)
markedreadydate_fi:         "2017-07-31T15:08:03"
markedreadydate_fi:         "2017-07-19T00:00:00"

confirmeddate_fi:           "2017-07-21T00:00:00"   (This field is often but not always timestamped)
confirmeddate_fi:           "2017-08-06T20:11:26"
confirmeddate_fi:           "2017-07-14T18:38:41"

confirmeddatefinance_fi:    "2017-07-31T15:54:10"   (This field is often but not always timestamped)
confirmeddatefinance_fi:    "2017-08-17T10:33:32"
confirmeddatefinance_fi:    "2017-07-26T08:21:34"

markedreadydate_fi:         "2017-07-19T00:00:00"   (This field is often but not always timestamped)

有没有人有关于这个问题的相关信息，因为我在我的智慧结束？关于这个日期问题已经耗尽了SAS技术支持 .

PS：作为概念证明，我们正在导入大约110,000行 . 导入完成没有任何错误 .

可以找到一个很好的PDF解释SAS中不同的ISO格式here

json sas

1 回答

  • 1

    显然，解决方案是开始将日期列导入为CHARACTER而不是数字 . 因此，在SAS代码中转换为日期格式，如下所示：

    Data SASDATA.Transaktioner(drop=
arrivaldate_fi_temp
departuredate_fi_temp
confirmeddate_fi_temp
confirmeddatefinance_fi_temp
datetoshow_fi_temp 
date_fi_temp 
invoicedate_fi_temp 
markedreadydate_fi_temp
);
Set space.Rows_value(rename=(
confirmeddate_fi=confirmeddate_fi_temp 
datetoshow_fi=datetoshow_fi_temp 
date_fi=date_fi_temp 
invoicedate_fi=invoicedate_fi_temp 
markedreadydate_fi=markedreadydate_fi_temp 
arrivaldate_fi=arrivaldate_fi_temp 
departuredate_fi=departuredate_fi_temp 
confirmeddatefinance_fi=confirmeddatefinance_fi_temp
));
*length invoicedate_fi 8.;
format 
confirmeddate_fi 
datetoshow_fi 
date_fi 
invoicedate_fi 
markedreadydate_fi
arrivaldate_fi
departuredate_fi
confirmeddatefinance_fi
datetime20.;
if confirmeddate_fi_temp ne '' then confirmeddate_fi=input(confirmeddate_fi_temp,E8601DT19.); else confirmeddate_fi=.;
if datetoshow_fi_temp ne '' then datetoshow_fi=input(datetoshow_fi_temp,E8601DT19.); else datetoshow_fi=.;
if date_fi_temp ne '' then date_fi=input(date_fi_temp,E8601DT19.); else date_fi=.;
if invoicedate_fi_temp ne '' then invoicedate_fi=input(invoicedate_fi_temp,E8601DT19.); else invoicedate_fi=.;
if markedreadydate_fi_temp ne '' then markedreadydate_fi=input(markedreadydate_fi_temp,E8601DT19.); else markedreadydate_fi=.;
if arrivaldate_fi_temp ne '' then arrivaldate_fi=input(arrivaldate_fi_temp,E8601DT19.); else arrivaldate_fi=.;
if departuredate_fi_temp ne '' then departuredate_fi=input(departuredate_fi_temp,E8601DT19.); else departuredate_fi=.;
if confirmeddatefinance_fi_temp ne '' then confirmeddatefinance_fi=input(confirmeddatefinance_fi_temp,E8601DT19.); else confirmeddatefinance_fi=.;
run;

    然后，我将删除NUMERIC类型的所有细节，以便在 Map 文件中导入日期字段 . 这样，JSON libname不会解释日期格式 . SAS确实如此 .

    即 . 对于alle date-fields，必须将映射文件规范更改回类似的内容 .

    {
  "NAME": "date_fi",
  "TYPE": "CHARACTER",
  "PATH": "/root/rows/value/date_fi",
  "CURRENT_LENGTH": 19
},
    回复于

相关问题