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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

  end;

put time time8. ' <- ' timetext ;

cards;

5s

0m 10s

2m 20s

4m

2h 8m 6s

4h

;

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

View solution in original post

14 REPLIES 14
jakarman
Barite | Level 11

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

---->-- ja karman --<-----
ballardw
Super User

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;

D_Z_
Obsidian | Level 7

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

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

ballardw
Super User

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.

SASKiwi
PROC Star

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

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;

D_Z_
Obsidian | Level 7

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.

Reeza
Super User

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

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;

Tom
Super User Tom
Super User

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;

  end;

put time time8. ' <- ' timetext ;

cards;

5s

0m 10s

2m 20s

4m

2h 8m 6s

4h

;

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

D_Z_
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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.

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
TomKari
Onyx | Level 15

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

D_Z_
Obsidian | Level 7

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;

  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!  Smiley Happy  This is a huge win and I appreciate you help!

Thanks,

Dean

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 14 replies
  • 1622 views
  • 0 likes
  • 7 in conversation