How to import time of hh:mm:ss AM

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

How to import time of hh:mm:ss AM

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;


Accepted Solutions
Solution
‎09-04-2013 01:13 PM
Super User
Super User
Posts: 6,495

Re: How to import time of hh:mm:ss AM

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).

View solution in original post


All Replies
Super User
Super User
Posts: 6,495

Re: How to import time of hh:mm:ss AM

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;

Super Contributor
Posts: 371

Re: How to import time of hh:mm:ss AM

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;

Super User
Super User
Posts: 6,495

Re: How to import time of hh:mm:ss AM

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;

Super Contributor
Posts: 371

Re: How to import time of hh:mm:ss AM

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

Solution
‎09-04-2013 01:13 PM
Super User
Super User
Posts: 6,495

Re: How to import time of hh:mm:ss AM

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).
Super Contributor
Posts: 371

Re: How to import time of hh:mm:ss AM

Hi Tom,

Thank you so much for your help.

It works nicely.

Have a nice day.

HHC

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 2142 views
  • 5 likes
  • 2 in conversation