Thursday, September 16, 2010

How to convert the datetime character string to SAS datetime value? (ANYDTDTM and MDYAMPM formats)

When we have a string like this "9/01/2010 11:52:54 AM" and would like to translate the string to a numeric SAS date time variable, most of the times we use SCAN function to extract the information to get the DATETIME format. This is definitely a tedious job.

SAS formats (MDYAMPM, ANTDTDTM) comes to rescue us. Here is how it works.

data test;
length date $25;

date="9/01/2010 11:52:54 AM";
*Convert the character string to SAS datetime value;
datetimevar =input(date,mdyampm25.2);
datetimevar1 =input(date,anydtdtm20.);
*Apply format to the SAS date time value;
format datetimevar datetimevar1 datetime19.;
run;

Result: 01SEP2010:11:52:54


*ANYDTDTM and MDYAMPM informats work together when the datetime value has AM PM specified or day, month, and year components are not ambiguous.

The MDYAMPMw. format writes datetime values with separators in the form mm/dd/yy hh:mm AM PM, and requires a space between the date and the time.
The ANYDTDTM w. format writes datetime values with separators in the form dd/mm/yy hh:mm AM PM, and requires a space between the date and the time.


When a value is read with ANYDTDTMw. informat and the style of the value is dd/dd/dd(dd) tt:tt:tt AM
PM, the MDYAMPMw.d informat is called to read the value. If the AM PM component is not present, the MDYAMPMw.d informat is used as long as the month and day components aren't ambiguous. If they are ambiguous, the value of the DATESTYLE= system option is used to determine the order of month, day, and year components.

MDYAMPMw.d Format
_______________________________________

Writes datetime values in the form mm/dd/yy hh:mm AM PM. The year can be either two or four digits.

Details

The MDYAMPMw.d format writes SAS datetime values in the following form:

mm/dd/yy hh:mm

The following list explains the datetime variables:

mm                     is an integer from 1 through 12 that represents the month.

dd                       is an integer from 1 through 31 that represents the day of the month.

yy or yyyy         specifies a two-digit or four-digit integer that represents the year.

hh                       is the number of hours that range from 0 through 23.

mm                     is the number of minutes that range from 00 through 59.

AM PM             specifies either the time period 00:01-12:00 noon (AM) or the time period 12:01-12:00   midnight (PM). The default is AM.

date and time separator characters     is one of several special characters, such as the slash (/), colon (:), or a blank character that SAS uses to separate date and time components.

Source:  SAS(R) 9.2 Language Reference: Dictionary, Third Edition

Examples:

These examples illustrate how the ANYDTDTMw. informat reads values based upon an AM PM specification and the DATESTYLE= system option.
--------------------------------------------------------------------------------
/* Since AM is specified with the value, the ANYDTDTM informat  is called to read the datetime value. */

options datestyle=dmy;


data test1;
format xtext $22. xdate DATETIME18.;
xtext="07/01/2008 12:30:00 AM";
xdate=input(xtext,ANYDTDTM30.);

proc print;
run;

/* Since AM PM aren't specified and the month and day components  are ambiguous, the DATESTYLE= system option is used to determine their order. */

options datestyle=dmy;


data test2;
format xtext $22. xdate DATETIME18.;
xtext="07/01/2008 12:30:00";
xdate=input(xtext,ANYDTDTM30.);


proc print;
run;
Source: http://support.sas.com/kb/37/309.html


If you have a date "Saturday, November 01, 2008"  and would like to convert it to numeric with DDMMYY10. format here is the way to do it.

data fmtchnge;length worddate $40;
worddate = "Saturday, November 01, 2008";
dmyfmt= input(substr(worddate,findc(worddate,',')+1),anydtdte30.);
*dmyfmt=input(substr(worddate,index(worddate,',')+2),anydtdte32.);

format dmyfmt mmddyy10.;
run;



 
 ('DiggThis’)
ShareThis