Issue with Data step import

[Babloo]

I'm trying to import the attached .csv file with the following code. It has 1 record but I end up with 39 records. Appreciate if someone guide me resolve this issue. Enclosed the code and the log for the quick reference.

DATA example2;
	LENGTH
		externalIdentifier $ 10
		dateRenewal      $ 10
		Veh_Driver_hashcode $ 23
		Version_subversion_hashcode $ 4
		Clause_Base      $ 4
		Clause_Major_change $ 4
		Clause_Minor_change $ 4
		Cover            $ 20
		DPF_base         $ 6
		Expl_base        $ 20
		DPF_major_change $ 6
		Expl_major_change $ 17
		DPF_minor_change $ 6
		Expl_minor_change $ 25;
	FORMAT
		externalIdentifier $CHAR10.
		dateRenewal      $CHAR10.
		Veh_Driver_hashcode $CHAR23.
		Version_subversion_hashcode $CHAR4.
		Clause_Base      $CHAR4.
		Clause_Major_change $CHAR4.
		Clause_Minor_change $CHAR4.
		Cover            $CHAR20.
		DPF_base         $CHAR6.
		Expl_base        $CHAR20.
		DPF_major_change $CHAR6.
		Expl_major_change $CHAR17.
		DPF_minor_change $CHAR6.
		Expl_minor_change $CHAR25.;
	INFORMAT
		externalIdentifier $CHAR10.
		dateRenewal      $CHAR10.
		Veh_Driver_hashcode $CHAR23.
		Version_subversion_hashcode $CHAR4.
		Clause_Base      $CHAR4.
		Clause_Major_change $CHAR4.
		Clause_Minor_change $CHAR4.
		Cover            $CHAR20.
		DPF_base         $CHAR6.
		Expl_base        $CHAR20.
		DPF_major_change $CHAR6.
		Expl_major_change $CHAR17.
		DPF_minor_change $CHAR6.
		Expl_minor_change $CHAR25.;
	INFILE '/wrk1/example2.csv'
		/*        LRECL=162*/

	/*        ENCODING="LATIN1"*/
	/*        TERMSTR=CRLF*/
	DLM=','
	/*        MISSOVER*/
	truncover
	DSD;
	INPUT
		externalIdentifier : $CHAR10.
		dateRenewal      : $CHAR10.
		Veh_Driver_hashcode : $CHAR23.
		Version_subversion_hashcode : $CHAR4.
		Clause_Base      : $CHAR4.
		Clause_Major_change : $CHAR4.
		Clause_Minor_change : $CHAR4.
		Cover            : $CHAR20.
		DPF_base         : $CHAR6.
		Expl_base        : $CHAR20.
		DPF_major_change : $CHAR6.
		Expl_major_change : $CHAR17.
		DPF_minor_change : $CHAR6.
		Expl_minor_change : $CHAR25.;
RUN;

Log:

24         DATA example2;
25         	LENGTH
26         		externalIdentifier $ 10
27         		dateRenewal      $ 10
28         		Veh_Driver_hashcode $ 23
29         		Version_subversion_hashcode $ 4
30         		Clause_Base      $ 4
31         		Clause_Major_change $ 4
32         		Clause_Minor_change $ 4
33         		Cover            $ 20
34         		DPF_base         $ 6
35         		Expl_base        $ 20
36         		DPF_major_change $ 6
37         		Expl_major_change $ 17
38         		DPF_minor_change $ 6
39         		Expl_minor_change $ 25;
40         	FORMAT
41         		externalIdentifier $CHAR10.
42         		dateRenewal      $CHAR10.
43         		Veh_Driver_hashcode $CHAR23.
44         		Version_subversion_hashcode $CHAR4.
45         		Clause_Base      $CHAR4.
46         		Clause_Major_change $CHAR4.
47         		Clause_Minor_change $CHAR4.
48         		Cover            $CHAR20.
49         		DPF_base         $CHAR6.
50         		Expl_base        $CHAR20.
51         		DPF_major_change $CHAR6.
52         		Expl_major_change $CHAR17.
53         		DPF_minor_change $CHAR6.
54         		Expl_minor_change $CHAR25.;
55         	INFORMAT
56         		externalIdentifier $CHAR10.
57         		dateRenewal      $CHAR10.
58         		Veh_Driver_hashcode $CHAR23.
59         		Version_subversion_hashcode $CHAR4.
60         		Clause_Base      $CHAR4.
61         		Clause_Major_change $CHAR4.
62         		Clause_Minor_change $CHAR4.
63         		Cover            $CHAR20.
64         		DPF_base         $CHAR6.
65         		Expl_base        $CHAR20.
66         		DPF_major_change $CHAR6.
67         		Expl_major_change $CHAR17.
68         		DPF_minor_change $CHAR6.
69         		Expl_minor_change $CHAR25.;
70         	INFILE '/wrk1/example2.csv'
71         		/*        LRECL=162*/
72         
73         	/*        ENCODING="LATIN1"*/
74         	/*        TERMSTR=CRLF*/
75         	DLM=','
76         	/*        MISSOVER*/
77         	truncover
78         	DSD;
79         	INPUT
80         		externalIdentifier : $CHAR10.
81         		dateRenewal      : $CHAR10.
82         		Veh_Driver_hashcode : $CHAR23.
83         		Version_subversion_hashcode : $CHAR4.
84         		Clause_Base      : $CHAR4.
85         		Clause_Major_change : $CHAR4.
86         		Clause_Minor_change : $CHAR4.
87         		Cover            : $CHAR20.
88         		DPF_base         : $CHAR6.
89         		Expl_base        : $CHAR20.
90         		DPF_major_change : $CHAR6.
91         		Expl_major_change : $CHAR17.
92         		DPF_minor_change : $CHAR6.
93         		Expl_minor_change : $CHAR25.;
94         RUN;

NOTE: The infile '/wrk1/example2.csv' is:
      Filename=/wrk1/example2.csv,
NOTE: 39 records were read from the infile '/wrk1/example2.csv'.
      The minimum record length was 9.
      The maximum record length was 256.
      One or more lines were truncated.
NOTE: The data set WORK.example2 has 39 observations and 14 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

95         

[srinath3111]

Hi,

You better write proc import data file="path/filename.csv"

out=libname.filename

dbms=csv

replace;

run;

Thanks

[srinath3111]

and one more i wanted to notice you that sas statements read line by line. if the data you are reading is not in a proper format, you have to first write informat statement then write format statement

[Babloo]

I need to write the code in data step. Also swapping the informat and format statements has not resolved my issue either.

[Babloo]

I need to write only the data step.

[Kurt_Bremser]

That is not a csv file at all, see this hex dump:

[Babloo]

That is really an .xls file which had some formats.I manually converted the .xls to .csv. Is there any way to get rid of that issue?

[Kurt_Bremser]

How did you "convert" it?

---

@Babloo wrote:

That is really an .xls file which had some formats.I manually converted the .xls to .csv. Is there any way to get rid of that issue?

---

[Babloo]

I just changed the extension of the file from .xlsx (it's not .xls) to csv.

[Kurt_Bremser]

---

@Babloo wrote:

I just changed the extension of the file from .xlsx (it's not .xls) to csv.

---

I can't believe I just read this.

THAT DOES NOT CHANGE THE CONTENTS OF THE FILE ONE LITTLE BIT!

Read this closely:

- open the file in Excel

- select File - Export

- select Change Type, and select CSV

- select Save as

- select location and filename, and hit Save

- confirm saving as non-Excel filetype

[Babloo]

How can I do this 'confirm saving as non-Excel filetype'? Based on your comments (except the last one) I've took an action to convert the .xlsx to .csv but still I'm receiving the same error.

[Kurt_Bremser]

The final confirmation prompt will be issued by Excel anytime you save to a different format; it tells you that formatting etc. will be lost if you continue.

A csv file is always a text file that can easily be viewed with any text editor, so check it before trying to import.

[Babloo]

I did that already, but I'm not certain why I'm receiving the same error again.

[Kurt_Bremser]

Post the new csv file (I also suggest you use a different name when saving, to be sure) here, once you have confirmed it is a text file.

[ballardw]

---

@Babloo wrote:

I did that already, but I'm not certain why I'm receiving the same error again.

---

Did you save the "new" csv to the same name or a new name? If a new name did you change the infile statement?

If the same name did you save it to the same location as the previous not-actually-a-csv was kept?

In either of those cases the infile statement may still be pointing to the old file and not the recently created CSV.

Or perhaps your infile statement has the new name misspelled from what it was actually saved as: Exmaple2 instead of Example2 for instance.