Why is the time truncating and how do I fix it?

[unwashedhelimix]

I have data on earthquakes that look something like this:

Date        Time       Magnitude Latitude  Longitude

2022/01/10 14:54:28.81 0.36 33.512 -116.768  
2022/01/10 15:16:32.60 0.96 33.184 -116.031   
2022/01/10 15:43:09.41 1.09 35.680 -117.535   
2022/01/10 16:33:07.48 0.68 33.588 -116.808 
2022/01/10 17:14:17.90 0.86 36.032 -117.807 
2022/01/10 18:00:07.32 0.61 35.765 -117.580 
2022/01/10 18:50:47.06 0.90 35.586 -117.462   
2022/01/10 18:59:47.00 2.61 35.648 -115.927 

I want to print the report showing MM/DD/YYYY date format and AM/PM date format, and my code is below:

data Earthquakes;
infile "path to .txt file";
input Date yymmdd10. Time time11. Magnitude $ Latitude Longitude $;
format Date mmddyy10. Time timeampm.;
run;

proc print data = Earthquakes;
run;

However, the output cuts off the milliseconds part of Time, rounds it to the nearest integer, and then puts it in the Magnitude column. The output looks something like this:

Why is the milliseconds part of time being put in the magnitude column, and then shifting every input one column over? How can I revise the code to show the time in AM/PM while having the correct entries in the magnitude, latitude, and longitude columns?

[Ksharp · Accepted solution]

Using right input method(list input) and pick up the right format .

data Earthquakes;
infile cards truncover;
input Date : yymmdd12. Time : time14. Magnitude  Latitude  Longitude ;
format Date mmddyy10. Time timeampm18.2 Magnitude f10.2  Latitude  Longitude f12.3;
cards;
2022/01/10 14:54:28.81 0.36 33.512 -116.768  
2022/01/10 15:16:32.60 0.96 33.184 -116.031   
2022/01/10 15:43:09.41 1.09 35.680 -117.535   
2022/01/10 16:33:07.48 0.68 33.588 -116.808 
2022/01/10 17:14:17.90 0.86 36.032 -117.807 
2022/01/10 18:00:07.32 0.61 35.765 -117.580 
2022/01/10 18:50:47.06 0.90 35.586 -117.462   
2022/01/10 18:59:47.00 2.61 35.648 -115.927 
;
run;

proc print data = Earthquakes;
run;

[Tom]

Count the columns. 

You can use the LIST statement to have SAS print it out for you to the SAS log.

RULE:     ----+----1----+----2----+----3----+----4----+----5-
1         2022/01/10 14:54:28.81 0.36 33.512 -116.768 43
2         2022/01/10 15:16:32.60 0.96 33.184 -116.031 43
3         2022/01/10 15:43:09.41 1.09 35.680 -117.535 43
4         2022/01/10 16:33:07.48 0.68 33.588 -116.808 43
5         2022/01/10 17:14:17.90 0.86 36.032 -117.807 43
6         2022/01/10 18:00:07.32 0.61 35.765 -117.580 43
7         2022/01/10 18:50:47.06 0.90 35.586 -117.462 43
8         2022/01/10 18:59:47.00 2.61 35.648 -115.927 43
NOTE: 8 records were read from the infile HAVE.
      The minimum record length was 43.
      The maximum record length was 43.

You asked the INPUT statement to read the first two variables using FORMATTED MODE and the other three variables using LIST MODE.  So it read DATE from columns 1 to 10. Then TIME from columns 11 to 21 leaving the pointer at column 22.  So for MAGNITUDE it read the last digit of the TIME value.  And you never read in the last value on the line.

So either adjust the width of the TIME informat to account for the leading and trailing space.

input Date yymmdd10. Time time13. Magnitude Latitude Longitude;

Or if the values are not in fixed column locations, but every field has a value on every line (with single periods used to mark the missing values) then just use LIST MODE for all of the fields instead of just the last three.  You could use an INFORMAT statement to attach the desired informats to DATE and TIME.

data have;
  infile have truncover;
  input Date Time Magnitude Latitude Longitude;
  informat date yymmdd. time time.;
  format date yymmdd10. time timeampm14.2;
run;

Or instead if you want to specify the informat in-line in the INPUT statement then make sure to use the colon modifier so that the INPUT statement still uses LIST MODE instead of switching to FORMATTED MODE for those variables.

  input Date :yymmdd. Time :time. Magnitude Latitude Longitude;

Remember that in LIST MODE input the width attached to the informat specification is ignored.  Instead the whole next field on the line is read, however many bytes it takes.  So no need to specify any width at all.

To display 8 character time values with a decimal point and two places to the right of the decimal point and a space and two letter suffix will requried using the format specification timeampm14.2.