topic Issue with Data step import in SAS Programming

Issue with Data step import

Babloo — Fri, 19 Jan 2018 07:29:59 GMT

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

Re: Issue with Data step import

srinath3111 — Fri, 19 Jan 2018 07:19:37 GMT

Hi,

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

out=libname.filename

dbms=csv

replace;

run;

Thanks

Re: Issue with Data step import

srinath3111 — Fri, 19 Jan 2018 07:24:23 GMT

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

Re: Issue with Data step import

Babloo — Fri, 19 Jan 2018 07:27:32 GMT

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

Re: Issue with Data step import

Babloo — Fri, 19 Jan 2018 07:50:35 GMT

I need to write only the data step.

Re: Issue with Data step import

Kurt_Bremser — Fri, 19 Jan 2018 12:47:37 GMT

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

Re: Issue with Data step import

Babloo — Fri, 19 Jan 2018 12:56:01 GMT

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?

Re: Issue with Data step import

Kurt_Bremser — Fri, 19 Jan 2018 13:05:59 GMT

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?

Re: Issue with Data step import

Babloo — Fri, 19 Jan 2018 13:07:50 GMT

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

Re: Issue with Data step import

Kurt_Bremser — Fri, 19 Jan 2018 13:14:59 GMT

@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

Re: Issue with Data step import

Babloo — Fri, 19 Jan 2018 13:28:34 GMT

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.

Re: Issue with Data step import

Kurt_Bremser — Fri, 19 Jan 2018 13:33:12 GMT

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.

Re: Issue with Data step import

Babloo — Fri, 19 Jan 2018 13:36:49 GMT

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

Re: Issue with Data step import

Kurt_Bremser — Fri, 19 Jan 2018 13:47:20 GMT

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.

Re: Issue with Data step import

ballardw — Fri, 19 Jan 2018 15:30:02 GMT

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