DATA Step, Macro, Functions and more

Date / Time Formatting Issue

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Date / Time Formatting Issue

Hi ,

 

 

CandidateDuration
Arun0hr 0min 44sec.
Arun0hr 0min 9sec.
Arun12hr 4min 10sec.
Vishal1hr 0min 24sec.
Vishal0hr 0min 35sec.
Vishal0hr 7min 29sec.
Vishal2hr 1min 9sec.


A) I want to find the total sum of duration candidate wise. 

B) How to covert duration to Time format (eg: 1:04:10)






Accepted Solutions
Solution
‎05-23-2017 02:22 PM
Super User
Posts: 17,960

Re: Date / Time Formatting Issue


AgentA wrote:

I was doing this but couldn't get through:

data d(keep=username duration time);
set a;
time=substr(duration,1,1)||':'||substr(duration,4,2)||':'||compress(substr(duration,10,2),'','kd');
run;



I'm not sure what that's trying to do. 

 

To convert to time you need to extract the time components and use them in the HMS function. 

Use SCAN to extract the specific component and COMPRESS to keep only digits. 

 

First_part = scan(old_time, 1);

first_char = compress(first_part, , 'kd');

first_digit = input(first_char, 8.);

 

Repeat for minutes and seconds. Then pass each component to HMS. 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,631

Re: Date / Time Formatting Issue

[ Edited ]

AgentA wrote:

Hi ,

 

 

Candidate Duration
Arun 0hr 0min 44sec.
Arun 0hr 0min 9sec.
Arun 12hr 4min 10sec.
Vishal 1hr 0min 24sec.
Vishal 0hr 0min 35sec.
Vishal 0hr 7min 29sec.
Vishal 2hr 1min 9sec.


A) I want to find the total sum of duration candidate wise. 

B) How to covert duration to Time format (eg: 1:04:10)






You will have to parse the strings and then apply the HMS function to get the results into a SAS time value

 

The ANYALPHA or NOTALPHA function can help you determine where the numbers end and the alphabetic characters begin in each "word" (a "word" being a string of text separated by a space)

 

 

Occasional Contributor
Posts: 15

Re: Date / Time Formatting Issue

code please
Super User
Posts: 6,972

Re: Date / Time Formatting Issue


AgentA wrote:
code please

Manners, please

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 17,960

Re: Date / Time Formatting Issue

1. Proc means

2. What variable type is it now? Use INPUT() to convert and Apply a format with the FORMAT statement. Didn't we do this on your last question for a date variable? 

 

FYI You forgot to include your data as a datastep this time. 

 

Occasional Contributor
Posts: 15

Re: Date / Time Formatting Issue

2. The data type is character. Kindly provide code for data step .
Trusted Advisor
Posts: 1,631

Re: Date / Time Formatting Issue

[ Edited ]

Partial code (UNTESTED)

 

hour_word = scan(duration,1,' ');
first_char = anyalpha(hour_word);
hour = substr(hour_word,1,first_char-1);
Super User
Posts: 17,960

Re: Date / Time Formatting Issue


AgentA wrote:
2. The data type is character. Kindly provide code for data step .

That's not how this works. Please try something, we aren't here to do your work. If you post what you've tried, we'll be more than happy to help correct or guide you in the right direction. 

 

If you must have code, hire a consultant. 

Occasional Contributor
Posts: 15

Re: Date / Time Formatting Issue

I was doing this but couldn't get through:

data d(keep=username duration time);
set a;
time=substr(duration,1,1)||':'||substr(duration,4,2)||':'||compress(substr(duration,10,2),'','kd');
run;


Solution
‎05-23-2017 02:22 PM
Super User
Posts: 17,960

Re: Date / Time Formatting Issue


AgentA wrote:

I was doing this but couldn't get through:

data d(keep=username duration time);
set a;
time=substr(duration,1,1)||':'||substr(duration,4,2)||':'||compress(substr(duration,10,2),'','kd');
run;



I'm not sure what that's trying to do. 

 

To convert to time you need to extract the time components and use them in the HMS function. 

Use SCAN to extract the specific component and COMPRESS to keep only digits. 

 

First_part = scan(old_time, 1);

first_char = compress(first_part, , 'kd');

first_digit = input(first_char, 8.);

 

Repeat for minutes and seconds. Then pass each component to HMS. 

Super Contributor
Posts: 265

Re: Date / Time Formatting Issue

Using a regular expression with prxposn could solve the problem; can't test it right now.

Respected Advisor
Posts: 3,907

Re: Date / Time Formatting Issue

@AgentA

Here one way to go.

proc format;
   invalue $toTimeString(default=16) 
    's/^[^\d]*(\d+)[^\d]+(\d+)[^\d]+(\d+)[^\d]*$/\1:\2:\3/o' (regexpe) = _same_ 
    other=_same_
    ;
run;

data test;
  format t time10.;
  timeString='0hr 0min 44sec';
  t=input(input(timeString,$toTimeString.),? time.);
run;

And before you ask here the links to the docu:

https://support.sas.com/documentation/cdl/en/proc/70377/HTML/default/viewer.htm#n1jriq5xib5j45n1pwpw...

http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#p0s9ilagexm...

 

Super User
Posts: 9,691

Re: Date / Time Formatting Issue

data have;
infile cards expandtabs truncover;
input Candidate	 $ Duration $40.;
cards;
Arun	0hr 0min 44sec.
Arun	0hr 0min 9sec.
Arun	12hr 4min 10sec.
Vishal	1hr 0min 24sec.
Vishal	0hr 0min 35sec.
Vishal	0hr 7min 29sec.
Vishal	2hr 1min 9sec.
;
run;

data want;
 set have;
 time=hms(input(scan(duration,1,,'kd'),best.),
 input(scan(duration,2,,'kd'),best.),
 input(scan(duration,3,,'kd'),best.));
format time tod10.;
run;
Occasional Contributor
Posts: 15

Re: Date / Time Formatting Issue

Thank you so much
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 209 views
  • 6 likes
  • 7 in conversation