BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

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         

 

 

14 REPLIES 14
srinath3111
Quartz | Level 8

Hi,

 

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

out=libname.filename

dbms=csv

replace;

run;

 

Thanks

srinath3111
Quartz | Level 8
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
Rhodochrosite | Level 12

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

Babloo
Rhodochrosite | Level 12
I need to write only the data step.
Babloo
Rhodochrosite | Level 12

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

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
Rhodochrosite | Level 12

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

Kurt_Bremser
Super User

@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
Rhodochrosite | Level 12

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

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
Rhodochrosite | Level 12

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

ballardw
Super User

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 14 replies
  • 1556 views
  • 2 likes
  • 4 in conversation