LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 2009, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 20 Oct 2009 13:14:19 -0400
Reply-To:     Nathaniel Wooding <nathaniel.wooding@DOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Nathaniel Wooding <nathaniel.wooding@DOM.COM>
Subject:      Re: reading excel datetime with AM PM
Comments: To: Annie Lee <hummingbird10111@HOTMAIL.COM>
In-Reply-To:  <>
Content-Type: text/plain; charset="us-ascii"


There are a couple undocumented informats that may be of use. I copied this out of my personal copy of TS486. I think that someone posted them on the L a while back.

Undocumented informats YMDDTTMw.d and MDYAMPMw.d and format MDYAMPMw.d --------------------------------------------------------------------------------

The MDYAMPMw.d and YMDDTTMw.d informats are available beginning in Release 8.2, but are not documented. MDYAMPMw.d reads values of the form:

mm/dd/yy[yy] hh:mm[:ss[.ss]] [am|pm]

and returns a datetime value. YMDDTTM reads values of the form:

yy[yy]/mm/dd hh:mm[:ss[.ss]]

Note in the example below that YMDDTTMw.d does not read "AM|PM" values. Also note that "AM|PM" values are optional for MDYAMPMw.d.

data _null_; x='2005/6/30 12:30'; y='6/30/2005 12:30am'; z='2005/6/30 12:30am'; a='6/30/2005 12:30'; ymd_no_ampm=input(x,ymddttm16.); mdy_ampm=input(y,mdyampm18.); ymd_ampm=input(z,?? ymddttm18.); mdy_noampm=input(a,mdyampm16.); put (ymd_no_ampm mdy_ampm ymd_ampm mdy_noampm) (= dateampm. /); run;


ymd_no_ampm=30JUN05:12:30:00 PM mdy_ampm=30JUN05:12:30:00 AM ymd_ampm=. mdy_noampm=30JUN05:12:30:00 PM

Nat Wooding

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Annie Lee Sent: Tuesday, October 20, 2009 12:49 PM To: SAS-L@LISTSERV.UGA.EDU Subject: reading excel datetime with AM PM


I am trying to read EXCEL datetime cell format with AM PM. I would like to learn how to deal with AM PM portion. Thank you.

Excel Data: 1/1/2000 4:23:00 AM 1/1/2000 5:15:00 PM

Results I would like in SAS: 01JAN2000:04:23:00 01JAN2000:17:15:00

Codes I tried:

LibName xlsLib ".\Demo.xls" mixed=yes stringDates=yes scanTime=yes ;

Data tod00 ; Set xlsLib."sheet1$"n ; Run ;

LibName xlsLib clear ;

data tod0; set tod00;

dod_tod1 = input(substr(mytod,1,10),mmddyy10.)*86400 + input(substr(mytod,12),time.); format mytod1 datetime19.;

run; CONFIDENTIALITY NOTICE: This electronic message contains information which may be legally confidential and or privileged and does not in any case represent a firm ENERGY COMMODITY bid or offer relating thereto which binds the sender without an additional express written confirmation to that effect. The information is intended solely for the individual or entity named above and access by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution, or use of the contents of this information is prohibited and may be unlawful. If you have received this electronic transmission in error, please reply immediately to the sender that you have received the message in error, and delete it. Thank you.

Back to: Top of message | Previous page | Main SAS-L page