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.
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
Please post the SAS log, this will help in diagnosing the problem.
How did you convert the Excel file to the text file?
Bruno
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
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
Hi Bruno,
I increased the LRECL to 100000 and got the result I was expecting. All the fields were read by SAS. Thank you!
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.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.