Import raw file pipe enclosed separated by comma exceeding 257 length

[angel17th]

Hi,

I would like to ask for your help regarding the issue on subject. I already tried the solution provided in the https://communities.sas.com/t5/SAS-Programming/Read-in-raw-pipe-enclosed-data-separated-by-commas-SA... solved by  @Tom but the records exceeding the 257 length are truncated causing for misaligned imported records. I've tried changing the lrecl value from 1 to 32767 and the char1 to char32767 but it still doesn't solved the issue with truncation.

Another issue with the raw data is that the empty records are not pipe enclosed. Hope that someone can help me with this.

Thanks and advance!

[Tom]

Why would records longer than 257 bytes be truncated?

If you cannot supply some example lines from the file then perform your own analysis of the file to see if it is well formed.

For example if you just read in the file without trying to save it you should see the record count and the min and max record length.

data _null_;
  infile 'myfile.csv' lrecl=1000000 ;
  input;
run;

Example:

50   data _null_;
51     infile csv lrecl=1000000;
52     input;
53   run;

NOTE: The infile CSV is:
      (system-specific pathname),
      (system-specific file attributes)

NOTE: 20 records were read from the infile (system-specific pathname).
      The minimum record length was 17.
      The maximum record length was 26.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

[angel17th]

Hi @Tom ,

Thank you for your response, and apologies for the lack of details. Here is the sample data I'm reading:

|11081990|,|41|,|X04|,|N-XYXYXYXY/SAMPLE DUMMY DATA TO T|,|05311999|,|1|,|08-JAN-2001 04.28.19.546000 AM|,|1|,|29-APR-2001 04.55.35.938000 PM|,|08182001|,,,,
|09201978|,|42|,|Y05|,|N-XYXYXY/SAMPLE DUMMY DATA TO TEST THE TRUNCATION OF IMPORTED RECORDS DUE TO THE LONG DATA SAMPLE DUMMY DATA TO TEST THE TRUNCATION OF IMPORTED RECORDS DUE TO THE LONG DATA SAMPLE DUMMY DATA TO TEST THE TRUNCATION OF |,|04211988|,|1|,|10-MAY-1989 05.28.30.550000 AM|,|1|,|20-JUN-1989 03.52.35.942000 PM|,|03031989|,,,,
|12031985|,|43|,|Z06|,|K-XYXYXYXYXYXYXYXY/ SAMPLE DUMMY DATA TO TEST THE TRUNCATION OF IMPORTED RECORDS DUE TO THE L|,|09131988|,|1|,|19-SEP-1988 15.28.19.552000 AM|,|1|,|02-OCT-1988 13.44.15.944000 PM|,|03191988|,,,,

If I use the your modified code below:

filename original "IMPORT_DATA.csv";
filename fixed "FIXED_IMPORT_DATA.csv";
data _null_;
  infile original lrecl=1 recfm=f ;
  file fixed termstr=crlf;
  input char $char1.;
  numpipe + (char='|');
  if char='0D'x then return;
  else if char='0A'x then do;
    if mod(numpipe,2)=1 then put '|' @;
    else do;
       numpipe=0;
       put;
    end;
  end;
  else if char='|' then put '"' @;
  else if char='"' then put '""' @;
  else put char $char1. @;
run;

The FIXED_IMPORT_DATA.CSV will be created with the following records:

"11081990","41","X04","N-XYXYXYXY/SAMPLE DUMMY DATA TO T","05311999","1","08-JAN-2001 04.28.19.546000 AM","1","29-APR-2001 04.55.35.938000 PM","08182001",,,,
"09201978","42","Y05","N-XYXYXY/SAMPLE DUMMY DATA TO TEST THE TRUNCATION OF IMPORTED RECORDS DUE TO THE LONG DATA SAMPLE DUMMY DATA TO TEST THE TRUNCATION OF IMPORTED RECORDS DUE TO THE LONG DATA SAMPLE DUMMY DATA TO TEST THE TRUNCATION OF ","04211988","1","10-MAY-1989 05.28.30.550000 AM"
"1","02-OCT-1988 13.44.15.944000 PM","03191988",,,,

You may notice that the second record was truncated and the third record was changed.

[Patrick]

@angel17th Your code works as it should in my Windows environment.

Are you sure this is not just a display issue? When I opened the generated file with Notepad++ and Word Wrapping selected I've seen exactly the same as you shared. But even though the 2nd record gets displayed on two lines it's still a single record (one CRLF). 

And here some code to compare source with target that also confirms that things appear to work as desired.

%let source=c:\temp\IMPORT_DATA.csv;
%let target=c:\temp\FIXED_IMPORT_DATA.csv;

filename original "&source";
filename fixed "&target";
data _null_;
  infile original lrecl=1 recfm=f ;
  file fixed termstr=crlf;
  input char $char1.;
  numpipe + (char='|');
  if char='0D'x then return;
  else if char='0A'x then do;
    if mod(numpipe,2)=1 then put '|' @;
    else do;
       numpipe=0;
       put;
    end;
  end;
  else if char='|' then put '"' @;
  else if char='"' then put '""' @;
  else put char $char1. @;
run;

data source;
  infile "&source" truncover;
  input line:$1000.;
run;
data target;
  infile "&target" truncover;
  input  line:$1000.;
  line=translate(line,'|','"');
run;

proc compare data=source comp=target;
run;

[angel17th]

Hi @Patrick,

I've tried to import the three dummy records to SAS but it outputted four rows, and it seems that it cuts the 2nd record and the remaining parts of that record were inserted to the 3rd row. 

Please note that I'm using the SAS9 9.3 version, maybe there are some codes that are not compatible?

[Patrick · Accepted solution]

SAS is highly backward compatible and if your code works in 9.3 then later SAS versions should return the same result. Normally such differences are caused by environmental differences like different options settings and the like. 

In my environment option lrecl is set to 32767. If in your env the value is lower than the length of the string you want to write then the default (flowover) will write the not fitting bit to the next line. 

To test the theory you could define lrecl for your output file (value at least as long as the max string length you want to write onto a single line).

filename fixed "&target" lrecl=32767;

[angel17th]

Hi Patrick,

Your suggestion worked! Thank you so much! I changed the lrecl for the output file and also added the lrecl=32767 option when importing it to the SAS dataset. 

[Patrick]

@angel17th 

I believe you could simplify your current code using syntax like below.

data _null_;
  infile original;
  file fixed termstr=crlf;
  input;
  _infile_=tranwrd(_infile_,'"','""');
  _infile_=tranwrd(_infile_,'|','"');
  put _infile_;
run;

[angel17th]

Hi @Patrick,

When I tested with your simplified code, I got some warnings about invalid data for the Date format columns. I read the columns first as DATETIME then format them as DATEAMPM. 

Sample data of the date format columns that I got warnings from:

01-JUN-2001 10.16.09.000000 AM

[Patrick]

Are you changing the source file for the sole person of import into a SAS table? If that's the case then below code to directly read your source file into a SAS table.

%let source=c:\temp\IMPORT_DATA.csv;

data work.out;
  infile "&source"
    lrecl=500
    dlm=','
    truncover
    dsd;

  input @;
  _infile_=compress(_infile_,"|");

  input
    var_1  : mmddyy8.
    var_2  : best32.
    var_3  : $3.
    var_4  : $300.
    var_5  : mmddyy8.
    var_6  : best32.
    var_7  : datetime30.
    var_8  : best32.
    var_9  : datetime30.
    var_10 : mmddyy8.
    ;

  format
    var_1    date9.
    var_5    date9.
    var_7    datetime30.6
    var_9    datetime30.6
    var_10   date9.
    ;
run;

[angel17th]

Hi @Patrick,

Yes I'm reading the CSV to a SAS table, however, that data of the CSV is a pipe enclosed with a comma delimiter. That is why I initially referenced the solution from Tom when reading the raw CSV. I added replaced the compress with the following code and it also worked: