topic Create Informat to Derive Time Interval In Seconds in SAS Procedures

Create Informat to Derive Time Interval In Seconds

IrishGuy — Fri, 31 Oct 2008 16:04:55 GMT

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);

Re: Create Informat to Derive Time Interval In Seconds

data_null__ — Fri, 31 Oct 2008 17:26:35 GMT

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.

Re: Create Informat to Derive Time Interval In Seconds

IrishGuy — Mon, 03 Nov 2008 09:39:38 GMT

Data_Null,
I am using SAS 9.1 so don't have access to that functionality and will need to create my own informat

Re: Create Informat to Derive Time Interval In Seconds

IrishGuy — Mon, 10 Nov 2008 10:31:49 GMT

Anyone have any thoughts on how to create the informat using the cntlin option? If not I will abandon thread!

Re: Create Informat to Derive Time Interval In Seconds

sbb — Mon, 10 Nov 2008 14:20:57 GMT

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

Re: Create Informat to Derive Time Interval In Seconds

IrishGuy — Tue, 11 Nov 2008 12:30:32 GMT

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.

Re: Create Informat to Derive Time Interval In Seconds

Cynthia_sas — Tue, 11 Nov 2008 12:35:09 GMT

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

Re: Create Informat to Derive Time Interval In Seconds

IrishGuy — Tue, 11 Nov 2008 12:53:27 GMT

Thanks, I will research further along those lines.