topic Re: Help Reformatting Character Text to Time in SAS Enterprise Guide

Help Reformatting Character Text to Time

D_Z_ — Wed, 20 Aug 2014 18:21:10 GMT

Hi All,

I am working on a project and I have a time field that displays the data in a character field like this:

2m20s or 6h2m20sec (That is the format the data is in when i import it into SAS form Excel)

I am looking to make this into a HH:MM:SS field. Can anyone assist in how I would do this... i cannot find anything that has been helpful so far. I am not sure where to go from here?

Dean

Re: Help Reformatting Character Text to Time

jakarman — Wed, 20 Aug 2014 20:11:57 GMT

Did you try the informats? SAS(R) 9.4 Formats and Informats: Reference

When missing you can do something with fcmp or other thing to build an own informat

Re: Help Reformatting Character Text to Time

ballardw — Wed, 20 Aug 2014 21:07:44 GMT

If the field is already character you can't change it to numeric, which is what a SAS time field would be. You can create a new variable that would be the time but we need to clarify if the value is 6h2m20sec of 6h2m20s and whether the h, m and s are ever upper case.

If the time bits are always h, m and s this is one way.

In a data step

data want;

set have;

time= input((translate(timecharactervariablenamegoeshere,':: ','hms')),time8.);

format time time8.;

run;

Re: Help Reformatting Character Text to Time

D_Z_ — Wed, 20 Aug 2014 21:38:09 GMT

Thanks Ballardw,

The values vary slightly. Here is an example of the list of values i have for my data from yesterday. This was copied directly from my SAS table to this post. As you can see... the variable changes slightly... i would imagine at some point in the future it would also be possible to see something like 45s. These are average response times...so in order to build my application to be able to automatically calculate and give me the data i need... i have to have these in a format that i can do the calculations. Appreciate any help you can give.

2m 20s.

3m 55s

3m 56s

4m 22s

4m 29s

4m 40s

4m 41s

4m 42s

6m 26s

7m 18s

8m 29s

8m 41s

9m 46s

11m 23s

15m 3s

19m 36s

23m 41s

27m 40s

2h 8m 6s

6h 16m 2s

6h 18m 50s

Dean

Re: Help Reformatting Character Text to Time

ballardw — Wed, 20 Aug 2014 22:19:34 GMT

my code won't work then as the informat time8. will assume the first number followed by : is hours.

The stimer8. may work though in the input clause.

Re: Help Reformatting Character Text to Time

SASKiwi — Wed, 20 Aug 2014 22:56:03 GMT

Thanks to Ballardw try this:

data times;

input @1 timetext $12.;

format sastime hhmm8.;

sastime = input(compress(timetext), stimer9.);

cards;

2m 20s

3m 55s

3m 56s

4m 22s

4m 29s

4m 40s

4m 41s

4m 42s

6m 26s

7m 18s

8m 29s

8m 41s

9m 46s

11m 23s

15m 3s

19m 36s

23m 41s

27m 40s

2h 8m 6s

6h 16m 2s

6h 18m 50s

;

run;

Re: Help Reformatting Character Text to Time

D_Z_ — Wed, 20 Aug 2014 23:19:50 GMT

So its a little closer now.. got the time format... but all the results show up 0:00 ...

here is the data step i inputted:

data work.want;

set WORK.QUERY_FOR_TEST;

format sastime hhmm8.;

sastime = input(compress(response_time__avg_), stimer9.);

run;

Thanks for any help you can give.

Re: Help Reformatting Character Text to Time

Reeza — Wed, 20 Aug 2014 23:39:40 GMT

This works for me:

data times;

input @1 timetext $12.;

format sastime time11.;

sastime = input(translate(timetext, '::.', 'hms'), stimer9.);

cards;

2m 20s

3m 55s

3m 56s

4m 22s

4m 29s

4m 40s

4m 41s

4m 42s

6m 26s

7m 18s

8m 29s

8m 41s

9m 46s

11m 23s

15m 3s

19m 36s

23m 41s

27m 40s

2h 8m 6s

6h 16m 2s

6h 18m 50s

;

run;

Re: Help Reformatting Character Text to Time

Tom — Wed, 20 Aug 2014 23:50:03 GMT

You could just brute force it.

data times;

input @1 timetext $12.;

format time time8.;

time=.;

word=timetext;

do i=1 to 3 ;

word=scan(timetext,i,' ');

select ( substr(word,length(word)));

when ('s') time+input(compress(word,'s'),12.);

when ('m') time+60*input(compress(word,'m'),12.);

when ('h') time+60*60*input(compress(word,'h'),12.);

otherwise ;

end;

put time time8. ' <- ' timetext ;

cards;

0m 10s

2m 20s

2h 8m 6s

;

run;

0:00:05 <- 5s

0:00:10 <- 0m 10s

0:02:20 <- 2m 20s

0:04:00 <- 4m

2:08:06 <- 2h 8m 6s

4:00:00 <- 4h

Re: Help Reformatting Character Text to Time

D_Z_ — Thu, 21 Aug 2014 00:10:19 GMT

It would be okay, except that the data is going to change daily... so the times will never be the same. This report is being used to generate daily data... and i am using an Excel report that i receive daily to make the program.

I can't change the times everyday because the # of entries and the response time will change everytime i run the report. We will also be turning it into an automated process... so the excel sheet will move to the directory automatically and the project will run automatically and that data will generate as part of a larger report that will be appended daily to a dataset within the repository.

Dean

Re: Help Reformatting Character Text to Time

Tom — Thu, 21 Aug 2014 00:26:19 GMT

Not sure I understand that. Not sure how having to fix this time variable changes the normal flow.

You read the data from Excel into SAS, Clean it up. Produce a report.

Re: Help Reformatting Character Text to Time

jakarman — Thu, 21 Aug 2014 06:54:58 GMT

D.Z every day an excel coming automatically is a contradiction in termino. Excel is meant to be operated manually.
When there will automated process there must be an automated source that will automated send files in an automated structure.

That automated structure is your borderline of communicate the information.
Perhaps you can give some influence on that perhaps not. Having that structure well described the mentioned solutions above can be tried.

Re: Help Reformatting Character Text to Time

TomKari — Thu, 21 Aug 2014 13:08:43 GMT

I hear you, Japp

I'm struggling right now with a client who receives key data sources on Excel. They swear up and down that the structure will never change, but color me doubtful.

Unfortunately, it's Microsoft's world and they're just nice enough to let us live in it!

Best,

Tom

Re: Help Reformatting Character Text to Time

jakarman — Thu, 21 Aug 2014 13:22:55 GMT

O my, two Tom's, I have to replicate. Tomkari, Microsoft is not that bad, they are also having SQL-server and more of those. Well suited for professional automated processes. The issue is all those human wanting to do it manually, not knowing better. Have to live with that is agreed.

Re: Help Reformatting Character Text to Time

D_Z_ — Thu, 21 Aug 2014 13:32:54 GMT

Thanks everyone for all the great input.

I finally got this to work last night. Here is the code that i used below, which was a direct variation of what Tom posted last night.

data work.want;

set WORK.QUERY_FOR_TEST;

format time time8.;

time=.;

word=response_time__avg_;

do i=1 to 3 ;

word=scan(response_time__avg_,i,' ');

select ( substr(word,length(word)));

when ('s') time+input(compress(word,'s'),12.);

when ('m') time+60*input(compress(word,'m'),12.);

when ('h') time+60*60*input(compress(word,'h'),12.);

otherwise ;

end;

put time time8. ' <- ' response_time__avg_;

cards;

run;

Japp - I hear ya man! I get excel data from one of our vendors that holds daily raw data... so when that comes in my inbox... i have VBA setup to move that to a network location... then have my EG project scheduled to run... so it picks up the file and runs it. I don't even look at the raw data files anymore. Unfortunately, they just changed their system and i have been rebuilding the project based on the new file structure.

Tom - Thank you so much... i think i fell off my chair when i saw a column of time formatted data... i was elated to see that column! This is a huge win and I appreciate you help!

Thanks,

Dean