3

I am an R user and SAS beginner trying to read a csv file into SAS. The problem I am facing is with a column called "TIME" which contains time data in the format "hh:mm " for e.g. "12:23 PM". In R it is as simple as as.POSIXct(df$TIME, format = "%I:%M %p") and my hour is converted to time values instantly (with time-zone and today's date which can be removed).

This is how I tried to implement this in SAS: 

/* firstly `rename` "TIME" to "DAY_HOUR" */
data mid.prac1;
set mid.prac1;
rename time = DAY_HOUR;
run;
/*runs successfully */

/* remove unwanted characters from DAY_HOUR */
data mid.prac2;
set mid.prac1;
DAY_HOUR = compress(DAY_HOUR, 'PMAM');
proc print; run;
/* runs successfully */

/* format hh:mm as time */
data mid.prac3;
set mid.prac2;  
informat DAY_HOUR time10.;
run;
**/*Error: ERROR 48-59: The informat $TIME was not found or could not be loaded.*/**

The time informat does not exist? I read this documentation on the SAS website: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000201204.htm and I think I am doing it right. I am new to the whole SAS universe so apologies if I am not following conventions in asking questions and pasting sample data etc.

vagabond
  • 3,526
  • 5
  • 43
  • 76
  • Look at these mate. http://support.sas.com/documentation/cdl/en/etsug/60372/HTML/default/viewer.htm#etsug_intervals_sect011.htm#etsug.intervals.itvformats2 – dannmate Dec 07 '14 at 23:38
  • looked and tried : `informat DAY_HOUR HHMM10.;` . same error. "ERROR 48-59: The format $HHMM was not found or could not be loaded." – vagabond Dec 07 '14 at 23:42

1 Answers1

3

Just parse it through the input function:

DAY_HOUR_FOR_ME = INPUT(DAY_HOUR,TIME10.);

This should convert the DAY_HOUR character variable to a DAY_HOUR_FOR_ME numeric variable. If you need to format this as a time, use the FORMAT statement:

FORMAT DAY_HOUR_FOR_ME time10.;

OK...

FORMAT DAY_HOUR_FOR_ME timeampm11.;

Informats

Informats are used by SAS to read data. So, given a string looking like this:

09:00:00

You can ask SAS to convert this to a numeric representation of 9AM, by submitting:

time_i_want = input(text,time8.);

Where 8 is simply the length of the string text. SAS will then store a variable time_i_want as the number of seconds between 00:00 and 09:00.

Formats

Formats are used to display data in a usable format. If I asked you the time and you told me how many seconds there had been since the 1st January 1960, I'd be displeased. However, this is how SAS stores DATETIME values:

'01JAN1960:00:01:00'dt = 60;
'31DEC1959:23:59:30'dt = -30;

etc.

So, the FORMAT statement can be used in any datastep to force the resulting dataset (that which is named in the DATA statement) to display values in a particular format.

TIMEAMPM

is one such format. However, TIMEAMPM is not an INFORMAT

Informats and formats

Some informats are mirrored by equivalent formats. TIME8. is an example. You can ask SAS to read in a time value using an informat:

01:00:32

This is then stored as a numeric value 3632. It can then be formatted using the TIME8. format, which displays it as such:

01:00:32

mjsqu
  • 5,151
  • 1
  • 17
  • 21
  • Thanks ! Follow up : Can I keep the AM / PM indicator somehow or convert it to a 24 hour clock otherwise I am losing that information . . . – vagabond Dec 07 '14 at 23:48
  • Yep, the value is kept as a time value containing the number of seconds since midnight (00:00), so you can find the right format to display AM/PM if needed. Google 'SAS Time formats by category' to save me some typing. – mjsqu Dec 07 '14 at 23:49
  • Cheers for the accept/upvote though, much appreciated. – mjsqu Dec 07 '14 at 23:50
  • http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000201272.htm , found the page and tried but not working ! `data mid.prac4; set mid.prac1; DAY_HOUR_FOR_ME = input(DAY_HOUR, timeampm10.); format DAY_HOUR_FOR_ME timeampm11.; run;` ERROR 48-59: The informat TIMEAMPM was not found or could not be loaded. – vagabond Dec 07 '14 at 23:59
  • `TIMEAMPMP` is not an INFORMAT. Informats are used to read data, so SAS doesn't like it when you try to use them in `FORMAT` statements. Keep `INPUT(DAY_HOUR,TIME10.)` as such, don't use `TIMEAMPM` in that statment at all. – mjsqu Dec 08 '14 at 00:04
  • Should I try: `data mid.prac4; set mid.prac1; DAY_HOUR_FOR_ME = input(DAY_HOUR, time10.); format DAY_HOUR_FOR_ME timeampm10.; run;` ? – vagabond Dec 08 '14 at 00:06
  • That seems to have worked . . it does not appear like the second of the day but AM / PM . . now to try running some difference calculations and check if it is working correctly . . Thanks ! Surprising this hasn't come up on SO before . . – vagabond Dec 08 '14 at 00:10
  • Happy to accept edits to this post, but I think I've covered informats and formats. There must be another SO post that covers it as @vagabond says. – mjsqu Dec 08 '14 at 00:17
  • Thanks @mjsqu . That's extensive! Upvote the Q if you think it's worthwhile. – vagabond Dec 08 '14 at 00:19
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/66370/discussion-between-mjsqu-and-vagabond). – mjsqu Dec 08 '14 at 00:20