Custom Datetime Informats

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Custom Datetime Informats

I'm looking to import datetime data that is originally in this format:

m/dd/yyyy [space] [space] hh:mm:ss AM/PM

i.e.

5/20/2012  00:00:00 AM

normally, I could use the MDYAMPMw.d format here, but the second space between years and hours causes this method to fail.

Clearly, the most straightforward method (barring treating it as character data and then stripping it and reformatting it) is to use a custom informat - but all the proc format documentation I have seen fails to deal with this particular (or a suitably applicable) case.

So here's the question: how would the custom informat be built?


Accepted Solutions
Solution
‎08-22-2014 02:42 AM
Valued Guide
Posts: 2,174

Re: Custom Datetime Informats

Andesco,

I don't know why you are being affected by those extra blanks?

I don't have any problem with informat MDYAMPM.

53   data ;

54   infile datalines truncover ;

55   input @1 ts1 ?? mdyampm.

56         @1 ts2 ?? mdyampm19.

57         @1 tLine $char20. ;

58   put (_all_)(=) ;

59   format ts: datetime. ;

60   list;datalines4;

ts1=20MAY12:00:00:00 ts2=20MAY12:00:00:00 tLine=5/20/2012 00:00:00

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

61         5/20/2012  00:00:00

NOTE: The data set WORK.DATA


In what release are you running?

View solution in original post


All Replies
Trusted Advisor
Posts: 1,203

Re: Custom Datetime Informats

Hi,

You can replace double space with :

data have;

dt='5/20/2012  00:00:00 AM';

date=input(transtrn(dt,'  ',':'),MDYAMPM.);

format date MDYAMPM.;

run;

Grand Advisor
Posts: 17,360

Re: Custom Datetime Informats

Are you importing this from a text file or do you already have it in a data set?

New Contributor
Posts: 4

Re: Custom Datetime Informats

Importing from .csv file.

Valued Guide
Posts: 3,206

Re: Custom Datetime Informats

The informat at the picture clause has directives special for DATATYPE=DATE  Base SAS(R) 9.4 Procedures Guide, Third Edition

http://www2.sas.com/proceedings/sugi27/p101-27.pdf  (tedious for recoding - not improved)

---->-- ja karman --<-----
New Contributor
Posts: 4

Re: Custom Datetime Informats

This is one of the resources I went through, but I kept encountering errors in processing the spaces.

Valued Guide
Posts: 2,174

Re: Custom Datetime Informats

The date and time "directives" work in PICTURE statements.

Unfortunately there is no INpicture statement

Valued Guide
Posts: 3,206

Re: Custom Datetime Informats

The trick would be generating a dataset to define an informat statement.  It will work for dates (not too many) not for time (to detailed).
As formats/informats are a kind of loadables (once used as library DD-name). You could think on using an external program/module like SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition (call module)
It will need access to the inputbuffer (often _infile_) and more. No this kind of interfaces is not made public open and available.

It could be an idea for opening those extensions. Allowing 3-rd party building some things.  

---->-- ja karman --<-----
Valued Guide
Posts: 2,174

Re: Custom Datetime Informats

Valued Guide
Posts: 2,174

Re: Custom Datetime Informats

Create and use a user function!

That can preprocess the string and return a number (after applying the regular informat)

There are plenty of examples in the doc, papers and discussion forums.

I think the only other way would be the slightly more complex route of defining the string adjustment in a perl regular expression as the informat "range". Then your 'label' would be the suitable informat....

Like

INVALUE yourInfmtName (just)

  "your perl expression to reorg the string to be valid"

= [stdTimeStampInfmt]

;

There are fewer examples of that.

Good  luck

Peter

Solution
‎08-22-2014 02:42 AM
Valued Guide
Posts: 2,174

Re: Custom Datetime Informats

Andesco,

I don't know why you are being affected by those extra blanks?

I don't have any problem with informat MDYAMPM.

53   data ;

54   infile datalines truncover ;

55   input @1 ts1 ?? mdyampm.

56         @1 ts2 ?? mdyampm19.

57         @1 tLine $char20. ;

58   put (_all_)(=) ;

59   format ts: datetime. ;

60   list;datalines4;

ts1=20MAY12:00:00:00 ts2=20MAY12:00:00:00 tLine=5/20/2012 00:00:00

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

61         5/20/2012  00:00:00

NOTE: The data set WORK.DATA


In what release are you running?

New Contributor
Posts: 4

Re: Custom Datetime Informats

This was correct - it appears that the problem I was actually encountering was due to needing the inputs as DATE not DATETIME - the MDYAMPM informat worked fine otherwise.

It seems that often the most difficult part of a problem is figuring out what the problem *actually* is.

Valued Guide
Posts: 2,174

Re: Custom Datetime Informats

and for that scenario I would suggest reading two values with mmddyy and time but throwing away the time value.

When you have no choice and receive a timestamp with only date value, just assign it the format DTdate9.

There are just a few if these built-in formats which deliver a date style for a datetime value.

(its faster than timepart()

Valued Guide
Posts: 3,206

Re: Custom Datetime Informats

PeterC,  I admit.... Missed it.    The usage of fcmp is not done for updated functionality at the at the proc format (procedures manual 9.4). The usage in a format is found.  Then remembering it somewhere seen... learned.
But it is in some notes like 52103 - Custom function and informat converts a year value to a SAS® date. The starting point makes sense, where the end pointer after reading will be is the be tested (length or processed).

Well that link did not came, I don not explicitly search sascommunity.org .  Reasons:

1- Trusting google (or other) will include that.

2- there are good (like this one) and bad articles there.  It is a wiki based on some persons ideas.

    The hit ratio using 1 is normally good, no reason for additional effort. Finding the correct words/hits is the thinking.

Nice to go into more advanced details of the input processing. I knew this SAS(R) 9.4 Statements: Reference, Third Edition (Modified List Input)   The ?? for modifying error message (forgotten).

Your statement about there is no problem on the number of spaces....

data _null_  ;

input datetm mdyampm30. ;

put datetm datetime. ;

datalines;

  5/20/2012 01:00:00 AM

  5/20/2012    02:00:00 AM

11/20/2012       30:00:03 PM

;

run;

You are right. The only problem I could make is having the field (default 19) being too short.  Making it longer (30) solved the issue.
(tested UE 9.4)

---->-- ja karman --<-----
☑ This topic is SOLVED.

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

Discussion stats
  • 13 replies
  • 888 views
  • 7 likes
  • 5 in conversation