DATA Step, Macro, Functions and more

Issue with Data step import

Reply
PROC Star
Posts: 634

Issue with Data step import

[ Edited ]

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         

 

 

Contributor
Posts: 40

Re: Issue with Data step import

Hi,

 

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

out=libname.filename

dbms=csv

replace;

run;

 

Thanks

Contributor
Posts: 40

Re: Issue with Data step import

Posted in reply to 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
PROC Star
Posts: 634

Re: Issue with Data step import

Posted in reply to srinath3111

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

PROC Star
Posts: 634

Re: Issue with Data step import

Posted in reply to srinath3111
I need to write only the data step.
Super User
Posts: 10,623

Re: Issue with Data step import

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

example2.jpg

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 634

Re: Issue with Data step import

Posted in reply to KurtBremser

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?

Super User
Posts: 10,623

Re: Issue with Data step import

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?


 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 634

Re: Issue with Data step import

Posted in reply to KurtBremser

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

Super User
Posts: 10,623

Re: Issue with Data step import


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 634

Re: Issue with Data step import

Posted in reply to KurtBremser

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.

Super User
Posts: 10,623

Re: Issue with Data step import

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 634

Re: Issue with Data step import

Posted in reply to KurtBremser

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

Super User
Posts: 10,623

Re: Issue with Data step import

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,950

Re: Issue with Data step import


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.

Ask a Question
Discussion stats
  • 14 replies
  • 208 views
  • 2 likes
  • 4 in conversation