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:  <200910201648.n9KAqnfk019022@malibu.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

Annie

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;

   produces:

     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

Hi,

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.