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
M.
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;
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
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;
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
@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
If
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...
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...
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;
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!)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.