topic read in time format (like 12:02:27 AM) in SAS Programming

read in time format (like 12:02:27 AM)

fengyuwuzu — Fri, 15 Jan 2016 21:30:40 GMT

  data WORK.test    ;
    infile 'data.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
       informat ID $40. ;
       informat CreatedDT anydttme.  ;
       informat EndDT anydttme.  ;
       informat PeriodInDays best32. ;
       informat PeriodType $14. ;
       format ID $40. ;
       format CreatedDT time11. ;
       format EndDT time11. ;
       format PeriodInDays best12. ;
       format PeriodType $14. ;
    input
                ID $
                CreatedDT 
                EndDT 
                PeriodInDays
                PeriodType $
    ;
    run;

I am using the the above code to read in a csv file, with two time variables in the format like 12:02:27 AM. However, after reading in, these two variables have blank columns, and there is no error message in the log.

can anyone give some hints?

Thank you

Re: read in time format (like 12:02:27 AM)

Steelers_In_DC — Fri, 15 Jan 2016 21:45:03 GMT

I think this will work for you:

data test;
informat time anydtdtm11.;
format time timeampm11.;
input time;
cards;
12:02:27 AM
;run;

Re: read in time format (like 12:02:27 AM)

FreelanceReinh — Sat, 16 Jan 2016 14:26:08 GMT

@Steelers_In_DC: It's true that informat ANYDTDTM11. can read time values of the form HH:MM:SS AM/PM, but your data step does not prove this. You are using modified list input with the default delimiter ' ' (blank). That is, your input statement reads "12:02:27", but not the "AM" after the blank. If you change the "AM" in your data line to "PM" (or to an invalid string such as "XY"), you will see that TIME will continue to show as 12:02:27 AM. You could use the "&" modifier (input time &;) or change the delimiter (infile cards dlm=',';) or use formatted input (input time anydtdtm11.;) to read the value correctly.

@fengyuwuzu: Your code is syntactically correct. Informat ANYDTTME. is a good choice to read values like 12:02:27 AM. (There is no date part to be read, hence no need to apply a datetime informat such as ANYDTDTM., although the result would be the same. You are also right not using a length specification, e.g. "11" in the informat. This would be ignored anyway in the reading process, as you don't use formatted input [cf. documentation of the INFORMAT statement].)

So, the reason why you obtain only missing values for the CreatedDT and EndDT variables will be found in your data. Please note that the ANYDTTME. informat is very tolerant: Unlike, e.g., the TIME. informat, it does not create "Invalid data ..." messages even if your time value is the string "blahblah". Could you please attach a single line (or a few lines) of your csv file with data, so we can see why it is not being read correctly?

Alternatively, you could temporarily replace anydttme. with time. in your INFORMAT statements and then post the log messages for your data step. The TIME. informat is also suitable to read values like 12:02:27 AM, but it's more likely to produce an "Invalid data ..." message, if the data are in fact invalid.

Re: read in time format (like 12:02:27 AM)

Tom — Sat, 16 Jan 2016 15:08:40 GMT

The example value you listed works fine for me. Note that I find that it works better to explicitly define the variable types rather than asking SAS to guess how I want them defined by the INFORMAT or FORMAT that I have attached to them. Also attaching INFORMATS or FORMATS like $14. do not add any value and can cause trouble later if want to change the length of the variable.

data WORK.test    ;
    infile cards dsd TRUNCOVER firstobs=2 ;
    length ID $40 CreatedDT EndDT PeriodInDays 8 PeriodType $14 ;
    informat CreatedDT anydttme.  ;
    informat EndDT anydttme.  ;
    format CreatedDT time11. ;
    format EndDT time11. ;
    input ID -- PeriodType ;
    put (_all_) (=);
cards;
ID,CreatedDT,EndDT,PeriodInDays,PeriodType
1,12:02:27 AM,12:02:27 PM,0,test
run;

Here are results for this data.

ID=1 CreatedDT=0:02:27 EndDT=12:02:27 PeriodInDays=0 PeriodType=test
NOTE: The data set WORK.TEST has 1 observations and 5 variables.

Re: read in time format (like 12:02:27 AM)

ballardw — Tue, 19 Jan 2016 21:18:49 GMT

Note that to display 12:02:27 AM you want to use a TIMEAMPM11. format, else you get the 0:02:27 time.