How to convert data to date/datetime in sas decision?

[Mayt]

Hi everybody!

I need to use date/datetime data in sas intelligent decision. But if I import table to caslib My data will become varchar

mydata in excel:

in caslib:

And I found a solution to convert data to date/datetime from sas document Converting DS2 Date, Time, and Timestamp Values to SAS Date, Time, or Datetime Values

So I applied to ds2 code file in sas decision. It works, but I can't find any way to make it receive my date/datetime input. 

Here is my initial code:

package "${PACKAGE_NAME}" /inline;
   method execute(in_out double sasdtval,
                  in_out double sasd,
                  in_out double sastm,
                  in_out varchar fmtdate,
                  in_out varchar fmttime,
                  in_out varchar fmtdt
                  );
       dcl timestamp DS2ts ;
       DS2ts = timestamp '2012-06-04 10:54:34.012';
       sasdtval = to_double(DS2ts);
       sasd = datepart(sasdtval);
       sastm = timepart(sasdtval);
       fmtdate = put(sasd, yymmdd10.);
       fmttime = put(sastm, time.);
       fmtdt = put(sasdtval, datetime21.7);
   end;
endpackage;

I adjusted it to receive datetime input (DS2ts = timestamp datewithtime;) , and it raised error.

package "${PACKAGE_NAME}" /inline;
   method execute(in_out double sasdtval,
                  in_out double sasd,
                  in_out double sastm,
                  in_out varchar fmtdate,
                  in_out varchar fmttime,
                  in_out varchar fmtdt,
                  in_out varchar datewithtime);
       dcl timestamp DS2ts ;
       DS2ts = timestamp datewithtime;
       sasdtval = to_double(DS2ts);
       sasd = datepart(sasdtval);
       sastm = timepart(sasdtval);
       fmtdate = put(sasd, yymmdd10.);
       fmttime = put(sastm, time.);
       fmtdt = put(sasdtval, datetime21.7);
   end;
endpackage;

So If you guys know how to make it work, either needing to convert data in ds2 or another way, please let me know. Thank you in advanced 🙏 

[Tom]

I don't use this Decision tool, but how did you "import" the data into?  Where did you import it from?

If it let's you just run SAS code to read the data then make sure to read those variables as date or datetime values using appropriate informats (or more complex code if needed).  Then try moving the resulting dataset into CAS/VIYA and see if DECISION can recognize the date and datetime variables.

[Mayt]

Thank you so much for your reply.
If I change format in sas studio then upload data to caslib, It will recognise data as date. But that means I need to prepare data in sas studio before use in decision. But I can't because I want to call decision through API and get results.

[Mayt · Accepted solution]

Here I found a solution. It works!!

package "${PACKAGE_NAME}" /inline;
   method execute(in_out double sasdtvalue,
                  in_out double sasdtval,
                  in_out double sasd,
                  in_out double sastm,
                  in_out varchar fmtdate,
                  in_out varchar fmttime,
                  in_out varchar fmtdt,
                  in_out varchar datewithtime,
                  in_out double mon,
                  in_out double d,
                  in_out double y,
                  in_out double h,
                  in_out double m,
                  in_out double s);
       dcl timestamp DS2ts ;
       dcl varchar(32) expression;
       re=prxparse('/(\d+)\/(\d+)\/(\d+)\s+(\d+):(\d+):(\d+)/');
       
       if prxmatch(re, datewithtime) then
       do;
         d=prxposn(re, 1, datewithtime);
         mon=prxposn(re, 2, datewithtime);
         y=prxposn(re, 3, datewithtime);
         h =prxposn(re, 4, datewithtime);
         m =prxposn(re, 5, datewithtime);
         s =prxposn(re, 6, datewithtime);
       end;
       h2s = h*60*60;
       m2s = m*60;
       fraction = h2s+m2s+s;
       sasdtval = mdy(mon,d,y);
       sasdtvalue = (sasdtval*86400)+fraction;
       sasd = datepart(sasdtvalue);
       sastm = timepart(sasdtvalue);
       fmtdate = put(sasd, yymmdd10.);
       fmttime = put(sastm, time.);
       fmtdt = put(sasdtvalue, datetime21.7);
   end;
endpackage;

[Tom]

Glad it worked, but not clear why you had to resort to regex and multiplying seconds since SAS informats could easily DD/MM/YYYY strings into dates and HH:MM:SS strings into times.

Look at the DDMMYY and TIME informats.

Also look at the DHMS() function for converting DATE and TIME into DATETIME.

sasdtval = input(scan(datewithtime,1,' '),ddmmyy10.);
fraction = input(scan(datewithtime,2,' '),time32.);
sasdtvalue=dhms(sasdtval ,0,0,fraction);