Stripping non-specific characters from string by number

[confooseddesi89]

Hello,

I have a weird format for datetime in a dataset where the datetime is followed by a decimal point and three random numbers. For example:

Start_Datetime

2022-06-26 19:43:00.358

2022-06-26 21:33:13.403
2022-08-30 07:26:04.178

It's possible those last three numbers refer to the millisecond, but I don't need this information. I want to make the format "yyyy-mm-dd hh:mm:ss" and datetime, which requires deleting the last four characters, the decimal and the three numbers. Could someone provide code? I've Googled around and only can find code to delete specific characters. Thank you.

[Tom]

First question you need to answer is what TYPE of variable you have.  It is character variable? Or a numeric variable with a display format that is making print those strings that have colons and periods in them?

If it is a character variable and the month number and day of month numbers always use 2 digits just use SUBSTR() take the first 19 characters.

short_string = substr(long_string,1,19);

I they are not so consistent then just use substr to take up to the place where the period is (if there is one).

if index(long_string,'.') then 
  short_string = substr(long_string,1,index(long_string,'.')-1)
;

If you have a numeric variable then just change the format you are using by removing the 3 from the end that is telling SAS you want 3 decimal places.  If you want to remove the milliseconds from the value you could use the INT() function to just take the whole number of seconds.

integer_value = int(real_value);

[confooseddesi89]

Thanks - it's a character variable, and I successfully used the first block of code to make the variable into "yyyy-mm-dd hh:mm:ss" format. However, I need it to be a numeric variable in DATETIME format.

I tried the code below:

Start_Datetime_num = input(Start_Datetime_2, 8.);

But got the constant error:

NOTE: Invalid argument to function INPUT at line ## column ##

And the Start_Datetime_num variable is entirely blank. Could you help? Thank you.

[SASKiwi · Accepted solution]

Try this:

data _null_;
  Date_String = '2022-06-26 19:43:00.358';
  DateTime = input (Date_String, anydtdtm23.);
  format DateTime datetime22.;
  put _all_;
run;

[confooseddesi89]

Thanks! Worked great.

[Tom]

You don't need to "strip" any of the characters from the string to convert it into a datetime value.  To ignore the milliseconds just use an INFORMAT with a width of only 19 and it will only read the first 19 bytes of the string.  

Of use a wider width and read in the milliseconds.  Then you can decide if you want to keep them in the value and set the format to not display them.  Or remove them by either truncating to an integer, or rounding to an integer.

data want;
  set have;
  actual_datetime = input(start_datetime,anydtdtm19.);
  format actual_datetime datetime19.;
run;

Result

Obs        Start_Datetime             actual_datetime

 1     2022-06-26 19:43:00.358     26JUN2022:19:43:00
 2     2022-06-26 21:33:13.403     26JUN2022:21:33:13
 3     2022-08-30 07:26:04.178     30AUG2022:07:26:04

[PaigeMiller]

This is just a formatting change. For datetime values, the underlying value is always the number of seconds since 01JAN1960:00:00:00, and then formatting changes the appearance to something that humans can read. So pick a format that matches what you want, like foprmat datetime19.0

data have;
Start_Datetime='2022-06-26 19:43:00.358'dt; output;
Start_Datetime='2022-06-26 21:33:13.403'dt; output;
Start_Datetime='2022-08-30 07:26:04.178'dt; output;
format start_datetime datetime25.6;
run;
data want;
    set have;
    format start_datetime datetime19.0;
run;

[PaigeMiller]

If that isn't close enough to what you want, you can create your own date/time format to make it look exactly the way you want.

proc format;
    picture myfmt
        low-high='%Y-%0m-%0d %0H:%0M:%0s' (datatype=datetime) ;
run;
data want;
    set have;
    format start_datetime myfmt19.;
run;