topic CSV file, datetime conversion problem in SAS Procedures

CSV file, datetime conversion problem

deleted_user — Fri, 24 Oct 2008 19:29:46 GMT

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?

Re: CSV file, datetime conversion problem

Cynthia_sas — Sat, 25 Oct 2008 13:06:30 GMT

Hi:
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
ddmmmyy hh:mm:ss.ss .) But the "ANY" informat should work (statement below assumes all vars are numeric):
[pre]

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

[/pre]

cynthia

Re: CSV file, datetime conversion problem

deleted_user — Tue, 28 Oct 2008 03:03:21 GMT

Thanks for the tip...works 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. ;

.
.
.

input
logger_id
date_time
.
.
.