BookmarkSubscribeRSS Feed
robertw
Calcite | Level 5

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;

9 REPLIES 9
andreas_lds
Jade | Level 19

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
Calcite | Level 5

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

Kurt_Bremser
Super User

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
Calcite | Level 5
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
Super User

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
Calcite | Level 5

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
Super User

@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
Calcite | Level 5

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
Super User

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.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 9 replies
  • 984 views
  • 0 likes
  • 3 in conversation