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

Hi,

I want to change one character or replace one character 'T' to ':' in datetime variable using SAS. If it is just few observations then I can use if-then-else statements but if there are thousands of records and they have different time values, how can I change it.

e.g. I have a time value as

'2017-12-01T12:43:28-05:00' and I have to replace T with (colon 🙂 '2017-12-01:12:43:28-05:00'

 

if date_time = '2017-12-01T12:43:28-05:00' then date_time = '2017-12-01:12:43:28-05:00';

It works with if-then-else to replace T with colon but I have different dates and times and I have thousands of records to do it.

 

Any help is greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Use SUBSTR - RIGHT option to replace the T

Or use INPUT to convert it directly to a SAS datetime and then you can apply whatever format you want afterwards.

 

data demo;

have='2017-12-01T12:43:28-05:00' ;
z=input(have, anydtdtm.); *Convert to SAS datetime;
format z datetime.;

have_char = have;
substr(have_char, 11, 1) = ':'; *replace character if desired;

run;

proc print data=demo;
run;

View solution in original post

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

So you have a character field which contains ISO dates and you want to change T to a colon.  Is this what you are asking?  If so then you can use the tranwrd() function.  However, that being said, it really isn't a good idea to do this.  ISO dates are standard datetime format character data, and removing the T makes them no longer a compatible data item.  So the question is why do you want to do this?  Is it because you want to have a date, date/time numeric SAS variable?  If so then read them in using the appropriate format, e8601dt generally works for me.  Need more info really, but really don't change T to : for no apparent reason.

Reeza
Super User

Use SUBSTR - RIGHT option to replace the T

Or use INPUT to convert it directly to a SAS datetime and then you can apply whatever format you want afterwards.

 

data demo;

have='2017-12-01T12:43:28-05:00' ;
z=input(have, anydtdtm.); *Convert to SAS datetime;
format z datetime.;

have_char = have;
substr(have_char, 11, 1) = ':'; *replace character if desired;

run;

proc print data=demo;
run;

Malathi13
Obsidian | Level 7

Hi Reeza,

thank you so much for the help, I got the correct one with the substr function.

 

Thanks

M

Malathi13
Obsidian | Level 7

As a follow-up to my previous question, I'm also trying to calculate the difference (in minutes, seconds) from one time variable with other.

 

e.g. difference in

'2017-12-01T12:47:19-05:00' and

'2017-12-01T12:54:36-05:00'. I'm not sure if I have to use INTCK or any other functions or just (time2-time1).

 

Can someone suggest me on this?

 

Thanks

M

 

Reeza
Super User

@Malathi13 wrote:

As a follow-up to my previous question, I'm also trying to calculate the difference (in minutes, seconds) from one time variable with other.

 

e.g. difference in

'2017-12-01T12:47:19-05:00' and

'2017-12-01T12:54:36-05:00'. I'm not sure if I have to use INTCK or any other functions or just (time2-time1).

 

Can someone suggest me on this?

 

Thanks

M

 


You can subtract datetimes to get the difference in seconds. And then you can convert that to the units of your choice. 

You can also use INTCK.

BrunoMueller
SAS Super FREQ

Noticed, that you have a time zone offset in your datetime value.

 

Have a look at the doc, Reading Dates and Times By Using the ISO 860 Basic and Extended Notations there are specific informats that will make use of the time zone offset information.

 

Here is an example:

data demo;
  have='2017-12-01T12:43:28-05:00';
  dt1 = input(have, anydtdtm.);
  dt2 = input(have, E8601DZ.);

  format dt: datetime19.;
run;

proc print data=demo;
run;
Malathi13
Obsidian | Level 7

Thank you Bruno_sas.

Malathi13
Obsidian | Level 7

Hi Bruno_sas,

your code worked and gives the +5 time zone instead of -5. e.g. for  01DEC2017:12:43:28, it gave me  01DEC2017:17:43:28. I think this is +5. I'm not sure how to work with time zones yet, maybe you are correct. I need to learn about time zones. But I really appreciate you giving me the code to work with the time zones.

 

Thanks a lot

M

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
  • 1560 views
  • 2 likes
  • 4 in conversation