error with proc import

[robertw]

Hi, I am trying to import a csv data set into sas and I keep geting this error.

Problems were detected with provided names. See LOG.
19950 proc import
19950! datafile='C:\Users\mp675\Contacts\Desktop\Robert\alimliquida\AlimLiqdataset.csv' out=one
19950! replace;
19951 /**********************************************************************
19952 * PRODUCT: SAS
19953 * VERSION: 9.4
19954 * CREATOR: External File Interface
19955 * DATE: 13MAR20
19956 * DESC: Generated SAS Datastep Code
19957 * TEMPLATE SOURCE: (None Specified.)
19958 ***********************************************************************/
19959 data WORK.ONE ;
19960 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
19961 infile 'C:\Users\mp675\Contacts\Desktop\Robert\alimliquida\AlimLiqdataset.csv'
19961! delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
19962 informat farm_date_id_id3_id2_nefa_bhba_b $82. ;
19963 format farm_date_id_id3_id2_nefa_bhba_b $82. ;
19964 input
19965 farm_date_id_id3_id2_nefa_bhba_b $
19966 ;
19967 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
19968 run;

[andreas_lds]

Are you sure that the file is a delimitted by comma? The line

19965 farm_date_id_id3_id2_nefa_bhba_b $

seems to show that field are separated by underscore.

[robertw]

Should be, I created the excel data set and then saved as csv.

[Kurt_Bremser]

Please copy/paste a few lines from the start of the file into a window opened with the {i} button. If sensitive information is included, replace it with X's or 0's, but keep all lengths as is.

[robertw]

farm	date	id	id3	id2	nefa	bhba	bcs	dim	lact	fdat	punteo	prep	treatment	cal
931	26/06/2019	39	93139	500049	0.07	0.164	3.5	7	6	19/06/2019	0	0	CONTROL	1.26
5587	29/07/2019	98	558798	500160	0.1	0.273	3.25	-20	7	18/08/2019	1	1	POWER.UP	2.46
5587	05/08/2019	98	558798	500161	0.11	0.18	.	-13	7	18/08/2019	1	1	POWER.UP	1.58
5587	12/08/2019	98	558798	500153	0.19	0.738	3.25	-6	7	18/08/2019	1	1	POWER.UP	2.13
5587	19/08/2019	98	558798	500171	0.75	0.61	2.75	1	7	18/08/2019	1	1	POWER.UP	1.56
5587	26/08/2019	98	558798	500174	0.2	0.879	2.75	8	7	18/08/2019	1	1	POWER.UP	2.12
5587	03/09/2019	98	558798	500176	0.3	0.22	2.25	16	7	18/08/2019	1	1	POWER.UP	2.01
5587	13/09/2019	98	558798	.	.	.	2.75	.	7	18/08/2019	1	1	POWER.UP	.

[Kurt_Bremser]

That is not a csv (no commas), this is tab-separated.

See this:

data want;
infile datalines dlm='09'x dsd truncover firstobs=2;
input
  farm :$4.
  date :ddmmyy10.
  id :$2.
  id3 :$6.
  id2 :$6.
  nefa
  bhba
  bcs
  dim
  lact
  fdat :ddmmyy10.
  punteo
  prep
  treatment :$10.
  cal
;
format date fdat ddmmyy10.;
datalines;
farm	date	id	id3	id2	nefa	bhba	bcs	dim	lact	fdat	punteo	prep	treatment	cal
931	26/06/2019	39	93139	500049	0.07	0.164	3.5	7	6	19/06/2019	0	0	CONTROL	1.26
5587	29/07/2019	98	558798	500160	0.1	0.273	3.25	-20	7	18/08/2019	1	1	POWER.UP	2.46
5587	05/08/2019	98	558798	500161	0.11	0.18	.	-13	7	18/08/2019	1	1	POWER.UP	1.58
5587	12/08/2019	98	558798	500153	0.19	0.738	3.25	-6	7	18/08/2019	1	1	POWER.UP	2.13
5587	19/08/2019	98	558798	500171	0.75	0.61	2.75	1	7	18/08/2019	1	1	POWER.UP	1.56
5587	26/08/2019	98	558798	500174	0.2	0.879	2.75	8	7	18/08/2019	1	1	POWER.UP	2.12
5587	03/09/2019	98	558798	500176	0.3	0.22	2.25	16	7	18/08/2019	1	1	POWER.UP	2.01
5587	13/09/2019	98	558798	.	.	.	2.75	.	7	18/08/2019	1	1	POWER.UP	.
;

Replace the datalines option in the infile statement with the path to your file, and replace the whole datalines block with a run; statement.

[robertw]

file is saved as CSV, and in file properties is described as comma separated values. can the problem be realed to my office being in spanish?

[Kurt_Bremser]

---

@robertw wrote:

file is saved as CSV, and in file properties is described as comma separated values. can the problem be realed to my office being in spanish?

---

I don't know, but the example you posted is clearly tab-separated.

Did you open the "csv" with a simple text editor (e.g. notepad++) and copy/paste from there?

[robertw]

Previously I copy pasted from excel, now I did opened it in netepad and for some reason it is usin " ; " instead of " , ". I will have to take a look at my office seting. Thanks

[Kurt_Bremser]

NEVER inspect a csv file with Excel, as Excel changes everything on opening. ALWAYS use a text editing tool, preferably one with a hex display mode. That'sw hy I suggested Notepad++.

So we just have to change the delimiter:

data want;
infile datalines dlm=';' dsd truncover firstobs=2;
input
  farm :$4.
  date :ddmmyy10.
  id :$2.
  id3 :$6.
  id2 :$6.
  nefa
  bhba
  bcs
  dim
  lact
  fdat :ddmmyy10.
  punteo
  prep
  treatment :$10.
  cal
;
format date fdat ddmmyy10.;
datalines4;
farm;date;id;id3;id2;nefa;bhba;bcs;dim;lact;fdat;punteo;prep;treatment;cal
931;26/06/2019;39;93139;500049;0.07;0.164;3.5;7;6;19/06/2019;0;0;CONTROL;1.26
5587;29/07/2019;98;558798;500160;0.1;0.273;3.25;-20;7;18/08/2019;1;1;POWER.UP;2.46
5587;05/08/2019;98;558798;500161;0.11;0.18;.;-13;7;18/08/2019;1;1;POWER.UP;1.58
5587;12/08/2019;98;558798;500153;0.19;0.738;3.25;-6;7;18/08/2019;1;1;POWER.UP;2.13
5587;19/08/2019;98;558798;500171;0.75;0.61;2.75;1;7;18/08/2019;1;1;POWER.UP;1.56
5587;26/08/2019;98;558798;500174;0.2;0.879;2.75;8;7;18/08/2019;1;1;POWER.UP;2.12
5587;03/09/2019;98;558798;500176;0.3;0.22;2.25;16;7;18/08/2019;1;1;POWER.UP;2.01
5587;13/09/2019;98;558798;.;.;.;2.75;.;7;18/08/2019;1;1;POWER.UP;.
;;;;

Note that I needed to use datalines4 instead of datalines, to accommodate the semicolons in the datalines block.