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: 55
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: 708

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

Posted in reply to angeliquec

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: 708

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

Posted in reply to angeliquec

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: 55

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

Posted in reply to Bruno_SAS

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: 708

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

Posted in reply to angeliquec

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: 55

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

[ Edited ]
Posted in reply to Bruno_SAS

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 and locked.

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

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