SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Why does SAS stops reading an external file after a certain field?

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Why does SAS stops reading an external file after a certain field?

Hi all,

 

I have a source Excel file from our Data Management Team. I converted this file to pipe-delimited text file for import purposes. 

I used the data step, infile with with the IGNOREDOSEOF statement to convert this text file to SAS. After import, I found that there was a row whose fields SAS left blank after this certain long field (appearing to be more than 32767 characters). Rows after that problematic row appear to be intact.

 

Perhaps a special character or a hidden character causes this occurence? I would like to know how can SAS ignore that character, and continue reading the rest of the row's data.


Accepted Solutions
Solution
‎10-08-2015 01:15 AM
SAS Super FREQ
Posts: 683

Re: Why does SAS stops reading an external file after a certain field?

The SAS Log says:

NOTE: 69312 records were read from the infile "E:\SAS_AC\98 SAS References\Import Pipe DLM to SAS\SOL_PIPE.txt".
3 The SAS System 16:02 Wednesday, October 7, 2015

The minimum record length was 606.
The maximum record length was 32767.
One or more lines were truncated.

 

I suggest you increase the value for the LRECL= option.

 

Bruno

 

View solution in original post


All Replies
SAS Super FREQ
Posts: 683

Re: Why does SAS stops reading an external file after a certain field?

Please post the SAS log, this will help in diagnosing the problem.

 

How did you convert the Excel file to the text file?

 

Bruno

Contributor
Posts: 53

Re: Why does SAS stops reading an external file after a certain field?

Hi Bruno,

 

I just changed the delimiter settings to pipe "|" via Control Panel, opened the Excel file, and saved it as a CSV file with the extension .txt.

 

Here is the SAS log. I'm converting the txt file using SAS EG.

 

57 %macro import_data;
58 data work.pipe212;
59 infile "&input.\&source..txt"
60 lrecl=32767
61 firstobs=2
62 encoding="WLATIN1"
63 termstr=CRLF
64 dlm='|'
65 missover
66 dsd
67 ignoredoseof;
68 input
69 %do i=1 %to &total;
70 &&field&i
71 %end;
72 ;
73 run;
74 %mend;
75 %import_data;

 

NOTE: The infile "E:\SAS_AC\98 SAS References\Import Pipe DLM to SAS\SOL_PIPE.txt" is:
Filename=E:\SAS_AC\98 SAS References\Import Pipe DLM to SAS\SOL_PIPE.txt,
RECFM=V,LRECL=32767,
File Size (bytes)=196517682,
Last Modified=29Sep2015:16:50:58,
Create Time=29Sep2015:16:50:40

 

NOTE: 69312 records were read from the infile "E:\SAS_AC\98 SAS References\Import Pipe DLM to SAS\SOL_PIPE.txt".
3 The SAS System 16:02 Wednesday, October 7, 2015

The minimum record length was 606.
The maximum record length was 32767.
One or more lines were truncated.
NOTE: The data set WORK.PIPE212 has 69312 observations and 66 variables.
NOTE: Compressing data set WORK.PIPE212 decreased size by 96.57 percent.
Compressed is 792 pages; un-compressed would require 23104 pages.
NOTE: DATA statement used (Total process time):
real time 6.11 seconds
cpu time 4.52 seconds

 

Thank you,

Angelique

Solution
‎10-08-2015 01:15 AM
SAS Super FREQ
Posts: 683

Re: Why does SAS stops reading an external file after a certain field?

The SAS Log says:

NOTE: 69312 records were read from the infile "E:\SAS_AC\98 SAS References\Import Pipe DLM to SAS\SOL_PIPE.txt".
3 The SAS System 16:02 Wednesday, October 7, 2015

The minimum record length was 606.
The maximum record length was 32767.
One or more lines were truncated.

 

I suggest you increase the value for the LRECL= option.

 

Bruno

 

Contributor
Posts: 53

Re: Why does SAS stops reading an external file after a certain field?

[ Edited ]

Hi Bruno,

 

I increased the LRECL to 100000 and got the result I was expecting. All the fields were read by SAS. Thank you!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 609 views
  • 0 likes
  • 2 in conversation