BookmarkSubscribeRSS Feed
Fluorite | Level 6

When I try to open a very large csv file, it keeps saying an error message as below. How can I open it? If it's the matter of raw data file, then how can I open a very large csv file?



proc import out = work.linkup1 datafile = "C:\Users\Desktop\file.csv"; run;

2 /**********************************************************************
4 * VERSION: 9.4
5 * CREATOR: External File Interface
6 * DATE: 07MAY21
7 * DESC: Generated SAS Datastep Code
8 * TEMPLATE SOURCE: (None Specified.)
9 ***********************************************************************/
10 data WORK.LINKUP1 ;
11 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
12 infile 'C:\Users\Desktop\file.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2
12 ! ;
13 informat hash $32. ;
14 informat title $78. ;
15 informat company_id best32. ;
16 informat company_name $36. ;
17 informat city $18. ;
18 informat state $27. ;
19 informat zip best32. ;
20 informat country $3. ;
21 informat created B8601DZ35. ;
22 informat last_checked B8601DZ35. ;
23 informat last_updated B8601DZ35. ;
24 informat delete_date B8601DZ35. ;
25 informat unmapped_location $5. ;
26 informat onet_occupation_code $10. ;
27 informat url $273. ;
28 format hash $32. ;
29 format title $78. ;
30 format company_id best12. ;
31 format company_name $36. ;
32 format city $18. ;
33 format state $27. ;
34 format zip best12. ;
35 format country $3. ;
36 format created B8601DZ35. ;
37 format last_checked B8601DZ35. ;
38 format last_updated B8601DZ35. ;
39 format delete_date B8601DZ35. ;
40 format unmapped_location $5. ;
41 format onet_occupation_code $10. ;
42 format url $273. ;
43 input
44 hash $
45 title $
46 company_id
47 company_name $
48 city $
49 state $
50 zip
51 country $
52 created
53 last_checked
54 last_updated
55 delete_date
56 unmapped_location $
57 onet_occupation_code $
58 url $
59 ;
60 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
61 run;

NOTE: The infile 'C:\Users\Desktop\file.csv' is:

File Size (bytes)=3343238343,
Last Modified=01May2021:05:15:40,
Create Time=01May2021:05:15:40

NOTE: Invalid data for zip in line 26 93-96.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+-
26 040c6adaf71f4c5725462c9d93812614,"Kitchen Assistant",33632,"Mitchells & Butlers",londo
87 n,ENG,SW1A,GBR,2019-02-28T01:18:00+00:00,2019-03-07T00:03:00+00:00,,2019-03-09T05:13:0
173 0+00:00,FALSE,99-9999.00, 238
hash=040c6adaf71f4c5725462c9d93812614 title=Kitchen Assistant company_id=33632
company_name=Mitchells & Butlers city=london state=ENG zip=. country=GBR
created=20190228T011800+0000 last_checked=20190307T000300+0000 last_updated=.
delete_date=20190309T051300+0000 unmapped_location=FALSE onet_occupation_code=99-9999.00
url= _ERROR_=1 _N_=25
NOTE: Invalid data for zip in line 53 84-87.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+-
53 040c6d3509d1deae0fe8083d3d3e918b,"Customer Assistant",24205,Tesco,wendover,England,HP2
87 2,GBR,2020-01-03T09:27:00+00:00,2020-01-07T15:33:00+00:00,,2020-01-09T18:51:00+00:00,F
173 ALSE,99-9999.00, 236
hash=040c6d3509d1deae0fe8083d3d3e918b title=Customer Assistant company_id=24205
company_name=Tesco city=wendover state=England zip=. country=GBR created=20200103T092700+0000
last_checked=20200107T153300+0000 last_updated=. delete_date=20200109T185100+0000
unmapped_location=FALSE onet_occupation_code=99-9999.00
url= _ERROR_=1 _N_=52
NOTE: Invalid data for zip in line 61 92-95.
61 040c6e4f1017f4fa949876c97eab6f9f,"CEM - Administrator",35965,Countrywide,liverpool,Eng
87 land,"L1 1",GBR,2018-05-19T19:52:00+00:00,2018-05-23T20:01:00+00:00,,,FALSE,43-1011.00
173 , 219
hash=040c6e4f1017f4fa949876c97eab6f9f title=CEM - Administrator company_id=35965
company_name=Countrywide city=liverpool state=England zip=. country=GBR
created=20180519T195200+0000 last_checked=20180523T200100+0000 last_updated=. delete_date=.
unmapped_location=FALSE onet_occupation_code=43-1011.00
url= _ERROR_=1 _N_=60
NOTE: Invalid data for zip in line 82 118-120.
82 040c7088cddd04b3ac4a4b0f75bb417a,"Citifinancial Branch Manager Trainee Calgary AB",224
87 47,"Citigroup Inc.",Calgary,AB,T2G,CAN,2014-03-15T16:39:00+00:00,2014-05-14T20:54:00+0
173 0:00,,2014-05-18T09:59:00+00:00,FALSE,55-2013.00,
259 2/jobdetail.ftl?job=14012027&lang=en 294
hash=040c7088cddd04b3ac4a4b0f75bb417a title=Citifinancial Branch Manager Trainee Calgary AB
company_id=22447 company_name=Citigroup Inc. city=Calgary state=AB zip=. country=CAN
created=20140315T163900+0000 last_checked=20140514T205400+0000 last_updated=.
delete_date=20140518T095900+0000 unmapped_location=FALSE onet_occupation_code=55-2013.00
url= _ERROR_=1 _N_=81
NOTE: Invalid data for zip in line 93 117-120.
93 040c718f2d9149bdaf8985a8e3f06f9f,"Peripatetic General Manager (RGN) - Care Home",22028
87 ,Barchester,aberdeen,Scotland,AB25,GBR,2020-12-17T10:40:00+00:00,2020-12-28T03:26:00+0
173 0:00,,2020-12-30T07:41:00+00:00,FALSE,11-9111.00,
259 ipatetic-general-manager-rgn-care-home-in-aberdeen-107304/ 316
hash=040c718f2d9149bdaf8985a8e3f06f9f title=Peripatetic General Manager (RGN) - Care Home
company_id=22028 company_name=Barchester city=aberdeen state=Scotland zip=. country=GBR
created=20201217T104000+0000 last_checked=20201228T032600+0000 last_updated=.
delete_date=20201230T074100+0000 unmapped_location=FALSE onet_occupation_code=11-9111.00
04/ _ERROR_=1 _N_=92
NOTE: Invalid data for zip in line 128 95-97.
128 040c772474c8ba870e9cffad559f8a42,"Block Property Manager",35965,Countrywide,Leicester,
87 England,"LE1 ",GBR,2017-10-19T15:36:00+00:00,2017-11-03T00:03:00+00:00,,2017-11-05T00:
173 18:00+00:00,FALSE,11-9141.00, 247
hash=040c772474c8ba870e9cffad559f8a42 title=Block Property Manager company_id=35965
company_name=Countrywide city=Leicester state=England zip=. country=GBR
created=20171019T153600+0000 last_checked=20171103T000300+0000 last_updated=.
delete_date=20171105T001800+0000 unmapped_location=FALSE onet_occupation_code=11-9141.00
url= _ERROR_=1 _N_=127
NOTE: Invalid data for zip in line 152 106-109.
152 040c79b5399ef97e0c23b1b6a1b44f85,"Customer Assistant - Checkouts",24205,Tesco,"Clydesd
87 ale West",Scotland,ML11,GBR,2014-10-20T00:07:00+00:00,2014-10-30T09:15:00+00:00,,2014-
173 11-01T22:50:00+00:00,FALSE,41-2011.00,
259 omer-Assistant-Checkouts/25806 288
hash=040c79b5399ef97e0c23b1b6a1b44f85 title=Customer Assistant - Checkouts company_id=24205
company_name=Tesco city=Clydesdale West state=Scotland zip=. country=GBR
created=20141020T000700+0000 last_checked=20141030T091500+0000 last_updated=.
delete_date=20141101T225000+0000 unmapped_location=FALSE onet_occupation_code=41-2011.00
url= _ERROR_=1
NOTE: Invalid data for zip in line 170 140-142.
170 040c7c195189ac2a9c03dfaa0c17f9ff,"Customer Service Supervisor - Eastland Centre, Sarni
87 a, ON (18.75 hours)",22306,"Banque Scotia",sarnia,ON,N7T,CAN,2018-05-09T13:51:00+00:00
173 ,2018-06-04T16:16:00+00:00,,2018-06-06T17:51:00+00:00,FALSE,43-1011.00,
345 818_75-hours%29-ON/357731917/ 373
title=Customer Service Supervisor - Eastland Centre, Sarnia, ON (18.75 hours) company_id=22306
company_name=Banque Scotia city=sarnia state=ON zip=. country=CAN created=20180509T135100+0000
last_checked=20180604T161600+0000 last_updated=. delete_date=20180606T175100+0000
unmapped_location=FALSE onet_occupation_code=43-1011.00
%2C-ON-%2818_75-hours%29-ON/357731917/ _ERROR_=1 _N_=169
NOTE: Invalid data for zip in line 180 127-129.
180 040c7d700fc94a3061623eff22599e66,"Specialist Nurse, Paediatric Nephrology",34851,"Nati
87 onal Health Service",Nottingham,England,"NG1 ",GBR,2014-07-05T23:22:00+00:00,2014-07-0
173 5T23:22:00+00:00,,2014-07-08T11:22:00+00:00,FALSE,29-1141.04,
259 /vacancy/31eaf522eb57a5a08d7c677048f63b64/?vac_ref=913440980 318
hash=040c7d700fc94a3061623eff22599e66 title=Specialist Nurse, Paediatric Nephrology
company_id=34851 company_name=National Health Service city=Nottingham state=England zip=.
country=GBR created=20140705T232200+0000 last_checked=20140705T232200+0000 last_updated=.
delete_date=20140708T112200+0000 unmapped_location=FALSE onet_occupation_code=29-1141.04
_ERROR_=1 _N_=179
NOTE: Invalid data for zip in line 183 87-90.
183 040c7de0284cfde302ae039ba550b860,"Customer Assistant",24205,Tesco,Aldermaston,England,
87 RG26,GBR,2016-04-13T22:49:00+00:00,2016-04-25T03:57:00+00:00,,2016-04-27T15:50:00+00:0
173 0,FALSE,43-4051.00, 242
hash=040c7de0284cfde302ae039ba550b860 title=Customer Assistant company_id=24205
company_name=Tesco city=Aldermaston state=England zip=. country=GBR created=20160413T224900+0000
last_checked=20160425T035700+0000 last_updated=. delete_date=20160427T155000+0000
unmapped_location=FALSE onet_occupation_code=43-4051.00
url= _ERROR_=1 _N_=182
NOTE: Invalid data for zip in line 188 123-126.
188 040c7e40a12657f274088c14d9db8cdd,"Graduate Recruitment Co-Ordinator (12 month contract
87 )",43313,"Allen & Overy",london,ENG,SW1A,GBR,2021-02-06T12:06:00+00:00,2021-02-14T12:4
173 7:00+00:00,,2021-02-16T13:05:00+00:00,FALSE,13-1071.00,
259 /TGnewUI/Search/home/HomeWithPreLoad?PageType=JobDetails&partnerid=30147&siteid=5040&j
345 obId=35136 354
title=Graduate Recruitment Co-Ordinator (12 month contract) company_id=43313
company_name=Allen & Overy city=london state=ENG zip=. country=GBR created=20210206T120600+0000
last_checked=20210214T124700+0000 last_updated=. delete_date=20210216T130500+0000
unmapped_location=FALSE onet_occupation_code=13-1071.00
nerid=30147&siteid=5040&jobId=35136 _ERROR_=1 _N_=187
NOTE: Invalid data for zip in line 191 84-87.
191 040c7e865a19df883709f21f13fee44f,"Uber Expert- York",38663,"Uber NEW",York,England,YO2
87 3,GBR,2016-11-03T22:11:00+00:00,2016-11-05T22:15:00+00:00,,2016-11-07T22:50:00+00:00,F
173 ALSE,13-1111.00,
259 ame=1 263
hash=040c7e865a19df883709f21f13fee44f title=Uber Expert- York company_id=38663
company_name=Uber NEW city=York state=England zip=. country=GBR created=20161103T221100+0000
last_checked=20161105T221500+0000 last_updated=. delete_date=20161107T225000+0000
unmapped_location=FALSE onet_occupation_code=13-1111.00
url= _ERROR_=1
NOTE: Invalid data for zip in line 193 116-119.
193 040c7e8bad08a71e6dbaf28b33aa9f66,"Channel Manager Northern Europe - Netherlands",26113
87 ,"Mulesoft, Inc.",London,ENG,SW1A,GBR,2014-03-08T15:54:00+00:00,2014-09-20T22:18:00+00
173 :00,,2014-09-23T03:53:00+00:00,FALSE,11-2022.00,
259 b.aspx?c=qR09VfwF&j=oRWzYfw4 286
hash=040c7e8bad08a71e6dbaf28b33aa9f66 title=Channel Manager Northern Europe - Netherlands
company_id=26113 company_name=Mulesoft, Inc. city=London state=ENG zip=. country=GBR
created=20140308T155400+0000 last_checked=20140920T221800+0000 last_updated=.
delete_date=20140923T035300+0000 unmapped_location=FALSE onet_occupation_code=11-2022.00
url= _ERROR_=1 _N_=192
NOTE: Invalid data for zip in line 202 115-118.
202 040c7f0482621ed7d8918f598ba20ff5,"Crew Member P/T Livingston Mall 2017",41868,"McDonal
87 d's UK",Livingston,Scotland,EH54,GBR,2017-09-05T08:34:00+00:00,2017-09-27T01:45:00+00:
173 00,,2017-09-28T04:41:00+00:00,FALSE,35-3021.00,
259 cp/client_mcdonalds/crew/
345 leCode=en-us 356
hash=040c7f0482621ed7d8918f598ba20ff5 title=Crew Member P/T Livingston Mall 2017
company_id=41868 company_name=McDonald's UK city=Livingston state=Scotland zip=. country=GBR
created=20170905T083400+0000 last_checked=20170927T014500+0000 last_updated=.
delete_date=20170928T044100+0000 unmapped_location=FALSE onet_occupation_code=35-3021.00
etJobDetail&jobPostId=198187&localeCode=en-us _ERROR_=1 _N_=201
NOTE: Invalid data for zip in line 236 113-116.
236 040c81c87b9fff3b6a4cb3f5adadaca0,"Neonatal Clinical Teacher",34851,"National Health Se
87 rvice",Birmingham,England,"B1 2",GBR,2014-10-04T14:55:00+00:00,2014-10-04T14:55:00+00:
173 00,,2014-10-06T22:21:00+00:00,FALSE,25-1071.00,
259 2cc8c0d45a92a1dbef5efcd0420/?vac_ref=913533974 304
hash=040c81c87b9fff3b6a4cb3f5adadaca0 title=Neonatal Clinical Teacher company_id=34851
company_name=National Health Service city=Birmingham state=England zip=. country=GBR
created=20141004T145500+0000 last_checked=20141004T145500+0000 last_updated=.
delete_date=20141006T222100+0000 unmapped_location=FALSE onet_occupation_code=25-1071.00
_ERROR_=1 _N_=235
NOTE: Invalid data for zip in line 258 100-102.
258 040c845f68cad9ed945fb13174c2e574,"BuyerPlanner III",24384,"Siemens Canada Limited",Pet
87 erborough,ON,K9H,CAN,2017-01-12T07:43:00+00:00,2017-02-10T00:26:00+00:00,,2017-02-12T0
173 3:50:00+00:00,FALSE,13-1023.00,
259 ftl?job=012002&lang=en 280
hash=040c845f68cad9ed945fb13174c2e574 title=BuyerPlanner III company_id=24384
company_name=Siemens Canada Limited city=Peterborough state=ON zip=. country=CAN
created=20170112T074300+0000 last_checked=20170210T002600+0000 last_updated=.
delete_date=20170212T035000+0000 unmapped_location=FALSE onet_occupation_code=13-1023.00
url= _ERROR_=1
NOTE: Invalid data for zip in line 281 91-93.
281 040c86a94509fcbb88191edf5d3b9df5,"Director of Risk Management",31628,Financeit,toronto
87 ,ON,M5H,CAN,2018-09-11T13:16:00+00:00,2018-10-31T04:39:00+00:00,,2018-11-02T04:53:00+0
173 0:00,FALSE,11-3031.02, 245
hash=040c86a94509fcbb88191edf5d3b9df5 title=Director of Risk Management company_id=31628
company_name=Financeit city=toronto state=ON zip=. country=CAN created=20180911T131600+0000
last_checked=20181031T043900+0000 last_updated=. delete_date=20181102T045300+0000
unmapped_location=FALSE onet_occupation_code=11-3031.02
url= _ERROR_=1 _N_=280
NOTE: Invalid data for zip in line 305 118-121.
305 040c895803df3a2d5718154d9282df6a,"Customer Delivery Driver - Stretford Extra",65607,"T
87 esco Lotus",manchester,England,"M3 3",GBR,2020-11-01T18:29:00+00:00,2020-11-08T11:24:0
173 0+00:00,,2020-11-10T18:08:00+00:00,FALSE,53-3033.00,
259 tails/597016.aspx 275
hash=040c895803df3a2d5718154d9282df6a title=Customer Delivery Driver - Stretford Extra
company_id=65607 company_name=Tesco Lotus city=manchester state=England zip=. country=GBR
created=20201101T182900+0000 last_checked=20201108T112400+0000 last_updated=.
delete_date=20201110T180800+0000 unmapped_location=FALSE onet_occupation_code=53-3033.00
url= _ERROR_=1 _N_=304
NOTE: Invalid data for zip in line 340 110-112.
340 040c8ba22caa2651b0ab7413f60b5484,"Tire & Lube Express (TLE) Technician",41746,"Walmart
87 Canada",lethbridge,AB,T1J,CAN,2018-10-24T05:45:00+00:00,2018-11-03T00:03:00+00:00,,20
173 18-11-04T00:45:00+00:00,FALSE,53-6031.00,
259 me/HomeWithPreLoad?PageType=JobDetails&jobId=1261737&partnerid=25222&siteid=5011 338
hash=040c8ba22caa2651b0ab7413f60b5484 title=Tire & Lube Express (TLE) Technician
company_id=41746 company_name=Walmart Canada city=lethbridge state=AB zip=. country=CAN
created=20181024T054500+0000 last_checked=20181103T000300+0000 last_updated=.
delete_date=20181104T004500+0000 unmapped_location=FALSE onet_occupation_code=53-6031.00
61737&partnerid=25222&siteid=5011 _ERROR_=1 _N_=339
NOTE: Invalid data for zip in line 398 120-122.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
398 040c9000027a0885c72ab70b1f3656ec,"Delivery Driver / Set Up Technician - SHHC",25439,"S
87 hoppers Drug Mart",Belleville,ON,K8N,CAN,2016-12-31T10:26:00+00:00,2017-01-10T18:25:00
173 +00:00,,2017-01-12T09:51:00+00:00,FALSE,53-1031.00,
259 pJobView.jsp?link=1082097 283
hash=040c9000027a0885c72ab70b1f3656ec title=Delivery Driver / Set Up Technician - SHHC
company_id=25439 company_name=Shoppers Drug Mart city=Belleville state=ON zip=. country=CAN
created=20161231T102600+0000 last_checked=20170110T182500+0000 last_updated=.
delete_date=20170112T095100+0000 unmapped_location=FALSE onet_occupation_code=53-1031.00
url= _ERROR_=1 _N_=397
NOTE: 10524943 records were read from the infile 'C:\Users\Desktop\file.csv'.
The minimum record length was 155.
The maximum record length was 1318.
NOTE: The data set WORK.LINKUP1 has 10524943 observations and 15 variables.
NOTE: DATA statement used (Total process time):
real time 28.11 seconds
cpu time 23.93 seconds

Errors detected in submitted DATA step. Examine log.
10524943 rows created in WORK.LINKUP1 from C:\Users\Desktop\file.csv.

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 28.51 seconds
cpu time 24.07 seconds

Editted: Removed duplicate lines, put in code box.  But it looks like the original paste in main body already messed up the spacing of the text.

Super User Tom
Super User

Looks like you tried to get SAS to guess how to read the data by using PROC IMPORT.

Probably going to be much easier to just write your own data step to read it.  Then you can do things like read the questionable fields as character to start with and check out what they contain before deciding if perhaps they are really numbers or dates.  


Plus you can force things like ZIP codes that might LOOK like numbers to be the strings they really are.

Plus you can write much clearer and easy to maintain code that PROC IMPORT generates.

data LINKUP1 ;
  infile 'C:\Users\Desktop\file.csv' TRUNCOVER DSD lrecl=32767 firstobs=2;
    hash $32 title $80 company_id 8 company_name $50 
    city $20 state $30 zip $10 country $3 
    created 8 last_checked 8 last_updated 8 delete_date 8
    unmapped_location $5 onet_occupation_code $10
  informat created last_checked last_updated delete_date B8601DZ35. ;
  format created last_checked last_updated delete_date datetime19. ;
  input hash -- onet_occupation_code ;


Opal | Level 21

Scenario 1 in the blog here explains what happens and how to resolve it using Proc Import.


Fully writing your own code as @Tom suggests will give you the most control over how SAS converts external data into SAS variables. It's certainly what one should be doing for any data import that isn't adhoc (i.e. run on a monthly bases for new external data).

You can always use Proc Import generated code as a starting point.

Super User

Zipcodes are codes, not numbers used for calculations. Since they can also contain characters (as seen here for England and Canada), read them as character.


Don't burden yourself with PROC IMPORT when reading csv files, write the data step yourself, using the file documentation (you have one, don't you?).

Super User

Those invalid data messages have nothing to do with the size of the file. The code generated by Proc Import guessed that Zip was numeric.


You might try going back to the import step and and add the GUESSINGROWS=MAX option, or at least set the value for guessingrows to a largish number like 10000 so more rows are read before properties set.


The default behavior of Proc Import, which is used by wizards or tasks, is to examine only about 20 rows of data to set properties. Since the first time that the postal code (Zip) is not all digits is on line 26 in the data file

NOTE: Invalid data for zip in line 26 93-96

 I am fairly sure that is what happened. You have have other text fields that are truncated, likely candidates are Company_name and Title.


Or you can copy that code from the log, paste into the editor, remove the line numbers and change the informats to character like Zip $10. and longer values for Company_name, title, City and URL.

If you have a file description that says how long text fields should be from the source I would use that to make sure the entire file is read properly.

Super User

Try add one more option GUESSINGROWS=


proc import out = work.linkup1 datafile = "C:\Users\Desktop\file.csv" dbms=csv replace; 
Super User
Is that private data that you're ok releasing to the public? Your log is containing a lot of information about your data set. It looks like you're trying to read Canadian Postal Codes which are not numeric and you've set ZIP to be numeric.


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 7 in conversation