How to import time of hh:mm:ss AM

[hhchenfx]

Good morning,

I have a scv file with time of the format hh:mm:ss AM. I try different format but still fail to import it to SAS.

All the time, missing data for the whole column.

Any help is very much appreciated.

Thank you,

HHC

Data

2006.01.30 12:00:00 AM

2006.12.30 12:00:00 AM

2006.12.25 12:00:00 AM

The code I use right now is:

data originaldata;

infile 'C:\test.csv'

delimiter=',';

   input date :yymmdd8. time ;

   format date yymmdd8.;

   format time ___________;

run;

[Tom]

The TIME informat will read the AM/PM, if you tell it to read enough characters.

data want;

  input date yymmdd10. time time11. ;

  format date date9. time time8. ;

  put date time;

cards;

2006.01.30 12:00:00 AM

2006.12.30 11:00:00 PM

2006.12.25 12:00:00 PM

run;

[hhchenfx]

Thanks, Tom.

When I try your code as below, it still doesn't work. All missing on Time column.

One thing in csv file is that: the time appear on screen is 0:00 for 12:00:00 AM, 1:00 for 1:00:00 AM. I see the full minutes and second (1:00:00 AM) when I click on that cell.

So I don't know if it is something that cause my trouble.

HHC

data originaldata;

infile 'C:\test.csv'

delimiter=',';

   input date :yymmdd8. time time11.;

   format date yymmdd8.;

   format time  time8.;

run;

[Tom]

CSV files do not have "cells".  Are you sure that it is two fields in the CSV file?  That is, do you see a comma between the date and time strings?  If you are having trouble seeing the actual text of the CSV then open it in the SAS editor or some other text editor or add a LIST statement to your data step..

You might need to read it as a character variable and then generate the date and time from that.

data want;

  infile cards dsd dlm=',' truncover ;

  input dt :$22. ;

  date = input(scan(dt,1,' '),yymmdd10.);

  time = input(substr(dt,length(scan(dt,1,' '))+2),time11.);

  format date date9. time time8. ;

  put date time;

cards;

2006.01.30 12:00:00 AM

2006.12.30 11:00:00 PM

2006.12.25 12:00:00 PM

run;

[hhchenfx]

I just get back and check.

The file type is: Microsoft Excel Comma Separated Value File.

When I open it, it opens in Excel and therefore I see the

" time appear on screen is 0:00 for 12:00:00 AM, 1:00 for 1:00:00 AM. I see the full minutes and second (1:00:00 AM) when I click on that cell."

To check if it has comma, I right click the file and chose to OPEN with Notepad. Then the data look like that.

So look like it depends on the software I use to open, I see different things.

2005.06.28,00:00

2005.06.29,1:00

2005.06.30,5:00

In this case what should I do?

Thank you, Tom.

HHC

[Tom · Accepted solution]

You should be able to read that with TIME. informat.

Try it this way.

data want;

  infile cards dsd dlm=',' truncover ;

  informat date yymmdd10. time time11. ;

  input date time;

  format date date9. time time8. ;

  put date time;

cards;

2005.06.28,00:00

2005.06.29,1:00

2005.06.30,5:00

run;

Some things check if does not work for you.

• Not using the TRUNCOVER option on the INFILE statement might what is tripping you up since your actual time values are much shorter than 11 characters.
• Are there "invisible" characters after the time value on the line?  Add the LIST statement after the INPUT statement and SAS will display what is in the file. If there are unprintable characters in a line it will display the hexadecimal values for all of  the characters in the line.
• If your SAS is running on Unix and the file was generated on a PC then you might have carriage return character at the end of the line that is making the time string invalid.  You can fix that by adding TERMSTR=CRLF to the INFILE statement OR by changing the delimiter from ',' to '2C0D'x (hex codes for comma and carriage return).

[hhchenfx]

Hi Tom,

Thank you so much for your help.

It works nicely.

Have a nice day.

HHC