Date:         Wed, 2 Sep 2009 16:29:07 -0500
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: csv/excel 2007/datetime informats
Comments: To: "Hondorp, Darryl" <hondorpd@si.edu>
In-Reply-To:  <01CF3EE967013C4985DD15A9E3FDB8DF01D5A773@SI-ECL01.US.SINET.SI.EDU>
Content-Type: text/plain; charset=windows-1252

Glad to hear you found the issue.  Unfortunately, CSV isn't really a
standard format in terms of data - it's only standard in the sense of
"values separated by commas".  What goes into making those values is highly
variable; you could argue that Excel should put the underlying value
[whatever Excel's equiavelent of 1496275200 is], the IE datetime value, or
the formatted value [as it apparently does] in there, or half a dozen other
things.  Depending on how you run it, SAS might do either the first or the
last of those itself...

The best option might be to force Excel to use the SAS Datetime standard
format.  Then you get the easiest conversion possible...

Also, if you upgraded to SAS 9.2, you could read in the .xlsx file directly
instead of going via csv (and also license SAS Access to PC Files).

-Joe

On Wed, Sep 2, 2009 at 4:20 PM, Hondorp, Darryl <hondorpd@si.edu> wrote:

>  Joe,
>
>
>
> I apologize.   You were right.  I looked at another pair of files in a text
> editor and there appears to be a difference in how the two versions of excel
> are formatting the datetime values.  Guess I didn’t look closely enough last
> time.
>
>
>
> -Darryl
>
>
>
> Darryl W. Hondorp
>
> Postdoctoral Fellow
>
> Smithsonian Environmental Research Center
>
> PO Box 28
>
> Edgewater, MD 21037
>
> voice:  443-482-2333
>
> fax:  443-482-2295
>
> hondorpd@si.edu
>
> *From:* Joe Matise [mailto:snoopy369@gmail.com]
> *Sent:* Wednesday, September 02, 2009 4:29 PM
> *To:* Hondorp, Darryl
> *Cc:* SAS-L@listserv.uga.edu
> *Subject:* Re: csv/excel 2007/datetime informats
>
>
>
> What version of SAS do you have?
>
> In general, I'm a bit confused by your email.  Are you creating a CSV in
> both?  If so, can you view it in a text editor and see what the difference
> is?  Perhaps Excel is doing something differently.
>
> Another interesting thing - read it in through SAS datalines.  You get a
> slightly different result!  Why?  Because it's not actually a valid datetime
> value.  You get 1496275200 which is 6/1/2007 0:00:00.  Technically it should
> be 6/1/2007:00:45:00 to read it in properly.
>
> When I go into excel (2007) and create the csv, I get sometimes a good
> result [read in] and sometimes bad.  The times when I get a successful
> result, it displays not as a datetime in excel but as the actual values
> 6/1/2007 00:45:00.  When it displays as 1-Jun it doesn't work.  That's what
> is being saved to the CSV - not the underlying value, but '1-Jun'.  You
> might try formatting the column in Excel to get the right result saved to
> the CSV.  I think that's the major issue.
>
> -Joe
>
>  On Wed, Sep 2, 2009 at 3:12 PM, Darryl Hondorp <hondorpd@si.edu> wrote:
>
> Our laboratory recently upgraded to the latest version of excel (excel
> 2007, I believe).  Since the upgrade, SAS is not correctly inputting
> datetime values from data files created (or saved) in the new version.  I
> am importing data using infile with the date/time informat set to
> ANYDTDTMw.  For example, a variable named DATE_TIME contains the
> value '6/1/2007 0:45.'  If the data file containing this variable was
> created in an older version of excel, sas correctly inputs this
> observation as 1496277900.  However, if the data file was created in the
> latest version of excel(or if a file created with an older version was
> opened and saved in the latest version) , sas reads this same observation
> as a missing value ('.').
>
> Does anybody know what excel is doing to datetime values to cause this
> problem (and how to correct it)?
>
>
>