how to read this 01/14/2020 09:40:20

[cellurl]

Please help me read this text file  😉

I am a newbie.

Thanks!

   proc format;
      picture $dtpic
      other='%0m/%0d/%Y %0H:%0M:%0S' (datatype=datetime)
   ;
   run;


   data tmp;
      infile '../data/RERATE-report_clean.dat' missover;
      input
           @69   mystr $19.
      ;

   data tmp2;
      set here.tmp;
      format mystr $dtpic.;

Heres the error.

MPRINT(DRIVER):   proc format;
NOTE 137-205: Line generated by the invoked macro "DRIVER".
114          %inc "./common";      proc format;       picture $dtpic       other='%0m/%0d/%Y %0H:%0M:%0S' (datatype=datetime)    ;
                                                              _
                                                              22
114      !   run;      data tmp;       infile '../data/RERATE-report_clean.dat' missover;       input             @69   mystr $19.
114      !      ;
ERROR 22-322: Expecting a name.  

[PaigeMiller]

Since the dollar sign is underlined, that is the problem. Remove it.

[cellurl]

now it says

ERROR 48-59: The format $DTPIC was not found or could not be loaded.

[cellurl]

   proc format;
      picture dtpic
      other='%0m/%0d/%Y %0H:%0M:%0S' (datatype=datetime)
   ;
   run;


   data tmp;
      infile '../data/RERATE-report_clean.dat' missover;
      input
           @69   mystr $19.
      ;

   data tmp2;
      set tmp;
      format mystr dtpic.;


NOTE: Line generated by the invoked macro "DRIVER".
226             data tmp2;       set tmp;       format mystr dtpic.;            endsas;        data here.tmp;       format
                                                             ______
                                                             48
226      ! request_dt mmddyy10.;       infile '../data/RERATE-report_clean.dat' missover;       input             @9    request_id
226      !       $8.
MPRINT(DRIVER):   data tmp2;
MPRINT(DRIVER):   set tmp;
MPRINT(DRIVER):   format mystr dtpic.;
MPRINT(DRIVER):   endsas
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Due to ERROR(s) above, SAS set option OBS=0, enabling syntax check mode. 

[PaigeMiller]

Your log does not indicate that you re-ran PROC FORMAT without the $ character.

[Tom · Accepted solution]

You seem confused about the difference between a FORMAT and an INFORMAT.  You use a format to convert values into text and and informat to convert text into values.  The PICTURE statement in PROC FORMAT can only be used to create numeric formats. You cannot use it to create an informat.

You might be able to find an existing informat that can read those values as datatime values.  But since your file seems to be in fixed column positions:

913   data _null_;
914     infile "&path/RERATE-report_clean.dat.txt" ;
915     input;
916     list;
917   run;

NOTE: The infile ".../RERATE-report_clean.dat.txt" is:
      Filename=.../RERATE-report_clean.dat.txt,
      RECFM=V,LRECL=32767,File Size (bytes)=435,
      Last Modified=15Jan2020:15:20:18,
      Create Time=15Jan2020:15:20:18

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
1                 1748882         Vida Shoe International - GR    01/13/2020  01/14/2020 09:40:20      0    1
     101    Port/Channel :5 IS BLOCKED BY <<<IDEA>>> 142
2             1   1743260   FXG   Blick Art Materials             12/03/2019  12/11/2019 02:25:22     34    8
     101    Completed by:IDEA,PARS,PEARS   Received by:Ground(withAgent),(WIP) 168
3            39   1747645*  CMB   Union Home Mortgage             01/03/2020  01/14/2020 09:10:14      0    1
     101    Completed by:IDEA 119

it is probably going to be easier to read the date and time as two separate variables. You can put them together again if you want.

data want;
  infile "&path/RERATE-report_clean.dat.txt" truncover ;
  input id1 1-5 id2 $ 6-15 name $25-55 
        @57 date1 mmddyy10. @69 date2 mmddyy10. @80 time time8. 
        num1 89-94 num2 95-99 @102 description $200. 
  ;
  datetime = dhms(date2,0,0,time);
  format date1 date2 yymmdd10. time time8. datetime datetime20.;
run;

Obs id1    id2                name                date1       date2      time

 1    .  1748882  Vida Shoe International - GR  2020-01-13  2020-01-14  9:40:20
 2    1  1743260  Blick Art Materials           2019-12-03  2019-12-11  2:25:22
 3   39  1747645  Union Home Mortgage           2020-01-03  2020-01-14  9:10:14

Obs num1  num2  description

 1    0     1   Port/Channel :5 IS BLOCKED BY <<<IDEA>>>
 2   34     8   Completed by:IDEA,PARS,PEARS   Received by:Ground(withAgent),(WIP)
 3    0     1   Completed by:IDEA

Obs      datetime

 1  14JAN2020:09:40:20
 2  11DEC2019:02:25:22
 3  14JAN2020:09:10:14