Date:         Thu, 15 Oct 2009 13:47:46 -0700
Reply-To:     "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Subject:      Re: Reading Excel datetime variable into SAS
Comments: To: Annie Lee <hummingbird10111@HOTMAIL.COM>
In-Reply-To:  A<200910152018.n9FG1q7w006273@malibu.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

Hi Annie,

One approach is to add the following
expression contained in the sample
datastep below into your datastep.




data _null_;
    DateStringFromExcel = '08/01/2008 17:18';

    SASDateTime = input(substr(DateStringFromExcel,1,10),mmddyy10.)
                  *86400+input(substr(DateStringFromExcel,12),time.);

    format SASDateTime datetime19.;
    put _all_;
run;




Hope this is helpful.


Mark Terjeson
Investment Business Intelligence
Investment Management & Research
Russell Investments
253-439-2367


Russell
Global Leaders in Multi-Manager Investing






-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Annie Lee
Sent: Thursday, October 15, 2009 1:18 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Reading Excel datetime variable into SAS

Hi,

I would like to read Excel file with ("mydatetime") variable (cell
format -
General in Excel)

I tried the following codes to read the variable ("mydatetime") into
SAS.
All other variables in Excel file were imported into SAS except for the
("mydatetime") variable.

Question1:

I would like to use this ("mydatetime") variable including the time
portion
to calculate time differences and be able to covert it to SAS datetime
variable.

Question2:
I also would like to know once this Excel variable ("mydatetime") is
read as
datetime17. in SAS, can it be changed to datetime19. so that I can
calculate
time differences with other datetime19. variables. in SAS?
Thank you.


Data:
("mydatetime") variable in Excel (General cell format) looks like:
08/01/2008 17:18

Results:

I would like this variable ("mydatetime") to be (Number 8 Datetime17.)
in SAS:
01AUG2008:17:18:00



Codes tried:

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

Data sheet1 ;
Format mydatetime datetime17. ;
Set xlsLib."sheet1$"n (dbSasType=( mydatetime=dateTime )) ;
Run ;
LibName xlsLib clear ;