BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cello23
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
paulkaefer
Lapis Lazuli | Level 10

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;

View solution in original post

8 REPLIES 8
ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
paulkaefer
Lapis Lazuli | Level 10

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;
Cello23
Quartz | Level 8
Thank's!!!! 🙂
Cello23
Quartz | Level 8

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

ScottBass
Rhodochrosite | Level 12

@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...


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
ScottBass
Rhodochrosite | Level 12

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...


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
paulkaefer
Lapis Lazuli | Level 10

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;

 

 

ScottBass
Rhodochrosite | Level 12

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!) 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 10660 views
  • 2 likes
  • 3 in conversation