error creating a table from a txt file

[kaoru013]

I'm tring to create a table from a txt file but i think that i don't use the correcto formats or somthing similar because and error appers in one of the columns, i attached the log :

-----------------------------------------------------------------------

NOTE: SAS initialization used:
real time 0.02 seconds
cpu time 0.02 seconds

1 options
2 metaserver=srvsaspvlsu101.us.santanderus.corp
3 metaport=8561
4 metauser="prhtsasd"
5 metapass=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
6 metarepository=Foundation;
7
8 libname Techlogs "/interfacesas/fraud/techlogs";
NOTE: Libref TECHLOGS was successfully assigned as follows:
Engine: V9
Physical Name: /interfacesas/fraud/techlogs
9
10 proc import datafile="/interfacesas/fraud/techlogs/ch_activity_log.txt" out=Techlogs.ch_activity_log dbms=DLM replace;
11 DELIMITER='|';
12 getnames=no;
13 run;

14 /**********************************************************************
15 * PRODUCT: SAS
16 * VERSION: 9.2
17 * CREATOR: External File Interface
18 * DATE: 11NOV16
19 * DESC: Generated SAS Datastep Code
20 * TEMPLATE SOURCE: (None Specified.)
21 ***********************************************************************/
22 data TECHLOGS.CH_ACTIVITY_LOG ;
23 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
24 infile '/interfacesas/fraud/techlogs/ch_activity_log.txt' delimiter = '|' MISSOVER DSD lrecl=32767 ;
25 informat VAR1 $12. ;
^L2 The SAS System 18:12 Friday, November 11, 2016

26 informat VAR2 $8. ;
27 informat VAR3 $6. ;
28 informat VAR4 $23. ;
29 informat VAR5 $12. ;
30 informat VAR6 $15. ;
31 informat VAR7 $465. ;
32 format VAR1 $12. ;
33 format VAR2 $8. ;
34 format VAR3 $6. ;
35 format VAR4 $23. ;
36 format VAR5 $12. ;
37 format VAR6 $15. ;
38 format VAR7 $465. ;
39 input
40 VAR1 $
41 VAR2 $
42 VAR3 $
43 VAR4 $
44 VAR5 $
45 VAR6 $
46 VAR7 $
47 ;
48 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
49 run;

NOTE: The infile '/interfacesas/fraud/techlogs/ch_activity_log.txt' is:
Filename=/interfacesas/fraud/techlogs/ch_activity_log.txt,
Owner Name=prhtsas,Group Name=sas,
Access Permission=rw-rw-r--,
Last Modified=Fri Nov 11 17:21:15 2016,
File Size (bytes)=6103862

NOTE: 30620 records were read from the infile '/interfacesas/fraud/techlogs/ch_activity_log.txt'.
The minimum record length was 70.
The maximum record length was 958.
NOTE: The data set TECHLOGS.CH_ACTIVITY_LOG has 30620 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.09 seconds
cpu time 0.08 seconds

30620 rows created in TECHLOGS.CH_ACTIVITY_LOG from /interfacesas/fraud/techlogs/ch_activity_log.txt.



NOTE: TECHLOGS.CH_ACTIVITY_LOG data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 12.47 seconds
cpu time 0.50 seconds

50
51 data Techlogs.ch_activity_log;
52 infile '/interfacesas/fraud/techlogs/ch_activity_log.txt' delimiter = '|' TRUNCOVER DSD lrecl=32767 ;
53 informat ACTIVITY_LOG_ID 12. ;
54 informat ACTIVITY_TYPE_ID 8. ;
55 informat PROBLEM_REASON_ID 8. ;
56 informat SOURCE_DT_TM yymmdd10. ;
^L3 The SAS System 18:12 Friday, November 11, 2016

57 informat AUTHENTICATION_METHOD $500. ;
58 informat ACTIVITY_KEY $50. ;
59 informat ACTIVITY_DETAIL $30000. ;
60 format ACTIVITY_LOG_ID 12. ;
61 format ACTIVITY_TYPE_ID 8. ;
62 format PROBLEM_REASON_ID 8. ;
63 format SOURCE_DT_TM yymmdd10. ;
64 format AUTHENTICATION_METHOD $500. ;
65 format ACTIVITY_KEY $50. ;
66 format ACTIVITY_DETAIL $30000. ;
67
68 input
69 ACTIVITY_LOG_ID
70 ACTIVITY_TYPE_ID
71 PROBLEM_REASON_ID
72 SOURCE_DT_TM
73 AUTHENTICATION_METHOD $
74 ACTIVITY_KEY $
75 ACTIVITY_DETAIL $
76
77 ;
78 run;

NOTE: The infile '/interfacesas/fraud/techlogs/ch_activity_log.txt' is:
Filename=/interfacesas/fraud/techlogs/ch_activity_log.txt,
Owner Name=prhtsas,Group Name=sas,
Access Permission=rw-rw-r--,
Last Modified=Fri Nov 11 17:21:15 2016,
File Size (bytes)=6103862

NOTE: Invalid data for PROBLEM_REASON_ID in line 1 20-23.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
1 "1257178407"|"683"|"null"|"2016-11-11 12:11:00.0"|"RETAIL OLB"|"1478884290064"|"User ID: I1677978; T
101 imestamp: 2016-11-11 12:11:30.036; Account From: A8 2313726919996325679; Account To: A8 231372691559
201 1083366; Date: 11/11/2016; Amount: $1,200.00; Customer Name: FABRICE TAFO; IP Address: 71.235.200.15
301 1; Device information: 99000449989937|SM-G900P; Geo-location information: sheryl||#0#||; " 390
ACTIVITY_LOG_ID=1257178407 ACTIVITY_TYPE_ID=683 PROBLEM_REASON_ID=. SOURCE_DT_TM=2016-11-11 AUTHENTICATION_METHOD=RETAIL OLB
ACTIVITY_KEY=1478884290064
ACTIVITY_DETAIL=User ID: I1677978; Timestamp: 2016-11-11 12:11:30.036; Account From: A8 2313726919996325679; Account To: A8 23137269
15591083366; Date: 11/11/2016; Amount: $1,200.00; Customer Name: FABRICE TAFO; IP Address: 71.235.200.151; Device information: 99000
449989937|SM-G900P; Geo-location information: sheryl||#0#||; _ERROR_=1 _N_=1
NOTE: Invalid data for PROBLEM_REASON_ID in line 2 22-25.
2 "1257178415"|"13244"|"null"|"2016-11-11 12:11:00.0"|"IVR"|"00000"|"Customer ID: #582579469#||IOC Acc
101 ount Type: Telephone Banking" 129
ACTIVITY_LOG_ID=1257178415 ACTIVITY_TYPE_ID=13244 PROBLEM_REASON_ID=. SOURCE_DT_TM=2016-11-11 AUTHENTICATION_METHOD=IVR
ACTIVITY_KEY=00000 ACTIVITY_DETAIL=Customer ID: #582579469#||IOC Account Type: Telephone Banking _ERROR_=1 _N_=2
NOTE: Invalid data for PROBLEM_REASON_ID in line 3 22-25.
3 "1257178417"|"13290"|"null"|"2016-11-11 12:11:00.0"|"IVR"|"00000"|"Customer ID: #193623035#||Account
101 Number: #2313726918191022877#" 131
ACTIVITY_LOG_ID=1257178417 ACTIVITY_TYPE_ID=13290 PROBLEM_REASON_ID=. SOURCE_DT_TM=2016-11-11 AUTHENTICATION_METHOD=IVR
ACTIVITY_KEY=00000 ACTIVITY_DETAIL=Customer ID: #193623035#||Account Number: #2313726918191022877# _ERROR_=1 _N_=3
NOTE: Invalid data for PROBLEM_REASON_ID in line 4 22-25.
4 "1257178419"|"14013"|"null"|"2016-11-11 12:11:00.0"|"RETAIL OLB"|"1478884291048"|"User: aDAccWZG; Ti
101 mestamp: 2016-11-11 12:11:30.983; Account From: A8 2313726910095172103; Account To: A8 2313726913671
201 330155; Date: 11/14/2016; Amount: $500.00; Customer Name: MAXINE A MARTIN; IP Address: 73.100.98.186

----------------------------------------------------------------------------------------------------------------------------------------------------------------

[Tom · Accepted solution]

Modelling your data step after what PROC IMPORT generates might be useful if you don't know anything about how SAS works, but if you do you can write a much clearer and shorter program yourself.

Use LENGTH or ATTRIB to define your variables instead of forcing SAS to guess what type of variables they are based on first seeing your variables referenced in an INFORMAT, FORMAT or INPUT statement.  

Also you don't need to define INFORMATs for normal text strings or numbers. Just add them for things like dates, times, or datetime values that need special treatement.  The same applies to FORMATs.  There is no value an much danger in permanently attaching $xxx. formats to text strings.  

You don't need to add the $ modifier in your INPUT statement if you have already defined the variable as character.

It looks like the source file you are reading has the literal string 'null' entered when the numeric variable is supposed to mssing. If you are sure that these are the only strange values then perhaps you just need to add ? or ?? modifier to your INPUT statement for that variable.

data want ;
  infile cards dlm='|' dsd truncover ;
  length
    ACTIVITY_LOG_ID  8
    ACTIVITY_TYPE_ID  8
    PROBLEM_REASON_ID  8
    SOURCE_DT_TM 8
    AUTHENTICATION_METHOD $500
    ACTIVITY_KEY $50
    ACTIVITY_DETAIL $30000
  ;
  informat SOURCE_DT_TM ANYDTDTM. ;
  format SOURCE_DT_TM datetime20. ;
  input
    ACTIVITY_LOG_ID
    ACTIVITY_TYPE_ID
    PROBLEM_REASON_ID  ?
    SOURCE_DT_TM
    AUTHENTICATION_METHOD
    ACTIVITY_KEY
    ACTIVITY_DETAIL
  ;
cards;
"1257178407"|"683"|"null"|"2016-11-11 12:11:00.0"|method|key|detail
;

Result for this one example line.

RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----
512        "1257178407"|"683"|"null"|"2016-11-11 12:11:00.0"|method|key|detail
ACTIVITY_LOG_ID=1257178407 ACTIVITY_TYPE_ID=683 PROBLEM_REASON_ID=. SOURCE_DT_TM=11NOV2016:12:11:00
AUTHENTICATION_METHOD=method ACTIVITY_KEY=key ACTIVITY_DETAIL=detail _ERROR_=1 _N_=1
NOTE: The data set WORK.WANT has 1 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.59 seconds
      cpu time            0.00 seconds


513  ;
514
515  data _null_; set want;
516   put (_all_) (=/);
517  run;


ACTIVITY_LOG_ID=1257178407
ACTIVITY_TYPE_ID=683
PROBLEM_REASON_ID=.
SOURCE_DT_TM=11NOV2016:12:11:00
AUTHENTICATION_METHOD=method
ACTIVITY_KEY=key
ACTIVITY_DETAIL=detail
NOTE: There were 1 observations read from the data set WORK.WANT.