BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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?
2 REPLIES 2
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
.
.
.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 4534 views
  • 0 likes
  • 2 in conversation