Importing JSON dates into SAS gives incorrect $ format where datetime data expected

Question

I am trying to import data containing some date-columns/fields into SAS. The data are in JSON format, and hence need to be converted before import. This I use SAS libname JSON for.

But when I convert/import the data, SAS does not interpret the dates as proper dates, and allow me to manipulate data with date-constraints and so on. Instead, SAS imports the dates as Format = $ whatever that is. But the data show in the imported data. SAS imports the data without errors but any other date-field than the 'date_fi' in the data are not properly formatted as a date.

I am using the following script

filename resp "C:\Temp\transaktioner_2017-07.json" lrecl=1000000000 ; filename jmap "C:\Temp\transaktioner.map"; filename head "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; run; libname space JSON fileref=resp map=jmap ;*automap=create;

LIBNAME SASDATA BASE "D:\SASData";* outencoding='UTF-8';

Data SASDATA.Transaktioner ; Set space.Rows_value; run;

data null; if exist("Acubiz.EMS_TRANSAKTIONER", "DATA") then rc=dosubl("proc sql noprint; drop table Acubiz.EMS_TRANSAKTIONER; quit;"); run;

data Acubiz.EMS_TRANSAKTIONER; set sasdata.transaktioner; run;

proc metalib; omr (library="/Shared Data/SAS Visual Analytics/Autoload/AcubizEMSAutoload/Acubiz_EMS" repname="Foundation"); folder="/Shared Data/SAS Visual Analytics/Autoload/AcubizEMSAutoload"; select ("EMS_TRANSAKTIONER"); run; quit;

libname CLIENT clear;

libname space clear;

For this conversion, I use the following JSON map.file called 'transaktioner.map'.

The field date_fi imports in the proper date format which I can manipulate as date-format in SAS Visual Analytics, but confirmeddate_fi does not.

The most important parts of this file are here.

{
  "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
},

Does any of you know how I might import the data and interpret the date-fields as such.

I have been messing with different informants in the JSON map-file to solve this riddle and have managed to get to where I can import the data without errors, but SAS does not interpret the date fields as such.

The actual fields are explained here with some examples (taken from the imported data):

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)

Does anyone have pertinent info on this issue, as I am at my wit's end? And have exhausted SAS Tech Support about this date-issue.

PS: As a proof of concept, we are importing approx 110.000 rows. And the import finishes without any errors.

A good PDF explaining the different ISO formats in SAS can be found here

Answer 1

Apparantly the solution is to start to import the date-columns as CHARACTERs instead of numbers. And hence do the conversion to date-format in the SAS code like so:

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;

I will then remove all specifics for NUMERIC type for importing date-fields in the map file. This way the JSON libname does NOT take care of interpreting the date-formats. SAS does.

ie. the map file specification must be changed back to something like this for alle date-fields.

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