I'm importing a .txt
file into SAS and I need to convert a time column in mm:ss
format into a numeric variable in the informat part (eg: 1:06 would become 66 seconds).
I've tried using time
, time4
, and time8
informats but they interpret the input as hh:mm:ss
and not mm:ss
.
A | Time |
---|---|
Peter | 2:12 |
Griffin | 3:30 |
DATA song;
INFILE 'file.txt' DLM=',' DSD;
INPUT Name $ duration :time4.;
RUN;
Is there a way to have the SAS informat interpret the mm:ss
properly?
If the values always have just the one colon then divide by 60 to convert HH:MM to MM:SS.
DATA song;
INFILE 'file.txt' DLM=',' DSD;
INPUT Name :$20. duration :time.;
duration = duration/60;
RUN;
Note there is not need to add a width to the TIME informat (or really any numeric informat) because when using LIST MODE input the width specified is ignored. For character variables the actual INPUT will also ignore the width specified, but the additional side effect of giving the data step information it can use to guess the storage length can be of value.
If sometimes the values have hours minutes and seconds then count the colons to decide if you need to divide by 60. Either by reading the value as character or testing the automatic variable _INFILE_.
DATA song;
INFILE 'file.txt' DLM=',' DSD;
INPUT Name :$20. duration :time.;
if countc(_infile_,':') < 2 then duration = duration/60;
RUN;