Help using Base SAS procedures

Import CSV error

Reply
Contributor
Posts: 27

Import CSV error

Hi Sas community, 

 

I have a problem when I import a CSV file. Could anyone help me ? here is my code:

 

proc import datafile="C:\Users\Asus\Desktop\file.csv"

out=table

dbms=dlm

replace;

delimiter=','; 

getnames=yes;
GUESSINGROWS=MAX;
run;

 

 

Here is the entire report:

data WORK.TABLE ;
48 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
49 infile 'C:\Users\Asus\Desktop\Submitted Trade.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
50 informat trade_customer_id $297. ;
51 informat "Trade customer phone number"N best32. ;
52 informat name $64. ;
53 informat position $4. ;
54 informat reference_number $10. ;
55 informat created_at anydtdtm40. ;
56 informat updated_at anydtdtm40. ;
2 Le Système SAS 08:34 Wednesday, June 13, 2018

57 informat status $10. ;
58 informat valid_till_ts best32. ;
59 informat origin_country $4. ;
60 informat trade_term $4. ;
61 informat pickup_gplace_id $27. ;
62 informat pickup_gplace_name $30. ;
63 informat shipment_date_ts $10. ;
64 informat packaging_type $11. ;
65 informat packaging_note $59. ;
66 informat quantity_value $7. ;
67 informat quantity_unit $4. ;
68 informat price_value $6. ;
69 informat price_currency $4. ;
70 informat payment_term $4. ;
71 informat "price_unit;"N $5. ;
72 format trade_customer_id $297. ;
73 format "Trade customer phone number"N best12. ;
74 format name $64. ;
75 format position $4. ;
76 format reference_number $10. ;
77 format created_at datetime. ;
78 format updated_at datetime. ;
79 format status $10. ;
80 format valid_till_ts best12. ;
81 format origin_country $4. ;
82 format trade_term $4. ;
83 format pickup_gplace_id $27. ;
84 format pickup_gplace_name $30. ;
85 format shipment_date_ts $10. ;
86 format packaging_type $11. ;
87 format packaging_note $59. ;
88 format quantity_value $7. ;
89 format quantity_unit $4. ;
90 format price_value $6. ;
91 format price_currency $4. ;
92 format payment_term $4. ;
93 format "price_unit;"N $5. ;
94 input
95 trade_customer_id $
96 "Trade customer phone number"N
97 name $
98 position $
99 reference_number $
100 created_at
101 updated_at
102 status $
103 valid_till_ts
104 origin_country $
105 trade_term $
106 pickup_gplace_id $
107 pickup_gplace_name $
108 shipment_date_ts $
109 packaging_type $
110 packaging_note $
111 quantity_value $
112 quantity_unit $
113 price_value $
114 price_currency $
3 Le Système SAS 08:34 Wednesday, June 13, 2018

115 payment_term $
116 "price_unit;"N $
117 ;
118 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
119 run;

NOTE: The infile 'C:\Users\Asus\Desktop\Submitted Trade.csv' is:
Nom du fichier=C:\Users\Asus\Desktop\Submitted Trade.csv,
RECFM=V,LRECL=32767,
Taille de fichier (octets)=403803,
Modifié(e) le=23 mai 2018 10 h 58,
Heure de création=21 mai 2018 17 h 00

NOTE: Invalid data for valid_till_ts in line 391 81-84.
REGLE : ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
391 "1282,6.28125E+12,""PAO, PFAD"",sell,QN17101282,10/24/2017 6:55,10/24/2017 6:55,open,1509429650,ID,F
101 OB,NULL,""Dumai, Indonesia"",1510987831,Bulk Vessel,NULL,3000,MT,425,USD,NULL,MT"; 182
trade_customer_id="1282 'Trade customer phone number'n=6.28125E12 name=""PAO position=PFAD reference_number=sell created_at=.
updated_at=24OCT17:06:55:00 status=10/24/2017 valid_till_ts=. origin_country=1509 trade_term=ID pickup_gplace_id=FOB
pickup_gplace_name=NULL shipment_date_ts=""Dumai packaging_type=Indonesia"" packaging_note=1510987831 quantity_value=Bulk Ve
quantity_unit=NULL price_value=3000 price_currency=MT payment_term=425 'price_unit;'n=USD _ERROR_=1 _N_=390
NOTE: Invalid data for valid_till_ts in line 575 94-108.
575 "1365,9.19664E+11,""Used Cooking Oil, Glycerine, Bio Diesel"",buy,EN17111365,11/2/2017 14:07,11/2/20
101 17 14:07,open,1510234235,IN,CIF,NULL,""Chennai Central, Chennai, Tamil Nadu, India"",1510236306,Drum
201 s,NULL,100,MT,250,USD,NULL,MT"; 231
trade_customer_id="1365 'Trade customer phone number'n=919664000000 name=""Used Cooking Oil position=Glyc
reference_number=Bio Diesel created_at=. updated_at=. status=11/2/2017 valid_till_ts=. origin_country=open trade_term=1510
pickup_gplace_id=IN pickup_gplace_name=CIF shipment_date_ts=NULL packaging_type=""Chennai C packaging_note=Chennai
quantity_value=Tamil N quantity_unit=Indi price_value=151023 price_currency=Drum payment_term=NULL 'price_unit;'n=100 _ERROR_=1
_N_=574
NOTE: Invalid data for valid_till_ts in line 648 78-84.
648 "615,60122515388,""Glycerin 99,5"",buy,EN1704615,4/4/2017 4:08,4/4/2017 4:08,invalid,1491868800,MY,C
101 FR,ChIJlZx5MuurzTER3b6plhNWg30,""Port Klang, Selangor Malaysia"",1491868800,Drums,NULL,20,MT,0,USD,N
201 ULL,MT"; 208
trade_customer_id="615 'Trade customer phone number'n=60122515388 name=""Glycerin 99 position=5"" reference_number=buy created_at=.
updated_at=04APR17:04:08:00 status=4/4/2017 4 valid_till_ts=. origin_country=1491 trade_term=MY pickup_gplace_id=CFR
pickup_gplace_name=ChIJlZx5MuurzTER3b6plhNWg30 shipment_date_ts=""Port Kla packaging_type=Selangor Ma packaging_note=1491868800
quantity_value=Drums quantity_unit=NULL price_value=20 price_currency=MT payment_term=0 'price_unit;'n=USD _ERROR_=1 _N_=647
NOTE: Invalid data for valid_till_ts in line 649 82-85.
649 "624,6581686320,""Glycerin 99,5"",sell,QN1704624,4/11/2017 10:34,4/11/2017 10:34,open,1492509631,MY,
101 FOB,NULL,""Malaysia, null"",1496226706,Drums,250 Kg Drums Wirhout Pallet,100,MT,100,USD,NULL,MT"; 198
trade_customer_id="624 'Trade customer phone number'n=6581686320 name=""Glycerin 99 position=5"" reference_number=sell created_at=.
updated_at=04NOV17:10:34:00 status=4/11/2017 valid_till_ts=. origin_country=1492 trade_term=MY pickup_gplace_id=FOB
pickup_gplace_name=NULL shipment_date_ts=""Malaysia packaging_type=null"" packaging_note=1496226706 quantity_value=Drums
quantity_unit=250 price_value=100 price_currency=MT payment_term=100 'price_unit;'n=USD _ERROR_=1 _N_=648
NOTE: Invalid data for valid_till_ts in line 650 82-85.
650 "1998,4.91521E+12,""Glycerin 99,5"",sell,QN18031998,3/7/2018 9:04,3/7/2018 10:28,open,1521018003,DE,
101 CIF,NULL, ,1521018207,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL"; 163
trade_customer_id="1998 'Trade customer phone number'n=4.91521E12 name=""Glycerin 99 position=5"" reference_number=sell created_at=.
updated_at=03JUL18:09:04:00 status=3/7/2018 1 valid_till_ts=. origin_country=1521 trade_term=DE pickup_gplace_id=CIF
pickup_gplace_name=NULL shipment_date_ts= packaging_type=1521018207 packaging_note=NULL quantity_value=NULL quantity_unit=NULL
price_value=NULL price_currency=NULL payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=649
NOTE: Invalid data for valid_till_ts in line 670 56-63.
670 "96,,""PKFAD, PFAD, CFAD, CCNO, Mixed Fatty Acid"",buy,EN170196,1/4/2017 3:43,1/4/2017 3:43,open,148
101 4107200,NULL,CIF,ChIJ0W7yfup5AjoRp3O7s89BUu4,""Kolkata Port Trust Quarters Kolkata, West Bengal, Ind
201 ia"",1484841600,Drums,NULL,40,MT,NULL,NULL,NULL,NULL"; 254
trade_customer_id="96 'Trade customer phone number'n=. name=""PKFAD position=PFAD reference_number=CFAD created_at=. updated_at=.
status=buy valid_till_ts=. origin_country=1/4/ trade_term=1/4/ pickup_gplace_id=open pickup_gplace_name=1484107200
4 Le Système SAS 08:34 Wednesday, June 13, 2018

shipment_date_ts=NULL packaging_type=CIF packaging_note=ChIJ0W7yfup5AjoRp3O7s89BUu4 quantity_value=""Kolka quantity_unit=West
price_value=India" price_currency=1484 payment_term=Drum 'price_unit;'n=NULL _ERROR_=1 _N_=669
NOTE: Invalid data for valid_till_ts in line 682 96-99.
REGLE : ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
682 "119,6596265190,""C8 Alcohol 99% (C6<0.3, C10<0.3)"",buy,EN1701119,1/4/2017 4:55,1/4/2017 4:55,open,
101 1484110800,NULL,CIF,ChIJYRKcR9DYszUR3RPGgIF3R3E,""Zhangjiagang Suzhou, Jiangsu, China"",1485964800,D
201 rums,New Drums Palletised,100,MT,NULL,NULL,NULL,NULL"; 254
trade_customer_id="119 'Trade customer phone number'n=6596265190 name=""C8 Alcohol 99% (C6<0.3 position=C10< reference_number=buy
created_at=. updated_at=01APR17:04:55:00 status=1/4/2017 4 valid_till_ts=. origin_country=1484 trade_term=NULL pickup_gplace_id=CIF
pickup_gplace_name=ChIJYRKcR9DYszUR3RPGgIF3R3E shipment_date_ts=""Zhangjia packaging_type=Jiangsu packaging_note=China""
quantity_value=1485964 quantity_unit=Drum price_value=New Dr price_currency=100 payment_term=MT 'price_unit;'n=NULL _ERROR_=1
_N_=681
NOTE: Invalid data for valid_till_ts in line 790 88-91.
790 "220,,""glyceryl behenate (C22, 85 plus)"",buy,EN1701220,1/5/2017 10:00,1/5/2017 10:00,open,14842152
101 00,NULL,NULL,NULL,NULL,NULL,Bags,25kg bags,5500,lb,7.5,USD,NULL,NULL"; 170
trade_customer_id="220 'Trade customer phone number'n=. name=""glyceryl behenate (C22 position=85 p reference_number=buy
created_at=. updated_at=01MAY17:10:00:00 status=1/5/2017 1 valid_till_ts=. origin_country=1484 trade_term=NULL pickup_gplace_id=NULL
pickup_gplace_name=NULL shipment_date_ts=NULL packaging_type=NULL packaging_note=Bags quantity_value=25kg ba quantity_unit=5500
price_value=lb price_currency=7.5 payment_term=USD 'price_unit;'n=NULL _ERROR_=1 _N_=789
NOTE: Invalid data for valid_till_ts in line 866 89-92.
866 "267,6.28119E+11,""RBD Coconut Oil, RBDCNO"",sell,QN1701267,1/6/2017 6:51,1/6/2017 6:51,open,1483945
101 200,ID,FOB,NULL,""Surabaya, Indonesia"",1485709200,Bags,18kg,100,MT,1905,USD,NULL,NULL"; 188
trade_customer_id="267 'Trade customer phone number'n=628119000000 name=""RBD Coconut Oil position=RBDC reference_number=sell
created_at=. updated_at=01JUN17:06:51:00 status=1/6/2017 6 valid_till_ts=. origin_country=1483 trade_term=ID pickup_gplace_id=FOB
pickup_gplace_name=NULL shipment_date_ts=""Surabaya packaging_type=Indonesia"" packaging_note=1485709200 quantity_value=Bags
quantity_unit=18kg price_value=100 price_currency=MT payment_term=1905 'price_unit;'n=USD _ERROR_=1 _N_=865
NOTE: Invalid data for valid_till_ts in line 867 92-95.
867 "1143,9.23332E+11,""RBD Coconut Oil, RBDCNO"",buy,EN17091143,9/21/2017 9:33,9/21/2017 9:33,open,1506
101 585600,VN,NULL,NULL,NULL,NULL,Flexibags,NULL,3,FCL,NULL,NULL,NULL,MT"; 170
trade_customer_id="1143 'Trade customer phone number'n=923332000000 name=""RBD Coconut Oil position=RBDC reference_number=buy
created_at=. updated_at=21SEP17:09:33:00 status=9/21/2017 valid_till_ts=. origin_country=1506 trade_term=VN pickup_gplace_id=NULL
pickup_gplace_name=NULL shipment_date_ts=NULL packaging_type=NULL packaging_note=Flexibags quantity_value=NULL quantity_unit=3
price_value=FCL price_currency=NULL payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=866
NOTE: Invalid data for valid_till_ts in line 1053 84-87.
1053 "2141,6.28231E+12,""UCO, Biodiesel"",sell,QN18042140,4/13/2018 4:46,4/13/2018 4:46,open,1524196844,I
101 D,FOB,NULL,Samarinda,1524199493,Flexibags,NULL,50,MT,500,USD,NULL,MT"; 170
trade_customer_id="2141 'Trade customer phone number'n=6.28231E12 name=""UCO position=Biod reference_number=sell created_at=.
updated_at=13APR18:04:46:00 status=4/13/2018 valid_till_ts=. origin_country=1524 trade_term=ID pickup_gplace_id=FOB
pickup_gplace_name=NULL shipment_date_ts=Samarinda packaging_type=1524199493 packaging_note=Flexibags quantity_value=NULL
quantity_unit=50 price_value=MT price_currency=500 payment_term=USD 'price_unit;'n=NULL _ERROR_=1 _N_=1052
NOTE: Invalid data for valid_till_ts in line 1067 81-84.
1067 "482,17066226770,""1,3 Propandiol"",buy,EN1701482,1/20/2017 4:04,1/25/2017 2:18,open,1485406800,NULL
101 ,CIF,ChIJj47ynNZ0YIgRULdClZJ3iSU,""Dalton GA, United States"",NULL,NULL,NULL,1,FCL,NULL,NULL,NULL,MT
201 "; 202
trade_customer_id="482 'Trade customer phone number'n=17066226770 name=""1 position=3 Pr reference_number=buy created_at=.
updated_at=20JAN17:04:04:00 status=1/25/2017 valid_till_ts=. origin_country=1485 trade_term=NULL pickup_gplace_id=CIF
pickup_gplace_name=ChIJj47ynNZ0YIgRULdClZJ3iSU shipment_date_ts=""Dalton G packaging_type=United Stat packaging_note=NULL
quantity_value=NULL quantity_unit=NULL price_value=1 price_currency=FCL payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1066
NOTE: Invalid data for valid_till_ts in line 1068 81-87.
1068 "483,9.19828E+11,""1,3 Propandiol"",buy,EN1701483,1/20/2017 4:11,1/20/2017 4:11,invalid,1485406800,I
101 N,FOB,NULL,NULL,NULL,NULL,NULL,1,FCL,NULL,NULL,NULL,MT"; 156
trade_customer_id="483 'Trade customer phone number'n=919828000000 name=""1 position=3 Pr reference_number=buy created_at=.
updated_at=20JAN17:04:11:00 status=1/20/2017 valid_till_ts=. origin_country=1485 trade_term=IN pickup_gplace_id=FOB
pickup_gplace_name=NULL shipment_date_ts=NULL packaging_type=NULL packaging_note=NULL quantity_value=NULL quantity_unit=1
price_value=FCL price_currency=NULL payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1067
NOTE: Invalid data for valid_till_ts in line 1105 98-111.
1105 "2134,8.86939E+11,""Palm Kernel Oil, Lauryl Alcohol, Laurie Acid, Stratic Acid"",sell,QN18042133,4/1
101 2/2018 3:11,4/12/2018 3:11,open,1524106810,TW,NULL,NULL,NULL,1524107431,NULL,NULL,NULL,NULL,NULL,NUL
5 Le Système SAS 08:34 Wednesday, June 13, 2018

REGLE : ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
201 L,NULL,NULL"; 213
trade_customer_id="2134 'Trade customer phone number'n=886939000000 name=""Palm Kernel Oil position=Laur reference_number=Laurie Aci
created_at=. updated_at=. status=QN18042133 valid_till_ts=. origin_country=4/12 trade_term=open pickup_gplace_id=1524106810
pickup_gplace_name=TW shipment_date_ts=NULL packaging_type=NULL packaging_note=NULL quantity_value=1524107 quantity_unit=NULL
price_value=NULL price_currency=NULL payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1104
NOTE: Invalid data for valid_till_ts in line 1136 72-75.
1136 "530,,""1,3 Propanediol"",sell,QN1701530,1/30/2017 6:40,1/30/2017 6:41,open,1488268800,IN,CIF,NULL,S
101 ingapore ,NULL,NULL,NULL,16,MT,3.2,USD,NULL,kg"; 148
trade_customer_id="530 'Trade customer phone number'n=. name=""1 position=3 Pr reference_number=sell created_at=.
updated_at=30JAN17:06:40:00 status=1/30/2017 valid_till_ts=. origin_country=1488 trade_term=IN pickup_gplace_id=CIF
pickup_gplace_name=NULL shipment_date_ts=Singapore packaging_type=NULL packaging_note=NULL quantity_value=NULL quantity_unit=16
price_value=MT price_currency=3.2 payment_term=USD 'price_unit;'n=NULL _ERROR_=1 _N_=1135
NOTE: Invalid data for valid_till_ts in line 1137 83-86.
1137 "564,60123267689,""1,3 Propanediol"",sell,QN1702564,2/10/2017 7:08,2/10/2017 7:08,open,1486789200,NU
101 LL,FOB,NULL,Shanghai,NULL,Drums,200kg,16,MT,3.24,USD,NULL,kg"; 162
trade_customer_id="564 'Trade customer phone number'n=60123267689 name=""1 position=3 Pr reference_number=sell created_at=.
updated_at=02OCT17:07:08:00 status=2/10/2017 valid_till_ts=. origin_country=1486 trade_term=NULL pickup_gplace_id=FOB
pickup_gplace_name=NULL shipment_date_ts=Shanghai packaging_type=NULL packaging_note=Drums quantity_value=200kg quantity_unit=16
price_value=MT price_currency=3.24 payment_term=USD 'price_unit;'n=NULL _ERROR_=1 _N_=1136
NOTE: Invalid data for valid_till_ts in line 1138 82-85.
1138 "793,17066226770,""1,3 Propanediol"",buy,EN1707793,7/2/2017 15:31,7/2/2017 15:31,open,1499572800,NUL
101 L,CIF,ChIJj47ynNZ0YIgRULdClZJ3iSU,""Dalton GA, United States"",NULL,NULL,NULL,NULL,MT,NULL,NULL,NULL
201 ,MT"; 205
trade_customer_id="793 'Trade customer phone number'n=17066226770 name=""1 position=3 Pr reference_number=buy created_at=.
updated_at=07FEB17:15:31:00 status=7/2/2017 1 valid_till_ts=. origin_country=1499 trade_term=NULL pickup_gplace_id=CIF
pickup_gplace_name=ChIJj47ynNZ0YIgRULdClZJ3iSU shipment_date_ts=""Dalton G packaging_type=United Stat packaging_note=NULL
quantity_value=NULL quantity_unit=NULL price_value=NULL price_currency=MT payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1137
NOTE: Invalid data for valid_till_ts in line 1529 81-94.
1529 "787,8.61392E+12,""Stearic Acid 1840, 1865, 1892"",buy,EN1706787,6/23/2017 3:41,6/23/2017 3:41,open,
101 1498795200,ID,CIF,ChIJa_D4gtUPljUR8_JMYfqCTWE,Qingdao,NULL,Bags,NULL,1000,MT,NULL,NULL,NULL,MT"; 196
trade_customer_id="787 'Trade customer phone number'n=8.61392E12 name=""Stearic Acid 1840 position=1865 reference_number=1892""
created_at=. updated_at=. status=6/23/2017 valid_till_ts=. origin_country=open trade_term=1498 pickup_gplace_id=ID
pickup_gplace_name=CIF shipment_date_ts=ChIJa_D4gt packaging_type=Qingdao packaging_note=NULL quantity_value=Bags quantity_unit=NULL
price_value=1000 price_currency=MT payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1528
NOTE: Invalid data for valid_till_ts in line 1530 72-85.
1530 "1230,,""Stearic Acid 1840, 1865, 1892"",buy,EN17101230,10/9/2017 9:34,10/9/2017 9:34,open,150814800
101 0,NULL,FOB,NULL,NULL,NULL,NULL,NULL,NULL,MT,NULL,NULL,NULL,MT"; 163
trade_customer_id="1230 'Trade customer phone number'n=. name=""Stearic Acid 1840 position=1865 reference_number=1892"" created_at=.
updated_at=. status=10/9/2017 valid_till_ts=. origin_country=open trade_term=1508 pickup_gplace_id=NULL pickup_gplace_name=FOB
shipment_date_ts=NULL packaging_type=NULL packaging_note=NULL quantity_value=NULL quantity_unit=NULL price_value=NULL
price_currency=MT payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1529
NOTE: Invalid data for valid_till_ts in line 1687 59-72.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
1687 "1026,,""LABSA, SLES, SLS"",buy,EN17071026,7/13/2017 9:49,7/13/2017 9:56,open,1500465600,NULL,NULL,N
101 ULL,NULL,NULL,NULL,NULL,NULL,MT,NULL,NULL,NULL,MT"; 151
trade_customer_id="1026 'Trade customer phone number'n=. name=""LABSA position=SLES reference_number=SLS"" created_at=. updated_at=.
status=7/13/2017 valid_till_ts=. origin_country=open trade_term=1500 pickup_gplace_id=NULL pickup_gplace_name=NULL
shipment_date_ts=NULL packaging_type=NULL packaging_note=NULL quantity_value=NULL quantity_unit=NULL price_value=NULL
price_currency=MT payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1686
NOTE: 2369 records were read from the infile 'C:\Users\Asus\Desktop\Submitted Trade.csv'.
The minimum record length was 126.
The maximum record length was 297.
NOTE: The data set WORK.TABLE has 2369 observations and 22 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

6 Le Système SAS 08:34 Wednesday, June 13, 2018


Erreurs détectées dans l'étape DATA soumise. Examinez le Journal.
2369 lignes créées dans WORK.TABLE à partir de C:\Users\Asus\Desktop\Submitted Trade.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 1.08 seconds
cpu time 0.89 seconds

120
121 GOPTIONS NOACCESSIBLE;
122 %LET _CLIENTTASKLABEL=;
123 %LET _CLIENTPROCESSFLOWNAME=;
124 %LET _CLIENTPROJECTPATH=;
125 %LET _CLIENTPROJECTPATHHOST=;
126 %LET _CLIENTPROJECTNAME=;
127 %LET _SASPROGRAMFILE=;
128 %LET _SASPROGRAMFILEHOST=;
129
130 ;*';*";*/;quit;run;
131 ODS _ALL_ CLOSE;
132
133
134 QUIT; RUN;
135

Super User
Posts: 23,778

Re: Import CSV error

Looks like a basic type mismatch. I suspect it may be the date time issue. In general, the way to fix these is to take the code and manually verify the types/lengths and modify the code accordingly to get the data you need.

 

The error is with the variable: valid_till_ts, which SAS thinks is a number for some reason:

informat valid_till_ts best32. ;

 

Check the row indicated, 390 (can't recall if it includes the header row or not), and see if the valid_till_ts is a number.

 

From the error log and code, valid_till_ts is your 9th variable and that row shows:

 

"1282,6.28125E+12,""PAO, PFAD"",

sell,

QN17101282,

10/24/2017 6:55,

10/24/2017 6:55,

open,

1509429650,

ID,

FOB, <- this is not a number

NULL 

 

Usually GUESSINGROWS=MAX means this doesn't occur so I'm surprised to see it occur. 

 


@madix wrote:

Hi Sas community, 

 

I have a problem when I import a CSV file. Could anyone help me ? here is my code:

 

proc import datafile="C:\Users\Asus\Desktop\file.csv"

out=table

dbms=dlm

replace;

delimiter=','; 

getnames=yes;
GUESSINGROWS=MAX;
run;

 

 

Here is the entire report:

data WORK.TABLE ;
48 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
49 infile 'C:\Users\Asus\Desktop\Submitted Trade.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
50 informat trade_customer_id $297. ;
51 informat "Trade customer phone number"N best32. ;
52 informat name $64. ;
53 informat position $4. ;
54 informat reference_number $10. ;
55 informat created_at anydtdtm40. ;
56 informat updated_at anydtdtm40. ;
2 Le Système SAS 08:34 Wednesday, June 13, 2018

57 informat status $10. ;
58 informat valid_till_ts best32. ;
59 informat origin_country $4. ;
60 informat trade_term $4. ;
61 informat pickup_gplace_id $27. ;
62 informat pickup_gplace_name $30. ;
63 informat shipment_date_ts $10. ;
64 informat packaging_type $11. ;
65 informat packaging_note $59. ;
66 informat quantity_value $7. ;
67 informat quantity_unit $4. ;
68 informat price_value $6. ;
69 informat price_currency $4. ;
70 informat payment_term $4. ;
71 informat "price_unit;"N $5. ;
72 format trade_customer_id $297. ;
73 format "Trade customer phone number"N best12. ;
74 format name $64. ;
75 format position $4. ;
76 format reference_number $10. ;
77 format created_at datetime. ;
78 format updated_at datetime. ;
79 format status $10. ;
80 format valid_till_ts best12. ;
81 format origin_country $4. ;
82 format trade_term $4. ;
83 format pickup_gplace_id $27. ;
84 format pickup_gplace_name $30. ;
85 format shipment_date_ts $10. ;
86 format packaging_type $11. ;
87 format packaging_note $59. ;
88 format quantity_value $7. ;
89 format quantity_unit $4. ;
90 format price_value $6. ;
91 format price_currency $4. ;
92 format payment_term $4. ;
93 format "price_unit;"N $5. ;
94 input
95 trade_customer_id $
96 "Trade customer phone number"N
97 name $
98 position $
99 reference_number $
100 created_at
101 updated_at
102 status $
103 valid_till_ts
104 origin_country $
105 trade_term $
106 pickup_gplace_id $
107 pickup_gplace_name $
108 shipment_date_ts $
109 packaging_type $
110 packaging_note $
111 quantity_value $
112 quantity_unit $
113 price_value $
114 price_currency $
3 Le Système SAS 08:34 Wednesday, June 13, 2018

115 payment_term $
116 "price_unit;"N $
117 ;
118 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
119 run;

NOTE: The infile 'C:\Users\Asus\Desktop\Submitted Trade.csv' is:
Nom du fichier=C:\Users\Asus\Desktop\Submitted Trade.csv,
RECFM=V,LRECL=32767,
Taille de fichier (octets)=403803,
Modifié(e) le=23 mai 2018 10 h 58,
Heure de création=21 mai 2018 17 h 00

NOTE: Invalid data for valid_till_ts in line 391 81-84.
REGLE : ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
391 "1282,6.28125E+12,""PAO, PFAD"",sell,QN17101282,10/24/2017 6:55,10/24/2017 6:55,open,1509429650,ID,F
101 OB,NULL,""Dumai, Indonesia"",1510987831,Bulk Vessel,NULL,3000,MT,425,USD,NULL,MT"; 182
trade_customer_id="1282 'Trade customer phone number'n=6.28125E12 name=""PAO position=PFAD reference_number=sell created_at=.
updated_at=24OCT17:06:55:00 status=10/24/2017 valid_till_ts=. origin_country=1509 trade_term=ID pickup_gplace_id=FOB
pickup_gplace_name=NULL shipment_date_ts=""Dumai packaging_type=Indonesia"" packaging_note=1510987831 quantity_value=Bulk Ve
quantity_unit=NULL price_value=3000 price_currency=MT payment_term=425 'price_unit;'n=USD _ERROR_=1 _N_=390
NOTE: Invalid data for valid_till_ts in line 575 94-108.
575 "1365,9.19664E+11,""Used Cooking Oil, Glycerine, Bio Diesel"",buy,EN17111365,11/2/2017 14:07,11/2/20
101 17 14:07,open,1510234235,IN,CIF,NULL,""Chennai Central, Chennai, Tamil Nadu, India"",1510236306,Drum
201 s,NULL,100,MT,250,USD,NULL,MT"; 231
trade_customer_id="1365 'Trade customer phone number'n=919664000000 name=""Used Cooking Oil position=Glyc
reference_number=Bio Diesel created_at=. updated_at=. status=11/2/2017 valid_till_ts=. origin_country=open trade_term=1510
pickup_gplace_id=IN pickup_gplace_name=CIF shipment_date_ts=NULL packaging_type=""Chennai C packaging_note=Chennai
quantity_value=Tamil N quantity_unit=Indi price_value=151023 price_currency=Drum payment_term=NULL 'price_unit;'n=100 _ERROR_=1
_N_=574
NOTE: Invalid data for valid_till_ts in line 648 78-84.
648 "615,60122515388,""Glycerin 99,5"",buy,EN1704615,4/4/2017 4:08,4/4/2017 4:08,invalid,1491868800,MY,C
101 FR,ChIJlZx5MuurzTER3b6plhNWg30,""Port Klang, Selangor Malaysia"",1491868800,Drums,NULL,20,MT,0,USD,N
201 ULL,MT"; 208
trade_customer_id="615 'Trade customer phone number'n=60122515388 name=""Glycerin 99 position=5"" reference_number=buy created_at=.
updated_at=04APR17:04:08:00 status=4/4/2017 4 valid_till_ts=. origin_country=1491 trade_term=MY pickup_gplace_id=CFR
pickup_gplace_name=ChIJlZx5MuurzTER3b6plhNWg30 shipment_date_ts=""Port Kla packaging_type=Selangor Ma packaging_note=1491868800
quantity_value=Drums quantity_unit=NULL price_value=20 price_currency=MT payment_term=0 'price_unit;'n=USD _ERROR_=1 _N_=647
NOTE: Invalid data for valid_till_ts in line 649 82-85.
649 "624,6581686320,""Glycerin 99,5"",sell,QN1704624,4/11/2017 10:34,4/11/2017 10:34,open,1492509631,MY,
101 FOB,NULL,""Malaysia, null"",1496226706,Drums,250 Kg Drums Wirhout Pallet,100,MT,100,USD,NULL,MT"; 198
trade_customer_id="624 'Trade customer phone number'n=6581686320 name=""Glycerin 99 position=5"" reference_number=sell created_at=.
updated_at=04NOV17:10:34:00 status=4/11/2017 valid_till_ts=. origin_country=1492 trade_term=MY pickup_gplace_id=FOB
pickup_gplace_name=NULL shipment_date_ts=""Malaysia packaging_type=null"" packaging_note=1496226706 quantity_value=Drums
quantity_unit=250 price_value=100 price_currency=MT payment_term=100 'price_unit;'n=USD _ERROR_=1 _N_=648
NOTE: Invalid data for valid_till_ts in line 650 82-85.
650 "1998,4.91521E+12,""Glycerin 99,5"",sell,QN18031998,3/7/2018 9:04,3/7/2018 10:28,open,1521018003,DE,
101 CIF,NULL, ,1521018207,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL"; 163
trade_customer_id="1998 'Trade customer phone number'n=4.91521E12 name=""Glycerin 99 position=5"" reference_number=sell created_at=.
updated_at=03JUL18:09:04:00 status=3/7/2018 1 valid_till_ts=. origin_country=1521 trade_term=DE pickup_gplace_id=CIF
pickup_gplace_name=NULL shipment_date_ts= packaging_type=1521018207 packaging_note=NULL quantity_value=NULL quantity_unit=NULL
price_value=NULL price_currency=NULL payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=649
NOTE: Invalid data for valid_till_ts in line 670 56-63.
670 "96,,""PKFAD, PFAD, CFAD, CCNO, Mixed Fatty Acid"",buy,EN170196,1/4/2017 3:43,1/4/2017 3:43,open,148
101 4107200,NULL,CIF,ChIJ0W7yfup5AjoRp3O7s89BUu4,""Kolkata Port Trust Quarters Kolkata, West Bengal, Ind
201 ia"",1484841600,Drums,NULL,40,MT,NULL,NULL,NULL,NULL"; 254
trade_customer_id="96 'Trade customer phone number'n=. name=""PKFAD position=PFAD reference_number=CFAD created_at=. updated_at=.
status=buy valid_till_ts=. origin_country=1/4/ trade_term=1/4/ pickup_gplace_id=open pickup_gplace_name=1484107200
4 Le Système SAS 08:34 Wednesday, June 13, 2018

shipment_date_ts=NULL packaging_type=CIF packaging_note=ChIJ0W7yfup5AjoRp3O7s89BUu4 quantity_value=""Kolka quantity_unit=West
price_value=India" price_currency=1484 payment_term=Drum 'price_unit;'n=NULL _ERROR_=1 _N_=669
NOTE: Invalid data for valid_till_ts in line 682 96-99.
REGLE : ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
682 "119,6596265190,""C8 Alcohol 99% (C6<0.3, C10<0.3)"",buy,EN1701119,1/4/2017 4:55,1/4/2017 4:55,open,
101 1484110800,NULL,CIF,ChIJYRKcR9DYszUR3RPGgIF3R3E,""Zhangjiagang Suzhou, Jiangsu, China"",1485964800,D
201 rums,New Drums Palletised,100,MT,NULL,NULL,NULL,NULL"; 254
trade_customer_id="119 'Trade customer phone number'n=6596265190 name=""C8 Alcohol 99% (C6<0.3 position=C10< reference_number=buy
created_at=. updated_at=01APR17:04:55:00 status=1/4/2017 4 valid_till_ts=. origin_country=1484 trade_term=NULL pickup_gplace_id=CIF
pickup_gplace_name=ChIJYRKcR9DYszUR3RPGgIF3R3E shipment_date_ts=""Zhangjia packaging_type=Jiangsu packaging_note=China""
quantity_value=1485964 quantity_unit=Drum price_value=New Dr price_currency=100 payment_term=MT 'price_unit;'n=NULL _ERROR_=1
_N_=681
NOTE: Invalid data for valid_till_ts in line 790 88-91.
790 "220,,""glyceryl behenate (C22, 85 plus)"",buy,EN1701220,1/5/2017 10:00,1/5/2017 10:00,open,14842152
101 00,NULL,NULL,NULL,NULL,NULL,Bags,25kg bags,5500,lb,7.5,USD,NULL,NULL"; 170
trade_customer_id="220 'Trade customer phone number'n=. name=""glyceryl behenate (C22 position=85 p reference_number=buy
created_at=. updated_at=01MAY17:10:00:00 status=1/5/2017 1 valid_till_ts=. origin_country=1484 trade_term=NULL pickup_gplace_id=NULL
pickup_gplace_name=NULL shipment_date_ts=NULL packaging_type=NULL packaging_note=Bags quantity_value=25kg ba quantity_unit=5500
price_value=lb price_currency=7.5 payment_term=USD 'price_unit;'n=NULL _ERROR_=1 _N_=789
NOTE: Invalid data for valid_till_ts in line 866 89-92.
866 "267,6.28119E+11,""RBD Coconut Oil, RBDCNO"",sell,QN1701267,1/6/2017 6:51,1/6/2017 6:51,open,1483945
101 200,ID,FOB,NULL,""Surabaya, Indonesia"",1485709200,Bags,18kg,100,MT,1905,USD,NULL,NULL"; 188
trade_customer_id="267 'Trade customer phone number'n=628119000000 name=""RBD Coconut Oil position=RBDC reference_number=sell
created_at=. updated_at=01JUN17:06:51:00 status=1/6/2017 6 valid_till_ts=. origin_country=1483 trade_term=ID pickup_gplace_id=FOB
pickup_gplace_name=NULL shipment_date_ts=""Surabaya packaging_type=Indonesia"" packaging_note=1485709200 quantity_value=Bags
quantity_unit=18kg price_value=100 price_currency=MT payment_term=1905 'price_unit;'n=USD _ERROR_=1 _N_=865
NOTE: Invalid data for valid_till_ts in line 867 92-95.
867 "1143,9.23332E+11,""RBD Coconut Oil, RBDCNO"",buy,EN17091143,9/21/2017 9:33,9/21/2017 9:33,open,1506
101 585600,VN,NULL,NULL,NULL,NULL,Flexibags,NULL,3,FCL,NULL,NULL,NULL,MT"; 170
trade_customer_id="1143 'Trade customer phone number'n=923332000000 name=""RBD Coconut Oil position=RBDC reference_number=buy
created_at=. updated_at=21SEP17:09:33:00 status=9/21/2017 valid_till_ts=. origin_country=1506 trade_term=VN pickup_gplace_id=NULL
pickup_gplace_name=NULL shipment_date_ts=NULL packaging_type=NULL packaging_note=Flexibags quantity_value=NULL quantity_unit=3
price_value=FCL price_currency=NULL payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=866
NOTE: Invalid data for valid_till_ts in line 1053 84-87.
1053 "2141,6.28231E+12,""UCO, Biodiesel"",sell,QN18042140,4/13/2018 4:46,4/13/2018 4:46,open,1524196844,I
101 D,FOB,NULL,Samarinda,1524199493,Flexibags,NULL,50,MT,500,USD,NULL,MT"; 170
trade_customer_id="2141 'Trade customer phone number'n=6.28231E12 name=""UCO position=Biod reference_number=sell created_at=.
updated_at=13APR18:04:46:00 status=4/13/2018 valid_till_ts=. origin_country=1524 trade_term=ID pickup_gplace_id=FOB
pickup_gplace_name=NULL shipment_date_ts=Samarinda packaging_type=1524199493 packaging_note=Flexibags quantity_value=NULL
quantity_unit=50 price_value=MT price_currency=500 payment_term=USD 'price_unit;'n=NULL _ERROR_=1 _N_=1052
NOTE: Invalid data for valid_till_ts in line 1067 81-84.
1067 "482,17066226770,""1,3 Propandiol"",buy,EN1701482,1/20/2017 4:04,1/25/2017 2:18,open,1485406800,NULL
101 ,CIF,ChIJj47ynNZ0YIgRULdClZJ3iSU,""Dalton GA, United States"",NULL,NULL,NULL,1,FCL,NULL,NULL,NULL,MT
201 "; 202
trade_customer_id="482 'Trade customer phone number'n=17066226770 name=""1 position=3 Pr reference_number=buy created_at=.
updated_at=20JAN17:04:04:00 status=1/25/2017 valid_till_ts=. origin_country=1485 trade_term=NULL pickup_gplace_id=CIF
pickup_gplace_name=ChIJj47ynNZ0YIgRULdClZJ3iSU shipment_date_ts=""Dalton G packaging_type=United Stat packaging_note=NULL
quantity_value=NULL quantity_unit=NULL price_value=1 price_currency=FCL payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1066
NOTE: Invalid data for valid_till_ts in line 1068 81-87.
1068 "483,9.19828E+11,""1,3 Propandiol"",buy,EN1701483,1/20/2017 4:11,1/20/2017 4:11,invalid,1485406800,I
101 N,FOB,NULL,NULL,NULL,NULL,NULL,1,FCL,NULL,NULL,NULL,MT"; 156
trade_customer_id="483 'Trade customer phone number'n=919828000000 name=""1 position=3 Pr reference_number=buy created_at=.
updated_at=20JAN17:04:11:00 status=1/20/2017 valid_till_ts=. origin_country=1485 trade_term=IN pickup_gplace_id=FOB
pickup_gplace_name=NULL shipment_date_ts=NULL packaging_type=NULL packaging_note=NULL quantity_value=NULL quantity_unit=1
price_value=FCL price_currency=NULL payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1067
NOTE: Invalid data for valid_till_ts in line 1105 98-111.
1105 "2134,8.86939E+11,""Palm Kernel Oil, Lauryl Alcohol, Laurie Acid, Stratic Acid"",sell,QN18042133,4/1
101 2/2018 3:11,4/12/2018 3:11,open,1524106810,TW,NULL,NULL,NULL,1524107431,NULL,NULL,NULL,NULL,NULL,NUL
5 Le Système SAS 08:34 Wednesday, June 13, 2018

REGLE : ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
201 L,NULL,NULL"; 213
trade_customer_id="2134 'Trade customer phone number'n=886939000000 name=""Palm Kernel Oil position=Laur reference_number=Laurie Aci
created_at=. updated_at=. status=QN18042133 valid_till_ts=. origin_country=4/12 trade_term=open pickup_gplace_id=1524106810
pickup_gplace_name=TW shipment_date_ts=NULL packaging_type=NULL packaging_note=NULL quantity_value=1524107 quantity_unit=NULL
price_value=NULL price_currency=NULL payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1104
NOTE: Invalid data for valid_till_ts in line 1136 72-75.
1136 "530,,""1,3 Propanediol"",sell,QN1701530,1/30/2017 6:40,1/30/2017 6:41,open,1488268800,IN,CIF,NULL,S
101 ingapore ,NULL,NULL,NULL,16,MT,3.2,USD,NULL,kg"; 148
trade_customer_id="530 'Trade customer phone number'n=. name=""1 position=3 Pr reference_number=sell created_at=.
updated_at=30JAN17:06:40:00 status=1/30/2017 valid_till_ts=. origin_country=1488 trade_term=IN pickup_gplace_id=CIF
pickup_gplace_name=NULL shipment_date_ts=Singapore packaging_type=NULL packaging_note=NULL quantity_value=NULL quantity_unit=16
price_value=MT price_currency=3.2 payment_term=USD 'price_unit;'n=NULL _ERROR_=1 _N_=1135
NOTE: Invalid data for valid_till_ts in line 1137 83-86.
1137 "564,60123267689,""1,3 Propanediol"",sell,QN1702564,2/10/2017 7:08,2/10/2017 7:08,open,1486789200,NU
101 LL,FOB,NULL,Shanghai,NULL,Drums,200kg,16,MT,3.24,USD,NULL,kg"; 162
trade_customer_id="564 'Trade customer phone number'n=60123267689 name=""1 position=3 Pr reference_number=sell created_at=.
updated_at=02OCT17:07:08:00 status=2/10/2017 valid_till_ts=. origin_country=1486 trade_term=NULL pickup_gplace_id=FOB
pickup_gplace_name=NULL shipment_date_ts=Shanghai packaging_type=NULL packaging_note=Drums quantity_value=200kg quantity_unit=16
price_value=MT price_currency=3.24 payment_term=USD 'price_unit;'n=NULL _ERROR_=1 _N_=1136
NOTE: Invalid data for valid_till_ts in line 1138 82-85.
1138 "793,17066226770,""1,3 Propanediol"",buy,EN1707793,7/2/2017 15:31,7/2/2017 15:31,open,1499572800,NUL
101 L,CIF,ChIJj47ynNZ0YIgRULdClZJ3iSU,""Dalton GA, United States"",NULL,NULL,NULL,NULL,MT,NULL,NULL,NULL
201 ,MT"; 205
trade_customer_id="793 'Trade customer phone number'n=17066226770 name=""1 position=3 Pr reference_number=buy created_at=.
updated_at=07FEB17:15:31:00 status=7/2/2017 1 valid_till_ts=. origin_country=1499 trade_term=NULL pickup_gplace_id=CIF
pickup_gplace_name=ChIJj47ynNZ0YIgRULdClZJ3iSU shipment_date_ts=""Dalton G packaging_type=United Stat packaging_note=NULL
quantity_value=NULL quantity_unit=NULL price_value=NULL price_currency=MT payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1137
NOTE: Invalid data for valid_till_ts in line 1529 81-94.
1529 "787,8.61392E+12,""Stearic Acid 1840, 1865, 1892"",buy,EN1706787,6/23/2017 3:41,6/23/2017 3:41,open,
101 1498795200,ID,CIF,ChIJa_D4gtUPljUR8_JMYfqCTWE,Qingdao,NULL,Bags,NULL,1000,MT,NULL,NULL,NULL,MT"; 196
trade_customer_id="787 'Trade customer phone number'n=8.61392E12 name=""Stearic Acid 1840 position=1865 reference_number=1892""
created_at=. updated_at=. status=6/23/2017 valid_till_ts=. origin_country=open trade_term=1498 pickup_gplace_id=ID
pickup_gplace_name=CIF shipment_date_ts=ChIJa_D4gt packaging_type=Qingdao packaging_note=NULL quantity_value=Bags quantity_unit=NULL
price_value=1000 price_currency=MT payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1528
NOTE: Invalid data for valid_till_ts in line 1530 72-85.
1530 "1230,,""Stearic Acid 1840, 1865, 1892"",buy,EN17101230,10/9/2017 9:34,10/9/2017 9:34,open,150814800
101 0,NULL,FOB,NULL,NULL,NULL,NULL,NULL,NULL,MT,NULL,NULL,NULL,MT"; 163
trade_customer_id="1230 'Trade customer phone number'n=. name=""Stearic Acid 1840 position=1865 reference_number=1892"" created_at=.
updated_at=. status=10/9/2017 valid_till_ts=. origin_country=open trade_term=1508 pickup_gplace_id=NULL pickup_gplace_name=FOB
shipment_date_ts=NULL packaging_type=NULL packaging_note=NULL quantity_value=NULL quantity_unit=NULL price_value=NULL
price_currency=MT payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1529
NOTE: Invalid data for valid_till_ts in line 1687 59-72.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
1687 "1026,,""LABSA, SLES, SLS"",buy,EN17071026,7/13/2017 9:49,7/13/2017 9:56,open,1500465600,NULL,NULL,N
101 ULL,NULL,NULL,NULL,NULL,NULL,MT,NULL,NULL,NULL,MT"; 151
trade_customer_id="1026 'Trade customer phone number'n=. name=""LABSA position=SLES reference_number=SLS"" created_at=. updated_at=.
status=7/13/2017 valid_till_ts=. origin_country=open trade_term=1500 pickup_gplace_id=NULL pickup_gplace_name=NULL
shipment_date_ts=NULL packaging_type=NULL packaging_note=NULL quantity_value=NULL quantity_unit=NULL price_value=NULL
price_currency=MT payment_term=NULL 'price_unit;'n=NULL _ERROR_=1 _N_=1686
NOTE: 2369 records were read from the infile 'C:\Users\Asus\Desktop\Submitted Trade.csv'.
The minimum record length was 126.
The maximum record length was 297.
NOTE: The data set WORK.TABLE has 2369 observations and 22 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

6 Le Système SAS 08:34 Wednesday, June 13, 2018


Erreurs détectées dans l'étape DATA soumise. Examinez le Journal.
2369 lignes créées dans WORK.TABLE à partir de C:\Users\Asus\Desktop\Submitted Trade.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 1.08 seconds
cpu time 0.89 seconds

120
121 GOPTIONS NOACCESSIBLE;
122 %LET _CLIENTTASKLABEL=;
123 %LET _CLIENTPROCESSFLOWNAME=;
124 %LET _CLIENTPROJECTPATH=;
125 %LET _CLIENTPROJECTPATHHOST=;
126 %LET _CLIENTPROJECTNAME=;
127 %LET _SASPROGRAMFILE=;
128 %LET _SASPROGRAMFILEHOST=;
129
130 ;*';*";*/;quit;run;
131 ODS _ALL_ CLOSE;
132
133
134 QUIT; RUN;
135


 

PROC Star
Posts: 266

Re: Import CSV error

When looking at the ruler for the first record in non-proportional font, I see this:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
"1282,6.28125E+12,""PAO, PFAD"",sell,QN17101282,10/24/2017 6:55,10/24/2017 6:55,open,1509429650,ID,F

Looks very strange, as the initial double-quote is apparently not read as a quote (is something wrong with the log display?), and trade_customer_id resolves to "1282 while the phone number correctly resolves (incorrectly, but that's because there is an error in the input file) to 6.28125E+12. Next, I think the name should have been "PAO, PFAD" and the position "sell", but because the quotes are not interpreted as such (or because they are doubled?), the name becomes ""PAO and the position becomes PFAD. And then all the following variables are also read "too early" and the program tries to read "open" for the number valid_till_ts, which should have been 1509429650.

 

There is something wrong with that .csv -file. Maybe that the whole line has been quoted when the file was written. 

 

 

Trusted Advisor
Posts: 2,125

Re: Import CSV error

status is formatted as $10, so it picks up the first 10 characters of the date time field.  The rest is ignored up to the comma.

valid_till_ts is numeric, but the field after the end of the time-stamp is character ("open" in the first error).

Ask a Question
Discussion stats
  • 3 replies
  • 167 views
  • 0 likes
  • 4 in conversation