Help using Base SAS procedures

CSV file, datetime conversion problem

Posts: 0

CSV file, datetime conversion problem

Hi Learned SAS Users,
I've researched this with no success. I have VERY large CSV files containing datetime variables (with lots of other data), and I can read these successfully using the IMPORT wizard successfully IF I convert the data to MS 2003 Excel files. However, I have had no luck converting the datatime values just reading them in with the IMPORT wizard as CSV, or trying my hand with inputs (and informats)

A typical data line looks like:
3,2008-04-22 12:10:00.0,1504.127,2352.149,15.703,15.451

where the second data value is a datetime string.

The error I get is 'invalid data for' time (second variable).

Any suggestions?
Posts: 8,861

Re: CSV file, datetime conversion problem

Posted in reply to deleted_user
The data step INFILE and INPUT should work for you. There are some "ANY" informats that you should be able to find in the INFORMAT list. The ANYDTDTE informat extracts the date part from the derived value. The ANYDTDTM informat extracts the datetime part. The ANYDTTME informat extracts the time part. If you read with ANYDTDTM, for example, to get the complete datetime value, then you can use the DATEPART and TIMEPART functions to create new variables for just date and/or just time if you need to.

The way you read with a special informat is to use the colon modifier to tell SAS which informat to use -- other than the standard numeric informat. (For your value, as shown in your posting, the standard DATETIME informat won't work because your date is not in the "standard" form of
.) But the "ANY" informat should work (statement below assumes all vars are numeric):

infile 'c:\temp\myfile.csv' dsd dlm=',';
input id date_time : anydtdtm21. var1 var2 var3 var4;
dateonly = datepart(date_time);
timeonly = timepart(date_time);


Posts: 0

Re: CSV file, datetime conversion problem

Posted in reply to Cynthia_sas
Thanks for the great!

Here's how I used that informat:

infile 'C:\perry\carbonscience\SasProgs\sens7_8-10.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat logger_id best32. ;
informat date_time anydtdtm21. ;
format logger_id best12. ;
format date_time anydtdtm21. ;


Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation