BookmarkSubscribeRSS Feed
IrishGuy
Calcite | Level 5
Hi there. This thread is further to the previous thread "Create Format to Display Time Interval" where I was told how to use cntlin to create a custom format to format a numeric field holding seconds as hours/minutes/seconds.

Can I do a similar thing to create an informat to take in the original time interval value in text from my input dataset and convert it to a numeric field holding seconds? I'm not clear how much of the Proc Format instructions apply to creating informats. What I need to do is create an informat to take in time interval values such as 1 hrs 0 mins 46 secs from a text field (or raw text file) and store them in seconds e.g. 3646 in a numeric field should be the result in this case.

The code I use is below. It takes the 'timespent' field and creates a timespent_seconds field from it. Probably handier ways to code it. But my questions is: Can this be transformation be done in a data step to create a fmtname and a permanent informat, in a similar way as the format was created in the thread: "Create Format to Display Time Interval" as advised by Geniz, solution 3. It would be ideal to hold all this code in an informat for re-use. Then I could do these kind of transformations in 1 or 2 lines for future datasets holding time intervals in this kind of format.


*3. Convert Time Interval field;
*******************************;

*3.1 Set up the Reg Expressions to be used for matching;
***************************************************;
*Put the prx setup in an if obs = 1 statement becaues doesn't need to keep being re-created..;
*Actually if don't do this prxparse runs millions of times and creates memory errors;
if _n_ = 1 then do;
reNum = "/\d+/";
reCNum = PRXPARSE(reNum);
retain reCNum;

reMins = "/\d+\s*mins/"; *builds the regular expression or search pattern. Will be
*looking for text containing char or varcher;
reCMins = PRXPARSE(reMins ); *compiles the expression for SAS, so called reText now;
retain reCMins;

reHours = "/\d+\s*hrs/";
reCHours = prxparse(reHours);
retain reCHours;

reSeconds = "/\d+\s*secs/";
reCSeconds = prxparse(reSeconds);
retain reCSeconds;
end;

*3.2 Extract the hours value;
******************************;
*put "************Matching reg expression" reCMins " against field TimeSpent";
If PRXMATCH(reCHours, timespent) then do;
call prxposn(reCHours, 0, pos, len);
Hours_text = substr(timespent, pos, len);
if prxmatch(reCNum, Hours_text) then do;
*Extracting just the numbers i.e. getting rid of the text characters like Hours or space;
call prxposn(reCNum, 0, pos, len);
Hours_num = substr(Hours_text, pos, len);
end;
end;
if Hours_num = . then Hours_num = 0;

*3.3 Extract the minutes value;
******************************;
*put "************Matching reg expression" reCMins " against field TimeSpent";
If PRXMATCH(reCMins, timespent) then do;
*put "Got a match on minutes for obs no: " _n_;
call prxposn(reCMins, 0, pos, len);
mins_text = substr(timespent, pos, len);
if prxmatch(reCNum, mins_text) then do;
*Extracting just the numbers i.e. getting rid of the text characters like mins or space;
call prxposn(reCNum, 0, pos, len);
mins_num = substr(mins_text, pos, len);
if mins_num = . then mins_num = 0;
end;
end;
if mins_num = . then mins_num = 0;

*3.4 Extract the seconds value;
******************************;
*put "************Matching reg expression" reCSeconds " against field TimeSpent";
If PRXMATCH(reCSeconds, timespent) then do;
*put "Got a match on minutes for obs no: " _n_;
call prxposn(reCSeconds, 0, pos, len);
Seconds_text = substr(timespent, pos, len);
if prxmatch(reCNum, Seconds_text) then do;
*Extracting just the numbers i.e. getting rid of the text characters like Seconds or space;
call prxposn(reCNum, 0, pos, len);
Seconds_num = substr(Seconds_text, pos, len);
end;
end;
if seconds_num = . then Seconds_num = 0;

*3.5 Add the values to get number of seconds in the field;
timespent_seconds = seconds_num + (mins_num * 60) + (hours_num * 60 * 60);
7 REPLIES 7
data_null__
Jade | Level 19
Are you trying to produce ISO 8601 date time intervals similar to the following...

P2Y3M4DT5H6M7S

You did not mention the version of SAS you are using but there are new 9.2 FUNCTIONS, FORMATS and INFORMATS related to this.

Start by looking at CALL IS8601_CONVERT Routine.

I don't have access to 9.2 so I have not bee able to try these function.
IrishGuy
Calcite | Level 5
Data_Null,
I am using SAS 9.1 so don't have access to that functionality and will need to create my own informat
IrishGuy
Calcite | Level 5
Anyone have any thoughts on how to create the informat using the cntlin option? If not I will abandon thread!
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Suggest you create an INFORMAT using the PROC FORMAT INVALUE option, and then use CNTLOUT= to unload the INFORMAT to a SAS dataset for review and dissection.

Scott Barry
SBBWorks, Inc.

SAS DOC on PROC FORMAT INVALUE:
http://support.sas.com/documentation/cdl/en/proc/59565/HTML/default/a002473466.htm
IrishGuy
Calcite | Level 5
Thanks SBB.
Thats probably the right track but I can't easily see how that will get over the issue. The cntlin option allowed for very complex transformations as you can use any data step code and functions to make the transformation from one value to another. But if I use invalue = I seem to be tied to making the transformation within proc format so it looks to be more limited. It may well work fine but am not very experienced in formats etc. Willl hack around with it. If anyone has more detail, great.
Cynthia_sas
SAS Super FREQ
Just as the PUT function and a format can be used to create a new character variable, the INPUT function works with an INFORMAT to create a numeric variable from a character string. The issue that I see is that you may have to break your character string into chunks in order to reassemble them into something that will generate the right time value for you....which gets you back to data step.

A closer look at some of the examples in the doc and searching for prior SUGI and Global Forum papers on the topic should help to show how INPUT/INFORMAT might work.

cynthia
IrishGuy
Calcite | Level 5
Thanks, I will research further along those lines.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 734 views
  • 0 likes
  • 4 in conversation