Converting CSV mm:ss.s format to SAS time12.3

Reply
Occasional Contributor
Posts: 16

Converting CSV mm:ss.s format to SAS time12.3

Hi all,

I have a csv file where the 'time' variable is formatted as mm:ss.s (ie the cell shows 03:48.7). I know that it is holding sufficient information to display the hours, minutes, seconds, and down to the centiseconds.

Is there a quick painless way to import this csv into SAS and have 'time' formatted as time12.3 (ie the 'time' variable in SAS displays eg. 1:03:48.69 instead). I know one of the way would be to open each csv files, format the 'time' variable and save it before importing it into SAS. But with more than 20 files and each taking over a minute to load, it is not really practical.

Any help is very much appreciated. Thank you in advance.

Super User
Posts: 10,019

Re: Converting CSV mm:ss.s format to SAS time12.3

Posted in reply to Brian_Chee

Use informat STIMER.

Code: Program

data have;
input x : stimer10.;
format x time10.1;
cards;
03:48.7
;
run;
Super User
Posts: 11,343

Re: Converting CSV mm:ss.s format to SAS time12.3

Posted in reply to Brian_Chee

You don't mention how your are getting the CSV into SAS. If you have been using Proc import and the variables are coming in with any time related format (you don't say) then you can change the display format using proc datasets to time12.3.

If you have many files of similar format it is well worth the time to create a data step program to read the files and control the informat / format. If you have used proc import in base SAS the log will have the data step code SAS created to read the file. You can copy, edit and run to your hearts content just changing the infile and output data set name.

Occasional Contributor
Posts: 16

Re: Converting CSV mm:ss.s format to SAS time12.3

Posted in reply to Brian_Chee

Thanks for all the help, but I've found the problem. I will share it anyway.

My original csv is really huge and takes hours to import to SAS. So I opened it in excel and copied and pasted a small portion of it to do a test run. When I save the new csv file, all the original formatting was erased.

As I proc import the new csv dataset to sas all variables are formatted as text. I do not think it was possible to format a text "55:04:078" to time12.3 of "11:55:04:078". 

The code I used was,

proc import datafile="C:have.csv"

     out = want

     dbms = csv

     replace;

     getnames=no;

run;

I set getnames=no because the variable names in the csv file have space in between them and that was giving me problems earlier.

Anyway, problem 'solved'. I'm still a rookie, but all this silly mistakes are really helping me to learn and of course, thanks to all the supports I get here!

Ask a Question
Discussion stats
  • 3 replies
  • 327 views
  • 0 likes
  • 3 in conversation