> -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of > Núria Chapinal > Sent: Tuesday, March 08, 2011 12:15 PM > To: SAS-L@LISTSERV.UGA.EDU > Subject: Re: problems importing datetimes from csv files > > Not accepted.... > > > > Núria Chapinal > Postdoctoral fellow > Animal Welfare Program, University of British Columbia > Department of Population Medicine, University of Guelph > > > > --- On Tue, 3/8/11, Nat Wooding <nathani@VERIZON.NET> wrote: > > > From: Nat Wooding <nathani@VERIZON.NET> > Subject: Re: problems importing datetimes from csv files > To: SAS-L@LISTSERV.UGA.EDU > Received: Tuesday, March 8, 2011, 3:06 PM > > > Nuria > > In my personal copy of TS486, there are two informats which apparently > are > not documented by SAS but which someone discovered and posted on SASL > some > years ago. I would try MDYAMPMw.d > > > Nat Wooding > > (from my TS486) > > 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 > > >    The MDYAMPMw.d format is also available and requires a datetime > value as >    input. > >    data a; >   x=datetime(); >   put x=mdyampm.; >    run; > > >    x=7/21/2006 1:57 PM > > -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of > Nuria > Chapinal > Sent: Tuesday, March 08, 2011 2:55 PM > To: SAS-L@LISTSERV.UGA.EDU > Subject: problems importing datetimes from csv files > > Hi, > > I am trying to import csv files that have a datetime column. > I can't manage to get it in the right format in the final SAS dataset. > I > have tried different strategies, and this is the best one, which gives > me > a very long number in the datetime column.... > > In the csv file, the datetime is in this format: 03-08-2010 11:09:18 > AM > > My SAS code is like this: > > filename FT46F001 'C:\Users\nuria\Desktop\hobo\c\*.csv' lrecl=256; > data nc; >    length fname $256; >    infile FT46F001 dlm=',' dsd eov=eov filename=fname truncover ; >    *dsd before dlm would treat 2 consecutive delimiters as missing > value > (the second); >    *dlm is a tabulation; >    input @; >    if _n_ eq 1 or eov then do; >    *filename=fname; > >    calf =input( scan (input (scan(fname,-1,'_'), $12.),1), 12.); >    retain filename calf; >    eov = 0; >  input #2; >    delete; >    end; >   input obs: 9.   dtx:$30. (y z) (:9.) ; >    datetime = input(dtx,anydtdtm22.); > run; > >

Nuria,

Are you sure that your code isn't reading the datetime value properly? I would expect it to result in a "very long number." It is the number of seconds since Jan 1, 1960. Did you try formatting the datetime variable with an appropriate format, like DATETIME22. ?

Dan

Daniel J. Nordlund Washington State Department of Social and Health Services Planning, Performance, and Accountability Research and Data Analysis Division Olympia, WA 98504-5204

>