topic Re: Convert text to timestamp in SAS Programming

Convert text to timestamp

Cello23 — Fri, 09 Sep 2016 13:42:27 GMT

Hi,

i need to convert a column TM_INS_C char ($26.) "2016-09-05-17.38.51.717187" to timestamp.

I use thsi query:

data WORKUSI.T03_totale_pag_P ;
set WORKUSI.T03_totale_pag;
new_var=input(TM_INS_C,datetime26.);
FORMAT new_var datetime26.;
WHERE NDG_TITR_CARTA = '034361749';
run;

but i have this error:

NOTE: Invalid argument to function INPUT at line 1723 column 13.
TM_INS_C=2016-07-25-17.03.46.213975 CO_CAU_OPER_ATM=M90 CO_SUB_CAU_OPE_ATM=0000
NDG_TITR_CARTA=034361749 CO_KEY_CARTA_ATM=20081436919871987 DT_OPE=25JUL2016
OR_OPE=17:03:00 DB_OPER=PAGAMENTO BOLLETTINI POST IM_OPER_ATM=0.000 FL_CARTA_BANCA=S
FL_CARTA_POOL=N CODCAB=03045 CO_ATM=05496 TIPOPER_=M900000 KEYATM=020080304505496
CONTO=PAGAMENTI DESCR=PAGAMENTO BOLLETTINI POST LOG=PAGAMENTI new_var=. _ERROR_=1 _N_=11
NOTE: Mathematical operations could not be performed at the following places. The results
      of the operations have been set to missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      11 at 1723:13
NOTE: There were 11 observations read from the data set WORKUSI.T03_TOTALE_PAG.
      WHERE NDG_TITR_CARTA='034361749';
NOTE: The data set WORKUSI.T03_TOTALE_PAG_P has 11 observations and 19 variables.
NOTE: DATA statement used (Total process time):
      real time           1.49 seconds
      cpu time            0.12 seconds

What is my error ?

Thank's

Re: Convert text to timestamp

ScottBass — Fri, 09 Sep 2016 14:03:37 GMT

Is "2016-09-05-17.38.51.717187" valid input to the datetime26. informat?

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000199624.htm

Re: Convert text to timestamp

paulkaefer — Fri, 09 Sep 2016 14:13:30 GMT

As @ScottBass mentions, the variable as-is does not work properly. So what I would do is parse the string for dates and times, and combine them with the DHMS function. This worked for me:

data WORK.timestamps;
    TM_INS_C = "2016-09-05-17.38.51.717187";

    date = input(substr(TM_INS_C, 1, 10), yymmdd10.);
    time = input(substr(TM_INS_C, 12, 10), time10.);
    date_time = dhms(date, 0, 0, time);

    format date yymmdd10.
           time time10.
           date_time datetime.;
run;

Re: Convert text to timestamp

Cello23 — Fri, 09 Sep 2016 14:48:01 GMT

Thank's!!!! 🙂

Re: Convert text to timestamp

Cello23 — Fri, 09 Sep 2016 15:32:18 GMT

Sorry ... another similar question.

I have two column:

excel_datetime (DATETIME27.) , example ( 01SEP2016:18:11:34)

excel_millis (BEST12.), example (54)

i need this final result: 01SEP2016:18:11:34.054 (datetime23.3).

My query is:

data WORKUSI.T02_ope_bar_p   ;
set  workusi.T02_ope_bar;
   	
datetime = compress(put(excel_datetime,datetime27.)||"."||put(excel_millis,z3.) );
 
date = input(substr(datetime, 1, 10), yymmdd10.);
time = input(substr(datetime, 12, 10), time10.);
date_time_new = dhms(date, 0, 0, time);
where atm_nr=3542;
 
    format date yymmdd10.
           time time10.
           date_time datetime23.3;
run;

what's my error ?

thank's

Re: Convert text to timestamp

ScottBass — Fri, 09 Sep 2016 15:45:28 GMT

@Cello23 wrote:
Sorry ... another similar question.

I have two column:
excel_datetime (DATETIME27.) , example ( 01SEP2016:18:11:34)
excel_millis (BEST12.), example (54)

i need this final result: 01SEP2016:18:11:34.054 (datetime23.3).

My query is:
data WORKUSI.T02_ope_bar_p   ;
set  workusi.T02_ope_bar;
   	
datetime = compress(put(excel_datetime,datetime27.)||"."||put(excel_millis,z3.) );
 
date = input(substr(datetime, 1, 10), yymmdd10.);
time = input(substr(datetime, 12, 10), time10.);
date_time_new = dhms(date, 0, 0, time);
where atm_nr=3542;
 
    format date yymmdd10.
           time time10.
           date_time datetime23.3;
run;
what's my error ?
thank's

excel_datetime (DATETIME27.) really is the text '01SEP2016:18:11:34', and excel_millis really is the text '54', then consider:

datetime=input(excel_datetime,datetime27.); * that text is a valid SAS datetime literal ;

millis=input(excel_millis,best.)/1000; * stored as the number of milliseconds.

datetime=datetime+millis; * add the fractional seconds to the datetime ;

TBH, I'm not sure if adding the fractional seconds to the datetime yields the correct result, and I don't have SAS in front of me.

But I do know you don't have to jump through hoops parsing the datetime string; it already works as a SAS datetime literal.

HTH...

Re: Convert text to timestamp

ScottBass — Fri, 09 Sep 2016 15:57:44 GMT

Sorry, I mis-read your post...

data WORKUSI.T02_ope_bar_p   ;
set  workusi.T02_ope_bar;
   	
datetime = compress(put(excel_datetime,datetime27.)||"."||put(excel_millis,z3.) );
 
date = input(substr(datetime, 1, 10), yymmdd10.);
time = input(substr(datetime, 12, 10), time10.);
date_time_new = dhms(date, 0, 0, time);
where atm_nr=3542;
 
    format date yymmdd10.
           time time10.
           date_time datetime23.3;
run;

So, excel_datetime is *already* a datetime value, and you're using the datetime27. format to convert it to text.

And excel_millis is *already* a numeric value, and you're using the z3. format to convert it to text.

Then you're concatenating them with a period.

Then parsing the concatenated string back into a date and time string, using the yymmdd10. and time10. informats to convert back to a date and time value.

Then using the DHMS function to convert the separate date and time values into a datetime value.

Whew!!!

If you already have a datetime value, and a numeric value for milliseconds, I think you should just be able to add them together??? You just need the offset required to get the right result.

Sorry, I don't have SAS in front of me, but if you generate the expected output, look at the numeric number generated, and compare your source data, you should be able to work it out.

If not, then sure, convert to text, but I don't think you have to parse it back again. Something like this should work:

datetime = input('12SEP2016:12.34.56.054',datetime27.3); * or whatever the w.d values should be ;

HTH...

Re: Convert text to timestamp

paulkaefer — Fri, 09 Sep 2016 15:54:38 GMT

One issue is that "01SEP2016" is not in the same format as "yymmdd10."

I am able to run it like so, and it works:

data WORK.datetimes2   ;
    excel_datetime="01SEP2016:18:11:34"dt;
    excel_millis = 54;
    
    datetime = compress(put(excel_datetime,datetime27.)||"."||put(excel_millis,z3.) );
    
    datetime2 = input(datetime, datetime23.3);
    
    format datetime2 datetime23.3;
run;

Re: Convert text to timestamp

ScottBass — Fri, 09 Sep 2016 16:07:07 GMT

01SEP2016 is in date9. format

18:11:34 is in time8. format

01SEP2016:18:11:34 is in datetime<w> format

Hit the doc on SAS formats and informats. For dates, review the section where they're grouped by category.

(I had to support code once where it always had compress(put(date,yymmdd10.),'-'). Why didn't they just code put(date,yymmddn8.)? Get familiar with all the formats and informats available, and where to quickly find them (I can never remember them all!) This will serve you in good stead in your SAS coding career!)