Date and Time format 6/2/2013 5:03:30PM

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

Date and Time format 6/2/2013 5:03:30PM

Hi,

I have a CSV file which have a column that combine time and date in the format 6/2/2013 5:03:30PM

To import this file to SAS, I use:

data abc;

infile 'Path...'

delimiter = ',';

input date_time;

It doesnt work.

I wonder what I should put in either input or format so that SAS can read this variable Time_date correctly?

Thank you for your help.

HC


Accepted Solutions
Solution
‎06-10-2013 06:09 PM
Super User
Super User
Posts: 6,502

Re: Date and Time format 6/2/2013 5:03:30PM

A few things to try.

First I prefer to specify the length of the variables rather than letting SAS guess from their usage in the input statement. Then when reading from delimited file I can just list the variable names without any formats.  SAS might be eating the delimiter after your one letter CD values because of the $2. format on the INPUT statement.

Second are you sure you delimiter is a comma?  You pasted in TAB delimited data. Use DLM='09'x to specify tab delimited instead of comma delimited. (I replaced the tabs with commas in example below so that it would be easier to read as card image data.)

Third looks like your first column is corrupted.

data abc;

  infile cards dsd truncover ;

  length ID 8 CD $2 ticker $7 date_time level exit 8;

  informat date_time  anydtdtm.;

  format date_time datetime.;

  input id cd ticker date_time level exit ;

  put (_all_) (=/);

cards;

2.79E+09,D,GBP/USD,6/2/2013 17:00,1.5207,1.521

;

run;

View solution in original post


All Replies
Super User
Super User
Posts: 6,502

Re: Date and Time format 6/2/2013 5:03:30PM

You need to add an INFORMAT statement so SAS knows how to interpret the text in the CSV file.  A FORMAT statement would also be useful so that you can interpret what SAS will print.

data abc;

  infile cards dsd truncover ;

  informat date_time  anydtdtm.;

  format date_time datetime.;

  input date_time;

  put date_time= ;

cards;

6/2/2013 5:03:30PM

run;

date_time=02JUN13:17:03:30

Super Contributor
Posts: 371

Re: Date and Time format 6/2/2013 5:03:30PM

Thank you, Tom.

I try the code below for my data below but it does not work.

I really appreciate it if you could help me.

HHC

data abc;

infile 'C\test1.csv' dsd truncover 

delimiter=',';

  informat date_time  anydtdtm.;

  format date_time datetime.;

input ID CD $2.  ticker $7.  date_time      level      exit;run;

2.79E+09DGBP/USD6/2/2013 17:001.52071.521
2.79E+09DGBP/USD6/2/2013 17:001.520651.52095
2.79E+09DGBP/USD6/2/2013 17:001.52071.521
2.79E+09DGBP/USD6/2/2013 17:011.520651.52095
2.79E+09DGBP/USD6/2/2013 17:021.520611.52091
2.79E+09DGBP/USD6/2/2013 17:021.520651.52095
2.79E+09DGBP/USD6/2/2013 17:021.520641.52094
2.79E+09DGBP/USD6/2/2013 17:031.51991.5202
2.79E+09DGBP/USD6/2/2013 17:031.519841.52014
Solution
‎06-10-2013 06:09 PM
Super User
Super User
Posts: 6,502

Re: Date and Time format 6/2/2013 5:03:30PM

A few things to try.

First I prefer to specify the length of the variables rather than letting SAS guess from their usage in the input statement. Then when reading from delimited file I can just list the variable names without any formats.  SAS might be eating the delimiter after your one letter CD values because of the $2. format on the INPUT statement.

Second are you sure you delimiter is a comma?  You pasted in TAB delimited data. Use DLM='09'x to specify tab delimited instead of comma delimited. (I replaced the tabs with commas in example below so that it would be easier to read as card image data.)

Third looks like your first column is corrupted.

data abc;

  infile cards dsd truncover ;

  length ID 8 CD $2 ticker $7 date_time level exit 8;

  informat date_time  anydtdtm.;

  format date_time datetime.;

  input id cd ticker date_time level exit ;

  put (_all_) (=/);

cards;

2.79E+09,D,GBP/USD,6/2/2013 17:00,1.5207,1.521

;

run;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 198 views
  • 0 likes
  • 2 in conversation