Write and run SAS programs in your web browser

Proc Import error

Reply
Occasional Contributor
Posts: 10

Proc Import error

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;


error_sas.PNG
Super User
Posts: 7,422

Re: Proc Import error

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,466

Re: Proc Import error

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

Occasional Contributor
Posts: 10

Re: Proc Import error

Thanks for your reply.  Here is the full log.

 

 

Errors (1)
Warnings
Notes (23)
 
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
55
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
Super User
Posts: 7,422

Re: Proc Import error

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).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 19,098

Re: Proc Import error

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
Occasional Contributor
Posts: 10

Re: Proc Import error

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? 

Super User
Posts: 19,098

Re: Proc Import error

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. 

Occasional Contributor
Posts: 10

Re: Proc Import error

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

Super User
Posts: 7,422

Re: Proc Import error


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 9 replies
  • 268 views
  • 4 likes
  • 4 in conversation