DATA Step, Macro, Functions and more

proc import - unsuccessful

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

proc import - unsuccessful

Was importing a comma-delimited csv file with 12,426 records and got the following log message. Could anyone help me understand the following messages in the log?

 

1. NOTE: Invalid data for ZIP in line 44 93-100.

How can SAS detect it is an invalid data for ZIP variable?

 

2. I can see this temp data, MYDATA, in my WORK library. However, I can not proc print or see the contents of this data. Is it because the following error message below in my Question 3?

 

3. ERROR: Import unsuccessful. See SAS Log for details.

I am not certain I understand this error message well. What caused the unsuccessful importing?

 

(Log file begins)

1

2 PROC IMPORT DATAFILE = "D:\data\T_FIRM_176_20130121.csv"

3 OUT = MYDATA DBMS = CSV REPLACE;

4 DELIMITER = ',';

5 GETNAMES = YES;

6 RUN;

7 /**********************************************************************

8 * PRODUCT: SAS

9 * VERSION: 9.4

10 * CREATOR: External File Interface

11 * DATE: 29APR16

12 * DESC: Generated SAS Datastep Code

13 * TEMPLATE SOURCE: (None Specified.)

14 ***********************************************************************/

15 data WORK.MYDATA ;

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

17 infile D:\data\T_FIRM_176_20130121.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

18 informat FIRM_ID best32. ;

19 informat FIRM_NAME $44. ;

20 informat CAPITAL_MGT best32. ;

21 informat SEEK $2. ;

22 informat INVEST_TYPE $4. ;

23 informat FOUNDED mmddyy10. ;

24 informat FOUND_YEAR best32. ;

25 informat COUNTY $16. ;

26 informat STREET1 $27. ;

27 informat STREET2 $30. ;

28 informat CITY $13. ;

29 informat STATE $2. ;

30 informat ZIP best32. ;

31 informat NATION $2. ;

32 informat AREA_CODE best32. ;

33 informat PHONE $13. ;

34 informat FAX $13. ;

35 informat E_MAIL $21. ;

36 informat WEBSITE $20. ;

37 informat METRO_REGION_CODE best32. ;

38 informat REGION_CODE $2. ;

39 informat WORLD_REGION_CODE1 $2. ;

40 informat WORLD_REGION_CODE2 $2. ;

41 informat FILE_COUNTRY $2. ;

42 informat FILE_TYPE $2. ;

43 format FIRM_ID best12. ;

44 format FIRM_NAME $44. ;

45 format CAPITAL_MGT best12. ;

46 format SEEK $2. ;

47 format INVEST_TYPE $4. ;

48 format FOUNDED mmddyy10. ;

49 format FOUND_YEAR best12. ;

50 format COUNTY $16. ;

51 format STREET1 $27. ;

52 format STREET2 $30. ;

53 format CITY $13. ;

54 format STATE $2. ;

55 format ZIP best12. ;

56 format NATION $2. ;

57 format AREA_CODE best12. ;

58 format PHONE $13. ;

59 format FAX $13. ;

60 format E_MAIL $21. ;

61 format WEBSITE $20. ;

62 format METRO_REGION_CODE best12. ;

63 format REGION_CODE $2. ;

64 format WORLD_REGION_CODE1 $2. ;

65 format WORLD_REGION_CODE2 $2. ;

66 format FILE_COUNTRY $2. ;

67 format FILE_TYPE $2. ;

68 input

69 FIRM_ID

70 FIRM_NAME $

71 CAPITAL_MGT

72 SEEK $

73 INVEST_TYPE $

74 FOUNDED

75 FOUND_YEAR

76 COUNTY $

77 STREET1 $

78 STREET2 $

79 CITY $

80 STATE $

81 ZIP

82 NATION $

83 AREA_CODE

84 PHONE $

85 FAX $

86 E_MAIL $

87 WEBSITE $

88 METRO_REGION_CODE

89 REGION_CODE $

90 WORLD_REGION_CODE1 $

91 WORLD_REGION_CODE2 $

92 FILE_COUNTRY $

93 FILE_TYPE $

94 ;

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

96 run;

NOTE: The infile 'M:\VC_Project\Gladiator\QA\Data\Source_Data\VC DATA\Thomson Venture Expert -

Purchased\T_FIRM_176_20130121.csv' is:

Filename=M:\VC_Project\Gladiator\QA\Data\Source_Data\VC DATA\Thomson Venture Expert -

Purchased\T_FIRM_176_20130121.csv,

RECFM=V,LRECL=32767,File Size (bytes)=2143969,

Last Modified=26Oct2015:10:29:23,

Create Time=21Apr2016:14:53:17

NOTE: Invalid data for ZIP in line 28 76-82.

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

28 75614,Apex Ventures BV,8.4,AS,VC,1/1/2000,2000,,Liessentstraat 9A,,Uden,ZF,5405 AH,NT,,

88 31413243480,31413255738,ventures@apex.nl,,9999,ZF,EU,EW,US,VC 148

FIRM_ID=75614 FIRM_NAME=Apex Ventures BV CAPITAL_MGT=8.4 SEEK=AS INVEST_TYPE=VC

FOUNDED=01/01/2000 FOUND_YEAR=2000 COUNTY= STREET1=Liessentstraat 9A STREET2= CITY=Uden

STATE=ZF ZIP=. NATION=NT AREA_CODE=. PHONE=31413243480 FAX=31413255738 E_MAIL=ventures@apex.nl

WEBSITE= METRO_REGION_CODE=9999 REGION_CODE=ZF WORLD_REGION_CODE1=EU WORLD_REGION_CODE2=EW

FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=27

NOTE: Invalid data for ZIP in line 44 93-100.

44 20203,Bankers Trust International,,IN,VC,1/1/1982,1982,,1 Appold Street,Broadgate,Londo

88 n,ZF,EC2A 2HE,UK,,44-20-7982-3728,44-20-7982-3318,,,9999,ZF,EU,EW,US,VC 158

FIRM_ID=20203 FIRM_NAME=Bankers Trust International CAPITAL_MGT=. SEEK=IN INVEST_TYPE=VC

FOUNDED=01/01/1982 FOUND_YEAR=1982 COUNTY= STREET1=1 Appold Street STREET2=Broadgate CITY=London

STATE=ZF ZIP=. NATION=UK AREA_CODE=. PHONE=44-20-7982-37 FAX=44-20-7982-33 E_MAIL= WEBSITE=

METRO_REGION_CODE=9999 REGION_CODE=ZF WORLD_REGION_CODE1=EU WORLD_REGION_CODE2=EW FILE_COUNTRY=US

FILE_TYPE=VC _ERROR_=1 _N_=43

NOTE: Invalid data for ZIP in line 68 81-88.

68 98182,Seki Technotron Corp,,AS,VC,1/1/1948,1948,,5-6-30 Kiba` Koto-Ku,,Tokyo,ZF,135-004

88 2,JP,,813-3820-1711,813-3820-1735,,www.sekitech.com,9999,ZF,AS,SE,US,VC 158

FIRM_ID=98182 FIRM_NAME=Seki Technotron Corp CAPITAL_MGT=. SEEK=AS INVEST_TYPE=VC

FOUNDED=01/01/1948 FOUND_YEAR=1948 COUNTY= STREET1=5-6-30 Kiba` Koto-Ku STREET2= CITY=Tokyo

STATE=ZF ZIP=. NATION=JP AREA_CODE=. PHONE=813-3820-1711 FAX=813-3820-1735 E_MAIL=

WEBSITE=www.sekitech.com METRO_REGION_CODE=9999 REGION_CODE=ZF WORLD_REGION_CODE1=AS

WORLD_REGION_CODE2=SE FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=67

NOTE: Invalid data for ZIP in line 72 72-78.

72 97249,DSM Venturing BV,,AS,VC,1/1/1902,1902,,Mauritslaan 49,,Urmond,ZF,6129 EL,NT,,3146

88 4763684,31455719753,info.venturing@dsm.com,www.dsm-venturing.com,9999,ZF,EU,EW,US,VC 171

FIRM_ID=97249 FIRM_NAME=DSM Venturing BV CAPITAL_MGT=. SEEK=AS INVEST_TYPE=VC FOUNDED=01/01/1902

FOUND_YEAR=1902 COUNTY= STREET1=Mauritslaan 49 STREET2= CITY=Urmond STATE=ZF ZIP=. NATION=NT

AREA_CODE=. PHONE=31464763684 FAX=31455719753 E_MAIL=info.venturing@dsm.co

WEBSITE=www.dsm-venturing.co METRO_REGION_CODE=9999 REGION_CODE=ZF WORLD_REGION_CODE1=EU

WORLD_REGION_CODE2=EW FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=71

NOTE: Invalid data for ZIP in line 74 94-100.

74 120668,Middlefield Ventures,,AS,VC,1/1/1984,1984,,First Canadian Place,58th Floor,Toron

88 to,ON,M5X 1A6,CA,416,4163620714,4163627925,invest@middlefield.com,www.middlefield.com,9

175 999,CN,AA,AA,US,VC 192

FIRM_ID=120668 FIRM_NAME=Middlefield Ventures CAPITAL_MGT=. SEEK=AS INVEST_TYPE=VC

FOUNDED=01/01/1984 FOUND_YEAR=1984 COUNTY= STREET1=First Canadian Place STREET2=58th Floor

CITY=Toronto STATE=ON ZIP=. NATION=CA AREA_CODE=416 PHONE=4163620714 FAX=4163627925

E_MAIL=invest@middlefield.co WEBSITE=www.middlefield.com METRO_REGION_CODE=9999 REGION_CODE=CN

WORLD_REGION_CODE1=AA WORLD_REGION_CODE2=AA FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=73

NOTE: Invalid data for ZIP in line 88 83-90.

88 20719,Eiken Chemical` Ltd.,,IN,VC,2/20/1939,1939,,1-33-8 Hongo,Bonkyo-Ku,Tokyo,ZF,113-8

88 408,JP,,81-3-3813-5405,81-3-3813-2206,,www.eiken.co.jp,9999,ZF,AS,SE,US,VC 161

FIRM_ID=20719 FIRM_NAME=Eiken Chemical` Ltd. CAPITAL_MGT=. SEEK=IN INVEST_TYPE=VC

FOUNDED=02/20/1939 FOUND_YEAR=1939 COUNTY= STREET1=1-33-8 Hongo STREET2=Bonkyo-Ku CITY=Tokyo

STATE=ZF ZIP=. NATION=JP AREA_CODE=. PHONE=81-3-3813-540 FAX=81-3-3813-220 E_MAIL=

WEBSITE=www.eiken.co.jp METRO_REGION_CODE=9999 REGION_CODE=ZF WORLD_REGION_CODE1=AS

WORLD_REGION_CODE2=SE FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=87

NOTE: Invalid data for ZIP in line 94 85-92.

94 120861,UFJ Capital Co.` Ltd.,,IN,VC,1/1/1984,1984,,2-14-1 Kyobashi,Chuo-ku,Tokyo,ZF,104

88 -0031,JP,,,,,www.ufjcapital.co.jp,9999,ZF,AS,SE,US,VC 140

FIRM_ID=120861 FIRM_NAME=UFJ Capital Co.` Ltd. CAPITAL_MGT=. SEEK=IN INVEST_TYPE=VC

FOUNDED=01/01/1984 FOUND_YEAR=1984 COUNTY= STREET1=2-14-1 Kyobashi STREET2=Chuo-ku CITY=Tokyo

STATE=ZF ZIP=. NATION=JP AREA_CODE=. PHONE= FAX= E_MAIL= WEBSITE=www.ufjcapital.co.jp

METRO_REGION_CODE=9999 REGION_CODE=ZF WORLD_REGION_CODE1=AS WORLD_REGION_CODE2=SE FILE_COUNTRY=US

FILE_TYPE=VC _ERROR_=1 _N_=93

NOTE: Invalid data for ZIP in line 106 106-111.

106 282226,EcomPark Inc.,,AS,VC,1/1/2009,2009,,BCE Place` 181 Bay Street,Suite 3100` P.O. B

88 ox 830,Toronto,ON,M5J2T3,CA,416,416-216-0822,416-216-0823,,,9999,CN,AA,AA,US,VC 166

FIRM_ID=282226 FIRM_NAME=EcomPark Inc. CAPITAL_MGT=. SEEK=AS INVEST_TYPE=VC FOUNDED=01/01/2009

FOUND_YEAR=2009 COUNTY= STREET1=BCE Place` 181 Bay Street STREET2=Suite 3100` P.O. Box 830

CITY=Toronto STATE=ON ZIP=. NATION=CA AREA_CODE=416 PHONE=416-216-0822 FAX=416-216-0823 E_MAIL=

WEBSITE= METRO_REGION_CODE=9999 REGION_CODE=CN WORLD_REGION_CODE1=AA WORLD_REGION_CODE2=AA

FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=105

NOTE: Invalid data for ZIP in line 110 80-86.

110 35381,NPM Capital NV,921.3,AS,PE,1/1/1948,1948,,Breitnerstraat 1,,Amsterdam,ZF,1077 BL,

88 NT,,31205705555,31204706454,mails@npm-capital.com,www.npm-capital.com,9999,ZF,EU,EW,US,

175 VC 176

FIRM_ID=35381 FIRM_NAME=NPM Capital NV CAPITAL_MGT=921.3 SEEK=AS INVEST_TYPE=PE

FOUNDED=01/01/1948 FOUND_YEAR=1948 COUNTY= STREET1=Breitnerstraat 1 STREET2= CITY=Amsterdam

STATE=ZF ZIP=. NATION=NT AREA_CODE=. PHONE=31205705555 FAX=31204706454

E_MAIL=mails@npm-capital.com WEBSITE=www.npm-capital.com METRO_REGION_CODE=9999 REGION_CODE=ZF

WORLD_REGION_CODE1=EU WORLD_REGION_CODE2=EW FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=109

NOTE: Invalid data for ZIP in line 113 106-112.

113 94297,Serome Technology` Inc,,DE,VC,1/1/1993,1993,,666-14` Bae-gang B/D,Shinsa-dong` Ka

88 ngnam-gu,Seoul,ZF,135 120,SK,,822-2056-7800,822-517-4234,business@serome.co.kr,www.sero

175 me.co.kr,9999,ZF,AS,SE,US,VC 202

FIRM_ID=94297 FIRM_NAME=Serome Technology` Inc CAPITAL_MGT=. SEEK=DE INVEST_TYPE=VC

FOUNDED=01/01/1993 FOUND_YEAR=1993 COUNTY= STREET1=666-14` Bae-gang B/D

STREET2=Shinsa-dong` Kangnam-gu CITY=Seoul STATE=ZF ZIP=. NATION=SK AREA_CODE=.

PHONE=822-2056-7800 FAX=822-517-4234 E_MAIL=business@serome.co.kr WEBSITE=www.serome.co.kr

METRO_REGION_CODE=9999 REGION_CODE=ZF WORLD_REGION_CODE1=AS WORLD_REGION_CODE2=SE FILE_COUNTRY=US

FILE_TYPE=VC _ERROR_=1 _N_=112

NOTE: Invalid data for ZIP in line 124 84-90.

124 312322,Spinnaker Capital Group,,AS,VC,1/1/2010,2010,,6 Grosvenor Street,,London,ZF,W1K

88 4DJ,UK,,44-20-7903-2900,44-20-7903-2999,,www.spinnakercapital.com,9999,ZF,EU,EW,US,VC 172

FIRM_ID=312322 FIRM_NAME=Spinnaker Capital Group CAPITAL_MGT=. SEEK=AS INVEST_TYPE=VC

FOUNDED=01/01/2010 FOUND_YEAR=2010 COUNTY= STREET1=6 Grosvenor Street STREET2= CITY=London

STATE=ZF ZIP=. NATION=UK AREA_CODE=. PHONE=44-20-7903-29 FAX=44-20-7903-29 E_MAIL=

WEBSITE=www.spinnakercapital METRO_REGION_CODE=9999 REGION_CODE=ZF WORLD_REGION_CODE1=EU

WORLD_REGION_CODE2=EW FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=123

NOTE: Invalid data for ZIP in line 126 103-108.

126 280800,Emerging ISV Capital Partners,,AS,VC,1/1/2009,2009,,179 Riverview Circle Southea

88 st,,Calgary,AB,T2C4J9,CA,403,4035605469,,info@emergingisv.com,www.emergingisv.com,9999,

175 CN,AA,AA,US,VC 188

FIRM_ID=280800 FIRM_NAME=Emerging ISV Capital Partners CAPITAL_MGT=. SEEK=AS INVEST_TYPE=VC

FOUNDED=01/01/2009 FOUND_YEAR=2009 COUNTY= STREET1=179 Riverview Circle Southe STREET2=

CITY=Calgary STATE=AB ZIP=. NATION=CA AREA_CODE=403 PHONE=4035605469 FAX=

E_MAIL=info@emergingisv.com WEBSITE=www.emergingisv.com METRO_REGION_CODE=9999 REGION_CODE=CN

WORLD_REGION_CODE1=AA WORLD_REGION_CODE2=AA FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=125

NOTE: Invalid data for ZIP in line 145 119-125.

145 65183,GVFL` Ltd.,34.6,AS,VC,7/2/1990,1990,,B/h Popular House` Ashram Road,1st Flr` Prem

88 chand House Annex,Ahmedabad,ZF,380 009,IN,,9.1794E+11,9.17927E+11,info@gvfl.com,www.gvf

175 l.com,9999,ZF,AS,SS,US,VC 199

FIRM_ID=65183 FIRM_NAME=GVFL` Ltd. CAPITAL_MGT=34.6 SEEK=AS INVEST_TYPE=VC FOUNDED=07/02/1990

FOUND_YEAR=1990 COUNTY= STREET1=B/h Popular House` Ashram R

STREET2=1st Flr` Premchand House Annex CITY=Ahmedabad STATE=ZF ZIP=. NATION=IN AREA_CODE=.

PHONE=9.1794E+11 FAX=9.17927E+11 E_MAIL=info@gvfl.com WEBSITE=www.gvfl.com METRO_REGION_CODE=9999

REGION_CODE=ZF WORLD_REGION_CODE1=AS WORLD_REGION_CODE2=SS FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1

_N_=144

NOTE: Invalid data for ZIP in line 194 89-95.

194 59547,Beamscope Canada` Inc.,,DE,VC,1/1/1997,1997,,35 Ironside Crescent,,Scarborough,ON

88 ,M1X 1G5,CA,416,416-291-0000,,,,9999,CN,AA,AA,US,VC 138

FIRM_ID=59547 FIRM_NAME=Beamscope Canada` Inc. CAPITAL_MGT=. SEEK=DE INVEST_TYPE=VC

FOUNDED=01/01/1997 FOUND_YEAR=1997 COUNTY= STREET1=35 Ironside Crescent STREET2=

CITY=Scarborough STATE=ON ZIP=. NATION=CA AREA_CODE=416 PHONE=416-291-0000 FAX= E_MAIL=

WEBSITE= METRO_REGION_CODE=9999 REGION_CODE=CN WORLD_REGION_CODE1=AA WORLD_REGION_CODE2=AA

FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=193

NOTE: Invalid data for ZIP in line 196 112-118.

196 92884,Nextlink` Ltd.,,AS,VC,2/1/1994,1994,,710 Techno Park #145,Yatap-dong` Bungdang-gu

88 ,Sungnam` Gyunggi-do,ZF,463-760,SK,,82-31-737-6060,82-31-737-6263,,www.nextlink.co.kr,9

175 999,ZF,AS,SE,US,VC 192

FIRM_ID=92884 FIRM_NAME=Nextlink` Ltd. CAPITAL_MGT=. SEEK=AS INVEST_TYPE=VC FOUNDED=02/01/1994

FOUND_YEAR=1994 COUNTY= STREET1=710 Techno Park #145 STREET2=Yatap-dong` Bungdang-gu

CITY=Sungnam` Gyun STATE=ZF ZIP=. NATION=SK AREA_CODE=. PHONE=82-31-737-606 FAX=82-31-737-626

E_MAIL= WEBSITE=www.nextlink.co.kr METRO_REGION_CODE=9999 REGION_CODE=ZF WORLD_REGION_CODE1=AS

WORLD_REGION_CODE2=SE FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=195

NOTE: Invalid data for ZIP in line 203 102-108.

203 243606,BC Advantage Funds` Ltd.,0.094,AS,VC,1/1/2004,2004,,221 West Esplanade,Suite 410

88 ,Vancouver,BC,V7M 3J3,CA,604,6046886877,6046886166,info@bcaf.ca,www.bcadvantagefunds.co

175 m,9999,CN,AA,AA,US,VC 195

FIRM_ID=243606 FIRM_NAME=BC Advantage Funds` Ltd. CAPITAL_MGT=0.094 SEEK=AS INVEST_TYPE=VC

FOUNDED=01/01/2004 FOUND_YEAR=2004 COUNTY= STREET1=221 West Esplanade STREET2=Suite 410

CITY=Vancouver STATE=BC ZIP=. NATION=CA AREA_CODE=604 PHONE=6046886877 FAX=6046886166

E_MAIL=info@bcaf.ca WEBSITE=www.bcadvantagefunds METRO_REGION_CODE=9999 REGION_CODE=CN

WORLD_REGION_CODE1=AA WORLD_REGION_CODE2=AA FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=202

NOTE: Invalid data for ZIP in line 206 104-111.

206 98709,August Equity LLP,487.3,AS,BO,10/1/2001,2001,,10 Slingsby Place,St. Martin's Cour

88 tyard,London,ZF,WC2E 9AB,UK,,4.42076E+11,4.42076E+11,info@augustequity.com,www.augusteq

175 uity.com,9999,ZF,EU,EW,US,VC 202

FIRM_ID=98709 FIRM_NAME=August Equity LLP CAPITAL_MGT=487.3 SEEK=AS INVEST_TYPE=BO

FOUNDED=10/01/2001 FOUND_YEAR=2001 COUNTY= STREET1=10 Slingsby Place

STREET2=St. Martin's Courtyard CITY=London STATE=ZF ZIP=. NATION=UK AREA_CODE=. PHONE=4.42076E+11

FAX=4.42076E+11 E_MAIL=info@augustequity.com WEBSITE=www.augustequity.com METRO_REGION_CODE=9999

REGION_CODE=ZF WORLD_REGION_CODE1=EU WORLD_REGION_CODE2=EW FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1

_N_=205

NOTE: Invalid data for ZIP in line 237 83-90.

237 22008,Sharp Corporation,,IN,VC,1/1/1985,1985,,22-22 Nagaike-cho,Abeno-ku,Osaka,ZF,545-8

88 522,JP,,81-6-6621-1221,,,sharp-world.com,9999,ZF,AS,SE,US,VC 147

FIRM_ID=22008 FIRM_NAME=Sharp Corporation CAPITAL_MGT=. SEEK=IN INVEST_TYPE=VC FOUNDED=01/01/1985

FOUND_YEAR=1985 COUNTY= STREET1=22-22 Nagaike-cho STREET2=Abeno-ku CITY=Osaka STATE=ZF ZIP=.

NATION=JP AREA_CODE=. PHONE=81-6-6621-122 FAX= E_MAIL= WEBSITE=sharp-world.com

METRO_REGION_CODE=9999 REGION_CODE=ZF WORLD_REGION_CODE1=AS WORLD_REGION_CODE2=SE FILE_COUNTRY=US

FILE_TYPE=VC _ERROR_=1 _N_=236

NOTE: Invalid data for ZIP in line 238 94-99.

238 77477,Odlander` Fredrikson & Co. AB,1121.8,AS,VC,1/1/1996,1996,,Strandvagen 5B,,Stockho

88 lm,ZF,114 51,SW,,4684425850,4684425879,,www.healthcap.se,9999,ZF,EU,EN,US,VC 163

FIRM_ID=77477 FIRM_NAME=Odlander` Fredrikson & Co. AB CAPITAL_MGT=1121.8 SEEK=AS INVEST_TYPE=VC

FOUNDED=01/01/1996 FOUND_YEAR=1996 COUNTY= STREET1=Strandvagen 5B STREET2= CITY=Stockholm

STATE=ZF ZIP=. NATION=SW AREA_CODE=. PHONE=4684425850 FAX=4684425879 E_MAIL=

WEBSITE=www.healthcap.se METRO_REGION_CODE=9999 REGION_CODE=ZF WORLD_REGION_CODE1=EU

WORLD_REGION_CODE2=EN FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=237

NOTE: Invalid data for ZIP in line 279 77-83.

WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.

279 33782,Gestion BioCapital Inc.,,IN,VC,1/1/1995,1995,,,Bureau 910,Montreal,ON,H7T 2S3,CA,

88 514,514-847-5750,514-687-9283,,,9999,CN,AA,AA,US,VC 138

FIRM_ID=33782 FIRM_NAME=Gestion BioCapital Inc. CAPITAL_MGT=. SEEK=IN INVEST_TYPE=VC

FOUNDED=01/01/1995 FOUND_YEAR=1995 COUNTY= STREET1= STREET2=Bureau 910 CITY=Montreal STATE=ON

ZIP=. NATION=CA AREA_CODE=514 PHONE=514-847-5750 FAX=514-687-9283 E_MAIL= WEBSITE=

METRO_REGION_CODE=9999 REGION_CODE=CN WORLD_REGION_CODE1=AA WORLD_REGION_CODE2=AA FILE_COUNTRY=US

FILE_TYPE=VC _ERROR_=1 _N_=278

NOTE: 12425 records were read from the infile 'MD:\data\T_FIRM_176_20130121.csv'.

The minimum record length was 64.

The maximum record length was 271.

NOTE: The data set WORK.MYDATA has 12425 observations and 25 variables.

NOTE: DATA statement used (Total process time):

real time 0.27 seconds

cpu time 0.12 seconds

 

Errors detected in submitted DATA step. Examine log.

12425 rows created in WORK.MYDATA from D:\data\T_FIRM_176_20130121.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 0.59 seconds

cpu time 0.23 seconds

(Log file ends)

 


Accepted Solutions
Solution
‎04-29-2016 03:45 PM
Trusted Advisor
Posts: 1,115

Re: proc import - unsuccessful

[ Edited ]

Hi @mehong19,

 

PROC IMPORT scans a number of rows of the input file to determine the appropriate data type and length of variables. The default number of rows is 20. Based on the first 20 rows of your .csv file, SAS found it appropriate to define ZIP as a numeric variable and to use informat best32. to read it (see line 30 of the log).

 

However, later rows in the file contain zip codes (from countries such as the UK) which contain letters. These cannot be read into numeric variables. The "Invalid data for ZIP in line 44 93-100," for example, can be identified as the string "EC2A 2HE" (which is a valid London postcode, but invalid data for a numeric variable in a SAS dataset).

 

So, the correct decision (of the IMPORT procedure) at the beginning would have been to read ZIP as a character variable. To help SAS in determining the correct data types and lengths, you can add the GUESSINGROWS statement to your PROC IMPORT step (e.g. after the GETNAMES statement) with a (row) number n greater than the default value of 20, i.e. large enough to include rows with non-numeric zip codes. Or, to be on the safe side, you can specify:

GUESSINGROWS=MAX;

If you are lucky (and SAS is "guessing" everything correctly), this will already solve all the issues.

View solution in original post


All Replies
Solution
‎04-29-2016 03:45 PM
Trusted Advisor
Posts: 1,115

Re: proc import - unsuccessful

[ Edited ]

Hi @mehong19,

 

PROC IMPORT scans a number of rows of the input file to determine the appropriate data type and length of variables. The default number of rows is 20. Based on the first 20 rows of your .csv file, SAS found it appropriate to define ZIP as a numeric variable and to use informat best32. to read it (see line 30 of the log).

 

However, later rows in the file contain zip codes (from countries such as the UK) which contain letters. These cannot be read into numeric variables. The "Invalid data for ZIP in line 44 93-100," for example, can be identified as the string "EC2A 2HE" (which is a valid London postcode, but invalid data for a numeric variable in a SAS dataset).

 

So, the correct decision (of the IMPORT procedure) at the beginning would have been to read ZIP as a character variable. To help SAS in determining the correct data types and lengths, you can add the GUESSINGROWS statement to your PROC IMPORT step (e.g. after the GETNAMES statement) with a (row) number n greater than the default value of 20, i.e. large enough to include rows with non-numeric zip codes. Or, to be on the safe side, you can specify:

GUESSINGROWS=MAX;

If you are lucky (and SAS is "guessing" everything correctly), this will already solve all the issues.

Established User
Posts: 1

Re: proc import - unsuccessful

GUESSINGROWS=

 

We have a winner! Thank you! Smiley Happy

Super User
Posts: 17,958

Re: proc import - unsuccessful

I'm going to highlight a piece of the log for you to illustrate what SAS is trying to tell you.

 

The data for ZIP for that field is 5405 AH, while SAS is expecting a numeric value. So for SAS this is an invalid data point. You could have an invalid ZIP, such as 00000 or 99999 and SAS wouldn't care because it met the set requirements which is a numeric value. 

 

Hope that helps with your debugging. 

 

NOTE: Invalid data for ZIP in line 28 76-82.

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

28 75614,Apex Ventures BV,8.4,AS,VC,1/1/2000,2000,,Liessentstraat 9A,,Uden,ZF,5405 AH,NT,,

88 31413243480,31413255738,ventures@apex.nl,,9999,ZF,EU,EW,US,VC 148

FIRM_ID=75614 FIRM_NAME=Apex Ventures BV CAPITAL_MGT=8.4 SEEK=AS INVEST_TYPE=VC

FOUNDED=01/01/2000 FOUND_YEAR=2000 COUNTY= STREET1=Liessentstraat 9A STREET2= CITY=Uden

STATE=ZF ZIP=. NATION=NT AREA_CODE=. PHONE=31413243480 FAX=31413255738 E_MAIL=ventures@apex.nl

WEBSITE= METRO_REGION_CODE=9999 REGION_CODE=ZF WORLD_REGION_CODE1=EU WORLD_REGION_CODE2=EW

FILE_COUNTRY=US FILE_TYPE=VC _ERROR_=1 _N_=27

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 1590 views
  • 4 likes
  • 4 in conversation