prxchange-adding digits after buffer

[Caetreviop543]

I'm trying to modify a timestamp variable (currently in character form) to include zeros in the milliseconds column:

Timestamp 
2010-12-07 10:20:00
2011-12-10 13:21:54
2011-10-09 08:10:45.333
2011-08-15 10:15:33.4
2009-07-27 11:26:24.56
2009-10-10 14:13:00

I need to add zeros to the milliseconds column, to look like this:

Timestamp
2010-12-07 10:20:00.000
2011-12-10 13:21:54.000
2011-10-09 08:10:45.333
2011-08-15 10:15:33.400
2009-07-27 11:26:24.560
2009-10-10 14:13:00.000

I tried using prxchange:

data want;
set have;
 *timestamps with just seconds;
timestamp_new=prxchange('s/(:\d\d)  /$1.000/', -1, timestamp);
*timestamp with one digit in milliseconds;
timestamp_new=prxchange('s/(\.\d)  /$100/', -1, timestamp_new);


run;

The first prxchange for records with just seconds using the capture buffer works. However, the second prxchange not only doesn't add 00 milliseconds but deletes seconds. It seems to be an issue with the fact that I want the digits 00 to come right after the capture buffer. Putting a space between the capture buffer and 00 produces expected results. 

There may also be an easier way to add zeros to the milliseconds column. 

[FreelanceReinh]

Hi @Caetreviop543,

---

@Caetreviop543 wrote:

There may also be an easier way to add zeros to the milliseconds column. 

---

Perhaps something like this?

data want;
set have;
timestamp=translate(put(input(timestamp,e8601dt23.),e8601dt23.3),' ','T');
run;

(If you really don't want to have a numeric timestamp with a datetime format.)

[Caetreviop543]

Thanks, that worked. I actually want to change it to numeric. Would the code instead look like this?

data want;
set have;
timestamp=translate(input(timestamp,e8601dt23.),' ','T');
run;

[FreelanceReinh · Accepted solution]

The character function TRANSLATE cannot be applied to a numeric argument. You need a new variable:

data want(drop=timestamp rename=(timestampn=timestamp));
set have;
timestampn=input(timestamp,e8601dt23.);
format timestampn e8601dt23.3;
run;

If the "T" in the ISO 8601 format is a problem, you may want to create a user-defined (picture) format:

proc format;
picture dspacet
other='%0Y-%0m-%0d %0H:%0M:%0s' (datatype=datetime);
run;

Then use this instead of e8601dt23.3 in the FORMAT statement:

format timestampn dspacet23.3;

[Caetreviop543]

Thanks!