topic Re: Proc Import error in SAS Studio

Proc Import error

agbpilot — Sun, 26 Feb 2017 19:16:16 GMT

Hi, I am using proc import to bring in a csv file into SAS Studio. It appears that Studio is successfully bringing in the csv file, however I continue to receive this error. Does someone have suggestions on what I can do to my code to prevent this error? See attached error msg and code below.

Appreciate help.

Andy

/*Imports TAD property location record txt file into SAS Studio*/
PROC IMPORT DATAFILE="/folders/myfolders/PropertyLocation-2-23-17.txt"
DBMS=DLM
OUT=tad_location
REPLACE;
DELIMITER='|';
GETNAMES=YES;
GUESSINGROWS = 32767;
RUN;

Re: Proc Import error

Kurt_Bremser — Sun, 26 Feb 2017 19:22:09 GMT

Please post the complete log from the import step, the important messages are further up.

You can copy/paste the log as text into a {i} window.

Re: Proc Import error

Shmuel — Sun, 26 Feb 2017 19:57:50 GMT

Add options source source2; before your code and post full log;

Re: Proc Import error

agbpilot — Sun, 26 Feb 2017 20:16:59 GMT

Thanks for your reply. Here is the full log.

Errors (1)

Warnings

Notes (23)

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

56 PROC IMPORT DATAFILE="/folders/myfolders/PropertyLocation-2-23-17.txt"

57 DBMS=DLM

58 OUT=tad_location

59 REPLACE;

60 DELIMITER='|';

61 GETNAMES=YES;

62 GUESSINGROWS = 32767;

63 RUN;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to

WORK.PARMS.PARMS.SLIST.

64 /**********************************************************************

65 * PRODUCT: SAS

66 * VERSION: 9.4

67 * CREATOR: External File Interface

68 * DATE: 26FEB17

69 * DESC: Generated SAS Datastep Code

70 * TEMPLATE SOURCE: (None Specified.)

71 ***********************************************************************/

72 data WORK.TAD_LOCATION ;

73 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

74 infile '/folders/myfolders/PropertyLocation-2-23-17.txt' delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2 ;

75 informat RP $1. ;

76 informat Appraisal_Year best32. ;

77 informat Account_Num best32. ;

78 informat Record_Type $4. ;

79 informat Seq_Num best32. ;

80 informat Street_Name $23. ;

81 informat Street_Type $4. ;

82 informat Pre_Dir $2. ;

83 informat Post_Dir $2. ;

84 informat Street_Num best32. ;

85 informat Street_Num_Suffix $1. ;

86 informat City $22. ;

87 informat ZipCode best32. ;

88 informat State $2. ;

89 format RP $1. ;

90 format Appraisal_Year best12. ;

91 format Account_Num best12. ;

92 format Record_Type $4. ;

93 format Seq_Num best12. ;

94 format Street_Name $23. ;

95 format Street_Type $4. ;

96 format Pre_Dir $2. ;

97 format Post_Dir $2. ;

98 format Street_Num best12. ;

99 format Street_Num_Suffix $1. ;

100 format City $22. ;

101 format ZipCode best12. ;

102 format State $2. ;

103 input

104 RP $

105 Appraisal_Year

106 Account_Num

107 Record_Type $

108 Seq_Num

109 Street_Name $

110 Street_Type $

111 Pre_Dir $

112 Post_Dir $

113 Street_Num

114 Street_Num_Suffix $

115 City $

116 ZipCode

117 State $

118 ;

119 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

120 run;

NOTE: The infile '/folders/myfolders/PropertyLocation-2-23-17.txt' is:

Filename=/folders/myfolders/PropertyLocation-2-23-17.txt,

Owner Name=root,Group Name=vboxsf,

Access Permission=-rwxrwx---,

Last Modified=26Feb2017:12:56:39,

File Size (bytes)=85058418

NOTE: Invalid data for ZipCode in line 70858 108-117.

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

70858 P|2017|11077425|LOCA|000|COPELAND |RD |E | |1250 | |ARLINGTON

101 |76011-1316|TX 120

RP=P Appraisal_Year=2017 Account_Num=11077425 Record_Type=LOCA Seq_Num=0 Street_Name=COPELAND Street_Type=RD Pre_Dir=E Post_Dir=

Street_Num=1250 Street_Num_Suffix= City=ARLINGTON ZipCode=. State=TX _ERROR_=1 _N_=70857

NOTE: Invalid data for ZipCode in line 73255 108-117.

73255 P|2017|11346078|LOCA|000|HOUSTON |ST | | |815 | |FORT WORTH

101 |76102-6202|TX 120

RP=P Appraisal_Year=2017 Account_Num=11346078 Record_Type=LOCA Seq_Num=0 Street_Name=HOUSTON Street_Type=ST Pre_Dir= Post_Dir=

Street_Num=815 Street_Num_Suffix= City=FORT WORTH ZipCode=. State=TX _ERROR_=1 _N_=73254

NOTE: Invalid data for ZipCode in line 76027 108-117.

76027 P|2017|11632992|LOCA|000|WESTCREEK |CIR | | |6600 | |FORT WORTH

101 |76126-5479|TX 120

RP=P Appraisal_Year=2017 Account_Num=11632992 Record_Type=LOCA Seq_Num=0 Street_Name=WESTCREEK Street_Type=CIR Pre_Dir= Post_Dir=

Street_Num=6600 Street_Num_Suffix= City=FORT WORTH ZipCode=. State=TX _ERROR_=1 _N_=76026

NOTE: Invalid data for ZipCode in line 77888 108-117.

77888 P|2017|11759534|LOCA|000|OLD DENTON |RD | | |10932 | |FORT WORTH

101 |76244-7137|TX 120

RP=P Appraisal_Year=2017 Account_Num=11759534 Record_Type=LOCA Seq_Num=0 Street_Name=OLD DENTON Street_Type=RD Pre_Dir= Post_Dir=

Street_Num=10932 Street_Num_Suffix= City=FORT WORTH ZipCode=. State=TX _ERROR_=1 _N_=77887

NOTE: Invalid data for ZipCode in line 81575 108-117.

81575 P|2017|12365793|LOCA|000|STATE 360 |HWY |N | |2080 | |GRAND PRAIRIE

101 |75050-0900|TX 120

RP=P Appraisal_Year=2017 Account_Num=12365793 Record_Type=LOCA Seq_Num=0 Street_Name=STATE 360 Street_Type=HWY Pre_Dir=N Post_Dir=

Street_Num=2080 Street_Num_Suffix= City=GRAND PRAIRIE ZipCode=. State=TX _ERROR_=1 _N_=81574

NOTE: Invalid data for ZipCode in line 88956 108-117.

88956 P|2017|13665693|LOCA|000|GREAT SOUTHWEST |PKWY |N | |900 | |ARLINGTON

101 |76011-5409|TX 120

RP=P Appraisal_Year=2017 Account_Num=13665693 Record_Type=LOCA Seq_Num=0 Street_Name=GREAT SOUTHWEST Street_Type=PKWY Pre_Dir=N

Post_Dir= Street_Num=900 Street_Num_Suffix= City=ARLINGTON ZipCode=. State=TX _ERROR_=1 _N_=88955

NOTE: Invalid data for ZipCode in line 89249 108-117.

89249 P|2017|13673734|LOCA|000|NORTHERN CROSSING |BLVD | | |2601 | |FORT WORTH

101 |76137-3646|TX 120

RP=P Appraisal_Year=2017 Account_Num=13673734 Record_Type=LOCA Seq_Num=0 Street_Name=NORTHERN CROSSING Street_Type=BLVD Pre_Dir=

Post_Dir= Street_Num=2601 Street_Num_Suffix= City=FORT WORTH ZipCode=. State=TX _ERROR_=1 _N_=89248

NOTE: Invalid data for ZipCode in line 91787 108-117.

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

91787 P|2017|13754149|LOCA|000|WALDROP |DR |N | |1001 | |ARLINGTON

101 |76012-4715|TX 120

RP=P Appraisal_Year=2017 Account_Num=13754149 Record_Type=LOCA Seq_Num=0 Street_Name=WALDROP Street_Type=DR Pre_Dir=N Post_Dir=

Street_Num=1001 Street_Num_Suffix= City=ARLINGTON ZipCode=. State=TX _ERROR_=1 _N_=91786

NOTE: Invalid data for ZipCode in line 93314 108-117.

93314 P|2017|13788965|LOCA|000|OVERTON HOLLOW |ST | | |4800 | |FORT WORTH

101 |76109-2417|TX 120

RP=P Appraisal_Year=2017 Account_Num=13788965 Record_Type=LOCA Seq_Num=0 Street_Name=OVERTON HOLLOW Street_Type=ST Pre_Dir=

Post_Dir= Street_Num=4800 Street_Num_Suffix= City=FORT WORTH ZipCode=. State=TX _ERROR_=1 _N_=93313

NOTE: Invalid data for ZipCode in line 95120 108-117.

95120 P|2017|13844652|LOCA|000|TARRANT |PKWY |N | |8849 | |NORTH RICHLAND HILLS

101 |76182-8612|TX 120

RP=P Appraisal_Year=2017 Account_Num=13844652 Record_Type=LOCA Seq_Num=0 Street_Name=TARRANT Street_Type=PKWY Pre_Dir=N Post_Dir=

Street_Num=8849 Street_Num_Suffix= City=NORTH RICHLAND HILLS ZipCode=. State=TX _ERROR_=1 _N_=95119

NOTE: Invalid data for ZipCode in line 95635 108-117.

95635 P|2017|13854682|LOCA|000|AVIATOR |DR | | |832 | |FORT WORTH

101 |76179-5419|TX 120

RP=P Appraisal_Year=2017 Account_Num=13854682 Record_Type=LOCA Seq_Num=0 Street_Name=AVIATOR Street_Type=DR Pre_Dir= Post_Dir=

Street_Num=832 Street_Num_Suffix= City=FORT WORTH ZipCode=. State=TX _ERROR_=1 _N_=95634

NOTE: Invalid data for ZipCode in line 97223 108-117.

97223 P|2017|14200703|LOCA|000|CLIFFORD |ST | | |9501 | |FORT WORTH

101 |76104-4515|TX 120

RP=P Appraisal_Year=2017 Account_Num=14200703 Record_Type=LOCA Seq_Num=0 Street_Name=CLIFFORD Street_Type=ST Pre_Dir= Post_Dir=

Street_Num=9501 Street_Num_Suffix= City=FORT WORTH ZipCode=. State=TX _ERROR_=1 _N_=97222

NOTE: Invalid data for ZipCode in line 97327 108-117.

97327 P|2017|14202358|LOCA|000|DICKSON |ST | | |616 |W |FORT WORTH

101 |76137-6074|TX 120

RP=P Appraisal_Year=2017 Account_Num=14202358 Record_Type=LOCA Seq_Num=0 Street_Name=DICKSON Street_Type=ST Pre_Dir= Post_Dir=

Street_Num=616 Street_Num_Suffix=W City=FORT WORTH ZipCode=. State=TX _ERROR_=1 _N_=97326

NOTE: Invalid data for ZipCode in line 99834 108-117.

99834 P|2017|14237216|LOCA|000|BEACH |ST |N | |3729 | |FORT WORTH

101 |76137-3202|TX 120

RP=P Appraisal_Year=2017 Account_Num=14237216 Record_Type=LOCA Seq_Num=0 Street_Name=BEACH Street_Type=ST Pre_Dir=N Post_Dir=

Street_Num=3729 Street_Num_Suffix= City=FORT WORTH ZipCode=. State=TX _ERROR_=1 _N_=99833

NOTE: Invalid data for ZipCode in line 99840 108-117.

99840 P|2017|14237275|LOCA|000|MERCANTILE |DR | | |4710 | |FORT WORTH

101 |76137-3605|TX 120

RP=P Appraisal_Year=2017 Account_Num=14237275 Record_Type=LOCA Seq_Num=0 Street_Name=MERCANTILE Street_Type=DR Pre_Dir= Post_Dir=

Street_Num=4710 Street_Num_Suffix= City=FORT WORTH ZipCode=. State=TX _ERROR_=1 _N_=99839

NOTE: Invalid data for ZipCode in line 103274 108-117.

103274 P|2017|14289003|LOCA|000|SOUTH |FWY | | |9501 | |FORT WORTH

101 |76140-4923|TX 120

RP=P Appraisal_Year=2017 Account_Num=14289003 Record_Type=LOCA Seq_Num=0 Street_Name=SOUTH Street_Type=FWY Pre_Dir= Post_Dir=

Street_Num=9501 Street_Num_Suffix= City=FORT WORTH ZipCode=. State=TX _ERROR_=1 _N_=103273

NOTE: Invalid data for ZipCode in line 686353 108-117.

686353 R|2017|42034050|LOCA|000|BONAVENTURE |BLVD | | |3313 | |FORT WORTH

101 |76140-8633|TX 120

RP=R Appraisal_Year=2017 Account_Num=42034050 Record_Type=LOCA Seq_Num=0 Street_Name=BONAVENTURE Street_Type=BLVD Pre_Dir=

Post_Dir= Street_Num=3313 Street_Num_Suffix= City=FORT WORTH ZipCode=. State=TX _ERROR_=1 _N_=686352

NOTE: 697199 records were read from the infile '/folders/myfolders/PropertyLocation-2-23-17.txt'.

The minimum record length was 120.

The maximum record length was 120.

NOTE: The data set WORK.TAD_LOCATION has 697199 observations and 14 variables.

NOTE: DATA statement used (Total process time):

real time 3.32 seconds

cpu time 2.07 seconds

Errors detected in submitted DATA step. Examine log.

697199 rows created in WORK.TAD_LOCATION from /folders/myfolders/PropertyLocation-2-23-17.txt.

ERROR: Import unsuccessful. See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 44.57 seconds

cpu time 25.67 seconds

121

122 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

134

Re: Proc Import error

Kurt_Bremser — Sun, 26 Feb 2017 22:40:44 GMT

Either use a higher guessingrows value in proc import, or take the data step code from the log and change zipcode to character (with a suitable length).

Re: Proc Import error

Reeza — Sun, 26 Feb 2017 23:05:29 GMT

It's worth learning how to read the error messages properly.

Here's a snapshot of what you posted, I've highlighted the relevant portions.

1. It tells you which variable is the issue (BLUE).

2. Look at the ZipCode in the RULE (GREEN).

3. Verify the import specifications for the variable, check code.

      informat ZipCode best32. ;
      format ZipCode best12. ;

4. Note that ZipCode is set as numeric. But if you look at the variable in the data, it's not a number since it has a ZIP+4 code format, which has a dash. You need to read it as a character format.

NOTE: Invalid data for ZipCode in line 70858 108-117.

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

70858 P|2017|11077425|LOCA|000|COPELAND |RD |E | |1250 | |ARLINGTON

101 |76011-1316|TX 120

RP=P Appraisal_Year=2017 Account_Num=11077425 Record_Type=LOCA Seq_Num=0 Street_Name=COPELAND Street_Type=RD Pre_Dir=E Post_Dir=

Street_Num=1250 Street_Num_Suffix= City=ARLINGTON ZipCode=. State=TX _ERROR_=1 _N_=70857

Re: Proc Import error

agbpilot — Tue, 28 Feb 2017 02:04:36 GMT

Thanks. What would be best practice? 1. Copy the data step code from the log, use in my production code and correct the formatting to character for the problem column. 2. Or, increase the number for "guessingrows" in the proc import? If #2, how would I determine what number to increase it to?

Re: Proc Import error

Reeza — Tue, 28 Feb 2017 02:31:23 GMT

Copying the code from the log, modifiying it appropriately and using the explicit code is best practice.

Regarding #2, set it to the number of rows in your dataset or something super high, like a million rows. It increases the time for the Proc Import but if its a one off it's not a huge deal.

Re: Proc Import error

agbpilot — Tue, 28 Feb 2017 02:44:15 GMT

Thanks Reeza. Using the explicit code solution is what I've done.

Re: Proc Import error

Kurt_Bremser — Tue, 28 Feb 2017 08:10:06 GMT

@agbpilot wrote:

Thanks. What would be best practice? 1. Copy the data step code from the log, use in my production code and correct the formatting to character for the problem column. 2. Or, increase the number for "guessingrows" in the proc import? If #2, how would I determine what number to increase it to?

#1, hands down. proc import is a helper that can get you through the first step. As you've seen, it does not always solve your problem completely.

Using it to import a file repeatedly in a production-type environment poses the danger that changes in the infile will go undetected until errors happen much later in your processing chain. With a fixed data step, a change causes detectable ERRORs during that step that allow you to react in a timely fashion and make finding the reason for a later error much easier.